Blog

DOP and explain plan notes

Share this article

Earlier this week I was discussing some parallel server parameters with a client, when I noticed something strange.
It looked like Oracle was upholding the parallel slave limits set in the parallel_degree_limit parameter, while the parallel_degree_policy was set to manual.

According to the documentation, this parameter should not be active with the parallel_degree_policy set to “MANUAL”

A numeric value for this parameter specifies the maximum degree of parallelism the optimizer can choose for a SQL statement when automatic degree of parallelism is active. Automatic degree of parallelism is only enabled if PARALLEL_DEGREE_POLICY is set to ADAPTIVE, AUTO, or LIMITED.

For this test I first created a test table on which I faked the cbo stats to make it look big.
The parallel clause on this table was set to default to trigger parallel query and let Oracle decide on the number of slaves (parallel policy is set to manual, remember).

I increased the parallel_threads_per_cpu to bump up the internal hard limit on the number of slaves on my small test vm (see https://blogs.oracle.com/datawarehousing/entry/parallel_degree_limit_parallel_max).
The parallel_degree_limit was set to 10

fdh@MYTDB> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE	12.1.0.2.0	Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

fdh@MYTDB> 
fdh@MYTDB> -- note, the parallel_threads_per_cpu needed to be inflated to bump up the fixed max 
      parallel slaves limit of cpu_count * parallel threads per cpu
fdh@MYTDB> -- see https://blogs.oracle.com/datawarehousing/entry/parallel_degree_limit_parallel_max
fdh@MYTDB> alter system set parallel_threads_per_cpu = 30;

System altered.

fdh@MYTDB> 
fdh@MYTDB> -- create the test table and make it look big
fdh@MYTDB> 
fdh@MYTDB> drop table T1 purge;

Table dropped.

fdh@MYTDB> 
fdh@MYTDB> create table T1 as select * from all_tables;

Table created.

fdh@MYTDB> alter table T1 parallel;

Table altered.

fdh@MYTDB> 
fdh@MYTDB> exec dbms_stats.set_table_stats(user, 'T1', numrows => 1000000000000, numblks => 1000000000);

PL/SQL procedure successfully completed.

fdh@MYTDB> 
fdh@MYTDB> 
fdh@MYTDB> --- setup the parallel environment
fdh@MYTDB> alter session set parallel_degree_policy=manual;

Session altered.

fdh@MYTDB> alter session set parallel_degree_limit=10;

Session altered.

fdh@MYTDB> 
fdh@MYTDB> 
fdh@MYTDB> -- show the current settings
fdh@MYTDB> show parameter parallel

name                                 TYPE        value
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      10
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     226
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     226
parallel_threads_per_cpu             integer     30
recovery_parallelism                 integer     0
fdh@MYTDB> show parameter cpu_count

name                                 TYPE        value
------------------------------------ ----------- ------------------------------
cpu_count                            integer     2

With a cpu count of 2, parallel_threads_per_cpu set to 30 and the parallel property of the table set to default, I would expect 60 slaves to be used by a query.
But….

fdh@MYTDB> explain plan for select count(*) from t1;

Explained.

fdh@MYTDB> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  5078K  (2)| 00:03:19 |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000G|  5078K  (2)| 00:03:19 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| T1       |  1000G|  5078K  (2)| 00:03:19 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Note
-----
   - Degree of Parallelism is 10 because of table property

17 rows selected.

hmm, could the documentation be wrong, or is explain plan lying?
For neither this would be a first, so time to dig a little bit deeper.

fdh@MYTDB> -- proof of the pudding ....
fdh@MYTDB> select count(*) from t1;

  COUNT(*)
----------
      1851

fdh@MYTDB> 
fdh@MYTDB> select * from v$pq_sesstat;

STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized                    1            16          0
DML Parallelized                        0             0          0
DDL Parallelized                        0             0          0
DFO Trees                               1            16          0
Server Threads                         60             0          0
Allocation Height                      60             0          0
Allocation Width                        1             0          0
Local Msgs Sent                       320          4580          0
Distr Msgs Sent                         0             0          0
Local Msgs Recv'd                     320          4580          0
Distr Msgs Recv'd                       0             0          0
DOP                                    60             0          0
Slave Sets                              1             0          0

13 rows selected.

60 slaves, not 10.
So it seems that the documentation was right and the execution plan notes are wrong.

What does the execution plan for the cursor tell us?

fdh@MYTDB> -- check if the same behaviour is shown when checking the execution plan for the cursor
fdh@MYTDB> set serveroutput off
fdh@MYTDB> select /* force parse */ count(*) from t1;

  COUNT(*)
----------
      1851

fdh@MYTDB> 
fdh@MYTDB> select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID  492vx1hjarj83, child number 1
-------------------------------------
select /* force parse */ count(*) from t1

Plan hash value: 3110199320

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |  5078K(100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000G|  5078K  (2)| 00:03:19 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T1       |  1000G|  5078K  (2)| 00:03:19 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 10 because of table property


27 rows selected.

Same problem.
Other tests also showed the same behavior when using a parallel hint (without specifying the number of slaves), and that sql monitor does show the correct DOP.

Conclusion is that the DOP reported by explain plan is wrongly limited to the parallel_degree_limit set.
Something that would go unnoticed if this parameter was left to it’s default of ‘CPU’.

Note that in 11.2, explain plan does not report on the DOP when the parallel_degree_policy is set to ‘MANUAL’.

I have also tested this on a 12c R2 beta and the problem is still exists.

Tags: Blog
Oracle parallel hint
The easiest database appliance becomes faster, cheaper and more flexible!

You May Also Like