通過hint push_subq優化sql
通過AWR報告捕獲了一條cost比較高的sql，考慮對其優化.
該sql大致結構如下:
select rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
sql最主要的效能指標是consistent gets，而通過explain plan for是得不出的，選用set autotrace traceonly來檢視，sql使用了繫結變數。
首先查出其繫結變數的值
SQL> select s.CHILD_NUMBER,s.NAME,s.POSITION,s.DATATYPE_STRING,s.VALUE_STRING from v$sql_bind_capture s where s.SQL_ID='4kz559dt818vm';
CHILD_NUMBER NAME POSITION DATATYPE_S VALUE_STRI
------------ -------------------- ---------- ---------- ----------
1 :SYS_B_0 1 NUMBER 1
1 :SYS_B_1 2 NUMBER 1
1 :SYS_B_2 3 NUMBER 0
1 :SYS_B_3 4 NUMBER 9
1 :SYS_B_4 5 NUMBER 2
1 :SYS_B_5 6 NUMBER 1
0 :SYS_B_0 1 NUMBER 1
0 :SYS_B_1 2 NUMBER 1
0 :SYS_B_2 3 NUMBER 0
0 :SYS_B_3 4 NUMBER 9
0 :SYS_B_4 5 NUMBER 2
0 :SYS_B_5 6 NUMBER 1
12 rows selected
然後給繫結變數賦值
SQL> var a number;
SQL> var b number;
SQL> var c number;
SQL> var d number;
SQL> var e number;
SQL> var f number;
SQL> exec :a:= 1;
PL/SQL procedure successfully completed.
SQL> exec :b:= 1;
PL/SQL procedure successfully completed.
SQL> exec :c:=0;
PL/SQL procedure successfully completed.
SQL> exec :d:=9;
PL/SQL procedure successfully completed.
SQL> exec :e:= 2;
PL/SQL procedure successfully completed.
SQL> exec :f:= 1;
PL/SQL procedure successfully completed.
呼叫set autotrace，獲取其執行計劃以及consistent gets值
SQL> set autotrace traceonly;
SQL> select rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2173875363
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 643 | | 163K (1)| 00:32:42 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 37206 | 22M| | 7029 (2)| 00:01:25 |
| 4 | TABLE ACCESS FULL | justin_cat | 3073 | 192K| | 16 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 37206 | 20M| 8800K| 7012 (2)| 00:01:25 |
|* 6 | TABLE ACCESS FULL | justin_pro | 17771 | 8590K| | 3542 (2)| 00:00:43 |
| 7 | TABLE ACCESS FULL | justin_pro_mer | 262K| 21M| | 1842 (2)| 00:00:23 |
| 8 | COUNT | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| justin_pro_mer | 1 | 15 | | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PRO_ID | 2 | | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(D))
2 - filter("PM"."ID"= (SELECT "ID" FROM "justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND
"P_M"."justin_pro_ID"=:B1 AND "P_M"."CAN_SALE"=TO_NUMBER(A)))
3 - access("C"."ID"(+)="T"."justin_cat_ID")
5 - access("PM"."justin_pro_ID"="T"."ID")
6 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
"T"."justin_pro_TYPE"<>TO_NUMBER(E))
9 - filter(ROWNUM=TO_NUMBER(B))
10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
11 - access("P_M"."justin_pro_ID"=:B1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
29382 consistent gets
6 physical reads
0 redo size
91945 bytes sent via SQL*Net to client
54056 bytes received via SQL*Net from client
38 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
根據執行計劃可以看出，對錶justin_pro_mer做了兩次訪問，其中一次還是全表掃描,而這兩次的訪問卻沒有任何的關聯，即子查詢沒有展開。
子查詢部分的代價很小，可以考慮將其展開處理，採用hint push_subq。
select /*+ push_subq(@tmp) */ rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select /*+QB_Name(tmp)*/ id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
Execution Plan
----------------------------------------------------------
Plan hash value: 17688155
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17771 | 10M| 21355 (1)| 00:04:17 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 17771 | 10M| 21350 (1)| 00:04:17 |
|* 3 | HASH JOIN RIGHT OUTER | | 17771 | 9701K| 3559 (2)| 00:00:43 |
| 4 | TABLE ACCESS FULL | justin_cat | 3073 | 192K| 16 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | justin_pro | 17771 | 8590K| 3542 (2)| 00:00:43 |
|* 6 | TABLE ACCESS BY INDEX ROWID | justin_pro_mer | 1 | 84 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK4 | 1 | | 0 (0)| 00:00:01 |
| 8 | COUNT | | | | | |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| justin_pro_mer | 1 | 15 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PRO_ID | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(D))
3 - access("C"."ID"(+)="T"."justin_cat_ID")
5 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
"T"."justin_pro_TYPE"<>TO_NUMBER(E))
6 - filter("PM"."justin_pro_ID"="T"."ID")
7 - access("PM"."ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("TMP") */ "ID" FROM
"justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND "P_M"."justin_pro_ID"=:B1 AND
"P_M"."CAN_SALE"=TO_NUMBER(A)))
9 - filter(ROWNUM=TO_NUMBER(B))
10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
11 - access("P_M"."justin_pro_ID"=:B1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
702 consistent gets
30 physical reads
0 redo size
81501 bytes sent via SQL*Net to client
45079 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以看到consistent gets由原來的2萬多下降了不到1000。
與push_subq相對的hint是no_unnest，即讓子查詢不展開，在處理多表連線並帶有子查詢的sql中，合理的使用這兩個hint會有非常好的效果，
[ 本帖最後由 myownstars 於 2011-1-24 16:05 編輯 ]
該sql大致結構如下:
select rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
sql最主要的效能指標是consistent gets，而通過explain plan for是得不出的，選用set autotrace traceonly來檢視，sql使用了繫結變數。
首先查出其繫結變數的值
SQL> select s.CHILD_NUMBER,s.NAME,s.POSITION,s.DATATYPE_STRING,s.VALUE_STRING from v$sql_bind_capture s where s.SQL_ID='4kz559dt818vm';
CHILD_NUMBER NAME POSITION DATATYPE_S VALUE_STRI
------------ -------------------- ---------- ---------- ----------
1 :SYS_B_0 1 NUMBER 1
1 :SYS_B_1 2 NUMBER 1
1 :SYS_B_2 3 NUMBER 0
1 :SYS_B_3 4 NUMBER 9
1 :SYS_B_4 5 NUMBER 2
1 :SYS_B_5 6 NUMBER 1
0 :SYS_B_0 1 NUMBER 1
0 :SYS_B_1 2 NUMBER 1
0 :SYS_B_2 3 NUMBER 0
0 :SYS_B_3 4 NUMBER 9
0 :SYS_B_4 5 NUMBER 2
0 :SYS_B_5 6 NUMBER 1
12 rows selected
然後給繫結變數賦值
SQL> var a number;
SQL> var b number;
SQL> var c number;
SQL> var d number;
SQL> var e number;
SQL> var f number;
SQL> exec :a:= 1;
PL/SQL procedure successfully completed.
SQL> exec :b:= 1;
PL/SQL procedure successfully completed.
SQL> exec :c:=0;
PL/SQL procedure successfully completed.
SQL> exec :d:=9;
PL/SQL procedure successfully completed.
SQL> exec :e:= 2;
PL/SQL procedure successfully completed.
SQL> exec :f:= 1;
PL/SQL procedure successfully completed.
呼叫set autotrace，獲取其執行計劃以及consistent gets值
SQL> set autotrace traceonly;
SQL> select rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2173875363
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 643 | | 163K (1)| 00:32:42 |
|* 1 | COUNT STOPKEY | | | | | | |
|* 2 | FILTER | | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 37206 | 22M| | 7029 (2)| 00:01:25 |
| 4 | TABLE ACCESS FULL | justin_cat | 3073 | 192K| | 16 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 37206 | 20M| 8800K| 7012 (2)| 00:01:25 |
|* 6 | TABLE ACCESS FULL | justin_pro | 17771 | 8590K| | 3542 (2)| 00:00:43 |
| 7 | TABLE ACCESS FULL | justin_pro_mer | 262K| 21M| | 1842 (2)| 00:00:23 |
| 8 | COUNT | | | | | | |
|* 9 | FILTER | | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| justin_pro_mer | 1 | 15 | | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PRO_ID | 2 | | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(D))
2 - filter("PM"."ID"= (SELECT "ID" FROM "justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND
"P_M"."justin_pro_ID"=:B1 AND "P_M"."CAN_SALE"=TO_NUMBER(A)))
3 - access("C"."ID"(+)="T"."justin_cat_ID")
5 - access("PM"."justin_pro_ID"="T"."ID")
6 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
"T"."justin_pro_TYPE"<>TO_NUMBER(E))
9 - filter(ROWNUM=TO_NUMBER(B))
10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
11 - access("P_M"."justin_pro_ID"=:B1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
29382 consistent gets
6 physical reads
0 redo size
91945 bytes sent via SQL*Net to client
54056 bytes received via SQL*Net from client
38 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
9 rows processed
根據執行計劃可以看出，對錶justin_pro_mer做了兩次訪問，其中一次還是全表掃描,而這兩次的訪問卻沒有任何的關聯，即子查詢沒有展開。
子查詢部分的代價很小，可以考慮將其展開處理，採用hint push_subq。
select /*+ push_subq(@tmp) */ rownum
t.*,
pm.id
pm.in_price
c.justin_cat__name,
pm.justin_pro_id
from justin_pro t
left join justin_cat c
on c.id = t.justin_cat_id
join justin_pro_mer pm
on pm.justin_pro_id = t.id
and pm.id = (select /*+QB_Name(tmp)*/ id
from justin_pro_mer p_m
where p_m.justin_pro_id = t.id
and p_m.CAN_SALE = :a
and rownum = :b)
where t.is_deleted = :c
and rownum <= :d
and t.justin_pro_type <> :e
and t.is_hot = :f;
Execution Plan
----------------------------------------------------------
Plan hash value: 17688155
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17771 | 10M| 21355 (1)| 00:04:17 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 17771 | 10M| 21350 (1)| 00:04:17 |
|* 3 | HASH JOIN RIGHT OUTER | | 17771 | 9701K| 3559 (2)| 00:00:43 |
| 4 | TABLE ACCESS FULL | justin_cat | 3073 | 192K| 16 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | justin_pro | 17771 | 8590K| 3542 (2)| 00:00:43 |
|* 6 | TABLE ACCESS BY INDEX ROWID | justin_pro_mer | 1 | 84 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK4 | 1 | | 0 (0)| 00:00:01 |
| 8 | COUNT | | | | | |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| justin_pro_mer | 1 | 15 | 5 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IDX_PRO_ID | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=TO_NUMBER(D))
3 - access("C"."ID"(+)="T"."justin_cat_ID")
5 - filter("T"."IS_DELETED"=TO_NUMBER(C) AND "T"."IS_HOT"=TO_NUMBER(F) AND
"T"."justin_pro_TYPE"<>TO_NUMBER(E))
6 - filter("PM"."justin_pro_ID"="T"."ID")
7 - access("PM"."ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("TMP") */ "ID" FROM
"justin_pro_mer" "P_M" WHERE ROWNUM=TO_NUMBER(B) AND "P_M"."justin_pro_ID"=:B1 AND
"P_M"."CAN_SALE"=TO_NUMBER(A)))
9 - filter(ROWNUM=TO_NUMBER(B))
10 - filter("P_M"."CAN_SALE"=TO_NUMBER(A))
11 - access("P_M"."justin_pro_ID"=:B1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
702 consistent gets
30 physical reads
0 redo size
81501 bytes sent via SQL*Net to client
45079 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
可以看到consistent gets由原來的2萬多下降了不到1000。
與push_subq相對的hint是no_unnest，即讓子查詢不展開，在處理多表連線並帶有子查詢的sql中，合理的使用這兩個hint會有非常好的效果，
[ 本帖最後由 myownstars 於 2011-1-24 16:05 編輯 ]
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-688364/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過使用hint unnest調優sql語句SQL
- 用push_subq優化批處理統計SQL優化SQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過新增條件優化SQL優化SQL
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- 透過使用hint unnest調優sql語句SQL
- 通過IP定位區域的SQL優化思路SQL優化
- SQL語句優化方法用hint的30種方法SQL優化
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 使用hint來調優sql語句SQL
- Oracle 通過rowid秒優SQLOracleSQL
- 通過pl/sql來格式化sqlSQL
- HINT篇---優化器相關優化
- sql調優一例---索引排序hintSQL索引排序
- 通過ADDM進行SQL調優SQL
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- 一條sql的優化過程SQL優化
- sql 優化過程之union 替換 orSQL優化
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- Sql最佳化(五) hint（提示）介紹SQL
- ORACLE常用SQL最佳化hint語句OracleSQL
- 通過分割槽進行優化(轉)優化
- MySQL SQL hint 提示MySql
- 通過java來格式化sql語句JavaSQL
- 通過Scope Hoisting優化Webpack輸出優化Web
- 使用USE_HASH Hint調優一個SQL語句SQL
- 通過內部的hint來控制執行計劃
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- 【SQL優化】SQL優化工具SQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- 一條SQL語句的優化過程SQL優化