With the release of Oracle8i, Oracle has provided some significant additional support for tuning parallel execution environments. Specifically, Oracle now has the ability to make some tuning decisions automatically. If you’ve already manually tuned your application, and it’s performing satisfactorily, automatic tuning may not be beneficial. However, new applications that run on Oracle8i and use Oracle8i ’s parallel execution features can benefit from automatic tuning.
To enable automatic tuning of parallel execution, set the initialization parameter PARALLEL_AUTOMATIC_TUNING to TRUE. At this point, you need to be aware of three effects of automatic tuning:
The effect of automatic tuning on the default value of the initialization parameters listed in Table 5.5
The effect of automatic tuning on the adaptive multiuser algorithm
The effect of the PARALLEL_THREADS_PER_CPU initialization parameter setting
When you set PARALLEL_AUTOMATIC_TUNING = TRUE (thereby enabling automatic tuning of parallel execution), that has an effect on the default values of several initialization parameters. Table 5.5 shows the changes in default values that occur when automatic tuning is used. Remember that you can always override these defaults by explicitly setting the parameters in your initialization file.
Table 5-5. The Effect of Automatic Tuning on Initialization Parameter Defaults
|
Parameter |
Default Value When PARALLEL_AUTOMATIC_TUNING = FALSE |
Default Value When PARALLEL_AUTOMATIC_TUNING = TRUE |
|---|---|---|
|
PARALLEL_ADAPTIVE_MULTI_USER |
FALSE |
TRUE |
|
PARALLEL_MAX_SERVERS |
5 |
10 × number of CPUs |
|
LARGE_POOL_SIZE |
0 |
Based on a complicated computation using various other parameters |
|
PARALLEL_EXECUTION_MESSAGE_SIZE |
2 K |
4 K |
When PARALLEL_ADAPTIVE_MULTI_USER = TRUE, Oracle determines the degree of parallelism based on the system’s current workload. For example, consider a table with DEGREE set to 8. Under a normal system load, a full table scan on the table entails eight parallel slave processes. However, under a heavy load, when many users are executing processes on the system, Oracle automatically reduces the degree of parallelism. For example, it may reduce the degree of parallelism from 8 to 4. This reduces the CPU and memory requirements of the statement, which helps in reducing CPU and memory bottlenecks for the instance, which, in turn, improves performance.
The default value on most platforms for PARALLEL_THREADS_PER_CPU is 2. Oracle uses this parameter to compute the default degree of parallelism for the instance. It does this by multiplying the number of CPUs on the system by the value of the PARALLEL_THREADS_PER_CPU parameter.
When using automatic tuning, you should leave the parameters shown in Table 5.5 set to their default values and then monitor performance over a reasonable period of time using the dynamic performance views. If you then determine that some parameters need manual tuning, you can explicitly set them. Oracle recommends that you leave the LARGE_POOL_SIZE and PARALLEL_EXECUTION_MESSAGE_SIZE parameters at their default values. You should, however, tune PARALLEL_MAX_SERVERS based on the statistics from V$PQ_SYSSTAT or V$PX_PROCESS_SYSSTAT.