Database Reference
In-Depth Information
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.
