CBO对于Oracle SQL执行计划的影响

        另一篇关于oracle使用基于成本进行执行计划的文章，可以看出oracle在这方面还是有很多指的改进的地方，必须开发者指定较优的执行计划优化器。所以请大家在遇到性能极差的sql语句时，请尝试根据执行计划寻找耗时的原因。原文（转自： http://www.eygle.com/archives/2004/10/how_cbo_effect_sql_explain01.html）如下

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

1. 原始SQL语句

这个SQL语句是一个动态查询语句的一部分，该查询根据不同条件生成不同的SQL语句。
本例为查询2003年以来的入库单据，很少的数据。

 

SELECT "SP_TRANS"."TRANS_NO", "SP_TRANS"."TRANS_TYPE", "SP_TRANS"."STORE_NO", "SP_TRANS"."BILL_NO", "SP_TRANS"."TRANSDATE", "SP_TRANS"."MANAGER_ID", "SP_TRANS"."REMARK", "SP_TRANS"."STATE", "SP_TRANS_SUB"."TRANS_NO", "SP_TRANS_SUB"."ITEM_CODE", "SP_TRANS_SUB"."COUNTRY", "SP_TRANS_SUB"."QTY", "SP_TRANS_SUB"."PRICE", "SP_TRANS_SUB"."TOTAL", "SP_CHK"."CHK_NO", "SP_CHK"."RECEIVE_NO", "SP_CHK"."CHECKER", "SP_CHK_SUB"."CHK_NO", "SP_CHK_SUB"."ITEM_CODE", "SP_CHK_SUB"."COUNTRY", "SP_CHK_SUB"."PLAN_NO", "SP_CHK_SUB"."PLAN_LINE", "SP_CHK_SUB"."QTY_CHECKOUT", "SP_CHK_SUB"."NOW_QTY", "SP_RECEIVE"."RECEIVE_NO", "SP_RECEIVE"."VENDOR_NAME", "SP_RECEIVE"."BUYER", "SP_RECEIVE_SUB"."RECEIVE_NO", "SP_RECEIVE_SUB"."PLAN_NO", "SP_RECEIVE_SUB"."PLAN_LINE", "SP_RECEIVE_SUB"."ITEM_NAME", "SP_RECEIVE_SUB"."COUNTRY", "SP_ITEM"."ITEM_CODE", "SP_ITEM"."CHART_ID", "SP_ITEM"."SPECIFICATION" FROM "SP_TRANS", "SP_TRANS_SUB", "SP_CHK", "SP_CHK_SUB", "SP_RECEIVE", "SP_RECEIVE_SUB", "SP_ITEM" WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and ("SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and ( "SP_CHK"."STATE" = 15 ) and ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01') /

2. 执行计划
我们的数据库使用dbms_stats.gather_schema_stats分析过，具有足够及时的所有数据，然而在CBO的执行计划下，优化器选择了完全
不同的执行计划.
a. no hints
这是未加任何提示时，Oralce选择的执行路径，在实际程序中，用户说死掉了，通过执行计划我们知道，不是死掉了，是慢！！！

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288) 1 0 NESTED LOOPS (Cost=2057 Card=1 Bytes=288) 2 1 NESTED LOOPS (Cost=2056 Card=1 Bytes=256) 3 2 NESTED LOOPS (Cost=2054 Card=1 Bytes=219) 4 3 NESTED LOOPS (Cost=2053 Card=1 Bytes=178) 5 4 NESTED LOOPS (Cost=2009 Card=1 Bytes=131) 6 5 MERGE JOIN (Cost=2008 Card=1 Bytes=100) 7 6 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776) 8 7 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776) 9 6 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960) 10 9 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970) 12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE) 13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456) 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727) 17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371) 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416) 19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE)

用足够的耐心，我们得到了该计划的执行结果。

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_TRANS_SUB", 38 "SP_CHK", 39 "SP_CHK_SUB", 40 "SP_RECEIVE", 41 "SP_RECEIVE_SUB", 42 "SP_ITEM" 43 WHERE ( "SP_TRANS_SUB"."TRANS_NO" = "SP_TRANS"."TRANS_NO" ) and 44 ( "SP_TRANS"."BILL_NO" = "SP_CHK"."CHK_NO") and 45 ( "SP_CHK_SUB"."CHK_NO" = "SP_CHK"."CHK_NO" ) and 46 ( "SP_CHK"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and 47 ( "SP_CHK"."STATE" = 15 ) and 48 ( "SP_RECEIVE_SUB"."RECEIVE_NO" = "SP_RECEIVE"."RECEIVE_NO" ) and 49 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_ITEM"."ITEM_CODE" ) and 50 ( "SP_TRANS_SUB"."ITEM_CODE" = "SP_CHK_SUB"."ITEM_CODE" ) and 51 ( "SP_CHK_SUB"."ITEM_CODE" = "SP_RECEIVE_SUB"."ITEM_CODE" ) and 52 ( "SP_CHK_SUB"."COUNTRY" = "SP_TRANS_SUB"."COUNTRY" ) and 53 ( "SP_CHK_SUB"."COUNTRY" = "SP_RECEIVE_SUB"."COUNTRY" ) and 54 ( "SP_CHK_SUB"."PLAN_NO" = "SP_RECEIVE_SUB"."PLAN_NO" ) and 55 ( "SP_CHK_SUB"."PLAN_LINE" = "SP_RECEIVE_SUB"."PLAN_LINE" ) and 56 (to_char("SP_TRANS"."TRANSDATE" ,'YYYY-MM-DD') >='2003-01-01') 57 / 130 rows selected. Elapsed: 00: 29: 1785.47 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2057 Card=1 Bytes=288) 1 0 NESTED LOOPS (Cost=2057 Card=1 Bytes=288) 2 1 NESTED LOOPS (Cost=2056 Card=1 Bytes=256) 3 2 NESTED LOOPS (Cost=2054 Card=1 Bytes=219) 4 3 NESTED LOOPS (Cost=2053 Card=1 Bytes=178) 5 4 NESTED LOOPS (Cost=2009 Card=1 Bytes=131) 6 5 MERGE JOIN (Cost=2008 Card=1 Bytes=100) 7 6 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776) 8 7 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776) 9 6 SORT (JOIN) (Cost=1058 Card=36730 Bytes=1909960) 10 9 TABLE ACCESS (FULL) OF 'SP_RECEIVE_SUB' (Cost=89 Card=36730 Bytes=1909960) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' (Cost=1 Card=3870 Bytes=119970) 12 11 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE) 13 4 TABLE ACCESS (FULL) OF 'SP_TRANS' (Cost=44 Card=1717 Bytes=80699) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' (Cost=1 Card=7816 Bytes=320456) 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS_SUB' (Cost=2 Card=136371 Bytes=5045727) 17 16 INDEX (UNIQUE SCAN) OF 'PK_SP_TRANS_SUB' (UNIQUE) (Cost=1 Card=136371) 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416) 19 18 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE) Statistics ---------------------------------------------------------- 16 recursive calls 186307 db block gets 10685361 consistent gets 2329 physical reads 0 redo size 38486 bytes sent via SQL*Net to client 1117 bytes received via SQL*Net from client 10 SQL*Net roundtrips to/from client 7 sorts (memory) 2 sorts (disk) 130 rows processed

 

可以看到，该执行计划消耗了大量的资源以及时间，这种情况是无法忍受的。

b. rule
在RBO条件下，该语句是执行很快的
加入rule提示，我们得到以下执行计划：

Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS 7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE) 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS' 11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE) 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' 13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB' 17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE) 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' 19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE)

执行该计划，我们得到以下输出：

SQL>@sql 130 rows selected. Elapsed: 00: 00: 12.17 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 NESTED LOOPS 2 1 NESTED LOOPS 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS 6 5 NESTED LOOPS 7 6 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' 9 8 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE) 10 5 TABLE ACCESS (BY INDEX ROWID) OF 'SP_TRANS' 11 10 INDEX (UNIQUE SCAN) OF 'PK_HSP_TRANS' (UNIQUE) 12 4 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK' 13 12 INDEX (UNIQUE SCAN) OF 'PK_SP_CHK' (UNIQUE) 14 3 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE' 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE' (UNIQUE) 16 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_CHK_SUB' 17 16 INDEX (RANGE SCAN) OF 'IDX_CHK_SUB_ITEM_CODE' (NON-UNIQUE) 18 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' 19 18 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 6 db block gets 829182 consistent gets 0 physical reads 0 redo size 37383 bytes sent via SQL*Net to client 1127 bytes received via SQL*Net from client 10 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 130 rows processed SQL>

c. ordered
然后我想起了Ordered提示
使用该提示的执行计划如下：

SQL>@sql 已选择130行。 已用时间: 00: 00: 05.67 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3284 Card=1 Bytes=288) 1 0 NESTED LOOPS (Cost=3284 Card=1 Bytes=288) 2 1 NESTED LOOPS (Cost=3283 Card=1 Bytes=256) 3 2 MERGE JOIN (Cost=3282 Card=1 Bytes=204) 4 3 SORT (JOIN) (Cost=2333 Card=6823 Bytes=1064388) 5 4 HASH JOIN (Cost=1848 Card=6823 Bytes=1064388) 6 5 HASH JOIN (Cost=216 Card=1717 Bytes=204323) 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_RECEIVE' (Cost=17 Card=7816 Bytes=320456) 11 5 TABLE ACCESS (FULL) OF 'SP_TRANS_SUB' (Cost=155 Card=136371 Bytes=5045727) 12 3 SORT (JOIN) (Cost=950 Card=36412 Bytes=1747776) 13 12 TABLE ACCESS (FULL) OF 'SP_CHK_SUB' (Cost=59 Card=36412 Bytes=1747776) 14 2 TABLE ACCESS (BY INDEX ROWID) OF 'SP_RECEIVE_SUB' (Cost=1 Card=36730 Bytes=1909960) 15 14 INDEX (UNIQUE SCAN) OF 'PK_SP_RECEIVE_SUB' (UNIQUE) 16 1 TABLE ACCESS (BY INDEX ROWID) OF 'SP_ITEM' (Cost=1 Card=29763 Bytes=952416) 17 16 INDEX (UNIQUE SCAN) OF 'SYS_C0012193' (UNIQUE) Statistics ---------------------------------------------------------- 8 recursive calls 88 db block gets 2667 consistent gets 1093 physical reads 0 redo size 37285 bytes sent via SQL*Net to client 1109 bytes received via SQL*Net from client 10 SQL*Net roundtrips to/from client 8 sorts (memory) 1 sorts (disk) 130 rows processed SQL>

很幸运，Ordered提示使Oracle选择了较好的执行计划。

所以会产生这样的效果，是因为在CBO的执行计划中，对于7张数据表，Oracle需要计算7！(5040)个连接顺序，然后比较各个顺序的
成本，最后选择成本较低的执行计划。
显然，在这一判断上耗费了大量的时间。当我们使用ordered hints的时候，Oracle就不需要这一计算步骤，它只需要使用我们指定的
顺序，然后快速的给出结果。然后问题迎刃而解。

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

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

在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这只是一种知识，一种方法而已。