CBO对于Oracle SQL执行计划的影响(之二)

原创 eygle 2004-10-30

658

初试化参数对于执行计划的影响
有几个初试化参数对于多表连接的执行计划有重要的关系。

在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT

optimizer_search_limit参数指定了在决定连接多个数据表的最好方式时，CBO需要衡量的数据表连接组合的最大数目。
该参数的缺省值是5。
如果连接表的数目小于optimizer_search_limit参数，那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。

我们刚才有7张表，那么有7!（5040）种组合。

optimizer_max_permutations参数定义了CBO所考虑的表连接的最大数目的上限。
当我们给这个参数设置很小的一个值的时候，Oracle的计算比较很快就可以被遏制。然后执行计划，给出结果。

optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容，如果定义了ordered提示，那么
optimizer_max_permutations参数将会失效。
实际上，当你定义了ordered提示时，oracle已经无需计算了。

optimizer_search_limit参数和optimizer_max_permutations参数要结合使用，优化器将在optimizer_search_limit参数或
optimizer_max_permutations参数值超出之前，生成可能的表连接转换。当优化器停止对表连接的评估时，它将选择成本最低的组合。

例如，需要连接9个表的查询已经超出了optimizer_search_limit参数的限制，但是仍然可能要花费大量的时间去试图评估所有362880个
可能的连接顺序(9!),直到超过了optimizer_max_permutations参数的默认值(80000个表连接顺序)。

optimizer_max_permutations参数为CBO需要评估的排列数量的最大值。
optimizer_max_permutations的默认值是80000。
在确定查询排列评估数量的上限时，CBO采用的原则是：
如果查询中存在的非单一记录表的数目小于optimizer_search_limit+1,那么排列的最大值等于下面两个表达式中较大的数值：
optimizer_max_permutations
______________________________
(可能启动表的数目+1)

和

optimizer_search_limit!
___________________________
(可能启动表的数目+1)

例如5个表连接
排列的最大值= 80000/6=13333
____________________________
搜索限制=5!/6=120/6=20

较大值是13333，这就是优化器要考虑的排列的最大数值(当然实际的数值要比这个小的多,Oracle会排除掉大部分不可能组合)。

 
SQL> alter session set optimizer_search_limit = 3;
会话已更改。
已用时间:  00: 00: 00.60
SQL> alter session set optimizer_max_permutations = 100;
会话已更改。
已用时间:  00: 00: 00.90
SQL> set autotrace traceonly
SQL>   SELECT "SP_TRANS"."TRANS_NO",   
  2           "SP_TRANS"."TRANS_TYPE",   
  3           "SP_TRANS"."STORE_NO",   
  4           "SP_TRANS"."BILL_NO",   
  5           "SP_TRANS"."TRANSDATE",   
  6           "SP_TRANS"."MANAGER_ID",   
  7           "SP_TRANS"."REMARK",   
  8           "SP_TRANS"."STATE",   
  9           "SP_TRANS_SUB"."TRANS_NO",   
 10           "SP_TRANS_SUB"."ITEM_CODE",   
 11           "SP_TRANS_SUB"."COUNTRY",   
 12           "SP_TRANS_SUB"."QTY",   
 13           "SP_TRANS_SUB"."PRICE",   
 14           "SP_TRANS_SUB"."TOTAL",   
 15           "SP_CHK"."CHK_NO",   
 16           "SP_CHK"."RECEIVE_NO",   
 17           "SP_CHK"."CHECKER",   
 18           "SP_CHK_SUB"."CHK_NO",   
 19           "SP_CHK_SUB"."ITEM_CODE",   
 20           "SP_CHK_SUB"."COUNTRY",   
 21           "SP_CHK_SUB"."PLAN_NO",   
 22           "SP_CHK_SUB"."PLAN_LINE",   
 23           "SP_CHK_SUB"."QTY_CHECKOUT",
 24           "SP_CHK_SUB"."NOW_QTY",   
 25           "SP_RECEIVE"."RECEIVE_NO",   
 26           "SP_RECEIVE"."VENDOR_NAME",   
 27           "SP_RECEIVE"."BUYER",   
 28           "SP_RECEIVE_SUB"."RECEIVE_NO",   
 29           "SP_RECEIVE_SUB"."PLAN_NO",   
 30           "SP_RECEIVE_SUB"."PLAN_LINE",   
 31           "SP_RECEIVE_SUB"."ITEM_NAME",   
 32           "SP_RECEIVE_SUB"."COUNTRY",
 33           "SP_ITEM"."ITEM_CODE",
 34           "SP_ITEM"."CHART_ID",
 35           "SP_ITEM"."SPECIFICATION"  
 36      FROM "SP_TRANS" ,
 37            "SP_CHK" ,
 38            "SP_RECEIVE" ,
 39            "SP_TRANS_SUB" ,
 40            "SP_CHK_SUB" ,
 41            "SP_RECEIVE_SUB" ,
 42            "SP_ITEM" 
 43     WHERE  
 44     ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and
 45     ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and
 46     ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and  
 47     ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and
 48     ( "SP_CHK"."STATE" = 15 ) and
 49     ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and  
 50     ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and
 51     ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and  
 52     ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and  
 53     ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and  
 54     ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and 
 55     ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and
 56     ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and
 57     (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01') 
 58  /
已选择130行。
已用时间:  00: 00: 05.78

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2177 Card=1 Bytes=288)
   1    0   NESTED LOOPS (Cost=2177 Card=1 Bytes=288)
   2    1     NESTED LOOPS (Cost=2176 Card=1 Bytes=256)
   3    2       NESTED LOOPS (Cost=2174 Card=1 Bytes=219)
   4    3         MERGE JOIN (Cost=2173 Card=1 Bytes=178)
   5    4           SORT (JOIN) (Cost=1115 Card=8081 Bytes=1018206)
   6    5             HASH JOIN (Cost=645 Card=8081 Bytes=1018206)
   7    6               HASH JOIN (Cost=96 Card=1717 Bytes=133926)
   8    7                 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699)
   9    7                 TABLE ACCESS (FULL) OF 'SP_CHK' (Cost=13 Card=3870 Bytes=119970)
  10    6               TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776)
  11    4           SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960)
  12   11             TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960)
  13    3         TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456)
  14   13           INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE)
  15    2       TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727)
  16   15         INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371)
  17    1     TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416)
  18   17       INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

Statistics
----------------------------------------------------------
          8  recursive calls
        131  db block gets
       3436  consistent gets
       1397  physical reads
          0  redo size
      38555  bytes sent via SQL*Net to client
       1085  bytes received via SQL*Net from client
         10  SQL*Net roundtrips to/from client
          8  sorts (memory)
          1  sorts (disk)
        130  rows processed
SQL>
复制

3. 其他
在有的系统视图查询中，很多时候会出现问题，比如以下的SQL：

select a.username, a.sid, a.serial#, b.id1 
from v$session a, v$lock b 
where a.lockwait = b.kaddr
/
       复制

这个语句用来查找锁，在Oracle7的年代，这样的SQL语句执行的很快，但是在Oracle8以后的数据库，如果碰巧你用的是CBO，那么
这样的语句执行结果可能是Hang了（其实不是死了，只是很多人没有耐心等而已），在Oracle7里，这样的语句毫无疑问使用RBO，
很快你就可以得到执行结果。可以对于CBO，你所看到的两个视图，对于数据库来说，实际上是6个表，单只6个表的可能顺序组合就有
6!（720）种，数据库时间都消耗在计算这些执行路径上了，所以你得到的就是hang的结果。
最简单的解决办法就是使用rule提示，或者使用ordered提示
我们可以看一下这两种方式的执行计划，如果你有兴趣的话，还可以研究一下X$视图：

SQL> select /*+ rule */ a.username, a.sid, a.serial#, b.id1 
  2  from v$session a, v$lock b 
  3  where a.lockwait = b.kaddr
  4  /
未选定行
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       MERGE JOIN
   4    3         SORT (JOIN)
   5    4           MERGE JOIN
   6    5             FIXED TABLE (FULL) OF 'X$KSQRS'
   7    5             SORT (JOIN)
   8    7               VIEW OF 'GV$_LOCK'
   9    8                 UNION-ALL
  10    9                   VIEW OF 'GV$_LOCK1'
  11   10                     UNION-ALL
  12   11                       FIXED TABLE (FULL) OF 'X$KDNSSF'
  13   11                       FIXED TABLE (FULL) OF 'X$KSQEQ'
  14    9                   FIXED TABLE (FULL) OF 'X$KTADM'
  15    9                   FIXED TABLE (FULL) OF 'X$KTCXB'
  16    3         SORT (JOIN)
  17   16           FIXED TABLE (FULL) OF 'X$KSUSE'
  18    1     SORT (JOIN)
  19   18       FIXED TABLE (FULL) OF 'X$KSUSE'
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        196  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          0  rows processed
      复制

对于Ordered提示：

 
SQL> select /*+ ordered */ a.username, a.sid, a.serial#, b.id1 
  2  from v$session a, v$lock b 
  3  where a.lockwait = b.kaddr
  4  /
未选定行
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=112 Card=1 Bytes=145 )
   1    0   NESTED LOOPS (Cost=112 Card=1 Bytes=145)
   2    1     NESTED LOOPS (Cost=96 Card=1 Bytes=128)
   3    2       NESTED LOOPS (Cost=80 Card=1 Bytes=111)
   4    3         FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=86)
   5    3         VIEW OF 'GV$_LOCK'
   6    5           UNION-ALL
   7    6             VIEW OF 'GV$_LOCK1' (Cost=32 Card=2 Bytes=162)
   8    7               UNION-ALL
   9    8                 FIXED TABLE (FULL) OF 'X$KDNSSF' (Cost=16 Card=1 Bytes=94)
  10    8                 FIXED TABLE (FULL) OF 'X$KSQEQ' (Cost=16 Card=1 Bytes=94)
  11    6             FIXED TABLE (FULL) OF 'X$KTADM' (Cost=16 Card=1 Bytes=94)
  12    6             FIXED TABLE (FULL) OF 'X$KTCXB' (Cost=16 Card=1 Bytes=94)
  13    2       FIXED TABLE (FULL) OF 'X$KSUSE' (Cost=16 Card=1 Bytes=17)
  14    1     FIXED TABLE (FIXED INDEX #1) OF 'X$KSQRS' (Cost=16 Card=100 Bytes=1700)

Statistics
----------------------------------------------------------
          0  recursive calls
         67  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        202  bytes sent via SQL*Net to client
        244  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL>
      复制

类似的

SELECT   /*+ RULE */
         s.SID, s.serial#, l.TYPE, l.id1, l.id2, l.lmode, l.request, l.addr,
         l.kaddr, l.ctime, l.BLOCK, s.username, s.osuser, s.machine,
         DECODE (l.id2,
                 0, TO_CHAR (o.owner#) || '-' || o.NAME,
                 'Trans-' || TO_CHAR (l.id1) || '-' || l.id2
                ) object_name,
         DECODE (l.lmode,
                 0, '--Waiting--',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl',
                 4, 'Share',
                 5, 'Sha Row Exc',
                 6, 'Exclusive',
                 'Other'
                ) lock_mode,
         DECODE (l.request,
                 0, ' ',
                 1, 'Null',
                 2, 'Row Share',
                 3, 'Row Excl',
                 4, 'Share',
                 5, 'Sha Row Exc',
                 6, 'Exclusive',
                 'Other'
                ) req_mode
    FROM v$lock l, v$session s, SYS.obj$ o
   WHERE l.request = 0
     AND l.SID = s.SID
     AND l.id1 = o.obj#(+)
     AND s.username IS NOT NULL
ORDER BY s.username, l.SID, l.BLOCK;    
  复制

以上问题对于CBO优化器普遍存在，对于Oracle9i2同样如此。

幸运的是在Oracle9i中，optimizer_max_permutations初始值降低到2000，从80000到2000，这是一个重大的进步

其实或者这不能算是问题，对于Oracle这只是一种知识，一种方法而已。

<<上一页

oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

CBO对于Oracle SQL执行计划的影响(之二)

评论

相关阅读