PARALLEL PROCESS

PARALLEL PROCESS:

DEFINITION - In computers, parallel processing is the processing of program instructions by dividing them among multiple processors with the objective of running a program in less time. In the earliest computers, only one program ran at a time. A computation-intensive program that took one hour to run and a tape copying program that took one hour to run would take a total of two hours to run. An early form of parallel processing allowed the interleaved execution of both programs together. The computer would start an I/O operation, and while it was waiting for the operation to complete, it would execute the processor-intensive program. The total execution time for the two jobs would be a little over one hour.


The Oracle Parallel Query Option

Introduced in later versions of Oracle7 the parallel query option (PQO) allows multiple processes to simultaneously fetch records and perform sorting operations. This parallelization of operations can lead to impressive speed improvements in a properly set up parallel environment.

The most important item to specify in a parallel environment is the number of parallel query slaves. Too few and you don’t get the full benefits, too many and they end up competing with each other for resources. Of course parallel operations are of little or no benefit if your system doesn’t have parallel processors and your tables aren’t spread across multiple disks in a stripe set or partitioned. Along with the number of parallel query slaves the degree of parallel (DOP) for the tables and indexes needs to be set properly.

The maximum number of parallel query slaves should generally be set to at least twice the number of CPUs or to twice the number of disks that he object was spread across. The DOP can be determined by forcing a full table scan for tables (use a where 1=2 in a select count(*) from the table) and then timing the response for different DOP settings. These numbers are then adjusted up or down depending on performance.

Parallel query settings

There are several initialization parameters that pertain to parallel query:

parallel_adaptive_multi_user

TRUE or FALSE, defaults to FALSE, when set to TRUE, enables an adaptive algorithm designed to improve performance in multi-user environments that use Parallel Query(PQ). It does this by automatically reducing the requested degree of parallelism based on the current number of active PQ users on the system. The effective degree of parallelism will be based on the degree of parallelism from the table or hint divided by the total number of PQ users. The algorithm assumes that the degree of parallelism provided has been tuned for optimal performance in a single user environment.

At Oracle Openworld 2007, Oracle recommends never to turn-on the parallel_adaptive_multi_user parameter.

optimizer_percent_parallel

Specifies the amount of parallelism that the optimizer uses in its cost functions. The default of 0 means that the optimizer chooses the best serial plan. A value of 100 means that the optimizer uses each object's degree of parallelism in computing the cost of a full table scan operation. Low values favor indexes, and high values favor table scans.

Cost-based optimization will always be used for any query that references an object with a nonzero degree of parallelism. For such queries a RULE hint or optimizer mode or goal will be ignored. Use of a FIRST_ROWS hint or optimizer mode will override a nonzero setting of OPTIMIZER_PERCENT_PARALLEL.

parallel_min_percent

This specifies the minimum percent of threads required for parallel query. Setting this parameter ensures that a parallel query will not be executed sequentially if adequate resources are not available. The default value of 0 means that this parameter is not used.

If too few query slaves are available, an error message is displayed and the query is not executed. Consider the following settings:

PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10

In a system with 20 instances up and running, the system would have a maximum of 200 query slaves available. If 190 slaves are already in use and a new user wants to run a query with 40 slaves (for example, degree 2 instances 20), an error message would be returned because 20 instances (that is, 50% of 40) are not available.

parallel_min_servers

This specifies the minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.

parallel_max_servers

Parallel_max_servers specifies the maximum number of parallel query servers or parallel recovery processes for an instance. Oracle will increase the number of query servers as demand requires from the number created at instance startup up to this value. The same value should be used for all instances in a parallel server environment.

Proper setting of the PARALLEL_MAX_SERVERS parameter ensures that the number of query servers in use will not cause a memory resource shortage during periods of peak database use.

If PARALLEL_MAX_SERVERS is set too low, some queries may not have a query server available to them during query processing.

Setting PARALLEL_MAX_SERVERS too high leads to memory resource shortages during peak periods, which can degrade performance. For each instance to which you do not want to apply the parallel query option, set this initialization parameter to zero.

If you have reached the limit of PARALLEL_MAX_SERVERS on an instance and you attempt to query a GV$ view, one additional parallel server process will be spawned for this purpose. This extra process will serve any subsequent GV$ queries until expiration of the PARALLEL_SERVER_IDLE_TIME, at which point the process will terminate. The extra process is not available for any parallel operation other than GV$ queries.

Note that if PARALLEL_MAX_SERVERS is set to zero for an instance, then no additional parallel server process will be allocated to accommodate a GV$ query.

parallel_server_idle_time

Specifies the amount of idle time after which Oracle terminates a process for parallel operations (parallel query, parallel DML, or parallel DDL). This value is expressed in minutes. The parameter must be set to 1 or greater for the query processes to terminate. 0 means the processes are never terminated.

parallel_execution_message_size

Specifies the size of messages for parallel execution (Parallel Query, PDML, Parallel Recovery, replication). The default value should be adequate for most applications. Typical values are 2148 or 4096 bytes. Larger values would require a larger shared pool.

parallel_min_message_pool

The parallel_min_message_pool parameter defaults to (cpus*parallel_max_servers*1.5*(OS message buffer size) or cpus*5*1.5*(OS message size)). Specifies the minimum permanent amount of memory which will be allocated from the SHARED POOL, to be used for messages in parallel execution.

This memory is allocated at startup time if PARALLEL_MIN_SERVERS is set to a non-zero value, or when the server is first allocated. Setting this parameter is most effective when PARALLEL_MIN_SERVERS is set to a non-zero value, because the memory will be allocated in a contiguous section.

This parameter should only be set if the default formula is known to be significantly inaccurate. setting this parameter too high will lead to a shortage of memory for the shared pool; setting it too low will lead to costlier memory allocation when doing parallel execution. This parameter cannot be set to a number higher than 90% of the shared pool.

parallel_broadcast_enabled

This defaults to FALSE and allows you to improve performance in certain cases involving hash and merge joins. When set to TRUE, if you are joining a very large join result set with a very small result set (size being measured in bytes, rather than number of rows), the optimizer has the option of broadcasting the row sources of the small result set, such that a single table queue will send all of the small set's rows to each of the parallel servers which are processing the rows of the larger set. The result is enhanced performance.

shared_pool_size

The shared pool will have to be increased in size to accommodate the parallel query message areas and IO queues. I suggest that the LARGE POOL be designated to prevent PQO from causing shared pool problems.

large_pool_size

The large pool will automatically be configured at a minimum size of 600k if certain initialization parameters are set in Oracle8. I suggest manually setting the size.


Oracle Parallel Automatic tuning parameter:

Oracle parallel query (OPQ) is a great way to speed full full-scan operations, but Oracle parallelism can be problematic if it is not implemented properly.

Oracle provides a general method for implementing parallelism with the parallel_automatic_tuning=true parameter setting, but there are cases when using PAT can actually degrade your Oracle performance. The Oracle docs note:

"With parallel automatic tuning enabled, Oracle determines parameter settings for each environment based on the number of CPUs on your system and the value set for PARALLEL_THREADS_PER_CPU.

The default values Oracle sets for parallel execution processing when PARALLEL_AUTOMATIC_TUNING is TRUE are usually optimal for most environments. In most cases, Oracle's automatically derived settings are at least as effective as manually derived settings."

I disagree.

The parallel_automatic_tuning parameter feature was created for DSS and Data Warehouse environments using the all_rows optimizer mode on servers with many CPU processors to ensure that the common full table scans are parallelized to an optimal DEGREE.

However, parallel_automatic_tuning is not always appropriate for OLTP and online systems, where setting parallel_automatic_tuning may change the SQL optimizers perception of the costs of full scan operations, causing indexes not to be used.

For non-warehouse environments, I do not recommend setting parallel_automatic_tuning, or turning on parallel query at the system or table level. I get the best performance my manually adding the "parallel" hint to query where I want the full can to be done in parallel.


PARALLEL_THREADS_PER_CPU specifies the default degree of parallelism for the instance and determines the parallel adaptive and load balancing algorithms. The parameter describes the number of parallel execution processes or threads that a CPU can handle during parallel execution.

The default is platform-dependent and is adequate in most cases. You should decrease the value of this parameter if the machine appears to be overloaded when a representative parallel query is executed. You should increase the value if the system is I/O bound.

* Parallel query. The ability to break up the execution of a SELECT statement into multiple tasks, and then to execute those tasks in parallel using multiple processors.
* Parallel data loading. The ability to run multiple SQL*Loader sessions in parallel, which all load data into the same table.
* Parallel DML (Data Manipulation Language). The ability to parallelize INSERT, UPDATE, and DELETE statements.
* Parallel DDL (object creation). The ability to parallelize DDL statements such as CREATE TABLE, CREATE INDEX, and ALTER INDEX.
* Parallel recovery. The ability to use multiple processes to perform instance and media recovery.
* Parallel replication propagation. The ability to propagate changes from one database to another using multiple processes working together in parallel.