Oracle Database - SQL Parallel Execution

1 - About

Parallel execution was first introduced in Oracle Version 7.3 in 1996

Oracle will make use of Parallel when a table or a statement is marked as “Parallel”.

The concept of parallelism is when many individual processors perform smaller tasks concurrently to accomplish an operation against a huge repository of data. To date, only parallel architectures can handle databases of this size.

Parallel operations speed up DML statement execution by dividing the work among multiple child processes. Each child process executes its portion of the work under its own parallel process transaction.

Regardless at which level you enable prallelism, the setting of parallelism for a table influences the optimizer.

Parallel execution uses multiple processes to accomplish a single task – to complete a SQL statement in the case of SQL parallel execution. The more effectively the database software can leverage all hardware resources:

  • multiple cores,
  • multiple I/O channels,
  • or even multiple nodes in a cluster

the more efficiently queries and other database operations will be processed.

Oracle SQL parallel execution

requires Oracle Database Enterprise Edition.

The majority of operations that execute in parallel bypass the buffer cache. A parallel operation will only use the buffer cache if the object has been either explicitly created with the CACHE option or if the object size is smaller than 2% of the buffer cache. If the object size is less than 2% of the buffer cache then the cost of the checkpoint to start the direct read is deemed more expensive than just reading the blocks into the cache.

In 9.2, You cannot parallelize the query portion of a DDL or DML statement if it references a remote object. When you issue a parallel DML or DDL statement in which the query portion references a remote object, the operation is automatically executed serially.

3 - Principles

SQL parallel execution in the Oracle Database is based on the principles of:

The QC is the session that initiates the parallel SQL statement and the PX servers are the individual sessions that perform work in parallel. The QC distributes the work to the PX servers and may have to perform a minimal – mostly logistical – portion of the work that cannot be executed in parallel.

For example a parallel query with a SUM() operation requires a final adding up of all individual sub-totals calculated by each PX server.

The QC is easily identified in a parallel execution plans as 'PX COORDINATOR'. The process acting as the QC of a parallel SQL operation is the actual user session process itself.

The PX servers are taken from a pool of globally available PX server processes and assigned to a given operation. All the work below the QC entry in a plans is done by the PX servers.

PX server processes can be easily identified on the OS level, for example on Linux they are the oracle processes ORA_P***

4 - Documentation / Reference

db/oracle/sql_parallel_execution.txt · Last modified: 2018/06/08 14:51 by gerardnico