Had discussed enough about the gather_plan_statistics
hint to validate the cardinality estimates, however this need to be revisited
for Parallel sql execution in the database.
demo@ORA12C> alter session set statistics_level=all;
Session altered.
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from
big_table b;
COUNT(*)
----------
1000000
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS
LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID gqr17qtmdmsmq,
child number 0
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
Plan hash value: 2894119656
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | | 1 |
| 1 |00:00:01.97 | 20 |
| 1 | SORT AGGREGATE | | 1 |
1 | 1 |00:00:01.97 | 20 |
| 2 | PX COORDINATOR | | 1 |
| 4 |00:00:01.97 | 20 |
| 3 | PX SEND QC (RANDOM) | :TQ10000 |
0 | 1 | 0 |00:00:00.01 | 0 |
| 4 | SORT AGGREGATE |
| 0 | 1 |
0
|00:00:00.01 | 0 |
| 5 | PX BLOCK ITERATOR | | 0 |
941K| 0 |00:00:00.01 | 0 |
|* 6 | TABLE ACCESS FULL| BIG_TABLE | 0 |
941K| 0 |00:00:00.01 | 0 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z
AND :Z<=:Z)
Note
-----
- dynamic statistics
used: dynamic sampling (level=2)
- Degree of Parallelism
is 4 because of table property
28 rows selected.
demo@ORA12C>
As you can see from the parallel execution plan the majority of the
rows in the plan have 0 in the A-rows column, this is caused by the nature of
parallel execution and how it interacts with the FORMAT we requested in the
DBMS_XPLAN.DISPLAY_CURSOR method. The FORMAT parameter value ‘ALLSTATS LAST’
request the execution statistics of the last execution of the cursor be shown.
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.
demo@ORA12C> set linesize 250
demo@ORA12C> alter session set statistics_level=all;
Session altered.
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from
big_table b;
COUNT(*)
----------
1000000
demo@ORA12C> select * from
table(dbms_xplan.display_cursor(format=>'ALLSTATS
ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
SQL_ID gqr17qtmdmsmq,
child number 1
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
Plan hash value: 2894119656
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
| 1421 (100)| | |
| | 1 |00:00:03.74
| 20 | 0 |
| 1 |
SORT AGGREGATE | | 1 |
1 | | | |
| | 1 |00:00:03.74
| 20 | 0 |
| 2 |
PX COORDINATOR | | 1 |
| | | |
| | 4 |00:00:03.74 | 20 |
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:14.84
| 19247 | 18570 |
| 5 |
PX BLOCK ITERATOR |
| 4 | 1000K|
1421 (1)| 00:00:01 | Q1,00 | PCWC | | 1000K|00:00:14.83 | 19247 |
18570 |
|* 6 |
TABLE ACCESS FULL| BIG_TABLE |
55 | 1000K| 1421
(1)| 00:00:01 | Q1,00 | PCWP
| | 1000K|00:00:14.83 | 19247 |
18570 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / B@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z
AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0)
COUNT()[22]
2 - SYS_OP_MSR()[10]
3 - (#keys=0)
SYS_OP_MSR()[10]
4 - (#keys=0)
SYS_OP_MSR()[10]
5 - (rowset=1019)
6 - (rowset=1019)
Note
-----
- Degree of Parallelism
is 4 because of table property
43 rows selected.
demo@ORA12C>
We should be very careful about the A-Rows listed here,
since those values listed in the execution plan will be cumulative (incremented
on every execution)
Running the same sql three times in the session, produced
the output (cumulative result in A-Rows columns) like this.
demo@ORA12C> alter session set statistics_level=all;
Session altered.
demo@ORA12C> set serveroutput off
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from
big_table b;
COUNT(*)
----------
1000000
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from
big_table b;
COUNT(*)
----------
1000000
demo@ORA12C> select /*+ parallel(b,4) */ count(*) from
big_table b;
COUNT(*)
----------
1000000
demo@ORA12C> select * from
table(dbms_xplan.display_cursor(format=>'ALLSTATS ALL'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------
SQL_ID gqr17qtmdmsmq,
child number 0
-------------------------------------
select /*+ parallel(b,4) */ count(*) from big_table b
Plan hash value: 2894119656
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)|
E-Time | TQ
|IN-OUT| PQ Distrib | A-Rows |
A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 |
| 1421 (100)| | |
| | 3 |00:00:08.27 | 62 |
0 |
| 1 |
SORT AGGREGATE | | 3 |
1 | | | |
| | 3 |00:00:08.27 | 62 |
0 |
| 2 |
PX COORDINATOR | | 3 |
| | | |
| | 12 |00:00:08.27 | 62 |
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 | |
11 | 1 | | |
Q1,00 | PCWP | | 11 |00:00:29.82 | 53474 |
51594 |
| 5 |
PX BLOCK ITERATOR |
| 12 | 1000K| 1421
(1)| 00:00:01 | Q1,00 | PCWC
| | 3000K|00:00:32.11
| 57739 | 55710 |
|* 6 |
TABLE ACCESS FULL| BIG_TABLE |
151 | 1000K| 1421
(1)| 00:00:01 | Q1,00 | PCWP
| | 2758K|00:00:29.19
| 52765 | 51203 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
6 - SEL$1 / B@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z
AND :Z<=:Z)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0)
COUNT()[22]
2 - SYS_OP_MSR()[10]
3 - (#keys=0)
SYS_OP_MSR()[10]
4 - (#keys=0)
SYS_OP_MSR()[10]
5 - (rowset=1019)
6 - (rowset=1019)
Note
-----
- Degree of Parallelism
is 4 because of table property
43 rows selected.
demo@ORA12C>
No comments:
Post a Comment