Parallel Execution of SQL StatementsEach SQL statement undergoes an optimization and parallelization process when it is parsed. If parallel execution is chosen, then the following steps occur:.The user session or shadow process takes on the role of a coordinator, often called the query coordinator.The query coordinator obtains the necessary number of parallel servers.The SQL statement is executed as a sequence of operations (a full table scan to perform a join on a nonindexed column, an ORDER BY clause, and so on). The parallel execution servers performs each operation in parallel if possible.When the parallel servers are finished executing the statement, the query coordinator performs any portion of the work that cannot be executed in parallel. For example, a parallel query with a SUM operation requires adding the individual subtotals calculated by each parallel server.Finally, the query coordinator returns any results to the user.After the optimizer determines the execution plan of a statement, the parallel execution coordinator determines the parallel execution method for each operation in the plan. For example, the parallel execution method might be to perform a parallel full table scan by block range or a parallel index range scan by partition. The coordinator must decide whether an operation can be performed in parallel and, if so, how many parallel execution servers to enlist. The number of parallel execution servers in one set is the degree of parallelism (DOP).

1. Example Of Set Processing In Peoplesoft
2. Peoplesoft Application Engine Tables
3. Peoplesoft Warning No Dedicated Instances Available

• In a similar fashion you can instruct Oracle to scan index ranges in parallel with the PARALLELINDEX(tabnameoralias indexname dop). With NOPARALLELINDEX( tabnameoralias indexname ) you can disable parallel index range scans.
• Identifying How to use PeopleCode in Application Engine Programs; Inserting. Describing Parallel Processing; Implementing Parallel Processing; Using.

Setting up Approval Process in PeopleSoft Approval Workflow Engine (AWE) Application Engine. Parallel Processing using Temporary Record with Application Engine in PeopleSoft; Parallel Processing using Temporary Record with Application Engine in PeopleSoft; Application Engine with Disable Restart 'off' is going 'No Success' in PeopleSoft; Integration Broker.

Parallelism Between OperationsGiven two sets of parallel execution servers SS1 and SS2 for the query plan illustrated in, the execution proceeds as follows: each server set (SS1 and SS2) has four execution processes because of the PARALLEL hint in the query that specifies the DOP.Child set SS1 first scans the table customers and sends rows to SS2, which builds a hash table on the rows. In other words, the consumers in SS2 and the producers in SS1 work concurrently: one in scanning customers in parallel, the other is consuming rows and building the hash table to enable the hash join in parallel. This is an example of inter-operation parallelism.After SS1 has finished scanning the entire customers table, it scans the sales table in parallel. It sends its rows to servers in SS2, which then perform the probes to finish the hash-join in parallel. After SS1 has scanned the sales table in parallel and sent the rows to SS2, it switches to performing the GROUP BY operation in parallel. This is how two server sets run concurrently to achieve inter-operation parallelism across various operators in the query tree.Another important aspect of parallel execution is the redistribution of rows when they are sent from servers in one server set to servers in another. For the query plan in, after a server process in SS1 scans a row from the customers table, which server process in SS2 should it send it to?

The operator into which the rows are flowing decides the redistribution. In this case, the redistribution of rows flowing up from SS1 performing the parallel scan of customers into SS2 performing the parallel hash-join is done by hash partitioning on the join column.

That is, a server process scanning customers computes a hash function of the value of the column customers.custid to decide the number of the server process in SS2 to send it to. The redistribution method used in parallel queries explicitly shows in the Distrib column in the EXPLAIN PLAN of the query. In, this can be seen on lines 5, 8, and 12 of the EXPLAIN PLAN. Producer or Consumer OperationsOperations that require the output of other operations are known as consumer operations. In, the GROUP BY SORT operation is the consumer of the HASH JOIN operation because GROUP BY SORT requires the HASH JOIN output.Consumer operations can begin consuming rows as soon as the producer operations have produced rows.

In, while the parallel execution servers are producing rows in the FULL SCAN of the sales table, another set of parallel execution servers can begin to perform the HASH JOIN operation to consume the rows.Each of the two operations performed concurrently is given its own set of parallel execution servers. Therefore, both query operations and the data flow tree itself have parallelism.

The parallelism of an individual operation is called intra-operation parallelism and the parallelism between operations in a data flow tree is called inter-operation parallelism. Due to the producer-consumer nature of the Oracle Database operations, only two operations in a given tree must be performed simultaneously to minimize execution time. To illustrate intra- and inter-operation parallelism, consider the following statement:SELECT. FROM employees ORDER BY lastname;The execution plan implements a full scan of the employees table.

This operation is followed by a sorting of the retrieved rows, based on the value of the lastname column. For the sake of this example, assume the lastname column is not indexed. Also assume that the DOP for the query is set to 4, which means that four parallel execution servers can be active for any given operation.illustrates the parallel execution of the example query. Figure 8-2 Inter-operation Parallelism and Dynamic PartitioningAs illustrated in, there are actually eight parallel execution servers involved in the query even though the DOP is 4. This is because a producer and consumer operator can be performed at the same time (inter-operation parallelism).Also note that all of the parallel execution servers involved in the scan operation send rows to the appropriate parallel execution server performing the SORT operation. If a row scanned by a parallel execution server contains a value for the lastname column between A and G, that row is sent to the first ORDER BY parallel execution server. When the scan operation is complete, the sorting processes can return the sorted results to the query coordinator, which, in turn, returns the complete query results to the user.

How Parallel Execution Servers CommunicateTo execute a query in parallel, Oracle Database generally creates a set of producer parallel execution servers and a set of consumer parallel execution servers. The producer server retrieves rows from tables and the consumer server performs operations such as join, sort, DML, and DDL on these rows. Each server in the producer set has a connection to each server in the consumer set. The number of virtual connections between parallel execution servers increases as the square of the degree of parallelism.Each communication channel has at least one, and sometimes up to four memory buffers, which are allocated from the shared pool. Multiple memory buffers facilitate asynchronous communication among the parallel execution servers.A single-instance environment uses at most three buffers for each communication channel. An Oracle Real Application Clusters environment uses at most four buffers for each channel.

Illustrates message buffers and how producer parallel execution servers connect to consumer parallel execution servers. Figure 8-3 Parallel Execution Server Connections and BuffersWhen a connection is between two processes on the same instance, the servers communicate by passing the buffers back and forth in memory (in the shared pool). When the connection is between processes in different instances, the messages are sent using external high-speed network protocols over the interconnect. In, the DOP equals the number of parallel execution servers, which in this case is n. Does not show the parallel execution coordinator. Each parallel execution server actually has an additional connection to the parallel execution coordinator. It is important to size the shared pool adequately when using parallel execution.

If there is not enough free space in the shared pool to allocate the necessary memory buffers for a parallel server, it fails to start. Manually Specifying the Degree of ParallelismA specific DOP can be requested from Oracle Database.

For example, you can set a fixed DOP at a table or index level:ALTER TABLES sales PARALLEL 8;ALTER TABLE customers PARALLEL 4;In this case, queries accessing just the sales table use a requested DOP of 8 and queries accessing the customers table request a DOP of 4. A query accessing both the sales and the customers tables is processed with a DOP of 8 and potentially allocates 16 parallel servers (producer or consumer); whenever different DOPs are specified, Oracle Database uses the higher DOP. Default ParallelismIf the PARALLEL clause is specified but no degree of parallelism is listed, the object gets the default DOP. Default parallelism uses a formula to determine the DOP based on the system configuration, as in the following:.For a single instance, DOP = PARALLELTHREADSPERCPU x CPUCOUNT.For an Oracle RAC configuration, DOP = PARALLELTHREADSPERCPU x CPUCOUNT x INSTANCECOUNTBy default, INSTANCECOUNT is all of the nodes in the cluster. However, if you have used Oracle RAC services to limit the number of nodes across which a parallel operation can execute, then the number of participating nodes is the number of nodes belonging to that service.

For example, on a 4-node Oracle RAC cluster, with each node having 8 CPU cores and no Oracle RAC services, the default DOP would be 2 x 8 x 4 = 64.The default DOP algorithm is designed to use maximum resources and assumes that the operation finishes faster if it can use more resources. Default parallelism targets the single-user workload. In a multiuser environment, default parallelism is not recommended.The DOP for a SQL statement can also be set or limited by the Resource Manager. See for more information. Automatic Parallel Degree PolicyWhen the parameter PARALLELDEGREEPOLICY is set to AUTO, Oracle Database automatically decides if a statement should execute in parallel or not and what DOP it should use.

Oracle Database also determines if the statement can be executed immediately or if it is queued until more system resources are available. Determining Degree of ParallelismThe optimizer automatically determines the DOP for a statement based on the resource requirements of the statement. The optimizer uses the cost of all scan operations (full table scan, index fast full scan, and so on) in the execution plan to determine the necessary DOP for the statement.However, the optimizer limits the actual DOP to ensure parallel server processes do not overwhelm the system.

This limit is set by the parameter PARALLELDEGREELIMIT. The default for value for this parameter is CPU, which means the number of processes is limited by the number of CPUs on the system ( PARALLELTHREADSPERCPU. CPUCOUNT.

number of instances available) also known as the default DOP. Setting Automatic Degree of Parallelism Using HintsYou can use the PARALLEL hint to force parallelism. It takes an optional parameter: the DOP at which the statement should run. In-Memory Parallel ExecutionWhen the parameter PARALLELDEGREEPOLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache).

The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.

Adaptive ParallelismThe adaptive multiuser algorithm, which is enabled by default, reduces the degree of parallelism as the load on the system increases. When using the Oracle Database adaptive parallelism capabilities, the database uses an algorithm at SQL execution time to determine whether a parallel operation should receive the requested DOP or have its DOP lower to ensure the system is not overloaded.In a system that makes aggressive use of parallel execution by using a high DOP, the adaptive algorithm adjusts the DOP down when only a few operations are running in parallel. While the algorithm still ensures optimal resource utilization, users may experience inconsistent response times. Using solely the adaptive parallelism capabilities in an environment that requires deterministic response times is not advised. Adaptive parallelism is controlled through the database initialization parameter PARALLELADAPTIVEMULTIUSER. Controlling Automatic DOP, Parallel Statement Queuing, and In-Memory Parallel ExecutionThe initialization parameter PARALLELDEGREEPOLICY controls whether automatic degree of parallelism (DOP), parallel statement queuing, and in-memory parallel execution are enabled. This parameter has three possible values:.MANUAL - Disables automatic DOP, statement queuing and in-memory parallel execution.

It reverts the behavior of parallel execution to what it was previous to Oracle Database 11 g, Release 2 (11.2), which is the default.LIMITED - Enables automatic DOP for some statements but parallel statement queuing and in-memory parallel execution are disabled. Automatic DOP is applied only to statements that access tables or indexes declared explicitly with the PARALLEL clause. Tables and indexes that have a DOP specified use that explicit DOP setting.AUTO - Enables automatic DOP, parallel statement queuing, and in-memory parallel execution.By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement. The degree of parallelism used is exactly what was specified. No parallel statement queue occurs and parallel execution does not use the buffer cache. For information about the parallel statement queue, refer to.If you want Oracle Database to automatically decide the degree of parallelism only for a subset of SQL statements that touch a specific subset of objects, then set PARALLELDEGREEPOLICY to LIMITED and set the parallel clause on that subset of objects. If you want Oracle Database to automatically decide the degree of parallelism, then set PARALLELDEGREEPOLICY to AUTO.When PARALLELDEGREEPOLICY is set to AUTO, Oracle Database determines whether the statement should run in parallel based on the cost of the operations in the execution plan and the hardware characteristics.

The hardware characteristics include I/O calibration statistics so these statistics must be gathered otherwise Oracle Database does not use the automatic degree policy feature.If I/O calibration is not run to gather the required statistics, the explain plan output includes the following text in its notes:automatic DOP: skipped because of IO calibrate statistics are missingI/O calibration statistics can be gathered with the PL/SQL DBMSRESOURCEMANAGER.CALIBRATEIO procedure. I/O calibration is a one-time action if the physical hardware does not change.

Parallel Statement QueuingWhen the parameter PARALLELDEGREEPOLICY is set to AUTO, Oracle Database queues SQL statements that require parallel execution if the necessary parallel server processes are not available. After the necessary resources become available, the SQL statement is dequeued and allowed to execute.

Note:This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).By default, the parallel statement queue operates as a first-in, first-out queue. By configuring and setting a resource plan, you can control the order in which parallel statements are dequeued and the number of parallel servers used by each workload or consumer group.Resource plans and consumer groups are created using the DBMSRESOURCEMANAGER PL/SQL package. A resource plan consists of a collection of directives for each consumer group which specify controls and allocations for various database resources, such as parallel servers. A resource plan is enabled by setting the RESOURCEMANAGERPLAN parameter to the name of the resource plan.The following sections describe the directives that can be used to manage the processing of parallel statements for consumer groups when the parallel degree policy is set to AUTO.In all cases, the parallel statement queue is managed as a single queue on an Oracle RAC database. Limits for each consumer group apply to all sessions across the Oracle RAC database that belong to that consumer group. The queuing of parallel statements occurs based on the sum of the values of the PARALLELSERVERSTARGET initialization parameter across all database instances. Managing the Order of the Parallel Statement QueueYou can use Resource Manager to manage the order that parallel statements are dequeued from the parallel statement queue.

The parallel statements for a particular consumer group are always dequeued in FIFO order. The directives mgmtp1. Mgmtp8 are used to determine which consumer group's parallel statement should be dequeued next.

These directives are configured using the CREATEPLANDIRECTIVE or UPDATEPLANDIRECTIVE procedure in the DBMSRESOURCEMANAGER PL/SQL package.For example, you can create the PQHIGH, PQMEDIUM, and PQLOW consumer groups and map parallel statement sessions to these consumer groups based on priority. You then create a resource plan that sets mgmtp1 to 70% for PQHIGH, 25% for PQMEDIUM, and 5% for PQLOW. This indicates that PQHIGH statements are dequeued next with a probability of 70% of the time, PQMEDIUM dequeued next with a probability of 25% of the time, and PQLOW dequeued next with a probability of 5% of the time. Limiting the Parallel Server Resources for a Consumer GroupYou can use Resource Manager to limit the number of parallel servers that parallel statements from lower priority consumer groups can use for parallel statement processing. Using Resource Manager you can map parallel statement sessions to different consumer groups that each have specific limits on the number of the parallel servers that can be used. When these limits are specified, parallel statements from a consumer group are queued when this limit would be exceeded.This limitation becomes useful when a database has high priority and low priority consumer groups.

Without limits, a user may issue a large number of parallel statements from a low-priority consumer group that utilizes all parallel servers. When a parallel statement from a high priority consumer group is issued, the resource allocation directives can ensure that the high priority parallel statement is dequeued first. By limiting the number of parallel servers a low-priority consumer group can use, you can ensure that there are always some parallel servers available for a high priority consumer group.To limit the parallel servers used by a consumer group, use the paralleltargetpercentage parameter with the CREATEPLANDIRECTIVE procedure or the newparalleltargetpercentage parameter with the UPDATEPLANDIRECTIVE procedure in the DBMSRESOURCEMANAGER package. The paralleltargetpercentage and newparalleltargetpercentage parameters specify the maximum percentage of the Oracle RAC-wide parallel server pool that is specified by PARALLELSERVERSTARGET that a consumer group can use.For example, on an Oracle RAC database system, the initialization parameter PARALLELSERVERSTARGET is set to 32 on two nodes so there are a total of 32 x 2 = 64 parallel servers that can be used before queuing begins. You can set up the consumer group PQLOW to use 50% of the available parallel servers ( paralleltargetpercentage = 50) and low priority statements can then be mapped to the PQLOW consumer group.

Example Of Set Processing In Peoplesoft

This scenario limits any parallel statements from the PQLOW consumer group to 64 x 50% = 32 parallel servers, even though there are more inactive or unused parallel servers. In this scenario, after the statements from the PQLOW consumer group have used 32 parallel servers, statements from that consumer group are queued.It is possible in one database to have some sessions with the parallelism degree policy set to MANUAL and some sessions set to AUTO.

In this scenario, only the sessions with parallelism degree policy set to AUTO can be queued. However, the parallel servers used in sessions where the parallelism degree policy is set to MANUAL are included in the total of all parallel servers used by a consumer group.For information about limiting parallel resources for users, refer to. Specifying a Parallel Statement Queue Timeout for Each Consumer GroupYou can use Resource Manager to set the maximum queue timeout limit so that parallel statements do not stay in the queue for long periods of time. Using Resource Manager you can map parallel statement sessions to different consumer groups that each have specific maximum timeout limits in a resource plan.To manage the queue timeout, the parallelqueuetimeout parameter is used with the CREATEPLANDIRECTIVE procedure or the newparallelqueuetimeout parameter is used with the UPDATEPLANDIRECTIVE procedure in the DBMSRESOURCEMANAGER package. The parallelqueuetimeout and newparallelqueuetimeout parameters specify the time in seconds that a statement can remain in a consumer group parallel statement queue.

After the timeout period has expired, the statement is terminated with error ORA-7454 and removed from the parallel statement queue. Specifying a Degree of Parallelism Limit for Consumer GroupsYou can use Resource Manager to the limit the degree of parallelism for specific consumer groups. Using Resource Manager you can map parallel statement sessions to different consumer groups that each have specific limits for the degree of parallelism in a resource plan.To manage the limit of parallelism in consumer groups, use the paralleldegreelimitp1 parameter with the CREATEPLANDIRECTIVE procedure in the DBMSRESOURCEMANAGER package or the newparalleldegreelimitp1 parameter with the UPDATEPLANDIRECTIVE procedure in the DBMSRESOURCEMANAGER package. The paralleldegreelimitp1 and newparalleldegreelimitp1 parameters specify a limit on the degree of parallelism for any operation.For example, you can create the PQHIGH, PQMEDIUM, and PQLOW consumer groups and map parallel statement sessions to these consumer groups based on priority. You then create a resource plan that specifies degree of parallelism limits so that the PQHIGH limit is set to 16, the PQMEDIUM limit is set to 8, and the PQLOW limit is set to 2.

A Sample Scenario for Managing Statements in the Parallel QueueThis scenario discusses how to manage statements in the parallel queue with consumer groups set up with Resource Manager. For this scenario, consider a data warehouse workload that consists of three types of SQL statements:.Short-running SQL statementsShort-running identifies statements running less than one minute.

You expect these statements to have very good response times.Medium-running SQL statementsMedium-running identifies statements running more than one minute, but less than 15 minutes. You expect these statements to have reasonably good response times.Long-running SQL statementsLong-running identifies statements that are ad-hoc or complex queries running more than 15 minutes. You expect these statements to take a long time.For this data warehouse workload, you want better response times for the short-running statements. Example 8-3 Using consumer groups to set priorities in the parallel statement queueBEGINDBMSRESOURCEMANAGER.CREATEPENDINGAREA;/. Create consumer groups. By default, users start in OTHERGROUPS, which is automatically.

created for every database./DBMSRESOURCEMANAGER.CREATECONSUMERGROUP('MEDIUMSQLGROUP','Medium-running SQL statements, between 1 and 15 minutes. Medium priority.' );DBMSRESOURCEMANAGER.CREATECONSUMERGROUP('LONGSQLGROUP','Long-running SQL statements of over 15 minutes.

Low priority.' Note:This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).Often it is important for a report or batch job that consists of multiple parallel statements to complete as quickly as possible. For example, when many reports are launched simultaneously, you may want all of the reports to complete as quickly as possible. However, you also want some specific reports to complete first, rather than all reports finishing at the same time.If a report contains multiple parallel statements and PARALLELDEGREEPOLICY is set to AUTO, then each parallel statement may be forced to wait in the queue on a busy database. Managing Parallel Statement Queuing with HintsYou can use the NOSTATEMENTQUEUING and STATEMENTQUEUING hints in SQL statements to manage parallel statement queuing.NOSTATEMENTQUEUINGWhen PARALLELDEGREEPOLICY is set to AUTO, this hint enables a statement to bypass the parallel statement queue.

For example:SELECT /.+ NOSTATEMENTQUEUING./ emp.lastname, dpt.departmentnameFROM employees emp, departments dptWHERE emp.departmentid = dpt.departmentid;.STATEMENTQUEUINGWhen PARALLELDEGREEPOLICY is not set to AUTO, this hint enables a statement to be delayed and to only run when parallel processes are available to run at the requested DOP. For example:SELECT /.+ STATEMENTQUEUING./ emp.lastname, dpt.departmentnameFROM employees emp, departments dptWHERE emp.departmentid = dpt.departmentid.

Peoplesoft Application Engine Tables

Parallel Execution Server PoolWhen an instance starts, Oracle Database creates a pool of parallel execution servers, which are available for any parallel operation. The initialization parameter PARALLELMINSERVERS specifies the number of parallel execution servers that Oracle Database creates at instance startup.When executing a parallel operation, the parallel execution coordinator obtains parallel execution servers from the pool and assigns them to the operation.

If necessary, Oracle Database can create additional parallel execution servers for the operation. These parallel execution servers remain with the operation throughout execution. After the statement has been processed completely, the parallel execution servers return to the pool.If the number of parallel operations increases, Oracle Database creates additional parallel execution servers to handle incoming requests. However, Oracle Database never creates more parallel execution servers for an instance than the value specified by the initialization parameter PARALLELMAXSERVERS.If the number of parallel operations decreases, Oracle Database terminates any parallel execution servers that have been idle for a threshold interval.

Oracle Database does not reduce the size of the pool less than the value of PARALLELMINSERVERS, no matter how long the parallel execution servers have been idle. Processing without Enough Parallel Execution ServersOracle Database can process a parallel operation with fewer than the requested number of processes. If all parallel execution servers in the pool are occupied and the maximum number of parallel execution servers has been started, the parallel execution coordinator switches to serial processing.See for information about using the initialization parameter PARALLELMINPERCENT and for information about the PARALLELMINPERCENT and PARALLELMAXSERVERS initialization parameters. Block Range GranulesBlock range granules are the basic unit of most parallel operations, even on partitioned tables. Therefore, from an Oracle Database perspective, the degree of parallelism is not related to the number of partitions.Block range granules are ranges of physical blocks from a table.

Oracle Database computes the number and the size of the granules during run-time to optimize and balance the work distribution for all affected parallel execution servers. The number and size of granules are dependent upon the size of the object and the DOP. Block range granules do not depend on static preallocation of tables or indexes. During the computation of the granules, Oracle Database takes the DOP into account and tries to assign granules from different data files to each of the parallel execution servers to avoid contention whenever possible. Additionally, Oracle Database considers the disk affinity of the granules on massive parallel processing (MPP) systems to take advantage of the physical proximity between parallel execution servers and disks.

Peoplesoft Warning No Dedicated Instances Available

Partition GranulesWhen partition granules are used, a parallel server process works on an entire partition or subpartition of a table or index. Because partition granules are statically determined by the structure of the table or index when a table or index is created, partition granules do not give you the flexibility in executing an operation in parallel that block granules do. The maximum allowable DOP is the number of partitions. Balancing the WorkloadTo optimize performance, all parallel execution servers should have equal workloads. For SQL statements run in parallel by block range or by parallel execution servers, the workload is dynamically divided among the parallel execution servers. This minimizes workload skewing, which occurs when some parallel execution servers perform significantly more work than the other processes.For the relatively few SQL statements executed in parallel by partitions, if the workload is evenly distributed among the partitions, you can optimize performance by matching the number of parallel execution servers to the number of partitions or by choosing a DOP in which the number of partitions is a multiple of the number of processes.

This applies to partition-wise joins and parallel DML on tables created before Oracle9 i. See for more information.For example, suppose a table has 16 partitions, and a parallel operation divides the work evenly among them. You can use 16 parallel execution servers (DOP equals 16) to do the work in approximately one-tenth the time that one process would take. You might also use five processes to do the work in one-fifth the time, or two processes to do the work in one-half the time.If, however, you use 15 processes to work on 16 partitions, the first process to finish its work on one partition then begins work on the 16th partition; and as the other processes finish their work, they become idle. This configuration does not provide good performance when the work is evenly divided among partitions.

When the work is unevenly divided, the performance varies depending on whether the partition that is left for last has more or less work than the other partitions.Similarly, suppose you use six processes to work on 16 partitions and the work is evenly divided. In this case, each process works on a second partition after finishing its first partition, but only four of the processes work on a third partition while the other two remain idle.In general, you cannot assume that the time taken to perform a parallel operation on a given number of partitions (N) with a given number of parallel execution servers (P) equals N divided by P. Get sf jpgs on olympus. This formula does not consider the possibility that some processes might have to wait while others finish working on the last partitions. By choosing an appropriate DOP, however, you can minimize the workload skew and optimize performance. Parallel Execution Using Oracle RACBy default, in an Oracle RAC environment, a SQL statement executed in parallel can run across all of the nodes in the cluster.

For this cross-node or inter-node parallel execution to perform, the interconnection in the Oracle RAC environment must be size appropriately because inter-node parallel execution may result in a lot of interconnect traffic. If the interconnection has a considerably lower bandwidth in comparison to the I/O bandwidth from the server to the storage subsystem, it may be better to restrict the parallel execution to a single node or to a limited number of nodes. Inter-node parallel execution does not scale with an undersized interconnection.To limit inter-node parallel execution, you can control parallel execution in an Oracle RAC environment using the PARALLELFORCELOCAL initialization parameter. By setting this parameter to TRUE, the parallel server processes can only execute on the same Oracle RAC node where the SQL statement was started.