Parallel Query FAQ

Oracle Parallel Query (OPQ) FAQ. Oracle Parallel Query was previously called Parallel Query Option.

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 table_name 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_name ...

SELECT /*+PARALLEL(table_alias, degree, nodes)*/ * 
  FROM table_name ...

How does one disable Parallel Query?

Per table, execute the following DDL commands:

ALTER TABLE table_name PARALLEL (DEGREE 1 INSTANCES 1);

ALTER TABLE table_name NOPARALLEL;

ALTER INDEX index_name PARALLEL (DEGREE 1 INSTANCES 1);

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

Minimum number of parallel server processes

• PARALLEL_MAX_SERVERS

Maximum number of parallel server processes

• PARALLEL_AUTOMATIC_TUNING = TRUE

To enable intelligent defaults for parallel execution parameters (8i and above)

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 you have the bigger the benefit you can expect to get from using Parallel Query. Nevertheless, even a single-CPU system can benefit from using it. The reason for this is that the system can continue processing a second thread while waiting for I/O or network activity in another thread.

Before enabling it on a single CPU system, ensure the system is not too busy and start with a low value for PARALLEL_MAX_SERVERS (i.e. 2 or 4).

Can Parallel Query operations also 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.

Also note that executing things in parallel is not always faster. In fact, you need to test all queries to ensure that they will benefit from the increased parallelism before permanently enabling it.