oracle性能优化总结

oracle是一个很成熟的数据库产品，当然性能方面也有不俗的表现。尤其是9i之后又做了很多好的改进。现在已经到12c了，不过本人只用过11g,最近有时间了，我把自己对性能的一些拙见总结一下。（有一些是来自网上，自己又给整理了一下）

ORACLE性能的体现主要在CPU利用率和I/O读写次数这两个方面。
自然优化也是围绕这两个方面展开。

1:oracle的内存结构
要说性能，先得说一下oracle的内存结构。
1.1：主要分SGA(system global area)和PGA(program global area)两大部分。
SGA主要包括SQL语句，数据，数据字典，redo日志等的缓存区。
PGA主要包括SQL语句排序区，游标状态区，会话信息区以及堆栈区。
这两部分的内存分配在一定程度上也会影响性能。虽然这两部分的大小，oracle会根据情况自动分配，不过也可以根据自己的需要进行设定，毕竟oracle没有业务人员懂业务，自然分配的不是那么智能。
ORACEL官方的建议是，PGA(20%)SGA(80%)

1.2数据块
每一次I/O读写的大小。一般是，2K,4K,8K,16K
块的设定会通过I/0读写次数影响性能。
比如说OLTP（事务联机）系统，它每次执行的语句比较单一，但是执行次数很多，所以它的块可以设的小一些。DSS（数据仓库）系统它每次执行的语句非常复杂，但是执行次数少，它的块就可以设的大一些。

2：优化措施 （开发人员的角度）
优化之前，先说一下怎么去看执行计划。
命令：
set autotrace on
set autotrace traceonly (不显示语句执行内容，只显示执行计划)
执行计划主要看已下指标：
响应时间，cost(越小越好),consistent gets(内存消耗)，physical reads(I/0消耗)
这里要说明一下，每回进行性能测试的时候，要清一下缓存。
命令：
alter system flush buffer_cache;
alter system flush shared_pool;

2.1索引建立的策略
Oracle的索引主要包含两类：BTree和位图索引。没有特别声明（create bitmap ind index on table(column)），oracle都建的是BTtree索引。BTtree索引又分为唯一索引，复合索引（聚簇索引），反向索引和函数索引（不推荐）等。
对于OLTP系统，一般都用BTtree索引（注意不是二叉树）。
对于DSS系统，最好用位图索引。
2.1.1BTtree索引
1）唯一索引：一般是主键字段，约束条件字段（where条件），基数大的字段（可分性高）
order by 字段，也尽量建立索引。这样就不用进行order by 了，减少了排序的消耗。
2）复合索引：条件定义模式固定，例如where条件中经常一起出现的字段。如果组合比较灵活，则分别建单一索引。
复合索引要注意前缀性风险。例如复合索引ind_1(a,b,c),如果没有a字段出现，则索引失效。
复合索引优先于单字段索引。复合索引中也要注意顺序，要按可选性高低或者条件定义的频度进行排序。例如，纳税人识别号，税务机关代码，月份。
3）反向索引：反向索引主要是建立在那些以序列号生成的列上，可以将本来是连在一起的index entry分散到不同的leaf block中去。当索引是从序列中取的时候，如果是一般的b-tree 索引，在大量的插入后会导致块的分裂以及树的倾斜，使用reverse key index可以使索引段条目被更均匀的分布。很多事务访问同一个块，对同一个块并发操作产生的I/0竞争。反向索引可以避免I/O竞争。缺点是当应用需要获取一段范围的数据时，reverse key index将不会被使用，因为键值不是连续的排列的。在这种情况下，CBO将会选择全表扫描。
4）函数索引：某表的一列在平常SQL中该列都是放在函数里面，为了能用到索引来提高检索速度。例如：create index idx_fun on emp (upper(name));

2.1.2位图索引

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

下面的程序清单给出了一个创建位图索引的例子：
create bitmap index dept_idx2_bm on dept (deptno);
Index created.

对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。

当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。位图索引更新时用的不是行级锁，而是位图锁，所以不适合平凡更新的OLTP系统，更合适查询量大的DSS系统。

2.3表连接的策略
在进行多表连接的时候，oracle有三种算法。
merge join，hash join，Nested Loops

2.3.1 merge join
操作通常分三步：
1）对连接的每个表做table access full;
2）对table access full的结果进行排序。
3）进行merge join对排序结果进行合并。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，merge join会比nested loops性能更佳。当表特别小或特别巨大的时候，实行全表访问可能会比索引范围扫描更有效。mrege join的性能开销几乎都在前两步。

2.3.2 hash join
对两个表进行全表扫描，然后oracle读取涉及连接的其中一个表，并且在内存里创建来自表的连接列的唯一关键字的位图。当读取和处理第二个表的行时，创建值的位图被用做过滤器。如果一个行成功的通过位图过滤，则hash算法用于数据查找和后来的连接。（适用于DSS系统。）

以下条件下hash join可能有优势：
两个巨大的表之间的连接。
在一个巨大的表和一个小表之间的连接。

2.3.3Nested Loops
会循环外表（驱动表），逐个比对和内表的连接是否符合条件。在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候，Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。适用于OLTP系统。

2.3.4总结
对于子查询in/exsits来说，能用多表查询，尽量用多表查询。
如果不可避免使用，要遵循下面的规则。
1）限制性强的条件在子查询中，建议用in
2）限制性强的条件在主查询中，建议用exsits
原理：in是先执行子查询，exsits是先执行主查询。
对于多表查询，尽量将限制性最强的表作为驱动表，在被驱动表上的外键字段建立索引。
（oracle根据基数来自动识别驱动表和被驱动表）

2.4 Oracle优化排序的操作
尽可能避免排序;尽可能在内存中排序;分配合适的临时空间以减少空间分配调用。

需要进行排序的操作：

A、创建索引;

B、涉及到索引维护的并行插入

C、order by或者group by(尽可能对索引字段排序)

D、Distinct

E、union/intersect/minus

F、sort-merge join

G、analyze命令(仅可能使用estamate而不是compute)

2.5 Oracle 并行处理

2.5.1查询
Sql代码
SELECT /*+ Parallel(t,8) */ * FROM emp t;       
2.5.2创建索引
Sql代码
create index idx_emp_test on emp(empno,ename,job) nologging parallel 32;
2.5.3.插入
Sql代码
INSERT /*+ append parallel(30) */ 
INTO t_a
SELECT /*+ parallel(30) */
FROM t_b
2.5.4 总结
自动化并行度，数据仓库应用程序通常会利用并行处理信息的优势，迅速有效地处理数据，特别是在大表上查询数据时，或者是一个非常复杂的连接查询时经常使用并行查询，在执行并行操作时，优化器应该使用并行度（Degree of Parallelism ，DOP），可以在查询本身内指定（通过+PARALLEL优化器提示），也可以作为表或索引本身的一个属性（通过它的PARALLEL属性），但要精确地确定一个合适的DOP是有难度的，通常需要对表是如何连接，哪些索引对并行处理有益，同时执行哪些查询都要有一个详细的了解。
Oracle 11g R2为我们带来了好消息，现在它可以自动为任何并行语句确定DOP，优化器使用两个新的初始化参数：PARALLEL_DEGREE_POLICY和PARALLEL_MIN_TIME_THRESHOLD来计算自动化并行度（ADOP）。例如，如果PARALLEL_DEGREE_POLICY被设为AUTO，如果查询确实能从并行操作受益，Oracle 11g R2优化器会首先确定一个合适的DOP值，如果查询的预计执行时间超出了PARALLEL_MIN_TIME_THRESHOLD可接受的值（单位：秒），并且有足够资源支持并行处理，它会允许查询执行，否则就会延迟执行，直到有足够的资源释放出来，这样可以防止一个并行查询过度地消耗资源，例如，所有并行执行线程，或集群环境中的所有CPU都被其它非并行操作占用了，值得注意的是ADOP特性无法扩展到并行恢复或并行复制，它们只适用于并行查询。

4）Oracle 绑定变量 详解
变量绑定是OLTP系统中一个非常值得关注的技术。良好的变量绑定会使OLTP系统数据库中的SQL 执行速度飞快，内存效率极高；不使用绑定变量可能会使OLTP 数据库不堪重负，资源被SQL解析严重耗尽，系统运行缓慢。

         当一个用户与数据库建立连接后，会向数据库发出操作请求，即向数据库送过去SQL语句。 Oracle 在接收到这些SQL后，会先对这个SQL做一个hash 函数运算，得到一个Hash值，然后到共享池中寻找是否有和这个hash 值匹配的SQL存在。 如果找到了，Oracle将直接使用已经存在的SQL 的执行计划去执行当前的SQL，然后将结果返回给用户。 如果在共享池中没有找到相同Hash 值的SQL，oracle 会认为这是一条新的SQL。 会进行解析。

在OLTP系统中，我们可以使用绑定变量是因为在OLTP中，SQL语句大多是比较简单或者操作的结果集都很小。当一个表上创建了索引，那么这种极小结果集的操作使用索引最合适，并且几乎所有的SQL的执行计划的索引都会被选择，因为这种情况下，索引可能只需要扫描几个数据块就可以定位到数据，而全表扫描将会相当耗资源。 因此，这种情况下，即使每个用户的谓词条件不一样，执行计划也是一样的，就是都用索引来访问数据，基本不会出现全表扫描的情况。 在这种执行计划几乎唯一的情况下，使用绑定变量来代替谓词常量，是合适的。

在OLAP系统中，SQL的操作就复杂很多，OLAP数据库上大多数时候运行的一些报表SQL，这些SQL经常会用到聚合查询（如：group by），而且结果集也是非常庞大，在这种情况下，索引并不是必然的选择，甚至有时候全表扫描的性能会更优于索引，即使相同的SQL，如果谓词不同，执行计划都可能不同。


7）global temporary table
http://hwhuang.iteye.com/blog/551864

8）物化视图
1、物化视图的类型：ON DEMAND、ON COMMIT
    二者的区别在于刷新方法的不同，ON DEMAND顾名思义，仅在该物化视图“需要”被刷新了，才进行刷新(REFRESH)，即更新物化视图，以保证和基表数据的一致性；而ON COMMIT是说，一旦基表有了COMMIT，即事务提交，则立刻刷新，立刻更新物化视图，使得数据和基表一致。

2、ON DEMAND物化视图
    物化视图的创建本身是很复杂和需要优化参数设置的，特别是针对大型生产数据库系统而言。但Oracle允许以这种最简单的，类似于普通视图的方式来做，所以不可避免的会涉及到默认值问题。也就是说Oracle给物化视图的重要定义参数的默认值处理是我们需要特别注意的。
    物化视图的特点：
    (1) 物化视图在某种意义上说就是一个物理表(而且不仅仅是一个物理表)，这通过其可以被user_tables查询出来，而得到佐证；
    (2) 物化视图也是一种段(segment)，所以其有自己的物理存储属性；
    (3) 物化视图会占用数据库磁盘空间，这点从user_segment的查询结果，可以得到佐证；
    创建语句：create materialized view mv_name as select * from table_name
    默认情况下，如果没指定刷新方法和刷新模式，则Oracle默认为FORCE和DEMAND。

    物化视图的数据怎么随着基表而更新？
    Oracle提供了两种方式，手工刷新和自动刷新，默认为手工刷新。也就是说，通过我们手工的执行某个Oracle提供的系统级存储过程或包，来保证物化视图与基表数据一致性。这是最基本的刷新办法了。自动刷新，其实也就是Oracle会建立一个job，通过这个job来调用相同的存储过程或包，加以实现。
  
    ON DEMAND物化视图的特性及其和ON COMMIT物化视图的区别，即前者不刷新(手工或自动)就不更新物化视图，而后者不刷新也会更新物化视图，——只要基表发生了COMMIT。
    创建定时刷新的物化视图：create materialized view mv_name refresh force on demand start with sysdate
next sysdate+1 （指定物化视图每天刷新一次）
    上述创建的物化视图每天刷新，但是没有指定刷新时间，如果要指定刷新时间（比如每天晚上10：00定时刷新一次）：create materialized view mv_name refresh force on demand start with sysdate next to_date( concat( to_char( sysdate+1,’dd-mm-yyyy’),’ 22:00:00′),’dd-mm-yyyy hh24:mi:ss’)

3、ON COMMIT物化视图
    ON COMMIT物化视图的创建，和上面创建ON DEMAND的物化视图区别不大。因为ON DEMAND是默认的，所以ON COMMIT物化视图，需要再增加个参数即可。

    需要注意的是，无法在定义时仅指定ON COMMIT，还得附带个参数才行。
    创建ON COMMIT物化视图：create materialized view mv_name refresh force on commit as select * from table_name
    备注：实际创建过程中，基表需要有主键约束，否则会报错（ORA-12014）
4、物化视图的刷新
    刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。（如上所述）
    刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。
     对于已经创建好的物化视图，可以修改其刷新方式，比如把物化视图mv_name的刷新方式修改为每天晚上10点刷新一次：alter materialized view mv_name refresh force on demand start with sysdate next to_date(concat(to_char(sysdate+1,’dd-mm-yyyy’),’ 22:00:00′),’dd-mm-yyyy hh24:mi:ss’)
 
5、物化视图具有表一样的特征，所以可以像对表一样，我们可以为它创建索引，创建方法和对表一样。

6、物化视图的删除：
     虽然物化视图是和表一起管理的，但是在经常使用的PLSQL工具中，并不能用删除表的方式来删除（在表上右键选择‘drop’并不能删除物化视图），可以使用语句来实现：drop materialized view mv_name

普通视图和物化视图根本就不是一个东西，说区别都是硬拼到一起的，首先明白基本概念，普通视图是不存储任何数据的，他只有定义，在查询中是转换为对应的定义SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。
普通视图的三个特征：
1、是简化设计，清晰编码的东西，他并不是提高性能的，他的存在只会降低性能（如一个视图7个表关联，另一个视图8个表，程序员不知道，觉得很方便，把两个视图关联再做一个视图，那就惨了），他的存在未了在设计上的方便性
2、其次，是安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改，单表也可以同WITH READ ONLY来控制，当然有些项目基于视图做面向对象的开发，即在视图上去做INSTAND OF触发器，就我个人而言是不站同的，虽然开发上方便，但是未必是好事。
3、从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少（如：人员表和身份表关联，从人员表可以查看人员的维度统计，从身份看，可以看不同种类的身份有那些人或者多少人），其次另一个如系统视图USER_TABLE、TAB、USER_OBJECTS这些视图，不同的用户下看到的肯定是不一样的，看的是自己的东西。

物化视图呢，用于OLAP系统中，当然部分OLTP系统的小部分功能未了提高性能会借鉴一点点，因为表关联的开销很大，所以在开发中很多人就像把这个代价交给定期转存来完成，ORACLE当然也提供了这个功能，就是将视图（或者一个大SQL）的信息转换为物理数据存储，然后提供不同的策略：定时刷还是及时刷、增量刷还是全局刷等等可以根据实际情况进行选择，总之你差的是表，不是视图。

数据库的设计
要遵循三范式
1）属性的唯一性
2）每一条记录要有主键，并且其他字段依赖于主键。
3）每一个属性仅依赖于主键。
有些情况下，为了性能要求，可以违背三范式。这样有利有弊。虽然一定程度上提高了性能，但是破环了数据的一致性，需要程序去保证数据的一致性。

from:http://www.w3c.com.cn/oracle%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96%E6%80%BB%E7%BB%93