Chapter 35 – Managing Oracle Database Resources

The Resource Manager Architecture

• Users are placed in Consumer Groups and Resource Plans to control and manage the allocation of resources across the groups.
• It is configured by default.

Consumer Groups

• Set of users with similar resource requirements
• A user can be a member of multiple groups
• At any given moment, a user session can only have one active group
• Requires a pending area before it can be created.
• There are 5 default groups:
  • SYS_GROUP – only SYSTEM user by default
  • DEFAULT_CONSUMER_GROUP – all users not assigned to group
  • OTHER_GROUPS – all users are member of this group
  • LOW_GROUP – Intended for low-priority sessions
  • AUTO_TASK_CONSUMER_GROUP – intended for running system maintenance jobs

Resource Manager Plans

• Default plans:
  • INTERNAL_PLAN – gives the OTHER_GROUPS 0% of CPU at all levels (equal access to all users). This is the default plan
  • SYSTEM_PLAN
    • 100% to SYS_GROUP at level 1
    • 100% to OTHER_GROUPS at level 2
    • 100% to LOW_GROUP at level 3
  • INTERNAL_QUIESCE – freezes all sessions out (by setting max number of sessions to 0) except for SYS_GROUP

Resource Manager Configuration Tools

• Two packages make up the Resource Manager API:
  • DBMS_RESOURCE_MANAGER_PRIVS – give resource manager privs to users or switch user to another group (GRANT[/REVOKE]_SWITCH_CONSUMER_GROUP or GRANT[/REVOKE]_SYSTEM_PRIVILEGE)
    • dbms_resource_manager_privs.grant_system_privilege(‘Mike’,'ADMINISTER_RESOURCE_MANAGER’,false)
  • DBMS_RESOURCE_MANAGER – create consumer groups, plans and directives (many functions available)
    • dbms_resource_manager.create_consumer_group(‘grp1′,’comment’)
    • a pending area (in the SGA) must be created before the group is added to make sure it is possible (ex. pct’s add up to 100). If the plan is valid, then it is saved to the Data Dictionary.
    • a pending area is not needed to put users into groups.
  • There is no need to add a user to group (only switch) since all users are members of OTHER_GROUPS on creation

Resource Manager Plans

• All plans must include a directive for OTHER_GROUPS or it will not be validated
• Methods to control consumer groups:
  • CPU
    • CPU uses emphasis method b/c it will vary depending on use
  • Number of active sessions
  • Degree of parallelism
  • Operation execution time
  • Idle time
  • Volume of undo data
    • All besides CPU use absolute limits which are not variables
  • Auto consumer group switching (execution time combined with CPU)
• Steps to create a plan:
  • Create pending area
    • SQL> exec dbms_resource_manager.create_pending_area;
  • Create plan
    • SQL> exec dbms_resource_manager.create_plan(plan=>’daytime’,comment=>’reg plan’);
  • Create directives
    • SQL> exec dbms_resource_manager.create_plan_directive(plan=>’daytime’, group_or_subplan=>’sys_group’, cpu_p1=>100);
    • … more directives …
  • Validate pending area:
    • SQL> exec dbms_resource_manager.validate_pending_area;
  • Submit pending area:
    • SQL> exec dbms_resource_manager.submit_pending_area;
  • Activate plan
    • SQL> alter system set resource_manager_plan=daytime;

CPU Method

• There are 8 possible priority levels
• It is impossible to allocate more than 100% of the CPU because the plan will not be saved. It is possible to allocate less than 100%, but there is little point in doing so.
• Another option is to make the group the plan and have other groups as subplans in a heirarchical fashion.

The Active Session Pool Method

• Limits the number of active sessions (not connections!!!). An active session is a session that is running a query or has an uncommitted transaction.
• If parallel processing is enabled, one operation still counts as only one
• By default, a session will be queued indefinitely but a timeout can be set

Limiting the Degree of Parallelism

• Can be used on single- or multi-processor machines.
• To enable parallel processing:
  • PARALLEL_MAX_SERVERS – must be set to create a pool of parallel execution servers
  • Enable parallelism for each table with ALTER TABLE <table_name> PARALLEL
  • Enable parallel DML for your session with ALTER SESSION ENABLE PARALLEL DML
  • Either set PARALLEL_AUTOMATIC_TUNING=TRUE or specify parallelism in each statement
• You can limit the number of parallel processors that each group has access to.

Controlling Jobs by Execution Time

• Jobs are estimated at how long they will run. If optimizer statistics are incorrect, then a job can execute when it wasn’t supposed to (too long).
• Jobs will not timeout. They will either run or be cancelled.

Terminating Sessions by Idle Time

• You can disconnect sessions by idle time or idle blocker time.
• Idle blocker time is how long there have been locks on data.

Restricting Generation of Undo Data

• The UNDO tablespace runs the risk of filling up if programmers poorly design queries.
• You can restrict groups on how much UNDO they are allowed to generate.
• When a group reaches its UNDO limit, the sessions will hang until a transaction is committed, freeing up UNDO space.

Automatic Consumer Group Switching

• You can force a group to switch privileges if their query runs too long.

Additional Features

Quiescing the Database

• Quiescing the database forces all groups besides the SYS_GROUP, to hang. It does so by activating the INTERNAL_QUIESCE plan
  • SQL> alter system quiesce restricted
  • SQL> alter system unquiesce
• The quiesce will only work if a resource plan has been active since startup. The plan must be specified in the s/pfile in order for this to be true

Consumer Group Switching for One Call

• Automatic consumer group switching can only be done via SQL execution time.
• Automatic consumer group switching via Resource Manager is permanent for the session, unless switched back manually.
  • Users can switch their sessions back via procedure in dbms_session or sys can use dbms_resource_manager.switch_consumer_group_for_sess
• Using the dbms_resource_manager API, you can switch consumer groups only for the duration of the call.
  • switch_time_in_call => — automatically switch back to the original consumer group after n seconds

Use of the Ratio CPU Method

• Instead of specifying the percent of CPU allowed to each group, you can simply use ratios between the groups.
• This is helpful when adding new groups because you won’t have to redistribute the the percentages.
• SQL> DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => ’service_level_plan’, CPU_MTH -> ‘RATIO’, COMMENT => ’service level plan’);
  • CPU_MTH can also be ‘EMPHASIS’ which is default

Create a Simple Resource Plan

• exec dbms_resource_manager.create_simple_plan(
  simple_plan=>’plan1′,
  consumer_group1=>’oltp’, group1_cpu=>50…)
• No need for pending_area.
• Level 1 is preconfigured at 100% for SYS_GROUP so all configs start at level 2.
• Bottom level is configured for OTHER_GROUPS at 100%.

Adaptive Consumer Group Mapping

• The two methods EXPLICIT & ORACLE_USER are available by default for determining the user’s consumer group
• Other methods available
  • EXPLICIT – switch groups via command to switch either code or Resource Plan
  • SERVICE_MODULE_ACTION – switch groups depending on service name used to connect, module and action of the code that is being executed. The programmers must embed these names within the code.
  • SERVICE_MODULE – switch groups depending on service name used to connect and program module
  • MODULE_NAME_ACTION – switch groups depending on program module and action being executed
  • MODULE_NAME – switch groups depending on program module
    • For all module or action methods, programmers must embed calls to DBMS_APPLICATION_INFO
  • SERVICE_NAME – switch groups depending on service name used to connect
  • ORACLE_USER – activate group at login depending on Oracle User ID
  • CLIENT_PROGRAM – switch groups depending on user process used to connect
  • CLIENT_OS_USER – switch groups depending on OS username
  • CLIENT_MACHINE – switch groups depending on name of client machine

Views & Tables

• V$SESSION – the column CURRENT_QUEUE_DURATION will show the duration that each queued session has been waiting
• V$RSRC_CONSUMER_GROUP – shows current session info for all consumer groups

Parameters

• RESOURCE_MANAGER_PLAN – used to enable a plan. By default it is not set, which means that INTERNAL_PLAN is the plan in effect.
• PARALLEL_MAX_SERVERS – used to enable parallelism on queries and DML