通過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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 通過新增條件優化SQL優化SQL
- Oracle中的sql hintOracleSQL
- openGauss 支援SQL-hintSQL
- [20200801]sql hint衝突.txtSQL
- 通過Scope Hoisting優化Webpack輸出優化Web
- [20190430]注意sql hint寫法.txtSQL
- 通過程式找sqlSQL
- sql優化之邏輯優化SQL優化
- 資料庫優化 - SQL優化資料庫優化SQL
- SQL SERVER優化SQLServer優化
- SQL優化指南SQL優化
- 宜信DBA實踐-SQL優化|一篇文章說清楚Oracle Hint的正確使用姿勢SQL優化Oracle
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- 通過自動化和現代化實現網路優化優化
- [20200718]注意sql hint寫法2.txtSQL
- Spark SQL 教程： 通過示例瞭解 Spark SQLSparkSQL
- SQL優化參考SQL優化
- sql優化專題SQL優化
- SQL語句優化SQL優化
- SQL效能優化技巧SQL優化
- MySQL-SQL優化MySql優化
- 慢Sql優化思路SQL優化
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- SQL優化案例-使用with as優化Subquery Unnesting（七）SQL優化
- 通過 ProxySQL 在 TiDB 上實現 SQL 的規則化路由SQLTiDB路由
- 資料庫優化SQL資料庫優化SQL
- MYSQL SQL語句優化MySql優化
- SQL優化之limit 1SQL優化MIT
- 優化sql查詢速度優化SQL
- (轉)SQL 優化原則SQL優化
- SQL優化的方法論SQL優化
- 史上最全SQL優化方案SQL優化
- MySQL SQL優化案例(一)MySql優化
- MySQL之SQL優化技巧MySql優化
- [20201224]sql優化困惑.txtSQL優化
- sql語句效能優化SQL優化