加hint改變執行計劃訪問順序優化sql
今天又捕獲到一個執行代價比較高的sql，下面是對其優化的大致步驟
sql語句如下
SELECT *
FROM (SELECT row_.*, rownum rn
FROM (select /*+ leading(eu,sh) */ sh.*,
pm.PAYMENT_TYPE,
pm.PAYMENT_NAME as ORDER_justin_method_NAME
from v_justin sh
left join justin_user eu
on eu.id = sh.justin_user_ID
left join justin_method pm
on pm.id = sh.ORDER_justin_method_ID
where sh.IS_LEAF = :g
and sh.mc_site_id in (:a)
and eu.justin_user_NAME like :b
and trunc(sh.ORDER_CREATE_TIME) >=
trunc(to_date(:c, 'yyyy-mm-dd'))
and trunc(sh.ORDER_CREATE_TIME) <=
trunc(to_date(:d, 'yyyy-mm-dd'))
order by sh.ORDER_CREATE_TIME desc) row_
WHERE rownum <= :e)
通過set autotrace檢視執行計劃和consistent gets
SQL> var a number;
SQL> var b varchar2(200);
SQL> var c varchar2(20);
SQL> var d varchar2(20);
SQL> var e number;
SQL> var f number;
SQL> var g number;
SQL> exec :a := 1;
PL/SQL procedure successfully completed.
SQL> exec :b :='%gui_8@126.com%';
PL/SQL procedure successfully completed.
SQL> exec :c := '2010-10-31';
PL/SQL procedure successfully completed.
SQL> exec :d := '2011-01-20';
PL/SQL procedure successfully completed.
SQL> exec :e := 20;
PL/SQL procedure successfully completed.
SQL> exec :f := 0;
PL/SQL procedure successfully completed.
SQL> exec :g := 1;
PL/SQL procedure successfully completed.
檢視原語句執行計劃，可以看到consisten gets值非常高，達到七位數
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| 12576 (1)| 00:02:31 |
|* 1 | VIEW | | 98 | 537K| 12576 (1)| 00:02:31 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 98 | 535K| 12576 (1)| 00:02:31 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 536K| 12576 (1)| 00:02:31 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 536K| 12575 (1)| 00:02:31 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 98 | 534K| 12573 (1)| 00:02:31 |
| 8 | VIEW | v_justin | 1952 | 10M| 8665 (1)| 00:01:44 |
| 9 | UNION-ALL | | | | | |
|* 10 | FILTER | | | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| justin_a | 151 | 71725 | 447 (1)| 00:00:06 |
|* 12 | INDEX RANGE SCAN | IDX_CREATE_TIME | 1089 | | 13 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| justin_b | 1801 | 851K| 8218 (1)| 00:01:39 |
|* 15 | INDEX RANGE SCAN | IDX_CREATE_DATE | 13007 | | 765 (1)| 00:00:10 |
|* 16 | TABLE ACCESS BY INDEX ROWID | justin_user | 1 | 25 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK1 | 1 | | 1 (0)| 00:00:01 --------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
2804436 consistent gets
0 physical reads
124 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可優化部分主要集中於黑體字區域，其中view v_justin部分是訪問檢視，暫不考慮優化，剩下的只有justin_user表了，該sql訪問了justin_user表裡的justin_user_name欄位，但是執行計劃中並未出現；
通過justin_user_name可以顧慮很大一部分條件，並且以該列為引導列建有複合的unique index，可以改變執行計劃的訪問順序，讓它用到該索引，且提早執行
考慮新增hint改變訪問順序，
SQL> SELECT *
2 FROM (SELECT row_.*, rownum rn
3 FROM (select /*+ leading(eu,sh) */ sh.*,
4 pm.PAYMENT_TYPE,
5 pm.PAYMENT_NAME as ORDER_justin_method_NAME
6 from v_justin sh
7 left join justin_user eu
8 on eu.id = sh.justin_user_ID
9 left join justin_method pm
10 on pm.id = sh.ORDER_justin_method_ID
11 where sh.IS_LEAF = :g
12 and sh.mc_site_id in (:a)
13 and eu.justin_user_NAME like :b
14 and trunc(sh.ORDER_CREATE_TIME) >=
15 trunc(to_date(:c, 'yyyy-mm-dd'))
16 and trunc(sh.ORDER_CREATE_TIME) <=
17 trunc(to_date(:d, 'yyyy-mm-dd'))
18 order by sh.ORDER_CREATE_TIME desc) row_
19 WHERE rownum <= :e)
20 WHERE RN > :f;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| | 54186 (1)| 00:10:51 |
|* 1 | VIEW | | 98 | 537K| | 54186 (1)| 00:10:51 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 98 | 535K| | 54186 (1)| 00:10:51 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 536K| | 54186 (1)| 00:10:51 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 536K| | 54185 (1)| 00:10:51 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 98 | 534K| 9088K| 54183 (1)| 00:10:51 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_user | 251K| 6137K| | 44557 (1)| 00:08:55 |
|* 9 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | | 271 (1)| 00:00:04 |
| 10 | VIEW | v_justin | 1952 | 10M| | 8665 (1)| 00:01:44 |
| 11 | UNION-ALL | | | | | | |
|* 12 | FILTER | | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| justin_a | 151 | 71725 | | 447 (1)| 00:00:06 |
|* 14 | INDEX RANGE SCAN | IDX_CREATE_TIME | 1089 | | | 13 (0)| 00:00:01 |
|* 15 | FILTER | | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| justin_b | 1801 | 851K| | 8218 (1)| 00:01:39 |
|* 17 | INDEX RANGE SCAN | IDX_CREATE_DATE | 13007 | | | 765 (1)| 00:00:10
----------------------------------------------------------
0 recursive calls
0 db block gets
161888 consistent gets
0 physical reads
0 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出consistent gets變成了6位數，有所好轉，但依然很高； 同時黃體部分，執行計劃採用了hash join，並把justin_user當成驅動表；
繼續嘗試新增hint
SQL> SELECT *
2 FROM (SELECT row_.*, rownum rn
3 FROM (select /*+ use_nl(eu,sh) leading(eu,sh) */ sh.*,
4 pm.PAYMENT_TYPE,
5 pm.PAYMENT_NAME as ORDER_justin_method_NAME
6 from v_justin sh
7 left join justin_user eu
8 on eu.id = sh.justin_user_ID
9 left join justin_method pm
10 on pm.id = sh.ORDER_justin_method_ID
11 where sh.IS_LEAF = :g
12 and sh.mc_site_id in (:a)
13 and eu.justin_user_NAME like :b
14 and trunc(sh.ORDER_CREATE_TIME) >=
15 trunc(to_date(:c, 'yyyy-mm-dd'))
16 and trunc(sh.ORDER_CREATE_TIME) <=
17 trunc(to_date(:d, 'yyyy-mm-dd'))
18 order by sh.ORDER_CREATE_TIME desc) row_
19 WHERE rownum <= :e)
20 WHERE RN > :f;
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| 5077K (1)| 16:55:36 |
|* 1 | VIEW | | 98 | 537K| 5077K (1)| 16:55:36 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 98 | 535K| 5077K (1)| 16:55:36 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 535K| 5077K (1)| 16:55:36 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 535K| 5077K (1)| 16:55:36 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 98 | 533K| 5077K (1)| 16:55:36 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_user | 251K| 6137K| 44557 (1)| 00:08:55 |
|* 9 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | 271 (1)| 00:00:04 |
| 10 | VIEW | v_justin | 1 | 5553 | 20 (0)| 00:00:01 |
| 11 | UNION ALL PUSHED PREDICATE | | | | | |
|* 12 | FILTER | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| justin_a | 1 | 475 | 8 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_SO_justin_user_ID | 5 | | 3 (0)| 00:00:01 |
|* 15 | FILTER | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| justin_b | 1 | 484 | 12 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_SH_justin_user_ID | 9 | | 3 (0)| 00:00:01 |
----------------------------------------------------------
0 recursive calls
0 db block gets
30074 consistent gets
0 physical reads
0 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
這次效果就非常明顯了，consistent gets變成了5位數，此時對錶justin_user走了基於justin_user_name的索引，但是使用的謂詞為like且繫結變數有前後都有萬用字元%%，所以其代價應該比較大
驗證一下
SQL> select id from justin_user where justin_user_name like :b;
Execution Plan
----------------------------------------------------------
Plan hash value: 4183727034
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 251K| 6137K| 44557 (1)| 00:08:55 |
| 1 | TABLE ACCESS BY INDEX ROWID| justin_user | 251K| 6137K| 44557 (1)| 00:08:55 |
|* 2 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | 271 (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("justin_user_NAME" LIKE :B)
filter("justin_user_NAME" LIKE :B)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30053 consistent gets
0 physical reads
0 redo size
580 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
此條sql的Consistent gets就有30053，而優化後的sql只有30074，可見優化後的sql應屬於當前最優
[ 本帖最後由 myownstars 於 2011-1-26 18:40 編輯 ]
sql語句如下
SELECT *
FROM (SELECT row_.*, rownum rn
FROM (select /*+ leading(eu,sh) */ sh.*,
pm.PAYMENT_TYPE,
pm.PAYMENT_NAME as ORDER_justin_method_NAME
from v_justin sh
left join justin_user eu
on eu.id = sh.justin_user_ID
left join justin_method pm
on pm.id = sh.ORDER_justin_method_ID
where sh.IS_LEAF = :g
and sh.mc_site_id in (:a)
and eu.justin_user_NAME like :b
and trunc(sh.ORDER_CREATE_TIME) >=
trunc(to_date(:c, 'yyyy-mm-dd'))
and trunc(sh.ORDER_CREATE_TIME) <=
trunc(to_date(:d, 'yyyy-mm-dd'))
order by sh.ORDER_CREATE_TIME desc) row_
WHERE rownum <= :e)
通過set autotrace檢視執行計劃和consistent gets
SQL> var a number;
SQL> var b varchar2(200);
SQL> var c varchar2(20);
SQL> var d varchar2(20);
SQL> var e number;
SQL> var f number;
SQL> var g number;
SQL> exec :a := 1;
PL/SQL procedure successfully completed.
SQL> exec :b :='%gui_8@126.com%';
PL/SQL procedure successfully completed.
SQL> exec :c := '2010-10-31';
PL/SQL procedure successfully completed.
SQL> exec :d := '2011-01-20';
PL/SQL procedure successfully completed.
SQL> exec :e := 20;
PL/SQL procedure successfully completed.
SQL> exec :f := 0;
PL/SQL procedure successfully completed.
SQL> exec :g := 1;
PL/SQL procedure successfully completed.
檢視原語句執行計劃，可以看到consisten gets值非常高，達到七位數
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| 12576 (1)| 00:02:31 |
|* 1 | VIEW | | 98 | 537K| 12576 (1)| 00:02:31 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 98 | 535K| 12576 (1)| 00:02:31 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 536K| 12576 (1)| 00:02:31 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 536K| 12575 (1)| 00:02:31 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 98 | 534K| 12573 (1)| 00:02:31 |
| 8 | VIEW | v_justin | 1952 | 10M| 8665 (1)| 00:01:44 |
| 9 | UNION-ALL | | | | | |
|* 10 | FILTER | | | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID| justin_a | 151 | 71725 | 447 (1)| 00:00:06 |
|* 12 | INDEX RANGE SCAN | IDX_CREATE_TIME | 1089 | | 13 (0)| 00:00:01 |
|* 13 | FILTER | | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| justin_b | 1801 | 851K| 8218 (1)| 00:01:39 |
|* 15 | INDEX RANGE SCAN | IDX_CREATE_DATE | 13007 | | 765 (1)| 00:00:10 |
|* 16 | TABLE ACCESS BY INDEX ROWID | justin_user | 1 | 25 | 2 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK1 | 1 | | 1 (0)| 00:00:01 --------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
2804436 consistent gets
0 physical reads
124 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可優化部分主要集中於黑體字區域，其中view v_justin部分是訪問檢視，暫不考慮優化，剩下的只有justin_user表了，該sql訪問了justin_user表裡的justin_user_name欄位，但是執行計劃中並未出現；
通過justin_user_name可以顧慮很大一部分條件，並且以該列為引導列建有複合的unique index，可以改變執行計劃的訪問順序，讓它用到該索引，且提早執行
考慮新增hint改變訪問順序，
SQL> SELECT *
2 FROM (SELECT row_.*, rownum rn
3 FROM (select /*+ leading(eu,sh) */ sh.*,
4 pm.PAYMENT_TYPE,
5 pm.PAYMENT_NAME as ORDER_justin_method_NAME
6 from v_justin sh
7 left join justin_user eu
8 on eu.id = sh.justin_user_ID
9 left join justin_method pm
10 on pm.id = sh.ORDER_justin_method_ID
11 where sh.IS_LEAF = :g
12 and sh.mc_site_id in (:a)
13 and eu.justin_user_NAME like :b
14 and trunc(sh.ORDER_CREATE_TIME) >=
15 trunc(to_date(:c, 'yyyy-mm-dd'))
16 and trunc(sh.ORDER_CREATE_TIME) <=
17 trunc(to_date(:d, 'yyyy-mm-dd'))
18 order by sh.ORDER_CREATE_TIME desc) row_
19 WHERE rownum <= :e)
20 WHERE RN > :f;
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| | 54186 (1)| 00:10:51 |
|* 1 | VIEW | | 98 | 537K| | 54186 (1)| 00:10:51 |
|* 2 | COUNT STOPKEY | | | | | | |
| 3 | VIEW | | 98 | 535K| | 54186 (1)| 00:10:51 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 536K| | 54186 (1)| 00:10:51 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 536K| | 54185 (1)| 00:10:51 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 98 | 534K| 9088K| 54183 (1)| 00:10:51 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_user | 251K| 6137K| | 44557 (1)| 00:08:55 |
|* 9 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | | 271 (1)| 00:00:04 |
| 10 | VIEW | v_justin | 1952 | 10M| | 8665 (1)| 00:01:44 |
| 11 | UNION-ALL | | | | | | |
|* 12 | FILTER | | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| justin_a | 151 | 71725 | | 447 (1)| 00:00:06 |
|* 14 | INDEX RANGE SCAN | IDX_CREATE_TIME | 1089 | | | 13 (0)| 00:00:01 |
|* 15 | FILTER | | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| justin_b | 1801 | 851K| | 8218 (1)| 00:01:39 |
|* 17 | INDEX RANGE SCAN | IDX_CREATE_DATE | 13007 | | | 765 (1)| 00:00:10
----------------------------------------------------------
0 recursive calls
0 db block gets
161888 consistent gets
0 physical reads
0 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出consistent gets變成了6位數，有所好轉，但依然很高； 同時黃體部分，執行計劃採用了hash join，並把justin_user當成驅動表；
繼續嘗試新增hint
SQL> SELECT *
2 FROM (SELECT row_.*, rownum rn
3 FROM (select /*+ use_nl(eu,sh) leading(eu,sh) */ sh.*,
4 pm.PAYMENT_TYPE,
5 pm.PAYMENT_NAME as ORDER_justin_method_NAME
6 from v_justin sh
7 left join justin_user eu
8 on eu.id = sh.justin_user_ID
9 left join justin_method pm
10 on pm.id = sh.ORDER_justin_method_ID
11 where sh.IS_LEAF = :g
12 and sh.mc_site_id in (:a)
13 and eu.justin_user_NAME like :b
14 and trunc(sh.ORDER_CREATE_TIME) >=
15 trunc(to_date(:c, 'yyyy-mm-dd'))
16 and trunc(sh.ORDER_CREATE_TIME) <=
17 trunc(to_date(:d, 'yyyy-mm-dd'))
18 order by sh.ORDER_CREATE_TIME desc) row_
19 WHERE rownum <= :e)
20 WHERE RN > :f;
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 537K| 5077K (1)| 16:55:36 |
|* 1 | VIEW | | 98 | 537K| 5077K (1)| 16:55:36 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 98 | 535K| 5077K (1)| 16:55:36 |
|* 4 | SORT ORDER BY STOPKEY | | 98 | 535K| 5077K (1)| 16:55:36 |
|* 5 | HASH JOIN RIGHT OUTER | | 98 | 535K| 5077K (1)| 16:55:36 |
| 6 | TABLE ACCESS FULL | justin_method | 77 | 1694 | 2 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 98 | 533K| 5077K (1)| 16:55:36 |
| 8 | TABLE ACCESS BY INDEX ROWID | justin_user | 251K| 6137K| 44557 (1)| 00:08:55 |
|* 9 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | 271 (1)| 00:00:04 |
| 10 | VIEW | v_justin | 1 | 5553 | 20 (0)| 00:00:01 |
| 11 | UNION ALL PUSHED PREDICATE | | | | | |
|* 12 | FILTER | | | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| justin_a | 1 | 475 | 8 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_SO_justin_user_ID | 5 | | 3 (0)| 00:00:01 |
|* 15 | FILTER | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID| justin_b | 1 | 484 | 12 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | IDX_SH_justin_user_ID | 9 | | 3 (0)| 00:00:01 |
----------------------------------------------------------
0 recursive calls
0 db block gets
30074 consistent gets
0 physical reads
0 redo size
11145 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
這次效果就非常明顯了，consistent gets變成了5位數，此時對錶justin_user走了基於justin_user_name的索引，但是使用的謂詞為like且繫結變數有前後都有萬用字元%%，所以其代價應該比較大
驗證一下
SQL> select id from justin_user where justin_user_name like :b;
Execution Plan
----------------------------------------------------------
Plan hash value: 4183727034
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 251K| 6137K| 44557 (1)| 00:08:55 |
| 1 | TABLE ACCESS BY INDEX ROWID| justin_user | 251K| 6137K| 44557 (1)| 00:08:55 |
|* 2 | INDEX RANGE SCAN | IDX_justin_user_NAME_UNIQ | 45248 | | 271 (1)| 00:00:04 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("justin_user_NAME" LIKE :B)
filter("justin_user_NAME" LIKE :B)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
30053 consistent gets
0 physical reads
0 redo size
580 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
此條sql的Consistent gets就有30053，而優化後的sql只有30074，可見優化後的sql應屬於當前最優
[ 本帖最後由 myownstars 於 2011-1-26 18:40 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-688365/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用hint改變執行計劃
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- sql執行計劃是否改變SQL
- 11g 改變SQL執行計劃SQL
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- sql語句執行順序與效能優化（1）SQL優化
- Oracle 優化器與sql查詢執行順序Oracle優化SQL
- SQL優化案例-改變那些CBO無能為力的執行計劃（一）SQL優化
- 增加索引改變執行計劃——SQL優化之Everything is possible索引SQL優化
- oracle實用sql(15)--檢視SQL執行計劃的順序OracleSQL
- Sql執行順序SQL
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 【Oracle】-【索引-HINT，執行計劃】-帶HINT的索引執行計劃Oracle索引
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 利用coe_xfr_sql_profile 改變執行計劃SQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 恆等查詢條件改變執行計劃——SQL優化之Everything is PossibleSQL優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- sql mysql 執行順序 （4）MySql
- SQL語句執行順序SQL
- 使用leading(,)優化sql執行計劃優化SQL
- SQL語句中的AND和OR執行順序問題SQL
- sql中的or與and的執行順序問題SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- SQL Server SQL語句執行順序SQLServer
- Oracle優化案例-改變那些CBO無能為力的執行計劃（一）Oracle優化
- PostgreSQL執行計劃變化SQL
- 【優化】Oracle 執行計劃優化Oracle
- SQL 語句的執行順序SQL
- SQL 執行順序 你懂的SQL
- java執行緒優先順序Java執行緒
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- SQL最佳化案例-改變那些CBO無能為力的執行計劃（一）SQL
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- 一條SQL語句的執行計劃變化探究SQL
- Oracle訪問表的執行計劃Oracle