【SQL】Using Oracle's Parallel Execution Features

The full list of Oracle parallel execution features currently includes the following

• Parallel Query

• Parallel DML

• Parallel DDL

• Parallel Data Loading

• Parallel Recovery

• Parallel Replication

• How Parallel Execution Works

Operations That Can Be Parallelized

Oracle can parallelize operations that involve processing an entire table or an entire partition. These operations include:

• SQL queries requiring at least one full table scan or queries involving an index range scan spanning multiple partitions.

• Operations such as creating or rebuilding an index or rebuilding one or more partitions of an index.

• Partition operations such as moving or splitting partitions

• CREATE TABLE AS SELECT operations, if the SELECT involves
  a full table or partition scan.

• INSERT INTO . . . SELECT operations, if the SELECT involves a full table or partition scan.

• Update and delete operations on partitioned tables

Parallel Query

Parallel query is the most commonly used of Oracle's parallel execution features. It was the first parallel execution feature to be developed by Oracle and was introduced in Oracle Release 7.1 as the Oracle Parallel Query Option (PQO). Parallel execution can significantly reduce the elapsed time for large queries, but it doesn't apply to every query.

To parallelize a SELECT statement, the following conditions must be met:

• At least one of the tables is accessed through a full table scan, or an index is accessed through a range scan involving multiple partitions.

• If the execution involves a full table scan, the statement must contain a PARALLEL hint specifying the corresponding table, or the corresponding table must have a parallel declaration in its definition.

• If the execution involves an index range scan spanning multiple partitions, the statement must contain a PARALLEL_INDEX hint specifying the corresponding index, or the corresponding index must have a parallel declaration in its definition.

The following two sections explain how the degree of parallelism is chosen for a SELECT statement and discuss restrictions on the use of the parallel query feature.

Setting the Degree of Parallelism

Once Oracle decides to execute a SELECT statement in parallel, the degree of parallelism is determined by following precedence rules:

• Oracle retrieves the DEGREE and INSTANCES specifications from the definition of all tables and indexes involved in the query and chooses the highest values found for those settings.

• Oracle checks the statement for a parallel hint. If such a hint is found, the hint overrides the degree of parallelism obtained as a result of the previous step.

You can use the PARALLEL and PARALLEL_INDEX hints to specify the degree of parallelism for a SELECT statement. You can use the NOPARALLEL and NOPARALLEL_INDEX hints to ensure that parallel execution is not performed.

Example

alter table emp parallel (degree 4);
select degree from user_tables where table_name = 'EMP';
select count(*) from emp;
alter table emp noparallel;

SELECT /*+ PARALLEL(emp,4) */ COUNT(*) 
FROM emp;

Parallel DML

Data Manipulation Language (DML) operations such as INSERT, UPDATE, and DELETE can be parallelized by Oracle. Parallel execution can speed up large DML operations and is particularly advantageous in data warehousing environments where it's necessary to maintain large summary or historical tables. In OLTP systems, parallel DML sometimes can be used to improve the performance of long-running batch jobs.

Deciding to Parallelize a DML Statement

When you issue a DML statement such as an INSERT, UPDATE, or DELETE, Oracle applies a set of rules to determine whether that statement can be parallelized. For UPDATE and DELETE statements, the rules are identical. INSERT statements, however, have their own set of rules.

Rules for UPDATE and DELETE statements

• Oracle can parallelize UPDATE and DELETE statements on partitioned tables, but only when multiple partitions are involved.

• You cannot parallelize UPDATE or DELETE operations on a nonpartitioned table or when such operations affect only a single partition.

Rules for INSERT statements

• Standard INSERT statements using a VALUES clause cannot be parallelized.

• Oracle can parallelize only INSERT . . . SELECT . . . FROM statements.

Examples

alter session enable parallel dml;
insert /*+ parallel (emp_big,4,1) */
  into emp_big select * from emp;
commit;
alter session disable parallel dml;

Parallel DDL

Parallel DDL works for both tables and indexes, whether partitioned or nonpartitioned.

For nonpartitioned tables and indexes, only the following types of DDL statements can be parallelized:

CREATE TABLE...AS SELECT
CREATE INDEX
ALTER INDEX...REBUILD

If you're working with partitioned tables and indexes, the scope of Oracle's parallel DDL support broadens. The following statements can be parallelized for partitioned tables and indexes:

CREATE TABLE...AS SELECT
ALTER TABLE...MOVE PARTITION
ALTER TABLE...SPLIT PARTITION
CREATE INDEX
ALTER INDEX...REBUILD PARTITION
ALTER INDEX...SPLIT PARTITION

Not all tables allow these operations to be executed in parallel. Tables with object columns or LOB columns don't allow parallel DDL.

Example

create table big_emp parallel (degree 4)
  as select * from emp;

CREATE INDEX emp_ix ON emp (emp_id)
   TABLESPACE ind
   STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0 MAXEXTENTS 20)
   PARALLEL (DEGREE 4);

Parallel Data Loading

Oracle's SQL*Loader utility loads data into Oracle tables from external files. With some restrictions, SQL*Loader supports the loading of data in parallel. If you have a large amount of data to load, SQL*Loader's parallel support can dramatically reduce the elapsed time needed to perform. that load.

Initiating Parallel Data Loading

SQL*Loader supports parallel loading by allowing you to initiate multiple concurrent direct path load sessions that all load data into the same table or into the same partition of a partitioned table. Unlike the case when you execute a SQL statement in parallel, the task of dividing up the work falls on your shoulders. Follow these steps to use parallel data loading:

• Create multiple input datafiles.

• Create a SQL*Loader control file for each input datafile.

• Initiate multiple SQL*Loader sessions, one for each control file 
  and datafile pair.

Example

SQLLOAD scott/tiger CONTROL=con1.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con2.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con3.ctl DIRECT=TRUE PARALLEL=TRUE
SQLLOAD scott/tiger CONTROL=con4.ctl DIRECT=TRUE PARALLEL=TRUE

Note that the commands here should be executed from four different operating system sessions. The intent is to get four SQL*Loader sessions going at once, not to run four sessions one at a time. For example, if you are using the Unix operating system, you might open four command-prompt windows and execute one SQL*Loader command in each window.

Another important thing to note here is that you need to use the direct path in order to perform. a load in parallel, as explained in the next section. This is achieved by the command-line argument DIRECT=TRUE. Parallel loads are not possible using the conventional path option.

Parallel Recovery

Parallel recovery can speed up both instance recovery and media recovery. In parallel recovery, multiple parallel slave processes are used to perform. recovery operations. The SMON background process reads the redo log files, and the parallel slave processes apply the changes to the datafiles. 

In a serial recovery scenario, the SMON background process both reads the redo log files and applies the changes to the datafiles. This may take a considerably long time when multiple datafiles need to be recovered. However, when parallel recovery is being used, the SMON process is responsible only for reading the redo log files. The changes are applied to the datafiles by multiple parallel slave processes, thereby reducing the recovery time.

Recovery requires that the changes be applied to the datafiles in exactly the same order in which they occurred. This is achieved by single-threading the read phase of the recovery process by the SMON process. SMON reads the redo log files and serializes the changes before dispatching them to the parallel slave processes. The parallel slave processes then apply those changes to the datafiles in the proper order. Therefore, the reading of the redo log files is performed serially even during a parallel recovery operation.

Specifying the RECOVERY_PARALLELISM Parameter

The RECOVERY_PARALLELISM initialization parameter controls the degree of parallelism to use for a recovery. You can override that setting for a specific situation by using the RECOVER command's PARALLEL clause.

A value of or 1 indicates serial recovery, no parallelism will be used. The RECOVERY_PARALLELISM parameter setting cannot exceed the PARALLEL_MAX_SERVERS setting.

Example

RECOVER TABLESPACE tab PARALLEL (DEGREE 4);
RECOVER DATABASE PARALLEL (DEGREE DEFAULT);

Parallel Replication

Oracle provides replication mechanisms allowing you to maintain copies of database objects in multiple databases. Changes are propagated among these databases over database links. The SNP (snapshot) background processes perform. the replication process. For large volumes of replicated data, parallel propagation can be used to enhance throughput.

With parallel propagation, Oracle enlists multiple parallel slave processes to propagate replicated transactions using multiple parallel streams. Oracle orders the dependent transactions properly based on the System Change Number (SCN). During parallel propagation, you can see multiple connections to the destination database.

You enable parallel replication propagation at the database link level. A database link is created for a particular destination database. When you enable parallel propagation for a database link, Oracle uses multiple parallel slave processes to replicate to the corresponding destination.

Enable Parallel Replication Propagation

To enable parallel replication propagation from the SQL*Plus command line, you need to use the Oracle built-in package DBMS_DEFER_SYS. Execute the DBMS_DEFER_SYS.SCHEDULE_PUSH procedure for the destination database link, and pass the desired degree of parallelism as the value for the parallelism argument. 

Example for SQL*Plus

EXECUTE DBMS_DEFER_SYS.SCHEDULE_PUSH (-
DESTINATION => 'por1.world', -
INTERVAL => 'SYSDATE+1/24', -
NEXT_DATE => 'SYSDATE+1/24', -
PARALLELISM => 6);

This example sets the degree of parallelism to 6 for propagating to the "por1.world" destination database.

How Parallel Execution Works

Oracle divides the task of executing a SQL statement into multiple smaller units, each of which is executed by a separate process. When parallel execution is used, the user's shadow process takes on the role of the parallel coordinator. The parallel coordinator is also referred to as parallel execution coordinator or query coordinator.

The parallel coordinator does the following:

1. Dynamically divides the work into smaller units that can be parallelized.
     

2. Acquires a sufficient number of parallel processes to execute the individual smaller units. These parallel processes are called parallel slave processes. They also are sometimes referred to as parallel execution server processes, parallel server processes, parallel query slaves, or simply slave processes. The most common of the terms, parallel slave processes and slave processes, are used throughout this book.
    

3. Assigns each unit of work to a slave process.
    

4. Collects and combines the results from the slave processes, and returns those 
   results to the user process.
    

5. Releases the slave processes after the work is done.

The Pool of Parallel Slave Processes

Oracle maintains a pool of parallel slave processes for each instance. The parallel coordinator for a SQL statement assigns parallel tasks to slave processes from this pool. These parallel slave processes remain assigned to a task until its execution is complete. After that, these processes return to the pool and can be assigned tasks from some other parallel operation. A parallel slave process serves only one SQL statement at a time.

The following parameters control the number of parallel slave processes in the pool:

• PARALLEL_MIN_SERVERS 

Specifies the minimum number of parallel slave processes for an instance. When an instance starts up, it creates the specified number of parallel slave processes. The default value for this parameter is 0, meaning that no slave processes would be created at startup.

• PARALLEL_MAX_SERVERS

Specifies the maximum number of parallel slave processes that an instance is allowed to have at one time. The default value for PARALLEL_MAX_SERVERS is platform-specific.

It takes time and resources to create parallel slave processes. Since parallel slave processes can serve only one statement at a time, you should set PARALLEL_MIN_SERVERS to a relatively high value if you need to run lots of parallel statements concurrently. That way, performance won't suffer from the need to constantly create slave processes.

You also need to consider how to set PARALLEL_MAX_SERVERS. Each parallel slave process consumes memory. Setting PARALLEL_MAX_SERVERS too high may lead to memory shortages during peak usage times. On the other hand, if PARALLEL_MAX_SERVERS is set too low, some operations may not get a sufficient number of parallel slave processes.

The Degree of Parallelism

The number of parallel slave processes associated with an operation is called its degree of parallelism . Don't confuse this term with the DEGREE keyword. They aren't exactly the same thing. In Oracle, the degree of parallelism consists of two components, the number of instances to use and the number of slave processes to use on each instance. In Oracle's SQL syntax, the keywords INSTANCES and DEGREE are always used to specify values for these two components as follows:

• INSTANCES: Specifies the number of instances to use

• DEGREE: Specifies the number of slave processes to use on each instance

INSTANCES applies only to the Oracle Parallel Server configuration. Unless you are using OPS, the value of INSTANCES should be set to 1; any other value is meaningless. 

Level of parallel execution

The degree of parallelism used for a SQL statement can be specified at three different levels:

• Statement level 

Using hints or the PARALLEL clause

• Object level

Found in the definition of the table, index, or other object

• Instance level 

Using default values for the instance

Oracle determines the degree of parallelism to use for a SQL statement by checking each item in this list in the order shown. Oracle first checks for a degree of parallelism specification at the statement level. If it can't find one, it then checks the table or index definition. If the table or index definition does not explicitly specify values for DEGREE and INSTANCES, Oracle uses the default values established for the instance.

Specifying the degree of parallelism at the statement level

You can specify the degree of parallelism at the statement level by using hints or by using a PARALLEL clause. PARALLEL and PARALLEL_INDEX hints are used to specify the degree of parallelism used for queries and DML statements. However, DDL statements that support parallel execution provide an explicit PARALLEL clause in their syntax.

SELECT /*+ PARALLEL(orders,4,1) */ COUNT(*)
FROM orders;

Specifying the degree of parallelism at the object definition level

You can specify the degree of parallelism to use for a table or an index when you create it. You do that by using the PARALLEL clause of the CREATE TABLE and CREATE INDEX statements.

You also can specify a PARALLEL clause when you alter a table or an index.

ALTER TABLE order_items PARALLEL (DEGREE 4);

When you specify DEGREE and INSTANCES values at the table or index level, those values are used for all SQL statements involving the table or index unless overridden by a hint.

Specifying the degree of parallelism at the instance level

Each instance has associated with it a set of default values for DEGREE and INSTANCES. The default DEGREE value is either the number of CPUs available or the number of disks upon which a table or index is stored, whichever is less.

Oracle will use the instance-level defaults whenever the keyword DEFAULT is used in a hint or in a table or index definition. Oracle also will use the instance-level defaults when there are no hints and when no degree of parallelism has been specified at the table or index level.