现在系统中有一条SQL执行速度有时候特别快,有时候特别慢,通过对原有语句和执行计划的分析,发现将“rownum between
:v_5 and :v_6;”由变量变为常量之后,执行计划会优化很多,但是该SQL在调整后反而执行消耗的时间更长了,哪位专家能帮帮忙,帮小弟分析一下这个问题,并给点处理建议.先谢谢各位了!
调优后的SQL语句,以及执行计划
SQL> select articleid,rownum irow from editorlib where deleteflag=:v_1 and approvalstate=:v_2 AND (deptid =:v_3 or destdeptid = :v_4) and rownum between 1 and 20 ORDER BY lastmodify DESC;
ARTICLEID IROW
---------- ----------
133905780 1
133908737 2
133533545 3
133737807 4
133737795 5
133737670 6
133737746 7
133730863 8
133576648 9
133570917 10
133220967 11
133059133 12
133260867 13
133283558 14
133245458 15
133235295 16
133052920 17
133114020 18
132366107 19
132505234 20
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2663671108
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 440 | 689 (1)| 00:00:09 |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| EDITORLIB | 23353 | 501K| 689 (1)| 00:00:09 |
| 4 | INDEX FULL SCAN | IDX_EDITORLIB_ID_LASTMODIFY | 739 | | 36 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
2 - filter(ROWNUM>=1)
3 - filter("APPROVALSTATE"=TO_NUMBER(:V_2) AND "DELETEFLAG"=TO_NUMBER(:V_1) AND
("DESTDEPTID"=TO_NUMBER(:V_4) OR "DEPTID"=TO_NUMBER(:V_3)))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
521970 consistent gets
0 physical reads
0 redo size
1086 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
调优前的SQL语句与执行计划
SQL> select * from (
2 select kk.*, rownum irow from (
3 select articleid from editorlib where deleteflag=:v_1 and approvalstate=:v_2 and (deptid =:v_3 or destdeptid = :v_4 ) ORDER BY lastmodify DESC
4 ) kk) aa where irow between :v_5 and :v_6;
ARTICLEID IROW
---------- ----------
133905780 1
133908737 2
133533545 3
133737807 4
133737795 5
133737670 6
133737746 7
133730863 8
133576648 9
133570917 10
133220967 11
133059133 12
133260867 13
133283558 14
133245458 15
133235295 16
133052920 17
133114020 18
132366107 19
132505234 20
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4217969665
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23353 | 592K| 21036 (1)| 00:04:13 |
|* 1 | FILTER | | | | | |
|* 2 | VIEW | | 23353 | 592K| 21036 (1)| 00:04:13 |
| 3 | COUNT | | | | | |
| 4 | VIEW | | 23353 | 296K| 21036 (1)| 00:04:13 |
| 5 | SORT ORDER BY | | 23353 | 501K| 21036 (1)| 00:04:13 |
| 6 | TABLE ACCESS BY INDEX ROWID| EDITORLIB | 23353 | 501K| 21034 (1)| 00:04:13 |
|* 7 | INDEX RANGE SCAN | IDX_EDITORLIB_LISTBYDEP | 23353 | | 1524 (1)| 00:00:19 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:V_5)<=TO_NUMBER(:V_6))
2 - filter("IROW"<=TO_NUMBER(:V_6) AND "IROW">=TO_NUMBER(:V_5))
7 - access("DELETEFLAG"=TO_NUMBER(:V_1) AND "APPROVALSTATE"=TO_NUMBER(:V_2))
filter("DESTDEPTID"=TO_NUMBER(:V_4) OR "DEPTID"=TO_NUMBER(:V_3))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23907 consistent gets
0 physical reads
0 redo size
1086 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
收起