This is just another short post about one of the little things worth knowing. Assume you are on 12.1.0.2 for example and you want to know if parallel_degree_limit reliably keeps the degree of parallelism at bay, for all sessions and queries across the board. After all, every machine has finite CPU (and other) resources that shouldn’t be exhausted. Setting parallel_degree_limit of course only has an effect if you set parallel_degree_policy to limited, auto or adaptive. The latter is the Big Knob to enable Automatic Degree Of Parallelism (DOP), and the post assumes that Auto DOP is enabled for the session. In this configuration, what happens to hinted statements?
Documentation
The documentation (12c Reference Guide, section on parallel_degree_limit) states that you can provide an integer in addition to “CPU” and “IO”:
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.
Without further context this may sound a bit ambiguous-does enabling Auto DOP plus setting parallel_degree_limit to 8 ensure no query can exceed that DOP? And does that setting actually stop users from going over and above that value?
Testing
I am using a 12.1.0.2 RAC database for the test. I would like to limit the maximum DOP of a given statement to 8. Here are my settings for my lab system (emphasis on lab):
SQL> show parameter parallel NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW parallel_adaptive_multi_user boolean FALSE parallel_automatic_tuning boolean FALSE parallel_degree_level integer 100 parallel_degree_limit string 8 parallel_degree_policy string AUTO parallel_execution_message_size integer 16384 parallel_force_local boolean TRUE parallel_instance_group string parallel_io_cap_enabled boolean FALSE parallel_max_servers integer 128 parallel_min_percent integer 0 parallel_min_servers integer 16 parallel_min_time_threshold string AUTO parallel_server boolean TRUE parallel_server_instances integer 2 parallel_servers_target integer 128 parallel_threads_per_cpu integer 2 recovery_parallelism integer 0
Now I need a table to query – after looking around in my demo-schema I found T1 which looks like a good candidate.
SQL> select table_name,num_rows,partitioned,compression,degree from tabs where table_name = 'T1'; TABLE_NAME NUM_ROWS PAR COMPRESS DEGREE ------------------------------ ---------- --- -------- ---------- T1 32000000 NO DISABLED 1
Unlike when setting parallel_degree_policy to limited I don’t need to worry about decorating the table with a default DOP. With parallel_degree_policy set to auto a parallel plan can be chosen even with the DOP on the table set to 1, provided the optimiser reckons that statement execution exceeds parallel_min_time_threshold. In my case I left its value at its default, which is 10 seconds. When querying the table I do not have to specify a hint to enable PX, or define PX on the session level as you can see in this example:
SQL> select count(*) from T1; COUNT(*) ---------- 32000000 SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------- SQL_ID byb4cbw4vy1cw, child number 1 ------------------------------------- select count(*) from T1 Plan hash value: 2755517917 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 200K(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 | | 32M| 200K (1)| 00:00:04 | Q1,00 | PCWC | | |* 6 | TABLE ACCESS STORAGE FULL| T1 | 32M| 200K (1)| 00:00:04 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - storage(:Z>=:Z AND :Z<=:Z) Note ----- - automatic DOP: Computed Degree of Parallelism is 8 because of degree limit
The important bit here is the information in the Note section: Oracle computed the DOP to be 8 because of the degree limit. Exactly what I wanted.
Now what if I use the hint?
No changes to the setup, this is the next statement I typed in:
SQL> select /*+ parallel(32) */ count(*) from T1; COUNT(*) ---------- 32000000 SQL> SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL_ID 9d9aha2bdd5zc, child number 0 ------------------------------------- select /*+ parallel(32) */ count(*) from T1 Plan hash value: 2755517917 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 50217 (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 | | 32M| 50217 (1)| 00:00:01 | Q1,00 | PCWC | | |* 6 | TABLE ACCESS STORAGE FULL| T1 | 32M| 50217 (1)| 00:00:01 | Q1,00 | PCWP | | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 6 - storage(:Z>=:Z AND :Z<=:Z) Note ----- - Degree of Parallelism is 32 because of hint
So based on this experiment it appears as if the hint took precedence over parallel_degree_limit in a session that had Auto DOP enabled. That doesn’t mean the DBA is out of luck, other tools come to mind to limit the DOP such as Database Resource Manager (DBRM). I have written about using DBRM to limit the DOP for all users of a consumer group some time ago. If you read that post make sure you look at Randolf’s comments.
Martins Blog 