This scenario discusses how to manage statements in the parallel queue with consumer groups set up with Oracle Database Resource Manager. For this scenario, consider a data warehouse workload that consists of three types of SQL statements:
Short-running SQL statements
Short-running identifies statements running less than one minute. You expect these statements to have very good response times.
Medium-running SQL statements
Medium-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 statements
Long-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. To achieve this goal, you must ensue that:
Long-running statements do not use all of the parallel server resources, forcing shorter statements to wait in the parallel statement queue.
When both short-running and long-running statements are queued, short-running statements should be dequeued ahead of long-running statements.
The DOP for short-running queries is limited because the speedup from a very high DOP is not significant enough to justify the use of a large number of parallel servers.
Example 8-3 shows how to set up consumer groups using Oracle Database Resource Manager to set priorities for statements in the parallel statement queue. Note the following for this example:
By default, users are assigned to the OTHER_GROUPS consumer group. If the estimated execution time of a SQL statement is longer than 1 minute (60 seconds), then the user switches to MEDIUM_SQL_GROUP. Because
switch_for_call is set to
TRUE, the user returns to OTHER_GROUPS when the statement has completed. If the user is in MEDIUM_SQL_GROUP and the estimated execution time of the statement is longer than 15 minutes (900 seconds), the user switches to LONG_SQL_GROUP. Similarly, because
switch_for_call is set to TRUE, the user returns to OTHER_GROUPS when the query has completed. The directives used to accomplish the switch process are
After the number of active parallel servers reaches the value of the
PARALLEL_SERVERS_TARGET initialization parameter, subsequent parallel statements are queued. The
mgmt_p[1-8] directives control the order in which parallel statements are dequeued when parallel servers become available. Because
mgmt_p1 is set to
100% for SYS_GROUP in this example, parallel statements from SYS_GROUP are always dequeued first. If no parallel statements from SYS_GROUP are queued, then parallel statements from OTHER_GROUPS are dequeued with probability 70%, from MEDIUM_SQL_GROUP with probability 20%, and LONG_SQL_GROUP with probability 10%.
Parallel statements issued from OTHER_GROUPS are limited to a DOP of 4 with the setting of the
To prevent parallel statements of the LONG_SQL_GROUP group from using all of the parallel servers, which could potentially cause parallel statements from OTHER_GROUPS or MEDIUM_SQL_GROUP to wait for long periods of time, its
parallel_server_limit directive is set to
50%. This setting means that after LONG_SQL_GROUP has used up 50% of the parallel servers set with the
PARALLEL_SERVERS_TARGET initialization parameter, its parallel statements are forced to wait in the queue.
Because parallel statements of the LONG_SQL_GROUP group may be queued for a significant amount of time, a timeout is configured for 14400 seconds (4 hours). When a parallel statement from LONG_SQL_GROUP has waited in the queue for 4 hours, the statement is terminated with the error ORA-7454.
Example 8-3 Using consumer groups to set priorities in the parallel statement queue
BEGIN DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA(); /* Create consumer groups. * By default, users start in OTHER_GROUPS, which is automatically * created for every database. */ DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( 'MEDIUM_SQL_GROUP', 'Medium-running SQL statements, between 1 and 15 minutes. Medium priority.'); DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( 'LONG_SQL_GROUP', 'Long-running SQL statements of over 15 minutes. Low priority.'); /* Create a plan to manage these consumer groups */ DBMS_RESOURCE_MANAGER.CREATE_PLAN( 'REPORTS_PLAN', 'Plan for daytime that prioritizes short-running queries'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( 'REPORTS_PLAN', 'SYS_GROUP', 'Directive for sys activity', mgmt_p1 => 100); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( 'REPORTS_PLAN', 'OTHER_GROUPS', 'Directive for short-running queries', mgmt_p2 => 70, parallel_degree_limit_p1 => 4, switch_time => 60, switch_estimate => TRUE, switch_for_call => TRUE, switch_group => 'MEDIUM_SQL_GROUP'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( 'REPORTS_PLAN', 'MEDIUM_SQL_GROUP', 'Directive for medium-running queries', mgmt_p2 => 20, parallel_server_limit => 80, switch_time => 900, switch_estimate => TRUE, switch_for_call => TRUE, switch_group => 'LONG_SQL_GROUP'); DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( 'REPORTS_PLAN', 'LONG_SQL_GROUP', 'Directive for medium-running queries', mgmt_p2 => 10, parallel_server_limit => 50, parallel_queue_timeout => 14400); DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA(); END; / /* Allow all users to run in these consumer groups */ EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP( 'public', 'MEDIUM_SQL_GROUP', FALSE); EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP( 'public', 'LONG_SQL_GROUP', FALSE);