DOP CONFIGURATION PARAMETERS

posted Jan 29, 2012, 7:10 AM by Sachchida Ojha   [ updated Jan 29, 2012, 7:18 AM ]
Configuring the optimal DOP is the biggest challenge DBA's faces when taking concurrent system activity into account. Oracle has improved its DOP automation process significantly in the new releases. We recommend you to try Oracle's automation before attempting to manually configure automatic processing.
Nevertheless, Significant tweaking is possible.  here are the list of most important parameter that you can adjust to optimize parallel SQL.

 Parameter name
 Description
 parallel_adaptive_multi_user When set to TRUE, Oracle will adjust the DOP to account for the load on the system. On a heavily loaded system, Oracle will reduce the DOP from the requested or default degree.
 parallel_degree_limit In Oracle 11g R2 and higher, places an absolute limit on DOP that can be achieved.  A value of CPU prevents the DOP from exceeding that specified by PARALLEL_THREADS_PER_CPU. A value of IO sets the maximum to the IO limit determined by running DBMS_RESOURCE_MANAGER.CALIBRATE_IO.AUTO allows Oracle to select a value. An integer value corresponding to a specific DOP might also be specified.
 parallel_degree_policy in 11G R2 and forward, this parameter controls the means by which the DOP will be calculated. Manual equates to oracle 11 and earlier behaviour. if AUTO, the DOP will be calculated based on the types of operations in the SQL statement and the sizes of the tables.  AUTO also enables parallel queries to fetch data from the buffer cache rather than using direct path IO and will queue parallel processes if the requested DOP execution is not immediately available.
 parallel_execution_message_size Sets the size of the buffers for communication between the processes involved in parallel processing.
 parallel_force_local From Oracle 11g R2 forward, this parameter is set to TRUE, suppresses multi-instance parallelism on RAC clusters.
 parallel_io_cap_enabled
 This is 11g parameter, if set to TRUE will limit the DOP to that which Oracle thinks the IO subsystem can support. to use this parameter, you should first use DBMS_RESOURCE_MANAGER.CALIBRATE_IO to determine these IO limits
 parallel_max_servers The max no of parallel servers that can be started. This provides ABSOLUTE LIMIT on the amount of concurrent parallel operations that can execute.
 parallel_min_percent If set to >0, this parameter determines the minimum acceptable DOP for a query. For example if a query requested a DOP of 8 and only 5 were available (5/8=62%), query will execute in parallel if PARALLEL_MIN_PERCENT was set below 62. if  PARALLEL_MIN_PERCENT>62, statement will either terminate with an error or , if PARALLEL_DEGREE_POLICY is set to AUTO, will be queued for later execution.
 parallel_min_servers The number that will be initialized when the database is first started.
 parallel_min_time_threshold Specifies the amount of elapsed time (in SECONDS) required for a SQL statement to be automatically parallelized. if the estimated elapsed time of a SQL statement exceeds the threshold, Oracle automatically parallelizes the SQL. The default of AUTO results in Oracle automatically calculating the value.
 parallel_thread_per_cpu Sets the no of parallel threads that can be applied per CPU. Oracle generally restricts the DOP that this limit is not exceeded.

V$PQ_TQSTAT view: This view contains information about the data transferred between each set of parallel query servers, including the no of rows transmitted and received. This view is visible only from within the sessions that issued the parallel query and only for the most recent query executed.

SQL> desc V$PQ_TQSTAT;
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 DFO_NUMBER                        NUMBER
 TQ_ID                            NUMBER
 SERVER_TYPE                        VARCHAR2(10)
 NUM_ROWS                        NUMBER
 BYTES                            NUMBER
 OPEN_TIME                        NUMBER
 AVG_LATENCY                        NUMBER
 WAITS                            NUMBER
 TIMEOUTS                        NUMBER
 PROCESS                        VARCHAR2(10)
 INSTANCE                        NUMBER

SQL>

Comments