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這只是一種知識，一種方法而已。
