sql tuning gather_plan_statistics與filter和執行計劃一點測試

wisdomone1發表於2015-06-27

測試結論

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稽核專案
          深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式:
手機:18201115468
qq   :   305076427
qq微博: wisdomone1
新浪微博:wisdomone9
qq群:275813900    
itpub部落格名稱:wisdomone1    http://blog.itpub.net/9240380/


來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1714408/,如需轉載,請註明出處,否則將追究法律責任。

相關文章