Oracle Database - SQL Parallel Execution

Card Puncher Data Processing

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.

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.

Oracle Database Sql Parallel Execution Principle

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

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle - Partitions (Table and Indexes)

partition in Oracle. Partitioning enables you to store one logical object – a table or index – transparently in several independent segments. Partitioning can provide great performance improvements...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Db File Sequential Read
Oracle Database - db file sequential read Wait Event

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read. From SQL Developer,...
Card Puncher Data Processing
Oracle Database - (Degree|Degree of Parallelism (DOP))

Oracle's parallel execution framework enables you to either explicitly chose - or even enforce - a specific degree of parallelism (DOP) or to rely on Oracle to control it. A degree of 1 is the default...
Card Puncher Data Processing
Oracle Database - (Integrity) Constraints

Constraints on tables. Violation of constraint is not a syntax error but a run-time error. See A UNIQUE key integrity constraint requires that every value in a column or set of columns (key)...
Card Puncher Data Processing
Oracle Database - (Parallel|PX) Server

Execution Thread in Oracle. Parallel server are the individual sessions that perform work in parallel in a parallel SQL execution. PX server processes can be easily identified on the OS level, for...
Card Puncher Data Processing
Oracle Database - CPU_COUNT parameter

CPU_COUNT specifies the number of CPUs core (processor) available for Oracle Database to use. CPU count is an automatically derived parameter by the Oracle system and is used to determine: the default...
Card Puncher Data Processing
Oracle Database - DB_FILE_MULTIBLOCK_READ_COUNT Parameter

This parameter determines how many database blocks are read in a single I/O (with a single operating system READ call) during a: full table scan or index fast full scan. SQL parallel execution is...
A Synchronous Read
Oracle Database - DISK_ASYNCH_IO parameter

For optimum performance make sure you use asynchronous I/Os. asynchronous stream TRUE is the default parameter value for the majority of platforms. These parameters enable or disable the operating...
Card Puncher Data Processing
Oracle Database - Data Loading Hint

The PL/SQL data loading procedures use the following HINTS for performance. They are /+ APPEND NOLOGGING PARALLEL / Append: - When “Append” hint is used in a PL/SQL INSERT statement, Oracle will...



Share this page:
Follow us:
Task Runner