一次系统视图查询的优化

今天在进行查询一个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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-69365/