分析執行計劃最佳化SQLORACLE的執行計劃(轉)

環境：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表的索引，查詢或許會更快一些，我沒有對此進行測試。

所以在進行效能調整時，具體問題要具體分析，沒有一個統一的標準。