ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 一次系统视图查询的优化

一次系统视图查询的优化

原创 Linux操作系统 作者:yangtingkun 时间:2007-07-27 00:00:00 0 删除 编辑

今天在进行查询一个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,导致优化模式变成了CBOOracle使用了索引来提高查询速度。

现在无论是逻辑读还是响应时间都是可以接受的。

最后说明一下,由于上面的SQL本身会对查询结果造成影响,所以每次的查询结果都是不一样的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69365/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10488244