There are several initialization parameters that control automatic DOP: PARALLEL_DEGREE_POLICY, PARALLEL_DEGREE_LIMIT, and PARALLEL_MIN_TIME_THRESHOLD. automatic DOP: Computed Degree of Parallelism is 4 |Id| Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop| TQ |IN-OUT| PQ Distrib | The DOP determined by the optimizer is shown in the notes section of an explain plan output, as shown in the following explain plan output, visible either using the explain plan statement or V$SQL_PLAN. I/O calibration is a one-time action if the physical hardware does not change. I/O calibration statistics can be gathered with the PL/SQL DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. 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 DOP. The hardware characteristics include I/O calibration statistics so these statistics should be gathered. To calculate the cost of operations for a SQL statement, Auto DOP uses information about the hardware characteristics of the system. The value AUTO for PARALLEL_DEGREE_LIMIT has the same functionality as the value CPU. The optimizer can further limit the maximum DOP that can be chosen if Oracle Database Resource Manager is used to limit the DOP. By adjusting this parameter setting, you can control the maximum DOP the optimizer can choose for a SQL statement. This default DOP ensures that a single user operation cannot overwhelm the system. The default value for this parameter is CPU, which means the DOP is limited by the number of CPUs on the system ( PARALLEL_THREADS_PER_CPU * sum(CPU_COUNT)) also known as the default DOP. This limit is set by the parameter PARALLEL_DEGREE_LIMIT. However, the optimizer limits the actual maximum DOP to ensure parallel execution servers do not overwhelm the system. The optimizer uses the cost of all scan operations, such as a full table scan or index fast full scan, and the cost of all CPU operations in the execution plan to determine the necessary DOP. With automatic degree of parallelism (DOP), the optimizer automatically determines the DOP for a statement based on the resource requirements of that statement. The actual runtime DOP of a SQL statement can be limited by Oracle Database Resource Manager. Default DOP targets the single-user workload and it is not recommended in a multiuser environment. The default DOP algorithm is designed to use maximum resources and assumes that the operation finishes faster if it can use more resources. The default DOP specified in the PARALLEL clause of a table or an index takes effect only when PARALLEL_DEGREE_POLICY is set to MANUAL. You can also request the default DOP by using statement level or object level parallel hints. For example, on a 4-node Oracle RAC cluster, with each node having 8 CPU cores and no Oracle RAC services, the default DOP would be 2 x (8+8+8+8) = 64. However, if you have used Oracle RAC services to limit the number of nodes across which a parallel operation can execute, then sum(CPU_COUNT) is the total number of CPUs across the nodes belonging to that service. If the PARALLEL clause is specified but no degree of parallelism (DOP) is listed, then the object gets the default DOP.įor example, you can set a table to the default DOP with the following SQL statement.ĭefault parallelism uses a formula to determine the DOP based on the system configuration, as in the following:įor a single instance, DOP = PARALLEL_THREADS_PER_CPU x CPU_COUNTįor an Oracle RAC configuration, DOP = PARALLEL_THREADS_PER_CPU x sum(CPU_COUNT)īy default, sum(CPU_COUNT) is the total number of CPUs in the cluster.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |