Instance_group and Parallel_instance_group

The parameter instance_group specifies the membership of an instance to an instance group. PX servers are allocated from an instance group specified by the parallel_instance_group parameter. Adjustment to these two parameters can be utilized to control the placement of PX servers.

The following discussion uses a three-node cluster configuration with instances named orcl1, orcl2, and orcl3. In the following parameter setup, the instance_groups parameter is specified at the instance level using the syntax <instance>.<parameter>. The value of the parameter orcl1.instance_groups is set to 'po','scm' and specifies that the orcl1 instance is a member of instance groups po and scm. Similarly, the orcl2 instance is a member of the po and scm instance groups, and the orcl3 instance is a member of the finance and scm instance groups. Hence, all three instances are members of the scm instance group, instances 1 and 2 are members of the po instance group, and instance 3 is the sole member of the finance instance group.

orcl1.instance_groups='po','scm'
orcl2.instance_groups='po','scm'
orcl3.instance_groups='finance','scm'

With the parameter setup just discussed, we can alter the parallel_instance_group parameter to control PX server allocation. In the following example, the value of parameter parallel_instance_group is set to scm in orcl1 instance. PX execution initiated from orcl1 can allocate PX servers in all instances, since all three instances are members of the scm instance group.

orcl1.parallel_instance_group= 'scm'

If we set the parallel_instance_group parameter to po in orcl1 instance, then PX executions initiated from the orcl1 instance can allocate PX servers from both orcl1 and orcl2 instances.

orcl1.parallel_instance_group= 'po'

If the parameter parallel_instance_group is set to finance in orcl3, then the PX execution can allocate PX servers only from the orcl3 instance, as only the orcl3 instance is a member of the finance instance group.

Orcl3.parallel_instance_group= 'finance'

If you set the parameter parallel_instance_group to finance in the orcl2 instance, then the PX execution initiated from the orcl2 instance will spawn PX servers in the orcl3 instance, as orcl3 is the sole member of the finance instance group.

Orcl2.parallel_instance_group= 'finance'

If the specified parallel_instance_group parameter value has no members, then the parallel execution will be disabled completely. For example, a parameter value of ar will disable parallel exectuion, since there are no instances in the ar instance group.

*.parallel_instance_group= 'ar'

You can alter the value of the parallel_instance_group parameter dynamically at either the session level or the system level. For example, the following alter session statement enables PX server allocation in orcl1 and orcl2 instances in the session. You can override instance- or database-level setup with session-level changes.

Alter session set parallel_instance_group= 'po';

The parameter instance_group cannot be altered dynamically (since release 11g), and instance restarts would be required to alter the instance_group parameter. There is no explicit method to create an instance group.

As of version 11g, the optimizer considers the parallelism setup before choosing a PX plan. If parallelism is disabled, in the session or globally, the optimizer will not choose a PX plan. Prior to version 11g, the optimizer could choose a PX plan even if the parallelism was disabled. At run time, the execution engine would execute the chosen PX plan serially, leading to worse performance. From Database version 11.2.0.2 onward, the cost-based optimizer is also aware of the parallelism setup. If the parallelism is disabled in the session, through either session-level parameter setup or global-level parameter setup, then the optimizer does not choose a parallel plan at all. For example, I modified the parallel_instance_group to dummy and executed the following statement. Cost-based optimizer 10053 event trace indicates that optimizer used a parallel degree of 1, even though the hint specifies a DOP of 8. Also notice that the cost of parallel plan (resp) is same as the cost of serial plan (Resc), indicating that the optimizer will not choose a PX plan.

alter session set events '10053 trace name context forever, level 1';
REM parallel_instance_group value is non-existent.
alter session set parallel_instance_group='dummy';
 
select /*+ use_hash (h l ) parallel (h 8) parallel (l 8) full(h) full(l) */
count(*)  from
oe_order_headers_all h, oe_order_lines_all l
where h.header_id = l.header_id and
h.open_flag='N'
 
/
Trace file due to 10053 event:
 
Best:: AccessPath: TableScan
      Cost: 197226.11  Degree: 1  Resp: 197226.11  Card: 10992402.90  Bytes: 0

After altering parallel_instance_group to the correct value, reparsing the same statement shows that the optimizer used a parallel degree of 8. Also, notice that the parallel cost is lower than the earlier serial cost of 197226.11.

Best:: AccessPath: TableScan
         Cost: 61202.86  Degree: 8  Resp: 61202.86  Card: 10992402.90  Bytes: 0

Even though the parameters instance_group and parallel_instance_group can be used to control PX server placement, use of these parameter combinations is deprecated from Oracle Database version 11.2. Now, it is recommended to use services to control placement of PX servers. Also, in 11.2, the incorrect value for parallel_instance_group parameter can lead to an excessive number of child cursor problems due to bugs such as 7352775. There are also a few unpublished bugs where the use of these two parameters leads to unshareable child cursors. So, it is preferable to use services to control PX server placement.

Services

Services are the preferred approach to control PX server placement. By default, PX servers are allocated in instances where the current service is active (“current service” refers to the service that the session initiating PX execution connected). For example, if you connect to po service and if po service is available in ORCL1 and ORCL2 instances, then PX servers can be allocated from both ORCL1 and ORCL2 instances.

The following output shows the service configuration in a database named ORCL. In this configuration, service po is active in ORCL1, ORCL2 instances, service scm is active in all three instances, and finance is active only in the ORCL3 instance. If you connect to the po service and initiate a parallel statement execution, then PX servers can be allocated from the ORCL1 and ORCL2 instances. Similarly, if you connect to finance service and initiate a parallel statement execution, then PX servers can be allocated from the ORCL3 instance only.

$ srvctl status service -d ORCL
Service po is running on instance(s) ORCL1,ORCL2
Service scm is running on instance(s) ORCL1,ORCL2,ORCL3
Service finance is running on instance(s) ORCL3

You can also use a combination of both services and the Parallel_instance_group parameter to control PX server placement. If you set the parallel_instance_group parameter to a value of service, then PX servers can be allocated from the instances that service is running. For example, after connecting to the finance service (the connection will go to the ORCL3 instance), you can alter the parallel_instance_group parameter at the session level to po service. Now, PX executions from the current session can allocate PX servers in the ORCL1 and ORCL2 instances, since po service is active in the ORCL1 and ORCL2 instances, even though PX is triggered from the ORCL3 instance. While this strategy provides the ability to control your PX server allocation, it is not a recommended approach.

$ sqlplus rs/temp123@//orcl-scan:1521/finance
...
Alter session set parallel_instance_group=po;

PX server placement utilizing the service configuration is elegant, since after a node/instance failure, services automatically fail over to the surviving nodes. You should consider instance failure scenarios while setting up services and make active/passive configuration for services too. Further, service configuration can be modified dynamically. In contrast, if you use instance_group and parallel_instance_group parameters, then during instance failure, you may have to modify the instance_group parameter, which might require another surviving instance restart.

Parallel_force_local

The parallel_force_local parameter can be used to localize the PX server allocation. If the parallel_force_local parameter is set to TRUE, then PX servers are allocated in the same instance that the PX query was initiated. For example, if you connect to the ORCL1 instance and initiate a PX execution, then all PX servers will be allocated from the ORCL1 instance. This parameter can be modified at the session level or at the global level the using alter session or alter system commands.

The problem with controlling the PX server placement with this parameter is that if all PX servers in the local instance are exhausted, then the query may run serially or with reduced parallelism. Thus, it is a preferable approach to use services to control the allocation. Also, this parameter doesn’t work well with the parallel_statement_queuing parameter, as discussed later in this chapter.²

In-Memory Parallelism

Traditionally, PX servers read blocks directly from the disk to PGA, bypassing buffer cache. In a few cases, it may be better to buffer the blocks in the database buffer cache so that subsequent PX server processes can reuse the buffers. This feature is pertinent in machines with huge swaths of memory allocated for SGA. Of course, buffering in SGA requires global cache locks, which might induce global cache event waits for the PX servers reading the block from disk.

From Oracle Database version 11.2 onward, if the parallel_degree_policy parameter is set to auto, then the in-memory parallelism feature is enabled. Objects are chosen to be cached in the buffer cache if the object is not too small or too big. If the segment is too small, then that segment can be cached in PGA itself; if the segment is too big, then buffering will overrun the buffer cache. Thus, this feature cleverly chooses segments of the right size. The advantage of this feature is that subsequent processes accessing the same table do not need to repeat the work of reading the blocks from disk.

The algorithm uses the following directives to determine if the objects can be buffered or not:

1. Size of the object (must fit in the buffer cache of one instance; the parameter _parallel_cluster_cache_pct limits the maximum percentage of buffer cache that can be used for affinity and defaults to 80%).
2. Object access frequency.
3. Object change frequency.

In RAC, object fragments are affinitized among active instances. For example, if there are three instances in a PX execution, then approximately 1/3rd of the object is affinitized to each instance. Affinitization is performed on a per-extent basis for a non-partitioned table; 1/3rd of extents are affinitized to each node in a three-node cluster. If the table is hash partitioned, then the object is affinitized per partition basis. PX server processes are also affinitized such a way that PX servers access extents/partitions in the local buffer cache. For example, if partition P1 is affinitized to the ORCL2 instance, then PX processes are allocated in the ORCL2 instance if the execution plan requires access to partition P1. Affinitization is designed to reduce cache fusion network traffic.

This feature can be enabled at the session level also:

alter session set "_parallel_cluster_cache_policy"=cached;

This feature can be disabled in session level using the following statement. Of course, it is always better to check with Oracle Support before using underscore parameters in production code. Also, instead of hard-coding these SQL statements in production code, wrap this code in a PL/SQL procedure and call the procedure from application code. This enables you to change the code in the database without altering the application.

alter session set "_parallel_cluster_cache_policy"=adaptive;

The following SQL statistics show a comparison between parallel execution  of a statement with caching enabled and disabled. The comparison shown here is for the second execution of the statement for each mode in a production database. If the caching is enabled, subsequent executions of the statement perform almost no disk I/O and reuse cached buffers. If the caching is disabled, then each execution performs direct I/O to read blocks from the disk. In the following example, SQL statement performed 232K reads with an increased elapsed time of 81 seconds, compared to 18 seconds when caching was enabled.

SQL_ID         EXEC CPU_TIME ELA_TIME BGETS    DRDS     ROWS
-------------  ---- -------- -------- -------- -------- -------
gj1p0kqyxvqas     1    17.37    18.66   455257        0     809 <- with caching enabled
gj1p0kqyxvqas     1    17.09    81.42   237443   232779     809 <- without caching

You need to understand that the effect of this feature depends upon the activity in the database buffer cache. If the buffers are not accessed frequently, then the buffers will be flushed out of the buffer cache, and so PX servers may be doing more work with no future benefit.⁴  On the Exadata platform, this feature is probably not useful, as the smart scan feature requires direct path reads.

As the tables are buffered, there is a possibility that this feature can increase interconnect network traffic inadvertently. So, if you are planning to enable in-memory parallelism, verify that interconnect hardware is properly sized to support a possible increase in private network traffic.

AutoDOP

AutoDOP is a new feature available from Oracle Database version 11.2. Prior to version 11.2, the optimizer chose PX only if the objects had a DOP of greater than 1 or if there were hints in the SQL statement enabling parallelism. From 11.2 onward, parallelism is considered if the estimated run time of a SQL statement exceeds a threshold. The following algorithm outlines the logic of the AutoDOP feature.

Estimate total run time of a SQL statement for serial execution.
If estimated_run_time > 10 seconds threshold then
Re-parse the statement with a default DOP and determine the cost.
If the estimated cost is lower than the estimated cost for serial execution, then
                Execute the statement with Default DOP.
End;
Else
Execute the statement serially.
End;

The AutoDOP feature can be enabled by setting parallel_degree_policy to auto or limited (11.2) or auto_feedback (12c). If the parallel_degree_policy is set to limited, then objects with parallelism must be accessed in the SQL statement for the AutoDOP to be enabled. The following few lines are printed from event 10053 trace file. These lines show that AutoDOP was disabled since the parallel_degree_policy parameter is set to a value of limited and none of the objects accessed have a parallel degree decorated.

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is disabled: limited mode but no parallel objects referenced.

If you set the parallel_degree_policy parameter to auto, then the AutoDOP feature is enabled, but I/O must be calibrated for AutoDOP to be enabled.

**************************
Automatic degree of parallelism (ADOP)
**************************
Automatic degree of parallelism is enabled for this statement in auto mode.
kkopqSetDopReason: Reason why we chose this DOP is: IO calibrate statistics are missing.

Enabling I/O calibration using dbms_resource_manager package will enable the AutoDOP feature. Calibration results are visible in v$io_calibration_status view. Note that there is no documented method to remove the calibration,⁵  and so this change must be tested in a non-production database before calibrating I/O in production.

To calibrate I/O, you can choose any instance of a cluster and trigger I/O calibration. The database engine uses just one instance to calibrate I/O, but since I/O resource is common to all nodes, performing calibration in one node is good enough.

SET SERVEROUTPUT ON
DECLARE
  l_lat  INTEGER;
  l_iops INTEGER;
  l_mbps INTEGER;
BEGIN
  -- Calibrate I/O and print statistics
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (20, 20, l_iops, l_mbps, l_lat);
  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || l_iops);
  DBMS_OUTPUT.PUT_LINE ('latency = ' || l_lat);
  DBMS_OUTPUT.PUT_LINE ('max_mbps = ' || l_mbps);
END;
/6

If the estimated run time exceeds the value of the parameter parallel_min_time_threshold, then the statement is reparsed with a higher DOP. The parameter value of parallel_min_time_threshold defaults to a value of AUTO, which indicates a threshold of 10 seconds. If you prefer more queries to be considered for parallelism, then you can decrease the value of the parallel_min_time_threshold parameter to less than 10 seconds.

After the completion of I/O calibration, regenerating event 10053 trace file for the same SQL statement shows that AutoDOP was enabled for this statement execution. The optimizer uses calibrated I/O statistics to convert the cost to time using I/O calibration statistics. In this example, the estimated time is 38 seconds, exceeding parallel_min_time_threshold parameter, and so optimizer considers AutoDOP.

kkeCostToTime: using io calibrate stats
 maxmbps=10(MB/s) maxpmbps=11(MB/s)
 block_size=8192 mb_io_count=4 mb_io_size=30247 (bytes)
 tot_io_size=422(MB) time=38387(ms)
AutoDOP: Table/Index(#76100) access (scan) cost=14638.09 estTime=38386.60 unit=10000.00
   dop=3 -> maxdop=3
Best:: AccessPath: TableScan
Cost: 14638.09 Degree: 1 Resp: 14638.09 Card: 159987.00 Bytes: 0

Following lines from the trace file shows that optimizer is triggering a reparse of the SQL statement with a statement-level parallelism of 3.

AUTODOP PLANS EVALUATION
***************************************
Compilation completed with DOP: 1.
   Cost_io: 59742.00 Cost_cpu: 1227068251.35
   Card: 318995.00 Bytes: 1279807940.00
   Cost: 59810.86 Est_time: 156847ms
Serial plan is expensive enough to be a candidate for parallelism (59811)
Signal reparse withDOP 3.
*****************************
Number of Compilations tried: 1

Let us review a few important parameters controlling the AutoDOP feature. Parameter parallel_degree_limit governs the maximum DOP that can be chosen for a SQL statement. By default, this parameter is set to CPU, implying default DOP as the maximum DOP for AutoDOP execution. Default DOP is calculated by multiplying the total number of CPUs in the cluster (Sum of cpu_count from all active instances) by the parallel_threads_per_cpu (Default=2) parameter. So, if there are 32 cores each in a three-node cluster, then the value of parallel_degree_limit is calculated as 32 * 3 * 2 (parallel_threads_per_cpu=2), equaling a value of 192. So, 192 PX servers can be used for a SQL statement execution.

The parameter parallel_max_servers is a hard upper bound determining the maximum number of PX servers available in any instance, and the parallel_max_servers parameter overrides the parallel_degree_limit parameter if the parallel_max_servers parameter is set to a value lower than the default DOP.

While parallel_degree_limit controls the upper bound for parallelism, the actual parallelism used in a query (calculated DOP or actual DOP) is less than the default DOP. The exact algorithm is not documented for the actual DOP calculation; however, from event 10053 trace files, we can infer that the optimizer is recosting the statement with different DOPs to find the optimal DOP for a SQL execution.

There are a few dangers with AutoDOP feature in a production database.

1. If many concurrent queries are parallelized inadvertently, then those queries can deplete I/O and CPU resources in the database server, causing performance issues with the online application.
2. In addition, inadvertent parallel queries can consume all parallel servers, starving critical parallel queries of parallel servers.
3. Also, in RAC, if many queries are parallelized, then there is a possibility that numerous queries might perform inter-instance PX, leading to worse interconnect latency for online application(s).

So, in the production database, it is generally a good idea to control parallelism so that you don’t accidentally run out of resources. Further, you need to understand that AutoDOP uses estimated run time to decide if AutoDOP should be enabled or not. Hence, it is critical to have optimal statistics in a RAC environment so that excessive PX executions are not accidentally triggered due to a statistical issue.

AutoDOP feature is more suitable for data warehousing environments. You might need to tune the parallel_min_time_threshold parameter to match the workload in your RAC cluster. By default, the parallel_min_time_threshold parameter is set to a value of 10 seconds. In my experience, a lower default value for the parallel_min_time_threshold parameter will trigger a tsunami of parallel executions. So, if you are planning to enable the AutoDOP feature, increase the parallel_min_time_threshold parameter to a higher value, at least 600 seconds.

This feature is very useful in ad hoc reporting and data warehousing environments, where queries initiated by the users may not specify selective predicates and parallelism may be the only option to tune those queries. It may not be feasible to tune those ad hoc queries individually either. In these scenarios, it may be prudent to use the AutoDOP feature and let the optimizer choose optimal parallelism, but use Database Resource Manager or proper parallel_max_servers to control query parallelism.

Version 12c introduces a new parameter, parallel_degree_level, and this parameter controls the aggressiveness of default DOP calculations. It also controls the scaling factor for default DOP calculations. If you set this parameter value to 50, then calculated default DOP will be multiplied by 0.50, reducing DOP to one-half of the default DOP.

Parallel Statement Queuing

It is all too common for SQL statements to acquire fewer PX servers than the requested DOP in a production database. Consider a SQL statement requesting a DOP of 32, but other SQL statements currently executing parallel statements are using all PX servers except for four; then, this SQL statement will execute with a reduced parallelism of 4. Even if more PX servers are available minutes after the start of parallel exectuion, the SQL statement will continue to execute with a DOP of 4. A process with a normal run time of 1 hour might run for 8 hours (assuming a linear extrapolation) and cause performance issues. Had that SQL statement delayed the execution for a few seconds, it could have acquired all requested PX servers and might have completed the task in normal run time.

Prior to Database version 11.2, the parameter parallel_min_percent could be used (with little coding) to wait for requested PX servers to be available. Since version 11.2, a waiting scheme for PX server availability can be achieved more elegantly using the Parallel Statement Queuing feature. Essentially, if the requested number of PX servers are not available, then the parallel execution is delayed and the process waits in a statement queue until the requested PX servers are available.

If the parallel_degree_policy is set to AUTO, then the Parallel Statement Queuing feature is enabled. This feature also can be enabled in a session by altering the _parallel_statement_queuing parameter to true.

alter session set "_parallel_statement_queuing"=true;

The parallel_servers_target parameter controls when the Parallel Statement Queuing feature becomes active. If the number of PX servers currently executing in a cluster is greater than parallel_servers_target, then the feature becomes active. Consider an example: if parallel_servers_target is set to 96 and the number of PX servers currently active is 100, then this feature becomes active. If a SQL statement requests a parallel degree of 32, then the session will be queued until at least 32 PX servers are available in the cluster.⁷  As soon as 32 PX servers are available, the session will allocate PX servers and continue processing. In essence, this feature is protecting a SQL statement from executing with reduced number of PX servers. Still, the maximum number of PX servers that can execute at any time in an instance is dictated by the parallel_max_servers parameter.

Consider the following setup to explain this feature with clarity. Assuming that the server has 32 cores (cpu_count=32) and three active instances in a cluster, then the default values for the parameters parallel_max_servers and parallel_servers_target are calculated as follows:

Parallel_max_servers = 32* parallel_threads_per_cpu * (2 if pga_aggregate_target >0) * 5
                = 32 * 2 * 2 * 5 = 640
       
Total parallel_max_servers     =  parallel_max_servers * active_instance_count
                = 640 * 3 = 1,920
 
Parallel_servers_target     = 4 * 32 * parallel_threads_per_cpu * active_instance_count
                = 4 * 32 * 2 * 3 = 768 PX servers

If the total number of active PX servers exceeds 768, then only Parallel Statement Queuing will become active. However, the feature would not limit PX allocation yet. Suppose that 800 PX servers are active; if another process requests 200 more PX servers, then the process will continue executing in parallel with 200 PX servers without waiting in the statement queue, as the total number of active PX servers is lower than the parallel_max_servers parameter.

Suppose that three sessions consumed 1,500 PX servers in total, leaving just 420 PX servers available. If another process requests 500 PX servers, then the process will wait until all 500 PX servers are available. So, the parallel_servers_target parameter determines the time at which this feature will become active, and the feature will limit PX server allocation only when requested PX servers are not available.

Default values for parallel_max_servers and parallel_servers_target parameters are very high and overly optimistic about I/O resources available in a database machine. It is not uncommon to keep the parallel_max_servers parameter to a lower value than the default. If you adjust the parallel_max_servers parameter, then you should adjust the parallel_servers_target parameter also. You should consider the type of workload, I/O resources available in the database machine, and PX activity to set these parameters.

A session enqueued waiting for PX servers will wait for resmgr: pq queued event.

It is possible to enable the Parallel Statement Queuing feature for a SQL statement using the hint STATEMENT_QUEUING even if the Parallel Statement Queuing feature is disabled in the database. Additionally, the hint NO_STATEMENT_QUEUING can be used to disable the Parallel Statement Queuing feature if the feature is enabled.

However, the Parallel Statement Queuing feature doesn’t work well with the parallel_force_local parameter. If the parameter parallel_force_local is TRUE, then parallel_statement_queuing keeps the session in the queue, even if PX servers are available in other nodes.⁸  It is a better idea to use services instead of the parallel_force_local parameter if you are planning to use the Parallel Statement Queuing feature.

Prioritizing Parallel Statement Queues (12c)

The Parallel Statement Queuing feature works in a strict First In- First Out (FIFO) model in Database versions up to 11.2. In version 12c, Database Resource Manager can be utilized to create multiple parallel statement queues and prioritize the next parallel statement execution. Parallel statements from a higher-priority parallel statement queue are dequeued for the next execution. The parallel statement queue feature is enhanced to have multiple statement queues.

It is easier to understand priority among parallel statement queues using an example. Consider that you want to design a queuing scheme with the following objectives:

1. SYS_GROUP should have highest priority and any statements in this consumer group must be dequeued first.
2. If no session is waiting from the SYS_GROUP queue, then the SQL statement should be dequeued in the order of PX_CRITICAL, PX_HIGH, PX_MEDIUM, and PX_LOW queues.

The following code will implement the designed priority objective. In this example, four new consumer groups are created and priority is assigned among those consumer groups. First, a pending area is created to make changes to resource consumer groups. The next step creates a resource plan named PQ_STMT_PLAN.

begin
 dbms_resource_manager.create_pending_area();
  end;
  /
--------------Create Resource Manager Plan -----------------------------
begin
 dbms_resource_manager.create_plan(
  plan => 'PQ_STMT_PLAN',
  comment => 'Resource plan for PQ statement Queuing 12c');
end;
/

The following step creates four Resource Manager consumer groups. These consumer groups will be associated with a plan directive in the next step.

-------------- Create Resource Manager Consumer Groups -------------------
begin
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'PX_CRITICAL',
  comment => 'Resource consumer group/method for online users / report sessions');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'PX_BATCH_HIGH',
  comment => 'Resource consumer group/method for batch high users');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'PX_BATCH_MED',
  comment => 'Resource consumer group/method for batch med users');
 dbms_resource_manager.create_consumer_group(
  consumer_group => 'PX_BATCH_LOW',
  comment => 'Resource consumer group/method for batch low users');
end;
/

The next step creates the priorities among parallel statement queues of the resource consumer groups. Directive mgmt_p1 decides the priority of a specific queue in level 1. In the following example, SYS_GROUP has a 100% priority at level 1. A parallel statement with SYS_GROUP as the consumer group will be chosen next to be executed. If there is no statement belonging to SYS_GROUP that is waiting in the parallel statement queue, then a parallel query from the next-lowest-level PX statement queues will be considered for execution.

Directive mgmt_p2 decides the priority of the parallel statement queue at level 2, and the value of mgmt_p2 is set to 50 for the consumer group PX_CRITICAL. So, there is a 50% probability that the next query will be scheduled for the sessions with a resource consumer group associated with the PX_CRITICAL group. Similarly, PX_BATCH_HIGH has a 30% probability that a PX session for the PX_BATCH_HIGH consumer group will be chosen to be executed next. Also, PX_BATCH_LOW and PX_BATCH_MED have a probability of 10%.

The priority of OTHER_GROUPS is set to 100 at level 3 by setting the mgmt_p3 attribute. A statement belonging to OTHER_GROUPS will be executed only if there is no other statement waiting for higher-level queues.

------------- Create Resource Manager Plan Directives --------------------
begin
 dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'SYS_GROUP',
  comment => 'SYS sessions at level 1',
  mgmt_p1 => 100 );
 dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_CRITICAL',
  comment => 'Online day users sessions at level 1',
  mgmt_p2 => 50);
 dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_BATCH_HIGH',
  comment => 'Batch users level 2 -HIGH- Fast running ',
  mgmt_p2 => 30);
 dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_BATCH_MED',
  comment => 'Batch users sessions at level 2 - MED - Medium running',
  mgmt_p2 => 10);
 dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_BATCH_LOW',
  comment => 'Batch users sessions at level 2 - LOW - long running',
  mgmt_p2 => 10);
dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'OTHER_GROUPS',
  comment => 'Batch users sessions for Other groups',
  mgmt_p3 => 100);
end;
/
begin
 dbms_resource_manager.validate_pending_area();
end;
/
begin
 dbms_resource_manager.submit_pending_area();
end;
/

In a nutshell, version 12c enhances the strict FIFO policy employed in earlier versions for PX statement execution. A complex scheme such as the one discussed in this section can be used to prioritize the workload so that higher-priority sessions do not have to wait for lower-priority parallel statement execution.

This enhancement is useful in mixed-workload environments such as highly critical short-running parallel executions and low-priority long-running parallel executions mixed in the same database. If critical SQL statement must wait for low-priority parallel statement execution, then critical parallel execution may be delayed. Worse, low-priority execution can be a long-running statement with a higher number of PX servers, and so the wait time for critical statements can be longer. By enabling prioritization within the workload, you can reduce the wait time for critical SQL execution in parallel statement queues.

Critical Parallel Statement Queues (12c)

In the preceding section, you learned how to prioritize parallel statement execution among consumer groups. You may recall that the Parallel Statement Queuing feature is enabled only after the active PX server count exceeds the parallel_servers_target parameter. In some cases, you might prefer for ultra-critical jobs to acquire PX servers completely bypassing the PX statement queue. For example, an important index must be rebuilt quickly: waiting in the parallel statement queue is not acceptable, and you prefer to bypass it completely. As the parallel_servers_target parameter is configured to be less than the parallel_max_servers parameter, it may be beneficial to bypass the queue for ultra-critical workloads.

Version 12c introduces the attribute parallel_stmt_critical for a resource consumer group. If you set this attribute to BYPASS_QUEUE, then sessions attached to this resource consumer group will bypass the PX statement queue and start execution immediately. As the parallel_max_servers parameter is a hard threshold, it is possible for this critical statement to execute with fewer PX servers than requested.

begin
      dbms_resource_manager.create_pending_area();
   end;
   /
   begin
dbms_resource_manager.create_consumer_group(
  consumer_group => 'PX_ULTRA_CRIT',
  comment => 'Resource consumer group/method for critical users');
dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_ULTRA_CRIT',
  comment => 'Very Critical stuff',
  parallel_stmt_critical => 'BYPASS_QUEUE');
    end;
/
begin
 dbms_resource_manager.validate_pending_area();
end;
/
begin
 dbms_resource_manager.submit_pending_area();
end;
/

Think of the bypass mechanism as an override mechanism. You should design a consumer group with this attribute only if it is absolutely required, as there is a danger of critical statements executing with a lower number of PX servers than requested.

Parallel Statement Queue Timeout (12c)

By default, PX statements will wait in the statement queue indefinitely, but that may not be preferred in a production environment. Version 12c introduces a timeout, namely, parallel_queue_timeout, for a resource consumer group. If a session waits in the parallel statement queue longer than the parallel_queue_timeout parameter value, then the statement is terminated with an ORA-7454 error and removed from the parallel statement queue. For example, sessions associated with PX_BATCH_LOW will wait in the parallel statement queue for an hour at most.

dbms_resource_manager.create_plan_directive(
  plan => 'PQ_STMT_PLAN',
  group_or_subplan => 'PX_BATCH_LOW',
  comment => 'Batch users sessions at level 2 - LOW - long running',
  mgmt_p2 => 10,parallel_queue_timeout =>3600);

You should also note that it is the responsibility of the application code to handle this error condition and react properly if ORA-7454 is received by the application.

Grouping Parallel Statement Queue Execution (12c)

A problem with the Parallel Statement Queuing feature is that a transaction may contain multiple parallel statements, and every parallel statement can wait in the statement queue, leading to a prolonged transaction. For example, a transaction may consist of two statements, with the first inserting 500 million rows into a table using parallelism and the second summarizing those 500 million rows using parallelism. Since the Parallel Statement Queuing feature works at the statement level, the session might wait twice for the whole transaction, inducing performance issues. Further, multiple waits in the parallel statement queue have the effect of prolonging a transaction, which could further lead to unnecessary locking issues or UNDO management issues. The following group of SQL statements shows the example scenario.

–- First parallel statement
Insert /*+ parallel(10) */ into huge_table
  Select  /*+ parallel (10) */ col1, col2 .. from oltp_transactions..;
update  emp..     ;              -– Serial statement
 
-- Second parallel statement
insert /*+ parallel (10) */ into summary_table
select /*+ parallel (10) */ ..from huge_table;
 
commit;

It may be acceptable for the first parallel SQL statement to wait in the parallel statement queue, but the subsequent, nearly immediate, parallel statement should not wait in the parallel statement queue. In 11gR2, every statement will wait in the PX statement queue if the requested PX servers are not available.

Version 12c introduces a new feature to group multiple parallel statements to reduce the number of waits in the parallel statement queue. Procedure call BEGIN_SQL_BLOCK and END_SQL_BLOCK can be used to group parallel statements. The following code groups prior parallel statements and enables reuse of PX servers acquired in the earlier parallel statement execution.

exec dbms_resource_manager.begin_sql_block;
Insert /*+ parallel(10) */ into huge_table
  Select  /*+ parallel (10) */ col1, col2 .. from oltp_transactions..;
update  emp..     ;              -– Serial statement
--second parallel statement
insert /*+ parallel (10) */ into summary_table
select /*+ parallel (10) */ ..from huge_table;
commit;
exec dbms_resource_manager.end_sql_block;

With this strategy, multiple parallel statements in the block will be considered as one group, and the waits in parallel statement queue will be minimal. The downside of this feature is that if there is a delay between two parallel statement executions in the same parallel statement block, then PX servers can be unnecessarily held up by this process.