Database Reference
In-Depth Information
SQL_ID EXEC CPU_TIME ELA_TIME BGETS DRDS ROWS
------------- ---- -------- -------- -------- -------- -------
gj1p0kqyxvqas 1 17.37 18.66 455257 0 809 <- with caching enabled
gj1p0kqyxvqas 1 17.09 81.42 237443 232779 809 <- without caching
You need to understand that the effect of this feature depends upon the activity in the database buffer cache.
If the buffers are not accessed frequently, then the buffers will be flushed out of the buffer cache, and so PX servers
may be doing more work with no future benefit.
4
On the Exadata platform, this feature is probably not useful, as the
smart scan feature requires direct path reads.
As the tables are buffered, there is a possibility that this feature can increase interconnect network traffic
inadvertently. So, if you are planning to enable in-memory parallelism, verify that interconnect hardware is properly
sized to support a possible increase in private network traffic.
AutoDOP
AutoDOP is a new feature available from Oracle Database version 11.2. Prior to version 11.2, the optimizer chose
PX only if the objects had a DOP of greater than 1 or if there were hints in the SQL statement enabling parallelism.
From 11.2 onward, parallelism is considered if the estimated run time of a SQL statement exceeds a threshold.
The following algorithm outlines the logic of the AutoDOP feature.
Estimate total run time of a SQL statement for serial execution.
If estimated_run_time > 10 seconds threshold then
Re-parse the statement with a default DOP and determine the cost.
If the estimated cost is lower than the estimated cost for serial execution, then
Execute the statement with Default DOP.
End;
Else
Execute the statement serially.
End;
The AutoDOP feature can be enabled by setting parallel_degree_policy to
auto
or
limited
(11.2) or
auto_feedback
(12c). If the parallel_degree_policy is set to limited, then objects with parallelism must be accessed
in the SQL statement for the AutoDOP to be enabled. The following few lines are printed from event 10053 trace file.
These lines show that AutoDOP was disabled since the parallel_degree_policy parameter is set to a value of limited
and none of the objects accessed have a parallel degree decorated.
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: limited mode but no parallel objects referenced.
If you set the parallel_degree_policy parameter to auto, then the AutoDOP feature is enabled, but I/O must be
calibrated for AutoDOP to be enabled.
**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is enabled for this statement in auto mode.
kkopqSetDopReason: Reason why we chose this DOP is: IO calibrate statistics are missing.
4
More work here refers to additional tasks of maintaining cache fusion lock structures to read buffers into buffer cache.
