Страницы

четверг, 29 октября 2015 г.

12c: PX Auto DOP without IO Calibration

I have upgraded one of data warehouse database this week and noticed changes in behaviour of PX Auto DOP feature.
According to the documentation of the parallel_degree_policy parameter Oracle Database Reference 11g:
  • MANUAL
    Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution. This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release 2 (11.2). This is the default.
  • LIMITED
    Enables automatic degree of parallelism for some statements but statement queuing and in-memory Parallel Execution are disabled. Automatic degree of parallelism is only applied to those statements that access tables or indexes decorated explicitly with the DEFAULT degree of parallelism using the PARALLEL clause. Statements that do not access any tables or indexes decorated with the DEFAULT degree of parallelism will retain the MANUAL behavior.
  • AUTO
    Enables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
In short:
MANUAL - disables Auto DOP, parallel statement queuing, and in-memory parallel execution.
AUTO - enables all of the above.
What if you need parallel statement queuing but dont need Auto DOP?
MOS note suggests to add hint STATEMENT_QUEUING in your statements manually: How to Achieve Parallel Statement Queuing for an SQL When PARALLEL_DEGREE_POLICY=MANUAL (Doc ID 1902069.1)
In 11g there was an clever automatic way to achieve this:
VLDB and Partitioning Guide 11g
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.

So I simply installed PARALLEL_DEGREE_POLICY=AUTO and didn't gather IO calibration statistics.
This prevents Auto DOP but parallel statement queuing and in-memory parallel execution still enabled.
Now in 12c things got changed:
VLDB and Partitioning Guide 12c
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 should be gathered.
If I/O calibration is not run to gather the required statistics, a default calibration value is used to calculate the cost of operations and the degree of parallelism.

Have you seen the difference?
It means that now Auto DOP works without IO calibration statistics.
I dont see any announcement in the New Features Guide or VLDB and Partitioning Guide about this change.
Yes, I agree that configuration with PARALLEL_DEGREE_POLICY=AUTO without IO calibration statistics looks an unusual thing.
But I would prefer that Oracle mentions such things at least in the "Changes in this release" section in the relevant book.
I.e. here.