What is Oracle Parallel Query?
Oracle Parallel Query (formerly Oracle Parallel Query Option or PQO) allows one to break-up a given SQL statement so that its parts can run simultaneously on different processors in a multi-processor machine. Typical operations that can run in parallel are: full table scans, sorts, sub-queries, data loading etc.
Parallel Query can improve performance of certain types of operations dramatically and is commonly used in Decision Support and Data Warehousing applications.


What is the difference between Parallel Query and Parallel Server?
Parallel Query allows one to break a SELECT or DML statements into multiple smaller chunks and have PQ slaves execute those smaller chunks on separate CPU's in a single box.
Oracle Real Application Clusters (RAC) allows one to take advantage of a multi-node clustered environment for availability and performance reasons. It is commonly used to access a very large database from different nodes of a cluster. More information about Oracle RAC can be obtained from the RAC FAQ.

If both RAC and OPQ are available one can split operations across multiple CPUs and multiple nodes in a cluster for even further performance improvements.



How does one invoke Parallel Query?
After setting the INIT.ORA parameters necessary for Parallel Query to work, do the following:
Alter the table (or index) to indicate that Oracle should try to parallelize operations performed against it

ALTER TABLE TAB_XXX PARALLEL (DEGREE 8);
Put hints in SQL statements to indicate that Oracle should try to execute them in parallel:

SELECT --+ PARALLEL(table_alias, degree, nodes) * FROM table ...

How does one disable Parallel Query?
Per table, execute the following DDL commands:
ALTER TABLE TAB_XXX PARALLEL (DEGREE 1 INSTANCES 1); or
ALTER TABLE TAB_XXX NOPARALLEL;

ALTER INDEX IND_XXX PARALLEL (DEGREE 1 INSTANCES 1); or
ALTER INDEX IND_XXX NOPARALLEL;

You can also remove the INIT.ORA parameters that allow Parallel Query to work.


What parameters can be set to control Parallel Query?
The following INIT.ORA initialization parameters can be set to control Parallel Query execution:
PARALLEL_MIN_SERVERS

PARALLEL_MAX_SERVERS

PARALLEL_AUTOMATIC_TUNING = TRUE
# enable intelligent defaults for parallel execution parameters (8i and above)

etc.


How does one monitor Parallel Query Execution?
Use the following Oracle data dictionary views to monitor parallel queries:

Prior to Oracle 8i:
select * from sys.v_$pq_sysstat;


Oracle 8i and above:
select * from v_$pq_sysstat;
select * from v_$px_process;
select * from v_$px_sesstat;
select * from v_$px_process_sysstat;


Can single-processor systems benefit from Parallel Query?
The more CPU's the bigger the benefit than can be obtained from Parallel Query. However, even a single-CPU system can benefit from it. The reason for this is that the system can continue processing a second thread while waiting for I/O completion on another thread.


Can Parallel Query operations cause performance problems?
If not carefully watched over, Parallel Query can completely saturate a machine. It is not difficult to bring some of the most powerful machines to their knees. One needs to carefully balance the number of people executing Parallel Query Operations and the degree of parallelism with the number of CPUs in the system.