[20171225]檢視並行執行計劃注意的問題.txt
[20171225]檢視並行執行計劃注意的問題.txt
--//如果使用dbms_xplan.display_cursor檢視並行執行計劃注意一些問題,透過例子說明:
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試:
SCOTT@book> create table t1 as select * from dba_objects ;
Table created.
SCOTT@book> alter session set statistics_level=all;
Session altered.
--//分析表略.
SCOTT@book> select /*+ parallel(t1,4) */ count(*) from t1;
COUNT(*)
----------
87016
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6yhkc72j9mnnt, child number 0
-------------------------------------
select /*+ parallel(t1,4) */ count(*) from t1
Plan hash value: 3110199320
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 96 (100)| | | | | 1 |00:00:00.02 | 5 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | | 1 |00:00:00.02 | 5 | 1 |
| 2 | PX COORDINATOR | | 1 | | | | | | | 4 |00:00:00.02 | 5 | 1 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 | | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWC | | 0 |00:00:00.01 | 0 | 0 |
|* 6 | TABLE ACCESS FULL| T1 | 0 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 | 0 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
--//全表掃描,但是注意看A-Rows實際上根本不對.看到是0行.而E-Rows看到是正確的.
SCOTT@book> select * from table(dbms_xplan.display_cursor('6yhkc72j9mnnt',NULL,'ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline parallel'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6yhkc72j9mnnt, child number 0
-------------------------------------
select /*+ parallel(t1,4) */ count(*) from t1
Plan hash value: 3110199320
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 96 (100)| | | | 1 |00:00:00.11 | 5 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | 1 |00:00:00.11 | 5 |
| 2 | PX COORDINATOR | | 1 | | | | | | 4 |00:00:00.11 | 5 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE | | 0 | 1 | | Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 | 87016 | 96 (0)| Q1,00 | PCWC | | 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL| T1 | 0 | 87016 | 96 (0)| Q1,00 | PCWP | | 0 |00:00:00.01 | 0 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
--//加入parallel提示也是一樣.
--//連結:raajeshwaran.blogspot.com/2017/12/gatherplanstatistics-hint-for-parallel.html
In a parallel execution the last process to execute the cursor is the Query coordinator (QC), typically this QC will
execute a small number of operations in the execution plan, while the majority of the operations in the plan was done by
the parallel execution server process. So when we issue the DBMS_XPLAN.DISPLAY_CURSOR and ask for the last execution we
only get the information about the operations in the plan that the QC actually executed. In this case the only operation
that QC did was return the final result to our SQL*Plus session, which is why the line 0 and 1 and 2 have entries in the
A-rows column.
In order to see A-rows values for all the operations in the plan, we have to use the FORMAT value as ALLSTATS ALL, which
will show you the execution statistics for ALL executions of the cursor.
SCOTT@book> select * from table(dbms_xplan.display_cursor('6yhkc72j9mnnt',NULL,'ALLSTATS ALL PEEKED_BINDS cost partition -projection -outline parallel'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6yhkc72j9mnnt, child number 0
-------------------------------------
select /*+ parallel(t1,4) */ count(*) from t1
Plan hash value: 3110199320
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 96 (100)| | | | | 2 |00:00:00.13 | 10 | 1 |
| 1 | SORT AGGREGATE | | 2 | 1 | | | | | | 2 |00:00:00.13 | 10 | 1 |
| 2 | PX COORDINATOR | | 2 | | | | | | | 8 |00:00:00.13 | 10 | 1 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 |
| 4 | SORT AGGREGATE | | 7 | 1 | | | Q1,00 | PCWP | | 7 |00:00:00.10 | 2265 | 2179 |
| 5 | PX BLOCK ITERATOR | | 8 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWC | | 152K|00:00:00.09 | 2590 | 2486 |
|* 6 | TABLE ACCESS FULL| T1 | 104 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWP | | 174K|00:00:00.04 | 2590 | 2486 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
--//而這裡看到的A-Rows實際上多次執行後的累積,並不能反應真實的情況.使用引數all的情況導致的結果.
--//加入提示,生成新的執行計劃:
SCOTT@book> select /*+ parallel(t1,4) test */ count(*) from t1;
COUNT(*)
----------
87016
SCOTT@book> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS ALL PEEKED_BINDS cost partition -projection -outline parallel'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6a3vj021614ft, child number 0
-------------------------------------
select /*+ parallel(t1,4) test */ count(*) from t1
Plan hash value: 3110199320
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | TQ |IN-OUT| PQ Distrib | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 96 (100)| | | | | 1 |00:00:00.11 | 5 | 0 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | | | | 1 |00:00:00.11 | 5 | 0 |
| 2 | PX COORDINATOR | | 1 | | | | | | | 4 |00:00:00.11 | 5 | 0 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 0 | 1 | | | Q1,00 | P->S | QC (RAND) | 0 |00:00:00.01 | 0 | 0 |
| 4 | SORT AGGREGATE | | 4 | 1 | | | Q1,00 | PCWP | | 4 |00:00:00.06 | 1295 | 1243 |
| 5 | PX BLOCK ITERATOR | | 4 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWC | | 87016 |00:00:00.05 | 1295 | 1243 |
|* 6 | TABLE ACCESS FULL| T1 | 52 | 87016 | 96 (0)| 00:00:02 | Q1,00 | PCWP | | 87016 |00:00:00.02 | 1295 | 1243 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z<=:Z)
--//這樣看到的執行計劃才是比較真實的數值.
--//我的dpc.sql指令碼如下:
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
--//我寫的指令碼也存在問題,不過最後的last掩蓋前面all引數的設定.^_^.
3.總結:
--//在設定statistics_level=all;或者提示gather_plan_statistics時,看到的並行執行計劃要特別注意.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2149221/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210114]toad檢視真實執行計劃問題.txt
- [20210926]並行執行計劃疑問.txt並行
- [20210205]toad檢視真實執行計劃問題3.txt
- [20230130]toad看執行計劃注意.txt
- 檢視 OceanBase 執行計劃
- 如何檢視SQL的執行計劃SQL
- Oracle檢視執行計劃的命令Oracle
- 執行計劃-2：檢視更多的資訊
- Oracle“並行執行”——監控檢視Oracle並行
- [20240313]toad gather_plan_statistics執行計劃相關問題.txt
- Oracle如何檢視真實執行計劃（一）Oracle
- [20190111]執行計劃bitmap and.txt
- 檢視SQL執行計劃的幾種常用方法YQSQL
- Oracle檢視sql_id 的歷史執行計劃OracleSQL
- 檢視一個正在執行的sql的執行計劃(explain for connection processlist_id)SQLAI
- 使用set autotrace on 檢視資料庫執行計劃資料庫
- [20191220]格式化執行計劃.txt
- [20190111]執行計劃走位與.txt
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- [20181120]toad看真實的執行計劃.txt
- [20181201]奇怪的INDEX SKIP SCAN執行計劃.txtIndex
- [20230224]ssh date執行問題.txt
- 達夢資料庫SQL執行計劃檢視方法資料庫SQL
- 檢視執行計劃出現ORA-22992錯誤
- 執行計劃-1：獲取執行計劃
- 微課sql最佳化(11) 、如何檢視執行計劃SQL
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- [20190527]注意表與索引的並行屬性.txt索引並行
- [20231210]執行計劃與繫結變數.txt變數
- [20190219]xargs -P實現並行執行.txt並行
- 關於物件導向的方法並行執行的問題物件並行
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- [20220414]toad呼叫執行指令碼問題.txt指令碼
- [20230921]為什麼執行計劃不再awr中.txt
- [20221104]執行計劃一樣Plan hash value不同.txt
- [20190720]12cR2顯示執行計劃.txt
- 如何進行直播軟體搭建計劃制定，需要注意那些問題？
- 執行緒問題2(注意例項變數)執行緒變數