[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时,看到的并行执行安排要专门注意.

相关文章