Parallel Queries

Parallel Queries

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
• If a table is....
  • 200 megabytes or smaller Do not enable parallelism
  • 200 megabytes up to 5gigabytes Use a DOP of at least 4
  • 5gigabytes or larger Use a DOP of 32
  
  
  When setting a degree of parallelism make sure to define a power of 2 so that work can be evenly distributed among the execution servers.
  
  
  Remember that these values are used when parallel_degree_policy is set to MANUAL or LIMITED. Oracle will determine the DOP when parallel_degree_policy is set to AUTO.
• Table and Index DOP should match in most cases. Having different values for an index and table may produce unexpected plans and performance.

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 IND_XXX NOPARALLEL;

ALTER INDEX index_name PARALLEL (DEGREE 1 INSTANCES 1);

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.