一次系統檢視查詢的優化

今天在進行查詢一個SQL的執行計劃的時候，發現查詢的SQL效率比較低，於是就優化了一下。

原始SQL如下：

SQL> set timing on
SQL> set autot trace
SQL> select * from v$sql_plan where hash_value in
2 (select hash_value from v$sql where sql_text like '%惠氏%where cm.molecule_orgid = c.id%');

14 rows selected.

Elapsed: 00:00:50.64

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS (OUTER)
4 3 NESTED LOOPS (OUTER)
5 4 FIXED TABLE (FULL) OF 'X$KQLFXPL'
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
7 6 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
8 3 TABLE ACCESS (CLUSTER) OF 'USER$'
9 8 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)
10 1 SORT (JOIN)
11 10 VIEW OF 'VW_NSO_1'
12 11 SORT (UNIQUE)
13 12 FIXED TABLE (FULL) OF 'X$KGLOB'

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
98578 consistent gets
0 physical reads
0 redo size
3213 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
14 rows processed

一個簡單的SQL居然執行了將近50秒，無論是執行計劃還是邏輯讀都無法讓人接受，而直接查詢V$SQL的結果是很快的。

嘗試將SQL改寫為連線的方式，這樣可以採用NO_MERGE的提示來避免Oracle將檢視打散：

SQL> select a.* from v$sql_plan a, v$sql b
2 where a.hash_value = b.hash_value
3 and sql_text like '%惠氏%where cm.molecule_orgid = c.id%'
4 ;

26 rows selected.

Elapsed: 00:00:49.66

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS (OUTER)
2 1 NESTED LOOPS (OUTER)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 FIXED TABLE (FULL) OF 'X$KGLOB'
6 3 SORT (JOIN)
7 6 FIXED TABLE (FULL) OF 'X$KQLFXPL'
8 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'
9 8 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
10 1 TABLE ACCESS (CLUSTER) OF 'USER$'
11 10 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
55 consistent gets
0 physical reads
0 redo size
4196 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
26 rows processed

採用這種方式邏輯讀雖然減少了，但是執行時間並沒有明顯下降。注意，這個SQL和上面的SQL並非完全等價，不過都可以滿足我的查詢目的。

最後嘗試新增NO_MERGE提示，使得Oracle先得到V$SQL的結果，然後關聯V$SQL_PLAN檢視：

SQL> select /*+ no_merge(b) */ a.*
2 from v$sql_plan a,
3 (
4 select hash_value from v$sql
5 where sql_text like '%惠氏%where cm.molecule_orgid = c.id%'
6 ) b
7 where a.hash_value = b.hash_value
8 ;

37 rows selected.

Elapsed: 00:00:02.69

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=104 Card=81 Bytes=536058)
1 0 NESTED LOOPS (OUTER) (Cost=104 Card=81 Bytes=536058)
2 1 NESTED LOOPS (OUTER) (Cost=23 Card=81 Bytes=533628)
3 2 NESTED LOOPS (Cost=22 Card=1 Bytes=6545)
4 3 VIEW (Cost=11 Card=1 Bytes=13)
5 4 FIXED TABLE (FULL) OF 'X$KGLOB' (Cost=11 Card=1 Bytes=553)
6 3 FIXED TABLE (FIXED INDEX) OF 'X$KQLFXPL (ind:3)' (Cost=11 Card=1 Bytes=6532)
7 2 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' (Cost=1 Card=82 Bytes=3526)
8 7 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE)
9 1 TABLE ACCESS (CLUSTER) OF 'USER$' (Cost=1 Card=1 Bytes=30)
10 9 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
80 consistent gets
0 physical reads
0 redo size
5218 bytes sent via SQL*Net to client
677 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
37 rows processed

採用HINT後，達到了預期目的，更重要的是，由於新增HINT，導致優化模式變成了CBO，Oracle使用了索引來提高查詢速度。

現在無論是邏輯讀還是響應時間都是可以接受的。

最後說明一下，由於上面的SQL本身會對查詢結果造成影響，所以每次的查詢結果都是不一樣的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-69365/，如需轉載，請註明出處，否則將追究法律責任。