sql tuning gather_plan_statistics與filter和執行計劃一點測試
測試結論
1,每次執行附加gather_plan_statistics hint，執行計劃的a-rows相關指標一直在遞增
原因在於這個a_rows與starts相關,starts即執行計劃當前步驟的累計執行次數，
故此，SQL執行多次，starts會一直遞增，所以a_rows也會隨之遞增
2,透過gather_plan_statistics hint的starts可以清晰知道當前步驟執行的次數，
如果基於hash join,merge join,nested loop，可以更好了解這些2表關聯方式的執行機制
這樣就可以知道到底SQL執行計劃哪步有問題，進而採取針對性的措施
3, select * from table(dbms_xplan.display_cursor(null,null,'iostats’))中的最後引數遠項iostats或者其它引數值
allstats last是返回SQL上次執行的相關效能指標
iostats是返回SQL累積執行的相關效能指標
最終呈現的形式不同，具體請參見官方手冊
4,透過exists關聯2表，預設子查詢展開，直接2表關聯
如強制在子查詢內表使用UN_UNNEST，則二表關聯後採用FILTER
測試明細
SQL> create table t_s1(a int,b int);
Table created.
SQL> create table t_s2(a int,b int);
Table created.
SQL> insert into t_s1 select level,level from dual connect by level<=3;
3 rows created.
SQL> insert into t_s2 select level,level from dual connect by level<=3;
3 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_s1 where b in (select b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from t_s1 where b in (select b from t_s2);
Execution Plan
----------------------------------------------------------
Plan hash value: 3214991949
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 3 | 117 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_S1 | 3 | 78 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_S2 | 3 | 39 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"="B")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> select * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
Execution Plan
----------------------------------------------------------
Plan hash value: 25546918
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 78 | 6 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_S1 | 3 | 78 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_S2 | 1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T_S2" "T_S2"
WHERE "B"=:B1))
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
653 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 1
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | | 33 |00:00:00.01 | 319 |
|* 1 | FILTER | | 11 | | 33 |00:00:00.01 | 319 |
| 2 | TABLE ACCESS FULL| T_S1 | 11 | 3 | 33 |00:00:00.01 | 88 |
|* 3 | TABLE ACCESS FULL| T_S2 | 33 | 1 | 33 |00:00:00.01 | 231 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 1
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | | 36 |00:00:00.01 | 348 |
|* 1 | FILTER | | 12 | | 36 |00:00:00.01 | 348 |
| 2 | TABLE ACCESS FULL| T_S1 | 12 | 3 | 36 |00:00:00.01 | 96 |
|* 3 | TABLE ACCESS FULL| T_S2 | 36 | 1 | 36 |00:00:00.01 | 252 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
26 rows selected.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 6 |00:00:00.01 | 58 |
|* 1 | FILTER | | 2 | | 6 |00:00:00.01 | 58 |
| 2 | TABLE ACCESS FULL| T_S1 | 2 | 3 | 6 |00:00:00.01 | 16 |
|* 3 | TABLE ACCESS FULL| T_S2 | 6 | 1 | 6 |00:00:00.01 | 42 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL>
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select /*+ no_unnest */ b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 4fkkrpdatmz7g, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select
/*+ no_unnest */ b from t_s2)
Plan hash value: 25546918
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 29 |
|* 1 | FILTER | | 1 | | 3 |00:00:00.01 | 29 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 21 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - filter("B"=:B1)
22 rows selected.
SQL>
SQL> select /*+ gather_plan_statisitics */ * from t_s1 where b in (select b from t_s2);
A B
---------- ----------
1 1
2 2
3 3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID chru42r8468j5, child number 0
-------------------------------------
select /*+ gather_plan_statisitics */ * from t_s1 where b in (select b
from t_s2)
Plan hash value: 3214991949
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 15 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 3 | 3 |00:00:00.01 | 15 | 1306K| 1306K| 631K (0)|
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
| 3 | TABLE ACCESS FULL| T_S2 | 1 | 3 | 3 |00:00:00.01 | 8 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"="B")
21 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9jt3m5g0368rd, child number 0
-------------------------------------
select /*+ gather_plan_statisitics use_nl(t_s1,t_s2) */ t_s1.* from
t_s1,t_s2 where t_s1.b=t_s2.b
Plan hash value: 1809320615
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 30 |
| 1 | NESTED LOOPS | | 1 | 3 | 3 |00:00:00.01 | 30 |
| 2 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL| T_S2 | 3 | 1 | 3 |00:00:00.01 | 22 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T_S1"."B"="T_S2"."B")
21 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d984hsskza450, child number 0
-------------------------------------
select /*+ gather_plan_statisitics use_merge(t_s1,t_s2) */ t_s1.* from
t_s1,t_s2 where t_s1.b=t_s2.b
Plan hash value: 2917195530
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 |00:00:00.01 | 14 | | | |
| 1 | MERGE JOIN | | 1 | 3 | 3 |00:00:00.01 | 14 | | | |
| 2 | SORT JOIN | | 1 | 3 | 3 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS FULL| T_S1 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
|* 4 | SORT JOIN | | 3 | 3 | 3 |00:00:00.01 | 7 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| T_S2 | 1 | 3 | 3 |00:00:00.01 | 7 | | | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T_S1"."B"="T_S2"."B")
filter("T_S1"."B"="T_S2"."B")
24 rows selected.
個人簡介
8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶：
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1714408/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用SQL Profile及SQL Tuning Advisor固定執行計劃SQL
- Oracle 同一sql語句在測試和開發環境不同的執行計劃OracleSQL開發環境
- Oracle 執行計劃中access 和 filter的區別OracleFilter
- sql 執行計劃SQL
- Oracle中SQL調優（SQL TUNING）之最權威獲取SQL執行計劃大全OracleSQL
- teprunner測試平臺測試計劃批量執行用例
- 控制執行計劃之-SQL Profile（一）SQL
- Oracle sql執行計劃OracleSQL
- SQL Server執行計劃SQLServer
- SQL的執行計劃SQL
- SQL執行計劃分析SQL
- 【sql調優之執行計劃】獲取執行計劃SQL
- sqlprofile繫結執行計劃實驗測試SQL
- sql執行計劃變更和刪除快取中執行計劃的方法SQL快取
- 測試計劃和測試報告測試報告
- 獲取SQL執行計劃SQL
- SQL 執行計劃案例1SQL
- 剖析SQL Server執行計劃SQLServer
- 檢視sql執行計劃SQL
- 演算法題-測試用例執行計劃演算法
- 軟體測試計劃與測試方案
- 測試平臺系列(74) 測試計劃定時執行初體驗
- 分割槽索引（Partition Index）與SQL執行計劃（中）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（下）索引IndexSQL
- 分割槽索引（Partition Index）與SQL執行計劃（上）索引IndexSQL
- sql執行計劃是否改變SQL
- sql的執行計劃 詳解SQL
- sql執行計劃基本命令SQL
- SQL PROFILE修改固定執行計劃SQL
- 使用sql profile固定執行計劃SQL
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- 手工執行sql tuning advisor和sql access advisorSQL
- Oracle中檢視已執行sql的執行計劃OracleSQL
- 測試方案和測試計劃的區別
- toad與執行計劃
- 決定一個SQL執行效率的是執行計劃, 而不是SQL的寫法SQL
- 如何檢視SQL的執行計劃SQL
- 檢視SQL的執行計劃方法SQL