工作中的经验。
此文写得仓促,较为完整的探讨在
http://karsus.itpub.net/post/36558/470885
[@more@]1.SELECT MAX(SEQ) FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0';是在OMS的 TOP SQL按Disk IO排序出最高的SQL.
2.select SEQ FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0' and rownum=1 order by seq desc;是改写之后的.
2比1的效能好很多,几乎完全消除了physical reads. consistent gets也少很多。按我的经验,MAX/MIN 类这样改写效能都会比原先好(包括SQL Server也是如此,rownum使用TOP 1替代,在SQL2005上效果稍弱一些)。
1. PLAN:
2. 15:45:29 SQL> SELECT MAX(SEQ) FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0';
3. 22
4.
5. Elapsed: 00:00:00.26
6.
7. Execution Plan
8. ----------------------------------------------------------
9. 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=30)
10. 1 0 SORT (AGGREGATE)
11. 2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MO_ROUTE' (Cost
12. =13 Card=9 Bytes=270)
13.
14. 3 2 INDEX (RANGE SCAN) OF 'MO_ROUTE1' (UNIQUE) (Cost=4 Car
15. d=9)
16.
17.
18.
19.
20.
21. Statistics
22. ----------------------------------------------------------
23. 18 recursive calls
24. 0 db block gets
25. 27 consistent gets-------------多次运行cache后,25
26. 23 physical reads--------------多次运行cache后,0
27. 0 redo size
28. 518 bytes sent via SQL*Net to client
29. 655 bytes received via SQL*Net from client
30. 2 SQL*Net roundtrips to/from client
31. 0 sorts (memory)
32. 0 sorts (disk)
33. 1 rows processed
2.PLAN
15:51:27 SQL> select seq FROM MO_ROUTE WHERE ISN=UPPER('NF1S6500020029' ) AND STEP <> '0' and rownum=1 order by seq desc;
22
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=1 Bytes=30)
1 0 COUNT (STOPKEY)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'MO_ROUTE' (Cost
=13 Card=9 Bytes=270)
3 2 INDEX (RANGE SCAN DESCENDING) OF 'MO_ROUTE1' (UNIQUE)
(Cost=4 Card=9)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets 首次执行和多次执行一样。
0 physical reads
0 redo size
513 bytes sent via SQL*Net to client
655 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10856805/viewspace-1000136/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10856805/viewspace-1000136/