Managing Resouces

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_P1CPU_P2CPU_P3
SYS_GROUPSys & System users100%0%0%
OTHER_GROUPSessions who are not part of the active resource plan0%100%0%
LOW_GROUPLower priority than SYS_GROUP and OTHER_GROUP0%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

No comments:

Post a Comment