Parallel execution can significantly reduce the processing time required for queries and other SQL statements, but only if it is properly implemented and tuned. You must monitor and tune the performance of parallel execution in your environment on a regular basis. In this chapter you’ll learn about the various tools and procedures at your disposal for monitoring the parallel execution performance of a database. You’ll also find some tuning tips and information on some important Oracle8i enhancements related to parallel execution performance.
Oracle tuning in general is a very large area, but when you focus strictly on parallel execution, there are only a few tuning-related adjustments that you can make. These include:
Changing the PARALLEL_MIN_SERVERS initialization parameter, which controls the minimum number of parallel slave processes maintained in the parallel slave process pool
Changing the PARALLEL_MAX_SERVERS initialization parameter, which controls the maximum number of parallel slave processes maintained in the parallel slave process pool
Changing the PARALLEL_SERVER_IDLE_TIME initialization parameter, which controls the amount of time a slave process is kept around when it’s not being used
Changing the degree of parallelism, using the DEGREE and INSTANCES keywords, for either a statement or a table
Changing your statements to avoid “parallel from serial” bottlenecks (i.e., those caused by your execution plan’s having to perform the PARALLEL_FROM_SERIAL step, described in the discussion of EXPLAIN PLAN later in this chapter)
Using Oracle8i’s new automatic parallel execution tuning feature
Of course, to tune something, you need a way to gather statistics and measure performance. As you might have guessed already, Oracle’s dynamic performance views (the V$ views) are your prime source of information on parallel execution performance. Another useful source of information is the EXPLAIN PLAN statement, which can tell you if the execution plan for a given SQL statement contains any “parallel from serial” bottlenecks.