You can use Oracle Database 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 Oracle Database 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
parallel_queue_timeout parameter is used with the
CREATE_PLAN_DIRECTIVE procedure or the
new_parallel_queue_timeout parameter is used with the
UPDATE_PLAN_DIRECTIVE procedure in the
DBMS_RESOURCE_MANAGER package. The
new_parallel_queue_timeout 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.