[20171225]查看并行执行计划注意的问题.txt

转载 于 2017-12-26 10:29:00 发布 · 71 阅读 · 0 ·
CC 4.0 BY-SA版权
原文链接：http://www.cnblogs.com/lfree/p/8116717.html
文章标签：

#数据库

本文探讨了在Oracle数据库中使用并行查询时如何正确地获取执行计划统计信息。具体介绍了使用DBMS_XPLAN.DISPLAY_CURSOR的不同参数选项来查看并行执行计划，并解释了不同参数设置下观察到的数据差异。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

[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时,看到的并行执行计划要特别注意.

转载于:https://www.cnblogs.com/lfree/p/8116717.html

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值