使用leading(,)優化sql執行計劃
今天在資料庫中捕獲到一條sql，其cost很小但是邏輯讀又極高，sql大致如下
select count(*)
from JUSTIN_REFER u_f
left join JUSTIN u
on u_f.refer_id = u.id
where u.ip =:a;
一般來說對於cost極小但是buffer gets又很高的語句，大多是由統計資訊不準確造成的，
但是這兩個表都是7月份收集的，且兩個表的資料改動都是很平穩的，不應該是統計資訊出錯。
SQL> select table_name,last_analyzed from user_tables where table_name in('JUSTIN_REFER','JUSTIN');
TABLE_NAME LAST_ANALYZED
------------------------------ -------------
JUSTIN 2011-7-27 上午
JUSTIN_REFER 2011-7-2 上午
檢視一下繫結變數的歷史記錄
SQL> select value_string,count(*) from dba_hist_sqlbind where sql_id ='dsc850x8y7can' group by value_string;
VALUE_STRING COUNT(*)
-------------------------------------------------------------------------------- ----------
180.a.b.c 1
222.a.b.c 401
58.a.b.c 1
60.a.b.c 1
218.a.b.c 1
可以看到大部分時候都是選擇222.a.b.c作為繫結值，採用autotrace檢視一下其執行計劃
SQL> set autotrace traceonly
SQL> set linesize 300 pagesize 300
SQL> variable a varchar2(200);
SQL> exec :a := '222.a.b.c';
PL/SQL procedure successfully completed.
SQL> select count(*)
2 from JUSTIN_REFER u_f
3 join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip = :a;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | NESTED LOOPS | | 11 | 209 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_JUSTIN_R_refer_id | 5 | 30 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
4 - access("U"."IP"=:A)
5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2183130 consistent gets
35 physical reads
12572 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
目前JUSTIN表已經很大了，有1000萬條記錄，而當ip值為222.a.b.c時，訪問JUSTIN的代價很大
SQL> select id from JUSTIN where ip = :a;
971191 rows selected.
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IP"=:A)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
350669 consistent gets
0 physical reads
0 redo size
18780090 bytes sent via SQL*Net to client
712693 bytes received via SQL*Net from client
64748 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
971191 rows processed
我們可以看到，上面這個單表查詢語句返回971191行，邏輯讀就有35萬多，無怪乎整個sql的邏輯讀如此之大；
再來看一下表JUSTIN_REFER的資料分佈
SQL> select count(*), count(distinct refer_id) from JUSTIN_REFER;
COUNT(*) COUNT(DISTINCTrefer_id)
---------- --------------------------
418756 89276
該表總共也就40萬條記錄，且refer_id的選擇性極佳。
現在我們可以嘗試通過加hint來優化該語句，把JUSTIN_REFER作為驅動表，將其過濾後的資料再與JUSTIN表關聯；
SQL> select /*+ leading(u_f,u) */ count(*)
2 from JUSTIN_REFER u_f
3 left join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip =:a;
COUNT(*)
----------
172625
Execution Plan
----------------------------------------------------------
Plan hash value: 3468976922
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | | 782 (2)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 19 | | | |
|* 2 | HASH JOIN | | 11 | 209 | 7048K| 782 (2)| 00:00:10 |
| 3 | INDEX FAST FULL SCAN | IDX_JUSTIN_R_refer_id | 400K| 2348K| | 427 (2)| 00:00:06 |
| 4 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("U_F"."refer_id"="U"."ID")
5 - access("U"."IP"=:A)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
238142 consistent gets
0 physical reads
80 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀原來的2183130下降為238142，代價下降為原來的1/10;
但此時訪問justin表還是選擇了ip欄位上的索引，再新增hint強制其選擇id主鍵
SQL> select /*+ leading(u_f,u) index(u,pk_justin) */ count(*)
2 from JUSTIN_REFER u_f
3 left join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip = :a;
Execution Plan
----------------------------------------------------------
Plan hash value: 3610563294
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 403K (1)| 01:20:45 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | NESTED LOOPS | | 11 | 209 | 403K (1)| 01:20:45 |
| 3 | INDEX FAST FULL SCAN | IDX_JUSTIN_R_refer_id | 400K| 2348K| 427 (2)| 00:00:06 |
|* 4 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_JUSTIN | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("U"."IP"=:A)
5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1277827 consistent gets
6 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此時的邏輯讀又升高到1277827，幾次嘗試過後，最終只新增了/*+ leading(u_f,u) */，最後的sql為
select /*+ leading(u_f,u) */ count(*)
from JUSTIN_REFER u_f
left join JUSTIN u
on u_f.refer_id = u.id
where u.ip =:a;
select count(*)
from JUSTIN_REFER u_f
left join JUSTIN u
on u_f.refer_id = u.id
where u.ip =:a;
一般來說對於cost極小但是buffer gets又很高的語句，大多是由統計資訊不準確造成的，
但是這兩個表都是7月份收集的，且兩個表的資料改動都是很平穩的，不應該是統計資訊出錯。
SQL> select table_name,last_analyzed from user_tables where table_name in('JUSTIN_REFER','JUSTIN');
TABLE_NAME LAST_ANALYZED
------------------------------ -------------
JUSTIN 2011-7-27 上午
JUSTIN_REFER 2011-7-2 上午
檢視一下繫結變數的歷史記錄
SQL> select value_string,count(*) from dba_hist_sqlbind where sql_id ='dsc850x8y7can' group by value_string;
VALUE_STRING COUNT(*)
-------------------------------------------------------------------------------- ----------
180.a.b.c 1
222.a.b.c 401
58.a.b.c 1
60.a.b.c 1
218.a.b.c 1
可以看到大部分時候都是選擇222.a.b.c作為繫結值，採用autotrace檢視一下其執行計劃
SQL> set autotrace traceonly
SQL> set linesize 300 pagesize 300
SQL> variable a varchar2(200);
SQL> exec :a := '222.a.b.c';
PL/SQL procedure successfully completed.
SQL> select count(*)
2 from JUSTIN_REFER u_f
3 join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip = :a;
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | NESTED LOOPS | | 11 | 209 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | 5 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_JUSTIN_R_refer_id | 5 | 30 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
4 - access("U"."IP"=:A)
5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
2183130 consistent gets
35 physical reads
12572 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
目前JUSTIN表已經很大了，有1000萬條記錄，而當ip值為222.a.b.c時，訪問JUSTIN的代價很大
SQL> select id from JUSTIN where ip = :a;
971191 rows selected.
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 26 | 5 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("IP"=:A)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
350669 consistent gets
0 physical reads
0 redo size
18780090 bytes sent via SQL*Net to client
712693 bytes received via SQL*Net from client
64748 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
971191 rows processed
我們可以看到，上面這個單表查詢語句返回971191行，邏輯讀就有35萬多，無怪乎整個sql的邏輯讀如此之大；
再來看一下表JUSTIN_REFER的資料分佈
SQL> select count(*), count(distinct refer_id) from JUSTIN_REFER;
COUNT(*) COUNT(DISTINCTrefer_id)
---------- --------------------------
418756 89276
該表總共也就40萬條記錄，且refer_id的選擇性極佳。
現在我們可以嘗試通過加hint來優化該語句，把JUSTIN_REFER作為驅動表，將其過濾後的資料再與JUSTIN表關聯；
SQL> select /*+ leading(u_f,u) */ count(*)
2 from JUSTIN_REFER u_f
3 left join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip =:a;
COUNT(*)
----------
172625
Execution Plan
----------------------------------------------------------
Plan hash value: 3468976922
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | | 782 (2)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 19 | | | |
|* 2 | HASH JOIN | | 11 | 209 | 7048K| 782 (2)| 00:00:10 |
| 3 | INDEX FAST FULL SCAN | IDX_JUSTIN_R_refer_id | 400K| 2348K| | 427 (2)| 00:00:06 |
| 4 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 2 | 26 | | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_JUSTIN_IP | 2 | | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("U_F"."refer_id"="U"."ID")
5 - access("U"."IP"=:A)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
238142 consistent gets
0 physical reads
80 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
邏輯讀原來的2183130下降為238142，代價下降為原來的1/10;
但此時訪問justin表還是選擇了ip欄位上的索引，再新增hint強制其選擇id主鍵
SQL> select /*+ leading(u_f,u) index(u,pk_justin) */ count(*)
2 from JUSTIN_REFER u_f
3 left join JUSTIN u
4 on u_f.refer_id = u.id
5 where u.ip = :a;
Execution Plan
----------------------------------------------------------
Plan hash value: 3610563294
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 403K (1)| 01:20:45 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | NESTED LOOPS | | 11 | 209 | 403K (1)| 01:20:45 |
| 3 | INDEX FAST FULL SCAN | IDX_JUSTIN_R_refer_id | 400K| 2348K| 427 (2)| 00:00:06 |
|* 4 | TABLE ACCESS BY INDEX ROWID| JUSTIN | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_JUSTIN | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("U"."IP"=:A)
5 - access("U_F"."refer_id"="U"."ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1277827 consistent gets
6 physical reads
0 redo size
517 bytes sent via SQL*Net to client
487 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此時的邏輯讀又升高到1277827，幾次嘗試過後，最終只新增了/*+ leading(u_f,u) */，最後的sql為
select /*+ leading(u_f,u) */ count(*)
from JUSTIN_REFER u_f
left join JUSTIN u
on u_f.refer_id = u.id
where u.ip =:a;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-703270/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 轉摘＿使用leading(,)優化sql執行計劃優化SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- 【優化】Oracle 執行計劃優化Oracle
- SQL優化案例-從執行計劃定位SQL問題（三）SQL優化
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 【優化】ORACLE執行計劃分析優化Oracle
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 使用sql profile固定執行計劃SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- Oracle優化案例-從執行計劃定位SQL問題（三）Oracle優化SQL
- 【sql調優之執行計劃】sort operationsSQL
- 【sql調優之執行計劃】estimator iSQL
- 【sql調優之執行計劃】hash joinSQL
- sql 執行計劃SQL
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- 使用PL/SQL檢視執行計劃SQL
- 加hint改變執行計劃訪問順序優化sql優化SQL
- 【sql調優之執行計劃】query transformerSQLORM
- 【sql調優之執行計劃】temp table transformationSQLORM
- SQL最佳化 —— 讀懂執行計劃SQL
- sql最佳化：使用儲存提綱穩定sql執行計劃SQL
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 【sql調優之執行計劃】in相關的operationSQL
- 【sql調優之執行計劃】merge sort joinSQL
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- MySQL 5.7 優化不能只看執行計劃MySql優化
- 【效能優化】執行計劃與直方圖優化直方圖
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 使用spm繫結執行計劃來線上優化資料庫優化資料庫
- Oracle 'or exists/in'結合使用引起的filter執行計劃 的優化OracleFilter優化