Parallel processing in Oracle Database 12c

Improving performance (and by this, we usually mean query performance) is always a hot item with database administrators and users. One of the best and easiest ways to boost performance is to take advantage of the parallel processing option offered by the Oracle Database 12c.

Using normal (that is, serial) processing, the data involved in a single request (for example, user query) is handled by one database process. Using parallel processing, the request is broken down into multiple units to be worked on by multiple database processes. Each process looks at only a portion of the total data for the request.

Oracle serial processing:

 

Here, query (data) is handled by one resource process in the database with a single request.

Oracle parallel processing:

 

Parallel processing can help improve performance in situations where large amounts of data need to be examined or processed, such as scanning or joining large tables, creating large indexes, or scanning partitioned indexes. In order to realize the benefits of parallel processing, your database environment should not already be running at, or near, capacity. Parallel processing requires more processing, memory, and I/O resources than serial processing. Before implementing parallel processing, you may need to add hardware resources. Now, let’s focus on parallel processing database components.

Parallel processing database components:

The parallel processing components are the parallel execution coordinator and the parallel execution servers. The parallel execution coordinator is responsible for breaking down the request into as many processes as is specified by the request. Each process is passed on to a parallel execution server for execution, during which only a portion of the total data is worked on. The coordinator then assembles the results from each server and presents the complete results to the requester.

Parallel processing configuration:

Not much configuration is required for the 12c database to perform parallel processing. However, the number of configuration options that are required and will influence the effectiveness of parallelism.

To begin with, parallel processing is enabled by default in Oracle Database 12c. Below are the built-in parameters from the Oracle database 12c.

Oracle Database 12c 12.1.0.2 version:

 

Oracle 12c built-in parallel parameters:

 

Enabling parallelism for DML lets queries run better and resolve performance issues, but if you wish to execute a DML command in parallel mode, you must first issue the following command for the session in which the command is to be executed:

alter session enable parallel <table_name>;

Parallel processing can also be disabled for queries, DML, and DDL:

alter session disable parallel <table_name>;

Also, it can be forced with a degree of parallelism:

alter session force parallel ddl parallel 5;

Before 12c, parallel parameter “parallel_force_local” was set to false, but in 12c, it is set as default to true so that it will force parallelism to all queries running in the database. However, for a few updates/create/alter or any specific custom tables, it may deliver as required by enabling parallelism at the table level, which will do our job.

Several database initialization parameters affect parallel processing. When an Oracle instance starts, the parameters in the initialization file are used to define or specify the settings for the instance. Table specifications identify the initialization parameters that affect parallel processing. In many cases, the default values will provide results for large databases.

As you can see from the table enable/force, there are dependencies between parameters. Modifying one may not necessarily modify others. If you modify any of the parallel processing parameters, you may also have to modify the following database/instance parameters:

INSTANCE GROUPS

PROCESSES

SESSIONS

TRANSACTIONS

Invoke Parallel Execution

Parallel execution can be applied to tables, views, and materialized views. If all necessary configurations have been made, there are several ways to invoke parallel execution. The first way is during table creation (including materialized views) by using the parallel clause. If the table is being created using the results of a subquery, the loading of the table will be parallelized. In addition, by default, all queries that are executed against the table will be parallelized to the same extent.

 

The important line here is Line 7, which specifies the parallel clause. This line could also have included an integer to specify the degree of parallelism—that is, the number of processes that are to be used to execute the parallel process. As the degree of parallelism is omitted in this example, the number of processes used will be calculated as (number of CPUs) × (value of the PARALLEL_THREADS_PER_CPU initialization parameter). The degree of parallelism for a table or materialized view can be changed using an alter statement.

Parallel processing can also be invoked when the parallel hint is used in a select statement. This hint will override any default parallel processing options specified during table creation. The following listing illustrates the use of the parallel hint. Line 1 contains the parallel hint, specifying the table to be parallelized (Infolob) and the degree of parallelism (4):

 

In some cases, the Oracle Database 12c will alter how, or if, parallel processing is executed.

Key points from the Oracle 12c database:

• Parallel processing will be disabled for DML commands (for example: insert, update, delete, and merge) on tables with triggers or referential integrity constraints.
• If a table has a bitmap index, DML commands are always executed using serial processing if the table is non-partitioned. If the table is partitioned, parallel processing will occur, but Oracle will limit the degree of parallelism to the number of partitions affected by the command.
• Parallel processing can have a significant positive impact on performance. Impacts on performance are even greater when you combine range- or hash-based partitioning with parallel processing. With this configuration, each parallel process can act on a particular partition. For example, if you had a table partitioned by month, the parallel execution coordinator could divide the workup according to those partitions. In this way, partitioning and parallelism work together to provide results even faster.

This post was written by Narsing Rao Vatnala, Senior Oracle Exadata and EBS Database Administrator at Infolob Solutions. He can be reached at narsing.vatnala@infolob.com.