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 system, parallel execution can cause bottlenecks and degrade performance even further.

Oracle’s cost-based optimizer bases its decisions on the table and index statistics that you collect using the ANALYZE command. It’s important to keep these statistics up to date. With outdated statistics, the optimizer might choose a poor execution plan, thereby adversely affecting performance. 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.

You can 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 also can use the UNRECOVERABLE 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.

Large tables often have several indexes on them to improve query performance. However, maintaining these indexes while loading data is resource-intensive and has a detrimental effect on the performance of the load. You can minimize the performance impact of this index maintenance by dropping all nonunique indexes before performing any large data load. After the load is complete, you can use Oracle’s parallel index creation feature to re-create those indexes efficiently.

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.

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

You can use either operating system striping or Oracle striping. Many operating systems provide utilities to stripe physical disks and create logical drives. To use Oracle striping, you should allocate multiple datafiles to each tablespace, where each datafile resides on a separate physical drive. Parallel SQL*Loader can take advantage of Oracle striping through the use of the FILE clause in the SQL*Loader control file.

Oracle8 and Oracle8i allow you to partition tables and indexes. Partitioning provides a variety of benefits and is useful for parallel execution too. On 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. Also, 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.