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就不需要这一计算步骤，它只需要使用我们指定的
顺序，然后快速的给出结果。然后问题迎刃而解。
初试化参数对于执行计划的影响
有几个初试化参数对于多表连接的执行计划有重要的关系。
在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这只是一种知识，一种方法而已。
