I was posed the following question by a colleague recently. I am using auto DOP in 11gr2 and I am setting parallel_degree_threshold=12. But when i execute my query, my query is still running with a parallelism of 48 (ie it seems to be ignoring my setting for parallel_degree_threshold). The problem turned out to be that for Auto DOP to work, you need to have dbms_workload_manager.calibrate_io run and the table resource_io_calibrate$ populated.
This requirement is explicitly stated in the oracle 11gr2 documentation at http://download.oracle.com/docs/cd/E11882_01/server.112/e25523/parallel002.htm#CIHEFJGC
“When PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics. The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature.”
Automatic DOP, where oracle figures out the degree of parallelism to use for a given query, gets turned on by setting the initialization parameter PARALLEL_DEGREE_POLICY to either AUTO or LIMITED (When it is limited it does NOT turn on parallel query queuing and in memory parallel query). So the requirement to gather calibrate_io output is applicable to both the setting AUTO and LIMITED.
When you are using Auto Dop, and you want to limit the maximum parallelism that a query can get, you can use the parameter parallel_degree_limit=cpu/io/<integer>, to limit the DOP of a query.
When you create a new database using a DBCA (Database configuration assistant) template, in the new database, the calibrate_io statistics is not present. You can verify this by running the queries below.
SQL> select * from resource_io_calibrate$;
no rows selected
SQL> select * from v$io_calibration_status;
STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
NOT AVAILABLE
Below is an example.
- I have created a new table named sales_rk that has 58 million rows in it.
- There are no indexes, or primary keys on this table.
- The table is decorated with parallel degree DEFAULT
- parallel_degree_policy=LIMITED
- parallel_degree_limit=2
- cpu_count=2
- parallel_threads_per_cpu=2
- At this time there are no rows in resource_io_calibrate$
I ran the query “select count(*) from sales_rk”
I would have expected auto dop to have kicked in and parallel_degree_limit to have limited the DOP of the query to 2.
However that is not what happened.
The query ran with a DOP of 4.
So oracle just picked the default DOP (cpu_count x parallel_threads_per_cpu).
Further evidence that Auto DOP did not kick in can be found by examining the dbms_xplan output of the statement.
SQL> select count(*) from sales_rk;
COUNT(*)
----------
58805952
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1bzaqj7scjp7p, child number 0
-------------------------------------
select count(*) from sales_rk
Plan hash value: 2302347944
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 21544 (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 | | 58M| 21544 (1)| 00:04:19 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| SALES_RK | 58M| 21544 (1)| 00:04:19 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z)
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
The Note section explicitly states that Auto DOP was skipped.
Now to fix the situation, i ran dbms_resource_manager.calibrate_io.
DECLARE
lat INTEGER;
iops INTEGER;
mbps INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (1, 10, iops, mbps, lat);
end;
/
Beware of calibrate_io generating bad numbers for io mbps. If it does, then follow instructions in MOS note 1269321.1 to delete the contents of resource_io_calibrate$ and populate the table manually.
Bounce the database.
Check whether calibrate_io worked
SQL> l
1* select * from v$io_calibration_status
SQL> /
STATUS
-------------
CALIBRATION_TIME
---------------------------------------------------------------------------
READY
07-OCT-11 05.56.40.911 PM
Now re-run the same query, Auto DOP kicks in, and it executes with a DOP of 2.
Looking at the execution plan, confirms that Auto DOP did kick in.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'TYPICAL'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 1bzaqj7scjp7p, child number 1
-------------------------------------
select count(*) from sales_rk
Plan hash value: 2302347944
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 43087 (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 | | 58M| 43087 (1)| 00:00:08 | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL| SALES_RK | 58M| 43087 (1)| 00:00:08 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(:Z>=:Z AND :Z)
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2 because of degree limit