Database Resource Manager allows the database more control over how machine resources are allocated regardless of the load on the system and the number of users. The Database Resource Manager involves the user of resource plans (how resource should be allocated), resource consumer groups (users, sessions), and resource plan directives (how resource is shared). You can manage database and OS resource such as:
- CPU
- Number of active sessions
- Degree of Parallelism
- Undo generation
- Operation execution time
- Idle Time
SYSTEM_PLAN --The Initial Plan
| Resource consumer Group | Allocation Methods | |||
| WHO? | CPU_P1 | CPU_P2 | CPU_P3 | |
| SYS_GROUP | Sys & System users | 100% | 0% | 0% |
| OTHER_GROUP | Sessions who are not part of the active resource plan | 0% | 100% | 0% |
| LOW_GROUP | Lower priority than SYS_GROUP and OTHER_GROUP | 0% | 0% | 100% |
There are 3 ways you can create a resource plan:
1) Enterprise Manager
2) CREATE_SIMPLE_PLAN: Up to 8 consumer groups can be specified and the only plan directive is for CPU. The plan uses the Emphasis CPU allocation policy and each consumer group uses the ROUND_ROBIN scheduling policy.
3) DBMS_RESOURCE_MANAGER package – to create a complex plan as follow:
Create a Pending Area
Create resource plans
Create resource consumer groups
Create Plan directives
Validate the pending area
Submit the pending area
ACTIVE_SESS_POOL_P1: Number of active sessions that establishes the resource consumer gorup’s threashold and thus its active session pool (default is 1000000)
QUEUEING_P1: How long (in seconds) an session will wait on the queue before aborting the current operation.
For example: DSS group
ACTIVE_SESS_POOL_P1 = 5
QUEUEING_P1 = 600
All resource consumer group DSS has an active session pool value of 5 and all DSS sessions waiting on the queue for more than 10 minutes will abort with an error.
MAX_EST_EXEC_TIME – the operation’s estimate is more than MAX_EST_EXEC_TIME, then the operation will not start
MAX_IDDLE_TIME =>600 : Pmon process kills sessions that are idle for longer than 600 seconds
MAX_IDDLE_BLOCKER_TIME=>: Pmon kills sessions that are idle for more than 300 seconds if they’re blocking oter sessions.
Resource Application Methods for Resource Plans
· CPU_MTH: how much CPU each consumer group or subplan gets. There are 2 ways - EMPAHSIS (default) is for multilevel plans that use percentages to specify how CPU is distributed among consumer groups. RATIO is for single-level plans to specify how CPU is distributed.
EMPHASIS CPU | RATIO CPU |
Maxium % of CPU resouces a consumer gorup can use | Ratio of CPUto be allocated to a consumer group |
You can allocate CPU levels from 1 to 8 | You can only specify values for one level |
Based on specified % and <=100 | Must use Integer value |
Default: NULL | Default: NULL |
2 possible values:
ROUND_ROBIN (default...Round-robin scheduler to ensure sessions are fairly executed)
RUN_TO_COMPLETION: Sessions with the largest active time ar scheduled ahead of other session.
· PARALLEL_DEGREE_LIMIT_MTH: Limits the maximum degree of parallelism of any operation for resource consumer groups only. The ABSOLUTE method is the possible value, specifying how many processes may be assigned to an operation.
· ACTIVE_SESS_POOL_MTH: Limits number of active sessions
· QUEUING_MTH: Controls order in which queued inactive sessions will execute
To view Database Resouce Manager Information:
Select plan, num_plan_directives, status, mandatory dba_rsrc_plans;
PLAN NUM_PLAN_DIRECTIVES STATUS MAN
------------------------------ ------------------- ------------------------------ ---
IMPORTER_PLAN 2 NO
SYSTEM_PLAN 3 NO
EDOC_DAILY_PLAN 7 NO
INTERNAL_PLAN 1 YES
READER_PLAN 1 NO
INTERNAL_QUIESCE 2 YES
select plan, group_or_subplan, cpu_p1, cpu_p2, cpu_p3, parallel_degree_limit_p1, status
from dba_rsrc_plan_directives;
PLAN GROUP_OR_SUBPLAN CPU_P1 CPU_P2 CPU_P3 PARALLEL_DEGREE_LIMIT_P1 STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ------------------------ --------
EDOC_DAILY_PLAN DBA_GROUP 0 0 50 1
EDOC_DAILY_PLAN SUPP_GROUP 0 0 0 1
EDOC_DAILY_PLAN DEV_GROUP 0 0 50 1
EDOC_DAILY_PLAN SSEARCH_GROUP 0 50 0 3
EDOC_DAILY_PLAN SYS_GROUP 100 0 0
SYSTEM_PLAN SYS_GROUP 100 0 0
SYSTEM_PLAN OTHER_GROUPS 0 100 0
SYSTEM_PLAN LOW_GROUP 0 0 100
INTERNAL_QUIESCE SYS_GROUP 0 0 0
INTERNAL_QUIESCE OTHER_GROUPS 0 0 0
INTERNAL_PLAN OTHER_GROUPS 0 0 0
EDOC_DAILY_PLAN OTHER_GROUPS 0 0 0 1
EDOC_DAILY_PLAN APP_GROUP 0 50 0 3
SQL> select consumer_group, status, mandatory from dba_rsrc_consumer_groups;
CONSUMER_GROUP STATUS MAN
------------------------------ ------------------------------ ---
OTHER_GROUPS YES
DEFAULT_CONSUMER_GROUP YES
SYS_GROUP YES
LOW_GROUP NO
AUTO_TASK_CONSUMER_GROUP NO
APP_GROUP NO
DBA_GROUP NO
SUPP_GROUP NO
DEV_GROUP NO
SSEARCH_GROUP NO
