Oracle Parallel Processing

Tuning Tips for Parallel Execution

So far in this chapter, we’ve discussed various tools and techniques that you can use to monitor and tune parallel execution performance. In this section, we’ll pass on some general tips that you also can use to improve the performance of parallel execution on your database:

Use parallel execution properly

Parallel execution helps improve performance for operations involving large amounts of data. The performance of large queries and bulk data loads, for example, can benefit immensely from parallel execution. Parallel execution usually is not helpful for operations involving a small amount of data. That’s because, for a small amount of data, the overhead of breaking the operation into subtasks and coordinating the execution of those subtasks among parallel slave processes far exceeds the actual processing time required to execute the query in serial. Decision support system (DSS) applications gain maximum benefit from parallel execution, because these applications usually handle large amounts of data, complex queries, and bulk data loading. Online transaction processing (OLTP) applications can benefit from parallel execution for batch processing.

Another thing to keep in mind is that parallel execution requires a multiprocessor system with spare CPU and memory resources. On a single-processor system, there is no reason to use parallel execution, as the parallel slave processes will contend for the same CPU and degrade performance. On an overutilized ...