對對象進行分析後：
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
select /*+ ORDERED USE_NL (A B C) */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
對於這個查詢我無論如何也沒有得到類似下面這樣的執行計劃：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 TABLE Access (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
3 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
4 3 TABLE Access (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
5 3 TABLE Access (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
從上面的這些例子我們可以看出：通過給語句添加HINTS，讓其按照我們的意願執行，有時是一件
很困難的事情，需要不斷的嘗試各種不同的hints。對於USE_NL與USE_HASH提示，建議同ORDERED提示一起
使用，否則不容易指定那個表為驅動表。
[center]具體案例分析：[/center]
環境：Oracle 817 + Linux + 陣列櫃
swd_billdetail 表5000萬條數據
SUPER_USER 表2800條數據
連接列上都有索引，而且super_user中的一條對應於swd_billdetail表中的很多條記錄
表與索引都做了分析。
實際應用的查詢為：
select a.CHANNEL, B.user_class
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
這樣在分析時導致查詢出的數據過多，不方便，所以用count(a.CHANNEL||B.user_class)來代替，而且
count(a.CHANNEL||B.user_class)操作本身並不占用過多的時間，所以可以接受此種替代。
利用索引查詢出SWD_BILLDETAIL表中所有記錄的方法
SQL> select count(id) from SWD_BILLDETAIL;
COUNT(ID)
----------
53923574
Elapsed: 00:02:166.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=18051 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'SYS_C001851' (UNIQUE) (Cost=18051 Card=54863946)
Statistics
----------------------------------------------------------
0 recursive calls
1952 db block gets
158776 consistent gets
158779 physical reads
1004 redo size
295 bytes sent via SQL*Net to clIEnt
421 bytes received via SQL*Net from clIEnt
2 SQL*Net roundtrips to/from clIEnt
1 sorts (memory)
0 sorts (disk)
1 rows processed
利用全表掃描從SWD_BILLDETAIL表中取出全部數據的方法。
SQL> select count(user_class) from swd_billdetail;
COUNT(USER_CLASS)
-----------------
53923574
Elapsed: 00:11:703.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=165412 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 TABLE Access (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946
Bytes=109727892)
Statistics
----------------------------------------------------------
0 recursive calls
8823 db block gets
1431070 consistent gets
1419520 physical reads
0 redo size
303 bytes sent via SQL*Net to clIEnt
421 bytes received via SQL*Net from clIEnt
2 SQL*Net roundtrips to/from clIEnt
1 sorts (memory)
0 sorts (disk)
1 rows processed
select count(a.CHANNEL||B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- --------------------------------------------------------------------------------
---------------------------
6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=108968,CARD=1,BYTES=21)
5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)
4 NESTED LOOPS (COST=108968,CARD=1213745,BYTES=25488645)
1 TABLE Access (FULL) OF 'SWord.SUPER_USER'
(COST=2,CARD=2794,BYTES=27940)
3 TABLE Access (BY INDEX ROWID) OF 'SWord.SWD_BILLDETAIL'
(COST=39,CARD=54863946,BYTES=603503406)
2 INDEX (RANGE SCAN) OF 'SWord.IDX_DETAIL_CN' (NON-UNIQUE)
(COST=3,CARD=54863946,BYTES=)
這個查詢耗費的時間很長，需要1個多小時。
運行後的信息如下：
COUNT(A.CHANNEL||B.USER_CLASS)
------------------------------
1186387
Elapsed: 01:107:6429.87
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=108968 Card=1 Bytes=21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=108968 Card=1213745 Bytes=25488645)
3 2 TABLE Access (FULL) OF 'SUPER_USER' (Cost=2 Card=2794Bytes=27940)
4 2 TABLE Access (BY INDEX ROWID) OF 'SWD_BILLDETAIL' (Cost=39 Card=54863946
Bytes=603503406)
5 4 INDEX (RANGE SCAN) OF 'IDX_DETAIL_CN' (NON-UNIQUE) (Cost=3 Card=54863946)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1196954 consistent gets
1165726 physical reads
0 redo size
316 bytes sent via SQL*Net to clIEnt
421 bytes received via SQL*Net from clIEnt
2 SQL*Net roundtrips to/from clIEnt
2 sorts (memory)
0 sorts (disk)
1 rows processed
將語句中加入hints，讓Oracle的優化器使用嵌套循環，並且大表作為驅動表，生成新的執行計劃：
select /*+ ORDERED USE_NL(A) */ count(a.CHANNEL||B.user_class)
from swd_billdetail B, SUPER_USER A
where A.cn = B.cn;
EXEC_ORDER PLANLINE
---------- --------------------------------------------------------------------------------
---------------------
6 SELECT STATEMENT OPT_MODE:CHOOSE (COST=109893304,CARD=1,BYTES=21)
5 SORT (AGGREGATE) (COST=,CARD=1,BYTES=21)
4 NESTED LOOPS (COST=109893304,CARD=1213745,BYTES=25488645)
1 TABLE Access (FULL) OF 'SWord.SWD_BILLDETAIL'
(COST=165412,CARD=54863946,BYTES=603503406)
3 TABLE Access (BY INDEX ROWID) OF 'SWord.SUPER_USER'
(COST=2,CARD=2794,BYTES=27940)
2 INDEX (RANGE SCAN) OF 'SWord.IDX_SUPER_USER_CN' (NON-UNIQUE)
(COST=1,CARD=2794,BYTES=)
這個查詢耗費的時間較短，才20分鐘，性能比較好。
運行後的信息如下：
COUNT(A.CHANNEL||B.USER_CLASS)
------------------------------
1186387
Elapsed: 00:20:1208.87
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=109893304 Card=1 Bytes=21)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=109893304 Card=1213745 Bytes=25488645)
3 2 TABLE Access (FULL) OF 'SWD_BILLDETAIL' (Cost=165412 Card=54863946
Bytes=603503406)
4 2 TABLE Access (BY INDEX ROWID) OF 'SUPER_USER' (Cost=2Card=2794 Bytes=27940)
5 4 INDEX (RANGE SCAN) OF 'IDX_SUPER_USER_CN' (NON-UNIQUE) (Cost=1 Card=2794)
Statistics
----------------------------------------------------------
0 recursive calls
8823 db block gets
56650250 consistent gets
1413250 physical reads
0 redo size
316 bytes sent via SQL*Net to clIEnt
421 bytes received via SQL*Net from clIEnt
2 SQL*Net roundtrips to/from clIEnt
2 sorts (memory)
0 sorts (disk)
1 rows processed
總結：
因為上兩個查詢都是采用nested loop循環，這時采用哪個表作為driving table就很重要。在第
一個sql中，小表(SUPER_USER)作為driving table，符合Oracle優化的建議，但是由於SWD_BILLDETAIL表
中cn列的值有很多重復的，這樣對於SUPER_USER中的每一行，都會在SWD_BILLDETAIL中有很多行，利用索
引查詢出這些行的rowid很快，但是再利用這些rowid去查詢SWD_BILLDETAIL表中的user_class列的值，就
比較慢了。原因是這些rowid是隨機的，而且該表比較大，不可能緩存到內存，所以幾乎每次按照rowid查
詢都需要讀物理磁盤，這就是該執行計劃比較慢的真正原因。從結果可以得到驗證：查詢出1186387行，
需要利用rowid從SWD_BILLDETAIL表中讀取1186387次，而且大部分為從硬盤上讀取。
反其道而行之，利用大表(SWD_BILLDETAIL)作為driving表，這樣大表只需要做一次全表掃描(而
且會使用多塊讀功能，每次物理I/O都會讀取幾個Oracle數據塊，從而一次讀取很多行，加快了執行效率)
，對於讀出的每一行，都與SUPER_USER中的行進行匹配，因為SUPER_USER表很小，所以可以全部放到內存
中，這樣匹配操作就極快，所以該sql執行的時間與SWD_BILLDETAIL表全表掃描的時間差不多
(SWD_BILLDETAIL全表用11分鐘，而此查詢用20分鐘)。
另外：如果SWD_BILLDETAIL表中cn列的值唯一，則第一個sql執行計劃執行的結果或許也會不錯
。如果SUPER_USER表也很大，如500萬行，則第2個sql執行計劃執行的結果反而又可能會差。其實，如果
SUPER_USER表很小，則第2個sql語句的執行計劃如果不利用SUPER_USER表的索引，查詢或許會更快一些，
我沒有對此進行測試。
所以在進行性能調整時，具體問題要具體分析，沒有一個統一的標准。
[center]第6章 其它注意事項[/center]
1. 不要認為將optimizer_mode參數設為rule，就認為所有的語句都使用基於規則的優化器
不管optimizer_mode參數如何設置，只要滿足下面3個條件，就一定使用CBO。
1) 如果使用Index Only Tables(IOTs), 自動使用CBO.
2) Oracle 7.3以後，如果表上的Paralle degree option設為>1，
則自動使用CBO, 而不管是否用rule hints.
3) 除rlue以外的任何hints都將導致自動使用CBO來執行語句
總結一下，一個語句在運行時到底使用何種優化器可以從下面的表格中識別出來，從上到下看你的語句到
底是否滿足description列中描述的條件：
Description 對象是否被分析
優化器的類型
~~~~~~~~~~~ ~~~~~~~~~~~~
~~~~~~~~~
Non-RBO Object (Eg:IOT) n/a
#1
Parallelism > 1 n/a
#1
RULE hint n/a
RULE
ALL_ROWS hint n/a
ALL_ROWS
FIRST_ROWS hint n/a
FIRST_ROWS
*Other Hint n/a
#1
OPTIMIZER_GOAL=RULE n/a RULE
OPTIMIZER_GOAL=ALL_ROWS n/a ALL_ROWS
OPTIMIZER_GOAL=FIRST_ROWS n/a
FIRST_ROWS
OPTIMIZER_GOAL=CHOOSE NO RULE
OPTIMIZER_GOAL=CHOOSE YES ALL_ROWS
#1 表示除非OPTIMIZER_GOAL 被設置為FIRST_ROWS ，否則將使用ALL_ROWS。在PL/SQL中，則一
直是使用ALL_ROWS
*Other Hint 表示是指除RULE、ALL_ROWS 和FIRST_ROWS以外的其它提示
2) 當CBO選擇了一個次優化的執行計劃時, 不要同CBO過意不去, 先采取如下措施:
a) 檢查是否在表與索引上又最新的統計數據
b) 對所有的數據進行分析，而不是只分析一部分數據
c) 檢查是否引用的數據字典表，在Oracle 10G之前，缺省情況下是不對數據字典表進行分析的。
d) 試試RBO優化器，看語句執行的效率如何，有時RBO能比CBO產生的更好的執行計劃
e) 如果還不行，跟蹤該語句的執行，生成trace信息，然後用tkprof格式化trace信息，這樣可以得到全
面的供優化的信息。
3) 假如利用附錄的方法對另一個會話進行trace，則該會話應該為專用連接
4) 不要認為綁定變量(bind variables)的缺點只有書寫麻煩，而優點多多，實際上使用綁定變量雖然避
免了重復parse，但是它導致優化器不能使用數據庫中的列統計，從而選擇了較差的執行計劃。而使用硬
編碼的SQL則可以使用列統計。當然隨著CBO功能的越來越強，這種情況會得到改善。目前就已經實現了在
第一次運行綁定變量的sql語句時，考慮列統計。
5) 如果一個row source 超過10000行數據，則可以被認為大row source
6) 有(+)的表不是driving table，注意：如果有外聯接，而且order hint指定的順序與外聯結決定的順
序沖突，則忽略order hint
7) 影響CBO選擇execution plan的初始化參數:
這些參數會影響cost值
ALWAYS_ANTI_JOIN
B_TREE_BITMAP_PLANS
COMPLEX_VIEW_MERGING
DB_FILE_MULTIBLOCK_READ_COUNT
FAST_FULL_SCAN_ENABLED
HASH_AREA_SIZE
HASH_JOIN_ENABLED
HASH_MULTIBLOCK_IO_COUNT
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE> / GOAL
OPTIMIZER_PERCENT_PARALLEL
OPTIMIZER_SEARCH_LIMIT
PARTITION_VIEW_ENABLED
PUSH_JOIN_PREDICATE
SORT_AREA_SIZE
SORT_DIRECT_WRITES
SORT_WRITE_BUFFER_SIZE
STAR_TRANSFORMATION_ENABLED
V733_PLANS_ENABLED
CURSOR_SHARING
