How-to: Parallel Processing

Server processes handle each user process request. Server processes communicate with the other Oracle background processes to read and write data.

In a dedicated server configuration, a server process handles requests for a single user process. In a shared server configuration a server process can handle requests from multiple user processes, this often allows better use of available system resources.

Client/server systems separate the user and server processes and execute them on different machines.

Oracle's shared server architecture increases the scalability of applications and the number of clients simultaneously connected to the database.
The number of shared server processes that can be created ranges between the values of the initialization parameters SHARED_SERVERS and MAX_SHARED_SERVERS.

Certain administrative activities cannot be performed while connected to a dispatcher process, including shutting down or starting an instance and media recovery.
To connect with administrator privileges in a system configured with shared servers, you must state in the connect string: SERVER=DEDICATED

Parallel Processing

When Oracle executes SQL statements in parallel, multiple processes work together simultaneously to execute a single SQL statement. By dividing the work among multiple processes, Oracle can execute the statement more quickly, this is called parallel execution or parallel processing.

During business hours, most OLTP systems should probably not use parallel execution. During off-hours, however, parallel execution can effectively process high-volume batch operations. Complex queries,
such as those involving joins or searches of very large tables, are often best executed in parallel.

You can use hints in a query to force the degree of parallelism (DOP) of each table, though in general, you should let the query optimizer determine this. Each SQL statement undergoes optimization when it is parsed. Therefore, the query optimizer will automatically adapt to improve performance.

In addition to queries, DDL and DML you can parallelize the use of SQL*Loader, where large amounts of data are routinely encountered. To speed up your loads, you can use a parallel direct-path load with the option: PARALLEL=TRUE

Parallel Query

To achieve parallelism for SQL query statements, one or more of the tables being scanned should have a parallel attribute.

Parallel DDL

To achieve parallelism for SQL DDL statements, the parallel clause should be specified.

Parallel DML

Due to the differences in locking between serial and parallel DML, you must explicitly enable parallel DML before you can use it.
To enable parallel DML in your session:
Then any DML issued against a table with a parallel attribute will occur in parallel,

Database Resource Manager

The Database Resource Manager controls the distribution of server resources among sessions by controlling the execution schedule inside the database (it does not enforce CPU allocation percentage limits as long as consumer groups are getting the resources they need.)

To create a resource plan use the PL/SQL package DBMS_RESOURCE_MANAGER
(create, update, and delete resource plans and resource consumer groups.)

Assign plans to a user with DBMS_RESOURCE_MANAGER_PRIVS

To activate a resource plan
Or modify INIT.ORA to include the following line:

To disable

On UNIX platforms, do not use the nice command to alter the operating-system run priorities of processes. Use of this command can lead to severe instability and unpredictable behavior of the Oracle Server.

HP's Process Resource Manager (PRM) or Sun's Solaris Resource Manager (SRM) are supported under specific conditions (listed in the Oracle documentation.) If you use one of these OS resource controls, make sure you don't also enable the Oracle Database Resource Manager.

"Parallel lines never do cross over" ~ Todd Rundgren

Copyright © 1999-2024
Some rights reserved