8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Oracle Resource Manager Enhancements In Oracle 9i
Oracle Resource Manager is an Enterprise Edition feature that was introduced in Oracle 8i, allowing users with differing processing needs to be allocated to different resource consumer groups, each with it's own limited allocation of system resources. The functionality of Resource Manager has been enhanced in Oracle 9i to include the following features:
- Active Session Pool
- Maximum Estimated Execution Time
- Automatic Consumer Group Switching
- UNDO_POOL
- Resource Plan Directive Interactions
- Modified Views
Related articles.
Active Session Pool
As new transactions start in a specific consumer group they take a share of the available resources. If too many transactions are active at once performance can suffer.The new Active Session Pool feature allows a maximum number of active sessions to be set for each resource consumer group. Once this figure is reached, all subsequent requests are queued until an active session completes or becomes inactive.
The active session pool currently works on the First-In-First-Out (FIFO) basis, with a timeout period. If the request times out an error is issued that can be trapped by an application. Parallel operations are counted as single sessions by resource manager.
If there are multiple resource plan directives that refer to the same consumer group, the active session pool is the sum of all the incoming values. In this case the queue timeout is the minimum of all incoming timeout values.
The active session pool is defined using the following parameters of the CREATE_PLAN_DIRECTIVE
and
UPDATE_PLAN_DIRECTIVE
procedures in the DBMS_RESOURCE_MANAGER
package.
[NEW_]ACTIVE_SESSION_POOL_P1
- Defines the active session pool limit.[NEW_]QUEUING_P1
- Defines the timeout period in seconds.
Maximum Estimated Execution Time
Sometimes you need to prevent long running operations from using up system resources. This can be
done by specifying the MAX_ESTIMATED_EXEC_TIME
parameter in the plan directive.
When a transaction is intiated resource manager makes an estimate of the processing time needed
to complete the transaction. If that time in seconds exceeds the value of this parameter the
transaction is aborted.
If a resource consumer group has more than one plan directive assigned to it, the most restrictive
MAX_ESTIMATED_EXEC_TIME
value is used.
Automatic Consumer Group Switching
Oracle8i allowed resource intensive transactions to be manually switched between consumer groups to give them less priority. Oracle 9i improves on this to allow the session to be automatically switched when it exceeds a specified threshold.
SWITCH_GROUP
- Defines the consumer group to switch to if the threshold is exceeded.SWITCH_TIME
- Defines the threshold time.SWITCH_ESTIMATE
- If set to TRUE, Oracle estimates the elapsed time and preemptively switches the consumer group if necessary before the execution is started.
The threshold only applies to active sessions. Once the session completes it is switched back to it's original consumer group.
If multiple plan directives are applied to a single consumer group the most restrictive SWITCH_TIME
is used. If the plan directives have different SWITCH_GROUP
values resource manager
decides which to use.
When a transaction is switched to a new consumer group the active session pool of that group is not taken into account. This means the group may exceed it's active session pool limit until the session completes.
Here is an example of automatic consumer group switching.
We create a test user.
CONN / AS SYSDBA --DROP USER testuser1 CASCADE; CREATE USER testuser1 IDENTIFIED BY testuser1 QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE TO testuser1;
In the following example we have a resource plan with a consumer group called NORMAL_CG
for normal sessions, and a consumer group called LONG_RUNNING_QUERY_CG
for queries. Any sessions with the NORMAL_CG
consumer group that have been active for more than 60 seconds will be automatically switched to the LONG_RUNNING_QUERY_CG
consumer group.
BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; -- Create plan DBMS_RESOURCE_MANAGER.create_plan( plan => 'long_running_query_plan', comment => 'Plan to handle long running queries.'); -- Create consumer groups DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'normal_cg', comment => 'Consumer group for normal sessions.'); DBMS_RESOURCE_MANAGER.create_consumer_group( consumer_group => 'long_running_query_cg', comment => 'Consumer group for long running queries.'); -- Assign consumer groups to plan and define priorities DBMS_RESOURCE_MANAGER.create_plan_directive ( plan => 'long_running_query_plan', group_or_subplan => 'normal_cg', comment => 'Normal Priority', cpu_p1 => 100, switch_group => 'long_running_query_cg', switch_time => 60); DBMS_RESOURCE_MANAGER.create_plan_directive ( plan => 'long_running_query_plan', group_or_subplan => 'long_running_query_cg', comment => 'Low Priority', cpu_p2 => 100); DBMS_RESOURCE_MANAGER.create_plan_directive( plan => 'long_running_query_plan', group_or_subplan => 'OTHER_GROUPS', comment => 'Default', cpu_p3 => 100); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
We allow the TESTUSER1
user to switch to the NORMAL_CG and the LONG_RUNNING_QUERY_CG
consumer groups, then we set the initial consumer group for the TESTUSER1
user to NORMAL_CG
.
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => 'testuser1', consumer_group => 'normal_cg', grant_option => FALSE); DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => 'testuser1', consumer_group => 'long_running_query_cg', grant_option => FALSE); DBMS_RESOURCE_MANAGER.set_initial_consumer_group('testuser1', 'normal_cg'); END; /
Finally we activate the plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'long_running_query_plan';
We can monitor the current consumer group assignment with the following query.
COLUMN username FORMAT A30 COLUMN resource_consumer_group FORMAT A30 SELECT username, resource_consumer_group FROM v$session WHERE username = 'TESTUSER1';
Leave this session open so we can monitor the progress.
In a separate session we connect to the test user, create a function that just sits on CPU for the specified number of minutes, then query the function.
CONN testuser1/testuser1 CREATE OR REPLACE FUNCTION burn_cpu (p_mins IN NUMBER) RETURN NUMBER AS l_start_time DATE; l_number NUMBER := 1; BEGIN l_start_time := SYSDATE; LOOP EXIT WHEN SYSDATE - l_start_time > (p_mins/24/60); l_number := l_number + 1; END LOOP; RETURN 0; END; / SELECT burn_cpu (5) FROM dual;
Back in our privileged session we see the initial consumer group.
COLUMN username FORMAT A30 COLUMN resource_consumer_group FORMAT A30 SELECT username, resource_consumer_group FROM v$session WHERE username = 'TESTUSER1'; USERNAME RESOURCE_CONSUMER_GROUP ------------------------------ ------------------------------ TESTUSER1 NORMAL_CG SQL>
After approximately 60 seconds the consumer group switches.
COLUMN username FORMAT A30 COLUMN resource_consumer_group FORMAT A30 SELECT username, resource_consumer_group FROM v$session WHERE username = 'TESTUSER1'; USERNAME RESOURCE_CONSUMER_GROUP ------------------------------ ------------------------------ TESTUSER1 LONG_RUNNING_QUERY_CG SQL>
We clean up the example by disabling the plan, then deleting it.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ''; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.delete_plan_cascade( plan => 'long_running_query_plan'); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
UNDO_POOL
The UNDO_POOL
parameter of a plan directive can be used to specify the maximum
amount of UNDO a resource consumer group can use. Once this threshold is met any transactions requiring
more UNDO will fail with the following error.
ORA-30027: "Undo quota violation - failed to get %s (bytes)"
This situation will persist until some UNDO is released from another transaction or the DBA increases the threshold. Once more UNDO is available transactions will proceed normally. This mechanism works regardless of whether UNDO is managed automatically or manually.
If processes die or are killed their undo is credited to the pool once recovery starts, regardless of how long recovery takes. That way users are not penalized during the recovery phase.
UNDO associated with recursive transactions is included with the top-level transaction and is not credited to the pool until the top-level transaction completes. Autonomous transactions are considered separately with their UNDO credited immediately on their completion.
Resource Plan Directive Interactions
If multiple resource plan directives are associated with a single resource consumer group the resulting actions will follow the following rules.
- The minimum parallel degree limit of the incoming values will be used.
- The sum of the incoming active session pools will be used.
- The minimum incoming active session pool timout will be used.
- The smallest incoming switch time.
- The value TRUE overrides FALSE for the switch estimate.
- Resource manager chooses between multiple switch groups.
- If a session is switched to a new consumer group it executes regardless of the status of the active session pool in the new group.
- The most restrictive incoming maximum estimated execution time is used.
Modified Views
The CURRENT_QUEUE_DURATION
column has been added to the V$SESSION
and V$MYSESSION
views displaying the number of seconds the session has been queued or 0.
The QUEUE_LENGTH
and CURRENT_UNDO_CONSUMPTION
columns have been added to the V$RSRC_CONSUMER_GROUP
view. These dispay the current queue length and the UNDO consumption for the consumer group respectively.
The QUEUEING_MTH
column on the DBA_RSRC_PLANS
views indicates the queuing resource allocation method
for the plan.
The DBA_RESRC_PLAN_DIRECTIVES
views has the QUEUEING_P1
, SWITCH_GROUP
, SWITCH_TIME
, SWITCH_ESTIMATE
, MAX_EST_EXEC_TIME
and UNDO_POOL
columns added to it.
For more information see:
- Resource Manager Quick Links : 8i, 9i, 10g, 11gR1, 11gR2, 12cR1, 12cR2, All Articles
- DBMS_RESOURCE_MANAGER
- DBMS_RESOURCE_MANAGER_PRIVS
Hope this helps. Regards Tim...