With the use of RAC on the rise it is time to talk about the use of a feature very under-utilized in Oracle. This feature I refer to is cross-instance parallel query. Many times on site visits I see people using single-instance parallel query, but no one whose system I reviewed has used cross-instance parallel. Some weren’t aware it was available while others assumed Oracle did it automatically.
In order to enable cross-instance parallel query you must specify the INSTANCES parameter in the PARALLEL clause, for example the statement:
ALTER TABLE h_lineitem PARALLEL(DEGREE 8);
Turns on single instance parallel query, however, the INSTANCES parameter defaults to 1 if not specified. If we want to use cross-instance parallel query in RAC (assuming a 2-node system) then we would change the above command to:
ALTER TABLE h_lineitem PARALLEL(DEGREE 8 INSTANCES 2);
This new command would allow the query to be parallelized on 2 instances.
The advantage to cross-instance parallel query is that you increase the IO bandwidth, available memory and CPU cycles available to the query for processing. However you must be careful not to over specify the degree of parallel in complex queries or poorer performance could be the result. You must also be careful to ensure the number of underlying disks ion the array subsystem will support the degree of concurrence implied in the degree of parallel. What I mean by this is that if you specify a degree of 8 you will get 8 effective processes or more all trying to access the disks at the same time if there aren’t enough disks to support this number of access processes then disk contention will occur.
Another feature that helps with a higher degree of parallel is the use of partitioning. Partitioning can be particularly useful to reduce the amount of block transfer across the interconnect and help with reduction of amount of data scanned by each parallel process.
You will need to work with your database to determine the proper degree of parallel for specific tables and queries. The degree of parallel can be specified at the database, object and statement level. In many cases you may want to add the PARALLEL hint to a query rather than globally turning on parallel at the system or object level. Remember that the PARALLEL_MIN_PROCESSES and PARALLEL_MAX_PROCESSES as well as the PROCESSES parameter must be sufficient to support the expected number of users and expected degree of parallel. Higher effective degrees of parallel can be supported in a RAC system by the use of cross-instance parallel operations.