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 Oracle Database 12c (Enterprise Edition only).
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. Serial and parallel processing are illustrated in Figure 1 and 2, respectively.
Parallel processing can help improve performance in situations where large amounts of data need to be examined or processed, such as scanning large tables, joining large tables, creating large indexes, and 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. Let’s forge ahead by looking at the components involved in parallel processing.
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 specified by the request. Each process is passed 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
Generally, not much configuration is required for the database to perform parallel processing. There are, however, a number of configuration options that are required and will affect the effectiveness of parallelism.
To begin with, parallel processing is enabled by default for DDL (for example, create and alter) and query (for example, select) commands, but disabled for DML (say, insert, update, delete and merge) commands. 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:
Parallel processing can also be disabled for queries, DML, and DDL:
It can also be forced with a degree of parallelism:
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 1 identifies the initialization parameters that affect parallel processing. In many cases, the default values will provide adequate results for your large database. Specifics of your own environment will influence your decisions on the best values to use.
As you can see from Table 1, there are dependencies between parameters. Modifying one may necessitate modifying 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), 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 next listing shows an example of specifying the parallel option for a table creation:
The important line here is Line 7, specifying 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 the number of CPUs × the 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 (commission) and the degree of parallelism (4):
In some cases, Oracle Database 12c will alter how, or if, parallel processing is executed. Examples of these include the following:
- 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 work up according to those partitions. This way, partitioning and parallelism work together to provide results even faster.
Leave a Reply