转：ORACLE SQL TUNING

 转载

 

ORACLE SQL TUNING  

 

一．优化器模式

   ORACLE的优化器共有3种:

   a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)

   为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息，以保证数据库中的对象统计信息(object statistics)的准确性.

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

 

二．访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a.  全表扫描

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

   

b.  索引扫描

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

 

其中ORACLE对索引又有两种访问模式.

a)索引唯一扫描 ( INDEX UNIQUE SCAN)

大多数情况下, 优化器通过WHERE子句访问INDEX.

例如:

表LOADING有两个索引 : 建立在LOADING列上的唯一性索引LOADING_PK和建立在MANAGER列上的非唯一性索引IDX_MANAGER.

SELECT loading  

FROM LOADING

WHERE LOADING = ‘ROSE HILL’;

   在内部 , 上述SQL将被分成两步执行, 首先 , LOADING_PK 索引将通过索引唯一扫描的方式被访问 , 获得相对应的ROWID, 通过ROWID访问表的方式执行下一步检索.

   如果被检索返回的列包括在INDEX列中,ORACLE将不执行第二步的处理(通过ROWID访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.

   下面SQL只需要INDEX UNIQUE SCAN 操作.

       SELECT LOADING

       FROM  LOADING

WHERE LOADING = ‘ROSE HILL’;

 

  b)索引范围查询(INDEX RANGE SCAN)

     适用于两种情况:

1. 基于一个范围的检索

2. 基于非唯一性索引的检索

 例1:

      SELECT LOADING

      FROM  LOADING

WHERE LOADING LIKE ‘M%’;

 

WHERE子句条件包括一系列值, ORACLE将通过索引范围查询的方式查询LODGING_PK . 由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描

低一些.  

例2:

      SELECT LOADING

      FROM  LOADING

WHERE MANAGER = ‘BILL GATES’;

    这个SQL的执行分两步, IDX_MANAGER的索引范围查询(得到所有符合条件记录的ROWID) 和下一步同过ROWID访问表得到LOADING列的值. 由于IDX_MANAGER是一个非唯一性的索引,数据库不能对它执行索引唯一扫描.

 

  由于SQL返回LOADING列,而它并不存在于IDX_MANAGER索引中, 所以在索引范围查询后会执行一个通过ROWID访问表的操作.

  WHERE子句中, 如果索引列所对应的值的第一个字符由通配符(WILDCARD)开始, 索引将不被采用.

SELECT LOADING

      FROM  LOADING

WHERE MANAGER LIKE ‘％HANMAN’;

在这种情况下，ORACLE将使用全表扫描.

 

 

三．SQL调优的本质就是调整执行计划。

       在好多情况下，oracle自动选择的执行计划并不是最优的，这时需要我们人工去干预。(什么是执行计划?)

          

 

对SQL调优基本步骤：

a)        捕获SQL语句

b)        产生SQL语句的执行计划；

c)        验证统计信息(SQL语句涉及到的表格是否做过分析)，表格信息(结果集的记录数，索引)，字段上面数据分布特点

d)       通过手工收集到的信息，形成自己理想的执行计划。

e)        如果做过分析，则重新分析相关表格或者做柱状图分析。

f)         如果没有做过分析，则通过尝试不同的Hint，从而获得合适的执行计划。

g)        当我们正常无法调优到位时，可以打开10053事件打开优化器的跟踪，看看Oracle如何选择的.

alter session set events='10053 trace name context forever,level 2';

           

四．如何捕获SQL语句

       捕获SQL语句的方法有如下几种：

              1．SQL TRACE或10046跟踪某个模块。

              2．PERFSTAT性能统计包，使用方法见附录二。

              3．V$SQL，V$SESSION_WAIT，V$SQL_TEXT

五．如何查看执行计划

       查看SQL语句的执行计划有以下几种：

       1．Set autotrace on(set autotrace traceonly exp)

       2．Explain plan for …..

              @?/rdbms/admin/utlxpls.sql

       3．V$SQL_PLAN视图

              column operation format a16

column "Query Plan" format a60

column options format a15

column object_name  format a20

column id  format 99

 

select id,lpad(' ',2*(level-1))||operation||' '||options||' '||object_name||' '

       ||decode(id,0,'Cost = '||position) "Query Plan"

from (select *

from v$sql_plan 

where address='&a') sql_plan

start with id = 0

connect by prior id = parent_id

/

 

       4．第三方工具，如pl/sql developer,TOAD

      

六．SQL语句主要的连接方法

 

a)        Nested-loop join

适合于小表(几千条，几万条记录)与大表做联接

在联接列上有索引。

 

    分内表和外表(驱动表)，靠近from子句的是内表。从效率上讲，小表应该作外表，大表应该作内表，即大表查询时走索引。

 

COST= Access cost of A(驱动表) + (access cost of B * number of rows from A)

 

成本计算方法：

    设小表100行，大表100000行。

 

    两表均有索引：

    如果小表在内，大表在外(驱动表)的话，则扫描次数为：

       100000+100000*2 (其中2表示IO次数，一次索引，一次数据)

    如果大表在内，小表在外(驱动表)的话，则扫描次数为：

       100+100*2.

 

    两表均无索引：

    如果小表在内，大表在外的话，则扫描次数为：

       100000+100*100000

    如果大表在内，小表在外的话，则扫描次数为：

       100+100000*100

 

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。

 

    基本的执行计划如下所示：

        NESTED LOOPS

           TABLE ACCESS (BY ROWID)  OF  our_outer_table

                   INDEX (..SCAN) OF outer_table_index(….)

           TABLE ACCESS (BY ROWID)  OF  our_inner_table

             INDEX (..SCAN) OF inner_table_index(….)

 

b)      Hash join

 

适合于大表与大表，小表(几十万，几百万)与大表之间的联连。

联接列上不需要索引。

 

基本执行计划如下：

    HASH JOIN

              TABLE ACCESS (….)  OF  tableA

              TABLE ACCESS (….)  OF  tableB

 

cost= (access cost of A * number of hash partitions of B) + access cost of B

 

可以看出主要成本在于A表是否可以被Cache。Hash_area_size的大小将决定Hash Join的主要成本。可以看出Hash Join的成本和返回集合并没有直接的关系，所以当返回结果集比较大的时候一般具有较好的性能。

 

为了加快hash join的速度，可以调大hash_area_size和pga_aggregate_target（默认为25M）的值。

 

 

c)      Sort Merge join

 

每一个Row Source在Join列上均排序。

    然后两个排序后的Row Source合并后，作一个结果集返回。

    Sort/Merge Join仅仅对equal Join有效。

 

 

基本执行计划

    MERGE (JOIN)

        SORT (JOIN)

                 TABLE ACCESS (….)  OF  tableA

        SORT (JOIN)

                 TABLE ACCESS (….)  OF  tableB

 

cost= access cost of A + access cost of B +(sort cost of A + sort cost of B)

 

可以看出Sort的成本是Merge Join的主要构成部分。这样sort_area_size的大小将很大程度决定Merge Join的大小。同样如果A表或者B表已经经过排序的，那么Merge Join往往具有很好的性能。其不会走索引。

 

 

 

没有驱动表的概念，即时响应能力较差。

 

 

 

七．一般情况下最常见的5种问题

 

1. Statement not written for indexes	25%
2. Indexes are missing or inappropriate	16%
3. Use of single-column index merge	15%
4. Misuse of nested loop, sort merge, or hash join	12%
5. Misuse of IN, EXISTS, NOT IN, NOT EXISTS, or table joins	8%

               

       不过在我们这里，最常见的问题是在第2条，第3条，第4条。

 

1．Statement not written for indexes

类似于这样的：

SELECT account_name, trans_date, amount

FROM transaction

WHERE SUBSTR(account_name,1,7) = ' CAPITAL'; 

 

WHERE account_name LIKE 'CAPITAL%';

 

Account_date 日期

 

To_char(Account_date,’YYYY-MM-DD:HH24:MI:SS’)=’200508XXX’;

 

Account_date=to_date(‘200508….’,’yyyy-mm-dd);

 

 

2．Indexes are missing or inappropriate

      

       例如REP_C021中有这样一句：

select SUBSIDIARYID,260,'    300电话卡',

               sum(decode(feetype, 1, ceil(duration / 60))) +

         sum(decode(feetype, 0, ceil(duration / 60))),

         sum(decode(feetype, 1, ceil(duration / 60))),

         sum(decode(feetype, 0, ceil(duration / 60))),0

    from cardsusage200508 a, service b

   where a.caller = b.servicecode and

         (b.property = i_property or i_property is null) and

         a.cdrtype = 102

   group by SUBSIDIARYID, 260, '    300电话卡';

 

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=RULE

   1    0   SORT (GROUP BY)

   2    1     NESTED LOOPS

   3    2       TABLE ACCESS (FULL) OF 'CARDSUSAGE200508'