Parallel Execution Tuning Tips

Most modern commercial applications involve large quantities of data. Processing large amounts of data serially sometimes can be very slow. Parallel execution--the spreading of work over multiple processors--can significantly improve the performance of data-intensive jobs. Like many database vendors, Oracle supports parallel execution in various forms. Oracle's parallel execution features include the following:

• Parallel query. The ability to break up the execution of a SELECT statement into multiple tasks, and then to execute those tasks in parallel using multiple processors.
• Parallel data loading. The ability to run multiple SQL*Loader sessions in parallel, which all load data into the same table.
• Parallel DML (Data Manipulation Language). The ability to parallelize INSERT, UPDATE, and DELETE statements.
• Parallel DDL (object creation). The ability to parallelize DDL statements such as CREATE TABLE, CREATE INDEX, and ALTER INDEX.
• Parallel recovery. The ability to use multiple processes to perform instance and media recovery.
• Parallel replication propagation. The ability to propagate changes from one database to another using multiple processes working together in parallel.

Here are ten tips on when and how to use Oracle's parallel execution features:

1. Use parallel execution properly.

   Parallel execution is not usually 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 different processors far exceeds the actual processing time required to execute the query in serial. Decision Support Systems (DSS) gain the most benefit from parallel execution because these applications usually handle large amounts of data, execute complex queries, and load large amounts of data in bulk. Online Transaction Processing (OLTP) applications can also 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 over-utilized system, parallel execution can cause bottlenecks, and degrade performance even further.

2. Analyze tables and indexes regularly.

   Analyze your tables and indexes regularly to keep their statistics up to date. Also, be sure to reanalyze after performing large data loads, bulk inserts, and index creations. This helps Oracle's cost-based optimizer to choose the best possible execution plan.

3. Use the NOLOGGING / UNRECOVERABLE option.

   All database operations are normally logged in the database redo log files. Logging involves extra I/O, which reduces performance. You can sometimes gain a significant performance boost by disabling logging for a non-critical operation. Specify the UNRECOVERABLE option for SQL*Loader sessions involved in a parallel load to avoid the generation of redo for that load. This saves a lot of time and redo log space. You can also use the NOLOGGING option while creating tables in parallel using CREATE TABLE ... AS SELECT statements, or while using INSERT INTO ... SELECT statements to do bulk inserts into a table. The disadvantage to using NOLOGGING / UNRECOVERABLE is that in a recovery situation, you may need to manually reperform the operations that you did not log.

4. Drop indexes while loading data.

   Maintaining indexes while loading data is resource-intensive and has a detrimental affect on the performance of the load. You can minimize the performance impact of this index maintenance by dropping all non-unique indexes before performing any large data load. After the load is complete, you can use Oracle's parallel index creation feature to recreate those indexes efficiently.

5. Choose an appropriate degree of parallelism.

   Oracle sets default values for the degree of parallelism at the instance level. However, those default values may not be appropriate for all queries. Therefore, you should override the default values by specifying the degree of parallelism at the table or index level, or at the statement level. For operations on a partitioned table, for example, you should set the degree of parallelism to a value equal to the number of partitions, or to a value such that the number of partitions is a multiple of the degree of parallelism.

6. Take advantage of disk striping.

   Oracle parallel execution is beneficial when the data being accessed is spread across multiple disk drives. I/O bottlenecks are avoided because the parallel slave processes can all read data from different disks. Therefore, you should stripe the tablespaces used for tables, indexes, and temporary segments over multiple devices.

   Related Reading Oracle Parallel Processing By Tushar Mahapatra, Sanjay Mishra Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

7. Partition your data.

   When SQL statements are executed in parallel against a partitioned table, the parallel coordinator divides the work by partition, and assigns a parallel slave process to each partition. Each slave process works independently on the partition to which it is assigned. To improve I/O performance, you can put partitions of a table on separate tablespaces, with each residing on a separate disk. Some operations, such as UPDATE and DELETE statements, can be parallelized only on partitioned tables.

8. Monitor performance using V$ views.

   Use Oracle's dynamic performance views--the V$ views--to monitor the parallel execution performance of an Oracle database. The V$ views that you can use are: V$PQ_SYSSTAT, V$PQ_SESSTAT, V$PQ_SLAVE, V$PQ_TQSTAT, V$PX_PROCESS, V$PX_SESSTAT, V$PX_SESSION, and V$PX_PROCESS_SYSSTAT. Query these views and use the information you derive from them to tune the initialization parameters that control parallel execution. Such parameters include: PARALLEL_MIN_SERVERS, PARALLEL_MAX_SERVERS, and PARALLEL_SERVER_IDLE_TIME.

9. Avoid "PARALLEL_FROM_SERIAL" bottlenecks.

   Use the EXPLAIN PLAN command to view the execution plan for parallel SQL statements that you write. Look for PARALLEL_FROM_SERIAL operations in the execution plan. These types of operations point to bottlenecks because they indicate that one serial operation is going to feed the multiple processes of a parallel operation. It's highly unlikely that one serial operation will be able to feed those processes fast enough, consequently time will be wasted, and performance will suffer. Set the degree of parallelism appropriately to avoid these types of operations in your execution plans.

10. Use Oracle8i's automatic tuning features.

    When Oracle8i was released, Oracle's parallel execution features were enhanced by the addition of an automatic tuning feature. Automatic tuning of parallel execution is enabled by setting the PARALLEL_AUTOMATIC_TUNING initialization parameter. When this parameter is set to TRUE, your Oracle instance will automatically control the values of several other initialization parameters, and your instance will automatically alter the degree of parallelism for SQL statements, thereby improving performance.

Sanjay Mishra is a certified Oracle database administrator with more than nine years of IT experience.