oracle性能优化

 

Oracle

1.    oracle的优化器

1）oracle的优化方式有两种：基于规则的优化方式(Rule-Based Optimization,简称为RBO) 和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。

RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。

CBO方式：是看语句的代价(Cost)了,这里的代价主要指Cpu、内存和network代价。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有多少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。在Oracle10g中,取消了RBO的支持。

                 如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划：

 ANALYZE TABLE table_name COMPUTE STATISTICS;
ANALYZE INDEX index_name ESTIMATE STATISTICS;

如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

2）优化器的优化模式(Optermizer Mode)有如下几种：Rule,Choose,First rows,All rows，first_rows_n。

Rule:即基于规则的方式

Choose:默认的情况下Oracle用的便是这种方式。当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式.

           在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器。

First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间

All Rows:all_rows是oracle优化器默认的模式,它将选择一种在最短时间内返回所有数据的执行计划,它将基于整体成本的考虑.

first_rows_n:first_rows_n是根据成本而不是基于硬编码的规则来选择执行计划.n可以是1,10,100,1000或者直接用first_rows(n) hint指定任意正数.这里的n是我们想获取结果集的前n条记录,这种需求在很多分页语句的需求中会碰到. 该参数为ORACLE 9I新引入的。

查看命令：show parameter OPTIMIZER_MODE

3）如何设定选用哪种优化模式

Instance级别：我们可以通过在init<SID>.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。

使用spfile的情况，使用下面的语句：

alter system set optimizer_mode=choose scope=spfile;

Sessions级别：通过SQL> ALTER SESSION SET OPTIMIZER_MODE=<Mode>;来设定。

语句级别：这些需要用到Hint

注：使用hints的语法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */ 或

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解：1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。

2) “+”号表示注释是一个hints，加号必须立即跟在”/*”后面，中间不能有空格。

3) hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。

4) text 是其它说明hint的注释性文本

如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。

2.    访问table的方式

1)全表扫描

      全表扫描就是顺序地访问表中每条记录。ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。

    2) 通过ROWID访问表

  你可以采用基于ROWID的访问方式情况，提高访问表的效率， ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法，因此那些基于索引列的查询就可以得到性能上的提高。

3.    共享SQL语句

为了不重复解析相同的SQL语句，在第一次解析之后，ORACLE将SQL语句存放在内存中。这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享。 因此，当你向ORACLE提交一个SQL语句时，ORACLE会首先在这块内存中查找相同的语句，如果它和之前的执行过的语句完全相同(这里需要注明的是，ORACLE对两者采取的是一种严格匹配，要达成共享，SQL语句必须完全相同,包括空格，换行等。)， ORACLE就能很快获得已经被解析的语句以及最好的执行路径。但是ORACLE只对简单的表提供高速缓冲(cache buffering)，这个功能并不适用于多表连接查询。

数据库管理员必须在init。ora中为这个区域设置合适的参数，当这个内存区域越大，就可以保留更多的语句，当然被共享的可能性也就越大了。 

共享的语句必须满足三个条件：

    1)字符级的比较： 当前被执行的语句和共享池中的语句必须完全相同，包括大小写，空格，换行等。

2)两个语句所指的对象必须完全相同：同义词与表是不同的对象

3)两个SQL语句中必须使用相同的名字的绑定变量(bind variables)。

4.    选择基础表  

所谓基础表是被最先被访问的表,通常是以全表扫描的方式被访问,由于优化器不同,SQL语句的基础表的选择也不同。

1)      COST优化器会检查SQL中每个表的物理大小,索引状态,然后选择花费最底的执行路径。他会自己选择基础表。

2)      RULE优化器,在所有连接条件都有索引对应的前提下,选择FROM子句中最后那个表作为基础表。ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理。 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。当ORACLE处理多个表时, 会运用排序及合并的方式连接它们。首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表。

5.    where子句中的连接顺序

ORACLE采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

6.    SELECT子句中避免使用*

当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 '*'是一个方便的方法。不幸的是，这是一个非常低效的方法。实际上，ORACLE在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

7.    减少访问数据库的次数

当执行每条SQL语句时，ORACLE在内部执行了许多工作：解析SQL语句，估算索引的利用率，绑定变量，读数据块等等。由此可见，减少访问数据库的次数，就能实际上减少ORACLE的工作量。

注意: 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200。

8.    使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表。

注意: DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。

9.    整合简单，无关联的数据库访问

如果你有几个简单的数据库查询语句，你可以把它们整合到一个查询中(即使它们之间没有关系)，虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者还是要权衡之间的利弊。

10.删除重复记录

最高效的删除重复记录方法是使用ROWID.例如：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

在ORACLE8以前的版本中，ROWID由FILE 、BLOCK、ROW NUMBER构成。随着oracle8中对象概念的扩展，ROWID发生了变化，ROWID由OBJECT、FILE、BLOCK、ROW NUMBER构成。利用DBMS_ROWID可以将rowid分解成上述的各部分，也可以将上述的各部分组成一个有效的rowid。

11.用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息。当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。

注意：TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML。

12.尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少。

COMMIT所释放的资源:

1) 回滚段上用于恢复数据的信息。

2) 被程序语句获得的锁 。

3) redo log buffer 中的空间 。

4) ORACLE为管理上述3种资源中的内部花费。

13.用Where子句替换HAVING子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤。 这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

14.减少对表的查询

在含有子查询的SQL语句中，要特别注意减少对表的查询。

15.通过内部函数提高SQL效率

函数是执行在数据库服务器端的,所以他的执行效率当然要比在客户端执行的速度要快的多。所以一般较复杂业务的SQL操作都用存储过程或者函数来完成。

16.使用表的别名

当在SQL语句中连接多个表时， 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

   注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属

17.关于EXIST

1)     用EXISTS替代IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。因为EXISTS只返回true or false不用返回结果集,而IN和NOT IN需要返回结果集并对结果集进行排序和合并。

2)      用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。

3)      用表连接替换EXISTS

通常来说，采用表连接的方式比EXISTS更有效率

4)      用EXISTS替换DISTINCT

当提交一个包含一对多表信息的查询时，避免在SELECT子句中使用DISTINCT。一般可以考虑用EXIST替换。即使在EXISTS的WHERE子句中使用子查询也比DISTINCT的效率要高。EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

18.关于索引

1)     索引简介

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构。通常,通过索引查询数据比全表扫描要快。当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引。同样在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证。除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列。通常,在大型表中使用索引特别有效。当然,你也会发现,在扫描小表时,使用索引同样能提高效率。虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价，索引提高的执行效率是用空间效率换来的。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT、 DELETE、UPDATE将为此多付出4 、5次的磁盘I/O 。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

定期的重构索引是有必要的: ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

2)     ORACLE对索引的访问有两种:

A.     索引唯一扫描(INDEX UNIQUE SCAN)

在一个表中有两个索引,在列C1上建立一个唯一性索引INDEX_KEY和C2列的一个非唯一性索引INDEX_01,在执行如下语句的时候 select * from table where C1=1; 这个时候唯一性索引INDEX_KEY将被访问,获得响应的ROWID然后通过ROWID访问的方式进行下一步检索。如果被索引返回的列包含于INDEX列中,那么ORACLE是不会通过ROWID访问表的。

B.      索引的范围查询(INDEX RANGE SCAN)

基于一个范围的检索和基于一个非唯一性索引的检索。这两种情况适用索引的范围查询。 select C1 from table where C1 like '?'; where子句包含一系列值,ORACLE将通过索引范围查询的方式查询唯一性索引INDEX_KEY,由于索引范围查询将返回一组值,他的效果就要比索引唯一扫描低。 select C1 from table where C2=3; 这个SQL首先按照非唯一性索引进行索引范围查询(得到所有符合条件记录的ROWID),然后通过ROWID访问表,得到C1的值。由于C2是一个非唯一索引,所以数据库不进行索引唯一扫描。由于返回列C1不在INDEX_01中,所以在索引范围查询后会进行一个ROWID访问表的操作。

注意：WHERE子句中如果索引列对应的值有匹配符开始,那么索引将不被使用。

3)     多个平等的索引

在SQL语句包含多个表时,且使用分布在表上的多个索引时,ORACLE会同时使用多个索引并运行时对他们的记录进行合并,检索出仅对全部索引有效的记录。 唯一性索引的等级是最高的,但是,只有当索引在WHERE子句中与常量比较的时候才有效,如果与其他表的索引相比较,那么这个子句在优化器中等级是最低的。如果不同表的两个同等级的索引被引用,那么将有FROM子句中表的顺序决定,既FROM最后面的表的索引优先级最高。

4)     等式比较和范围比较

当WHERE子句中存在索引列的比较时,ORACLE是不会优先对进行比较的索引进行访问的。所以尽量避免对索引列进行范围比较。进行范围比较的索引访问级别很低。即使是唯一性索引也是一样。

5)     强制索引失效

如果存在两个以上相同级别的索引,可以使用强制命令ORACLE只使用其中一个(检索出的记录数量少)

6)     避免对索引进行计算

在WHERE子句中对索引进行计算,ORACLE就不会对索引进行访问,而直接对表进行全表扫描。这个规则非常实用。

7)     索引的自动选择规则

如果一个表中有很多索引,但是只有一个是唯一性索引,那么ORACLE只会对唯一性索引进行访问而完全忽略其他非唯一性索引。

8)     避免对索引使用NOT 这会让ORACLE忽略索引而使用全表扫描。

9)     避免在索引列上使用IS NULL OR IS NOT NULL 这会造成索引不被使用。

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引．对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。　如果至少有一个列不为空，则记录存在于索引中。举例: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一条记录的A,B值为(123,null) , ORACLE将不接受下一条具有相同A,B值（123,null）的记录(插入)。 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。 因此你可以插入1000 条具有相同键值的记录,当然它们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

10)  使用索引的第一个列

如果索引建立在多个列上,那么只有在第一个列被where引用的时候,ORACLE才回选择使用该索引。这是非常重要的规则,如果仅使用第二个列,那么ORACLE会忽略该索引而进行全表扫描。 同时,如果表记录不是很多的情况下,ORACLE优化器会忽略索引而直接进行表扫描

11)  ORDER BY语句中使用索引的要求

ORDER BY要求ORFER BY子句中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。且必须定义为非空列。

12)  避免改变索引列的类型

当比较不同数据类型的数据的时候,ORACLE会进行简单的类型转换,但是这样的类型转换是需要代价的,需要消耗掉一部分ORACLE的处理时间,但是如果在索引列上发生这样的使用,那么索引将不会被访问，会引起全表访问。

13)  相同的索引列不能互相比较,这将会启用全表扫描。

19.用UNION-ALL替换UNION

当使用UNION的时候,这两个结果集会以UNION-ALL的方式合并,然后对输出结果进行排序(这个操作会使用到SORT_AREA_SIZE这块内存。 对于这块内存的优化也是相当重要的。),而用UNION-ALL的话, 这样排序就不是必要的了，效率就会因此得到提高。需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用UNION ALL的可行性。通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。如果你坚持要用OR,那就需要返回记录最少的索引列写在最前面。

20.关于WHERE

1) ‘!='将不使用索引。索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。

2) ‘||'是字符连接函数。 就象其他函数那样, 停用了索引。

3) ‘+' 是数学函数。 就象其他数学函数那样, 停用了索引。

4)  用>=代替>

两者的区别在于,>=比>少检索一些记录,从而节约执行扫描表所用时间。

例如：高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3

两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

21.带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎，执行耗费资源的排序(SORT)功能。

DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。通常,带有UNION, MINUS, INTERSECT的SQL语句都可以用其他方式重写。 如果你的数据库的SORT_AREA_SIZE调配得好, 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强

22.优化GROUP BY:
提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。