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.
