Database Reference
In-Depth Information
Cost: 59810.86 Est_time: 156847ms
Serial plan is expensive enough to be a candidate for parallelism (59811)
Signal reparse with
DOP 3
.
*****************************
Number of Compilations tried: 1
Let us review a few important parameters controlling the AutoDOP feature. Parameter
parallel_degree_limit
governs the maximum DOP that can be chosen for a SQL statement. By default, this parameter is set to CPU,
implying default DOP as the maximum DOP for AutoDOP execution. Default DOP is calculated by multiplying the
total number of CPUs in the cluster (Sum of cpu_count from all active instances) by the parallel_threads_per_cpu
(Default=2) parameter. So, if there are 32 cores each in a three-node cluster, then the value of parallel_degree_limit is
calculated as 32 * 3 * 2 (parallel_threads_per_cpu=2), equaling a value of 192. So, 192 PX servers
can
be used for a SQL
statement execution.
■
The parameter parallel_degree_limit can be set to i/O or an integer. if you set the parameter value to i/O, then
i/O bandwidth derived from dbms_resource_manager.calibrate_iO procedure call is used to derive upper bounds for
default DOP. you can also set the parallel_degree_limit parameter to a number specifying the maximum default
parallelism to be used for AutoDOP calculations.
Note
The parameter
parallel_max_servers
is a hard upper bound determining the maximum number of PX servers
available in any instance, and the parallel_max_servers parameter overrides the parallel_degree_limit parameter if the
parallel_max_servers parameter is set to a value lower than the default DOP.
While parallel_degree_limit controls the upper bound for parallelism, the actual parallelism used in a query
(calculated DOP or actual DOP) is less than the default DOP. The exact algorithm is not documented for the actual
DOP calculation; however, from event 10053 trace files, we can infer that the optimizer is recosting the statement with
different DOPs to find the optimal DOP for a SQL execution.
There are a few dangers with AutoDOP feature in a production database.
1.
If many concurrent queries are parallelized inadvertently, then those queries can deplete
I/O and CPU resources in the database server, causing performance issues with the online
application.
2.
In addition, inadvertent parallel queries can consume all parallel servers, starving critical
parallel queries of parallel servers.
3.
Also, in RAC, if many queries are parallelized, then there is a possibility that numerous
queries might perform inter-instance PX, leading to worse interconnect latency for online
application(s).
So, in the production database, it is generally a good idea to control parallelism so that you don't accidentally run
out of resources. Further, you need to understand that AutoDOP uses
estimated
run time to decide if AutoDOP should
be enabled or not. Hence, it is critical to have optimal statistics in a RAC environment so that excessive PX executions
are not accidentally triggered due to a statistical issue.
AutoDOP feature is more suitable for data warehousing environments. You might need to tune the
parallel_min_time_threshold
parameter to match the workload in your RAC cluster. By default, the
parallel_min_time_threshold parameter is set to a value of 10 seconds. In my experience, a lower default value for
the parallel_min_time_threshold parameter will trigger a tsunami of parallel executions. So, if you are planning
to enable the AutoDOP feature, increase the parallel_min_time_threshold parameter to a higher value, at least
600 seconds.
