Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

Parallelism And Oracle Data Warehousing

Parallel Index Building

Figure 7.10 A sample parallel sort.

However, parallel query works best with SMP boxes, which have more than one internal CPU. Also, it is important to configure the system to maximize the I/O bandwidth, either through disk striping or high-speed channels. Because of the parallel sorting feature, it is also a good idea to beef up the memory on the processor.

While sorting is no substitute for using a presorted index, the parallel query manager will service requests far faster than a single process. While the data retrieval will not be significantly faster because all of the retrieval processes are competing for a channel on the same disk, each sort process has its own sort area (as determined by the SORT_AREA_SIZE init.ora parameter), which speeds along the sorting of the result set. In addition to full-table scans and sorting, the parallel query option allows for parallel processes for merge joins and nested loops.
Invoking the parallel query option requires all indexing to be bypassed. And, most importantly, the execution plan for the query should specify a full-table scan. If the output of the explain plan does not indicate a full-table scan, the query can be forced to ignore the index by using query hints.

The number of processors dedicated to servicing an SQL request is ultimately determined by the Oracle query manager, but the programmer can specify the upper limit on the number of simultaneous processes. When using the cost-based optimizer, the PARALLEL hint can be embedded into the SQL to specify the number of processes. For example:

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.