Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Sunday, October 4, 2015

Administer Resource Manager

Oracle Database Resource Manager (the Resource Manager) enables you to optimize resource allocation among the many concurrent database sessions.


  • Resource consumer group

A group of sessions that are grouped together based on resource requirements. The Resource Manager allocates resources to resource consumer groups, not to individual sessions.

  • Resource plan

A container for directives that specify how resources are allocated to resource consumer groups. You specify how the database allocates resources by activating a specific resource plan.

  • Resource plan directive

Associates a resource consumer group with a particular plan and specifies how resources are to be allocated to that resource consumer group.

Resource Consumer Groups

A resource consumer group (consumer group) is a collection of user sessions that are grouped together based on their processing needs. When a session is created, it is automatically mapped to a consumer group based on mapping rules that you set up. As a database administrator (DBA), you can manually switch a session to a different consumer group. Similarly, an application can run a PL/SQL package procedure that switches its session to a particular consumer group.

Because the Resource Manager allocates resources (such as CPU) only to consumer groups, when a session becomes a member of a consumer group, its resource allocation is then determined by the allocation for the consumer group. By default, each session in a consumer group shares the resources allocated to that group with other sessions in the group in a round robin fashion.

There are three special consumer groups that are always present in the data dictionary. They cannot be modified or deleted. They are:
  • SYS_GROUP
  • This is the initial consumer group for all sessions created by user accounts SYS or SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules.
  • DEFAULT_CONSUMER_GROUP
  • This is the initial consumer group for all sessions started by user accounts other than SYS and SYSTEM. This initial consumer group can be overridden by session-to–consumer group mapping rules. DEFAULT_CONSUMER_GROUP cannot be named in a resource plan directive.
  • OTHER_GROUPS
  • This group applies collectively to all sessions that belong to a consumer group that is not part of the currently active plan, including sessions that belong to DEFAULT_CONSUMER_GROUP. OTHER_GROUPS must have a resource plan directive specified in every plan. It cannot be explicitly assigned to sessions through mapping rules.



Procedure
Description
GRANT_SYSTEM_PRIVILEGE
Grants the ADMINISTER_RESOURCE_MANAGER system privilege to a user or role.
REVOKE_SYSTEM_PRIVILEGE
Revokes the ADMINISTER_RESOURCE_MANAGER system privilege from a user or role.



The following PL/SQL block grants the administrative privilege to user SCOTT, but does not grant SCOTT the ADMIN option. Therefore, RESMGR can execute all of the procedures in the DBMS_RESOURCE_MANAGER package, but SCOTT cannot use the GRANT_SYSTEM_PRIVILEGE procedure to grant the administrative privilege to others.

BEGIN
 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
  GRANTEE_NAME   => 'RSMGR,
  PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
  ADMIN_OPTION   => FALSE);
END;
/


The following PL/SQL block creates a simple resource plan with two user-specified consumer groups:


SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;    

PLAN            GROUP_OR_SUBPLAN           CPU_P1       CPU_P2     CPU_P3    MGMT_P1    MGMT_P2    MGMT_P3
-------------------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MY_SIMPLE_PLAN         MY_SIMPLE_GROUP1                0           80       0          0     80        0
MY_SIMPLE_PLAN         MY_SIMPLE_GROUP2                0           20       0          0     20        0
MY_SIMPLE_PLAN         OTHER_GROUPS                0       0     100          0      0      100
MY_SIMPLE_PLAN         SYS_GROUP                   100       0       0        100      0        0


SHAIKDB>begin
 2  dbms_resource_manager.create_simple_plan(simple_plan=>'MY_SIMPLE_PLAN',
 3  CONSUMER_GROUP1=>'MY_SIMPLE_GROUP1',GROUP1_PERCENT=>80,
 4  CONSUMER_GROUP2=>'MY_SIMPLE_GROUP2',GROUP2_PERCENT=>20);
 5  END;
 6  /

PL/SQL procedure successfully completed.


Consumer Group
Level 1
Level 2
Level 3
SYS_GROUP
100%
-
-
MYGROUP1
-
80%
-
MYGROUP2
-
20%
-
OTHER_GROUPS
-
-
100%

A complex resource plan is any resource plan that is not created with the DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN procedure.

for a more complex resource plan, you must create the plan, with its directives and consumer groups, in a staging area called the pending area, and then validate the plan before storing it in the data dictionary.

Step 1: Create a pending area.
Step 2: Create, modify, or delete consumer groups.
Step 3: Create the resource plan.
Step 4: Create resource plan directives.
Step 5: Validate the pending area.
Step 6: Submit the pending area.

Pending Area

The pending area is a staging area where you can create a new resource plan, update an existing plan, or delete a plan without affecting currently running applications. When you create a pending area, the database initializes it and then copies existing plans into the pending area so that they can be updated

SHAIKDB>exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

Creating a Resource Consumer Group
The following PL/SQL block creates a consumer group called OLTP with the default (ROUND-ROBIN) method of allocating resources to sessions in the group:


SHAIKDB>exec dbms_resource_manager.create_consumer_group('CRITICAL','Group for Critical Apps');

PL/SQL procedure successfully completed.
Available Procedures under Resource_manager:


Subprogram
Description
CALIBRATE_IO Procedure
Calibrates the I/O capabilities of storage
CLEAR_PENDING_AREA Procedure
Clears the work area for the resource manager
CREATE_CATEGORY Procedure
Creates a new resource consumer group category
CREATE_CONSUMER_GROUP Procedure
Creates entries which define resource consumer groups
CREATE_PENDING_AREA Procedure
Creates a work area for changes to resource manager objects
CREATE_PLAN Procedure
Creates entries which define resource plans
CREATE_PLAN_DIRECTIVE Procedure
Creates resource plan directives
CREATE_SIMPLE_PLAN Procedure
Creates a single-level resource plan containing up to eight consumer groups in one step
DELETE_CATEGORY Procedure
Deletes an existing resource consumer group category
DELETE_CONSUMER_GROUP Procedure
Deletes entries which define resource consumer groups
DELETE_PLAN Procedure
Deletes the specified plan as well as all the plan directives it refers to
DELETE_PLAN_CASCADE Procedure
Deletes the specified plan as well as all its descendants (plan directives, subplans, consumer groups)
DELETE_PLAN_DIRECTIVE Procedure
Deletes resource plan directives
SET_CONSUMER_GROUP_MAPPING Procedure
Adds, deletes, or modifies entries for the login and run-time attribute mappings
SET_CONSUMER_GROUP_MAPPING_PRI Procedure
Creates the session attribute mapping priority list
SET_INITIAL_CONSUMER_GROUP Procedure
Assigns the initial resource consumer group for a user (Caution: Deprecated Subprogram)
SUBMIT_PENDING_AREA Procedure
Submits pending changes for the resource manager
SWITCH_CONSUMER_GROUP_FOR_SESS Procedure
Changes the resource consumer group of a specific session
SWITCH_CONSUMER_GROUP_FOR_USER Procedure
Changes the resource consumer group for all sessions with a given user name
SWITCH_PLAN Procedure
Sets the current resource manager plan
UPDATE_CATEGORY Procedure
Updates an existing resource consumer group category
UPDATE_CONSUMER_GROUP Procedure
Updates entries which define resource consumer groups
UPDATE_PLAN Procedure
Updates entries which define resource plans
UPDATE_PLAN_DIRECTIVE Procedure
Updates resource plan directives
VALIDATE_PENDING_AREA Procedure
Validates pending changes for the resource manager


Creating a Resource Plan

Parameter
Description
PLAN
Name to assign to the plan.
COMMENT
Any descriptive comment.
CPU_MTH
Deprecated. Use MGMT_MTH.
ACTIVE_SESS_POOL_MTH
Active session pool resource allocation method. ACTIVE_SESS_POOL_ABSOLUTE is the default and only method available.
PARALLEL_DEGREE_LIMIT_MTH
Resource allocation method for specifying a limit on the degree of parallelism of any operation. PARALLEL_DEGREE_LIMIT_ABSOLUTE is the default and only method available.
QUEUEING_MTH
Queuing resource allocation method. Controls the order in which queued inactive sessions are removed from the queue and added to the active session pool. FIFO_TIMEOUT is the default and only method available.
MGMT_MTH
Resource allocation method for specifying how much CPU each consumer group or subplan gets. 'EMPHASIS', the default method, is for single-level or multilevel plans that use percentages to specify how CPU is distributed among consumer groups. 'RATIO' is for single-level plans that use ratios to specify how CPU is distributed.
SUB_PLAN
If TRUE, the plan cannot be used as the top plan; it can be used as a subplan only. Default is FALSE.

The following shows resource plan options:



PLAN
Name of the resource plan to which the directive belongs.
GROUP_OR_SUBPLAN
Name of the consumer group or subplan to which to allocate resources.
COMMENT
Any comment.
CPU_P1
Deprecated. Use MGMT_P1.
CPU_P2
Deprecated. Use MGMT_P2.
CPU_P3
Deprecated. Use MGMT_P3.
CPU_P4
Deprecated. Use MGMT_P4.
CPU_P5
Deprecated. Use MGMT_P5.
CPU_P6
Deprecated. Use MGMT_P6.
CPU_P7
Deprecated. Use MGMT_P7.
CPU_P8
Deprecated. Use MGMT_P8.
ACTIVE_SESS_POOL_P1
Specifies the maximum number of concurrently active sessions for a consumer group. Other sessions await execution in an inactive session queue. Default is UNLIMITED.
QUEUEING_P1
Specifies time (in seconds) after which a session in an inactive session queue (waiting for execution) times out and the call is aborted. Default is UNLIMITED.
PARALLEL_DEGREE_LIMIT_P1
Specifies a limit on the degree of parallelism for any operation. Default is UNLIMITED.
SWITCH_GROUP
Specifies the consumer group to which a session is switched if switch criteria are met. If the group name is 'CANCEL_SQL', then the current call is canceled when switch criteria are met. If the group name is 'KILL_SESSION', then the session is killed when switch criteria are met. Default is NULL. If the group name is 'CANCEL_SQL', the SWITCH_FOR_CALL parameter is always set to TRUE, overriding the user-specified setting.
SWITCH_TIME
Specifies the time (in CPU seconds) that a call can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_ESTIMATE
If TRUE, the database estimates the execution time of each call, and if estimated execution time exceeds SWITCH_TIME, the session is switched to the SWITCH_GROUP before beginning the call. Default is FALSE. The execution time estimate is obtained from the optimizer. The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics. In general, you should expect statistics to be no more accurate than ± 10 minutes.
MAX_EST_EXEC_TIME
Specifies the maximum execution time (in CPU seconds) allowed for a call. If the optimizer estimates that a call will take longer than MAX_EST_EXEC_TIME, the call is not allowed to proceed and ORA-07455 is issued. If the optimizer does not provide an estimate, this directive has no effect. Default is UNLIMITED. The accuracy of the estimate is dependent on many factors, especially the quality of the optimizer statistics. In general, you should expect statistics to be no more accurate than ± 10 minutes.
UNDO_POOL
Sets a maximum in kilobytes (K) on the total amount of undo for uncommitted transactions that can be generated by a consumer group. Default is UNLIMITED.
MAX_IDLE_TIME
Indicates the maximum session idle time, in seconds. Default is NULL, which implies unlimited.
MAX_IDLE_BLOCKER_TIME
Indicates the maximum session idle time of a blocking session, in seconds. Default is NULL, which implies unlimited.
SWITCH_TIME_IN_CALL
Deprecated. Use SWITCH_FOR_CALL.
MGMT_P1
For a plan with the MGMT_MTH parameter set to EMPHASIS, specifies the CPU percentage to allocate at the first level. For MGMT_MTH set to RATIO, specifies the weight of CPU usage. Default is NULL for all MGMT_Pn parameters.
MGMT_P2
For EMPHASIS, specifies CPU percentage to allocate at the second level. Not applicable for RATIO.
MGMT_P3
For EMPHASIS, specifies CPU percentage to allocate at the third level. Not applicable for RATIO.
MGMT_P4
For EMPHASIS, specifies CPU percentage to allocate at the fourth level. Not applicable for RATIO.
MGMT_P5
For EMPHASIS, specifies CPU percentage to allocate at the fifth level. Not applicable for RATIO.
MGMT_P6
For EMPHASIS, specifies CPU percentage to allocate at the sixth level. Not applicable for RATIO.
MGMT_P7
For EMPHASIS, specifies CPU percentage to allocate at the seventh level. Not applicable for RATIO.
MGMT_P8
For EMPHASIS, specifies CPU percentage to allocate at the eighth level. Not applicable for RATIO.
SWITCH_IO_MEGABYTES
Specifies the number of megabytes of I/O that a session can transfer (read and write) before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_IO_REQS
Specifies the number of I/O requests that a session can execute before an action is taken. Default is UNLIMITED. The action is specified by SWITCH_GROUP.
SWITCH_FOR_CALL
If TRUE, a session that was automatically switched to another consumer group (according to SWITCH_TIME, SWITCH_IO_MEGABYTES, or SWITCH_IO_REQS) is returned to its original consumer group when the top level call completes. Default is NULL.
MAX_UTILIZATION_LIMIT
Absolute maximum CPU utilization percentage permitted for the consumer group. This value overrides any level allocations for CPU (MGMT_P1 through MGMT_P8), and also imposes a limit on total CPU utilization when unused allocations are redistributed. You can specify this attribute and leave MGMT_P1 through MGMT_P8 NULL. You cannot specify this attribute for a subplan.


Validating the Pending Area:
The following PL/SQL block validates the pending area.
BEGIN
 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/


Submitting the Pending Area:
The following PL/SQL block submits the pending area:
BEGIN
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
Clearing the Pending Area
There is also a procedure for clearing the pending area at any time. This PL/SQL block causes all of your changes to be cleared from the pending area and deactivates the pending area:
BEGIN
 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

Switching a Single Session

The SWITCH_CONSUMER_GROUP_FOR_SESS procedure causes the specified session to immediately be moved into the specified resource consumer group. In effect, this procedure can raise or lower priority of the session.
The following PL/SQL block switches a specific session to a new consumer group. The session identifier (SID) is 17, the session serial number (SERIAL#) is 12345, and the new consumer group is the HIGH_PRIORITY consumer group.
BEGIN
 DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS ('17', '12345',
  'HIGH_PRIORITY');
END;
/

Switching All Sessions for a User

The SWITCH_CONSUMER_GROUP_FOR_USER procedure changes the resource consumer group for all sessions pertaining to the specified user name. The following PL/SQL block switches all sessions that belong to user SCOTT to the LOW_GROUP consumer group:
BEGIN
 DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER ('SCOTT',
   'LOW_GROUP');
END;
/
Disabling the Resource Manager
To disable the Resource Manager, complete the following steps:
  1. Issue the following SQL statement:
  2. ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';


The following statement in a text initialization parameter file activates the Resource Manager upon database startup and sets the top plan as mydb_plan.
RESOURCE_MANAGER_PLAN = mydb_plan
You can also activate or deactivate the Resource Manager, or change the current top plan, using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure or the ALTER SYSTEM statement.
The following SQL statement sets the top plan to mydb_plan, and activates the Resource Manager if it is not already active:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';
 
Deleting a Plan
The DELETE_PLAN procedure deletes the specified plan as well as all the plan directives associated with it. The pending area must be created first, and then submitted after the plan is deleted.
The following PL/SQL block deletes the great_bread plan and its directives.
BEGIN
 DBMS_RESOURCE_MANAGER.DELETE_PLAN(PLAN => 'SIMPLE');
END;
/

Updating a Resource Plan Directive

Use the UPDATE_PLAN_DIRECTIVE procedure to update plan directives. The pending area must be created first, and then submitted after the resource plan directive is updated. If you do not specify an argument for the UPDATE_PLAN_DIRECTIVE procedure, its corresponding parameter in the directive remains unchanged.
The following example adds a comment to a directive:
BEGIN
 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        PLAN             => 'SIMPLE_PLAN1',
        GROUP_OR_SUBPLAN => 'MYGROUP1',
        NEW_COMMENT      => 'Higher priority'
       );
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/
To clear (nullify) a comment, pass a null string (''). To clear (zero or nullify) any numeric directive parameter, set its new value to -1:
BEGIN
 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
 DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
        PLAN                  => 'SIMPLE_PLAN1',
        GROUP_OR_SUBPLAN      => 'MYGROUP1',
        NEW_MAX_EST_EXEC_TIME => -1
       );
 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

SELECT PLAN,STATUS, COMMENTS FROM DBA_RSRC_PLANS;
SQL> SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION;
SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN;


View
Description
DBA_RSRC_CONSUMER_GROUP_PRIVS
USER_RSRC_CONSUMER_GROUP_PRIVS
DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.
DBA_RSRC_CONSUMER_GROUPS
Lists all resource consumer groups that exist in the database.
DBA_RSRC_MANAGER_SYSTEM_PRIVS
USER_RSRC_MANAGER_SYSTEM_PRIVS
DBA view lists all users and roles that have been granted Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.
DBA_RSRC_PLAN_DIRECTIVES
Lists all resource plan directives that exist in the database.
DBA_RSRC_PLANS
Lists all resource plans that exist in the database.
DBA_RSRC_GROUP_MAPPINGS
Lists all of the various mapping pairs for all of the session attributes.
DBA_RSRC_MAPPING_PRIORITY
Lists the current mapping priority of each attribute.
DBA_HIST_RSRC_PLAN
Displays historical information about resource plan activation. This view contains AWR snapshots of V$RSRC_PLAN_HISTORY.
DBA_HIST_RSRC_CONSUMER_GROUP
Displays historical statistical information about consumer groups. This view contains AWR snapshots of V$RSRC_CONS_GROUP_HISTORY.
DBA_USERS
USERS_USERS
DBA view contains information about all users of the database. It contains the initial resource consumer group for each user. USER view contains information about the current user. It contains the current user's initial resource consumer group.
V$ACTIVE_SESS_POOL_MTH
Displays all available active session pool resource allocation methods.
V$PARALLEL_DEGREE_LIMIT_MTH
Displays all available parallel degree limit resource allocation methods.
V$QUEUEING_MTH
Displays all available queuing resource allocation methods.
V$RSRC_CONS_GROUP_HISTORY
For each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.
V$RSRC_CONSUMER_GROUP
Displays information about active resource consumer groups. This view can be used for tuning.
V$RSRC_CONSUMER_GROUP_CPU_MTH
Displays all available CPU resource allocation methods for resource consumer groups.
V$RSRCMGRMETRIC
Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past minute.
V$RSRCMGRMETRIC_HISTORY
Displays a history of resources consumed and cumulative CPU wait time (due to resource management) per consumer group for the past hour on a minute-by-minute basis. If a new resource plan is enabled, the history is cleared.
V$RSRC_PLAN
Displays the names of all currently active resource plans.
V$RSRC_PLAN_CPU_MTH
Displays all available CPU resource allocation methods for resource plans.
V$RSRC_PLAN_HISTORY
Shows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.
V$RSRC_SESSION_INFO
Displays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.
V$SESSION
Lists session information for each current session. Specifically, lists the name of the resource consumer group of each current session.



Demo:

SHAIKDB>create user resmgr identified by resmgr default tablespace tbs1 temporary tablespace temp;

User created.

SHAIKDB>grant create session,create table,select any table,create procedure,create trigger to resmgr;

Grant succeeded.

SHAIKDB>SELECT GRANTEE,PRIVILEGE FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;

GRANTEE               PRIVILEGE
------------------------------ ----------------------------------------
SYS                  ADMINISTER RESOURCE MANAGER
DBA                  ADMINISTER RESOURCE MANAGER
EXP_FULL_DATABASE           ADMINISTER RESOURCE MANAGER
IMP_FULL_DATABASE           ADMINISTER RESOURCE MANAGER
APPQOSSYS              ADMINISTER RESOURCE MANAGER



SHAIKDB>BEGIN DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE('RESMGR','ADMINISTER_RESOURCE_MANAGER',FALSE);
  END;
 /

PL/SQL procedure successfully completed.


USERNAME   PRIVILEGE             ADM
---------- ------------------------------ ---
RESMGR       ADMINISTER RESOURCE MANAGER      NO
RESMGR       CREATE TRIGGER         NO
RESMGR       SELECT ANY TABLE         NO
RESMGR       CREATE PROCEDURE         NO
RESMGR       CREATE SESSION         NO
RESMGR       CREATE TABLE          NO

6 rows selected.

Granting the RESOURCE_MANAGER_ADMIN privilege using GRID Control:
















Delete the plans:


SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP('CRITICAL');

PL/SQL procedure successfully completed.


If the pending area is not active:


SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN');
BEGIN DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN'); END;

*
ERROR at line 1:
ORA-29371: pending area is not active
ORA-06512: at "SYS.DBMS_RMIN", line 105
ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 180
ORA-06512: at line 1

Create the pending Area:


SHAIKDB>BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    END;
    /  2    3    4  

PL/SQL procedure successfully completed.

Delete the PLAN:

SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;


PLAN                  GROUP_OR_SUBPLAN          CPU_P1     CPU_P2    CPU_P3      MGMT_P1    MGMT_P2    MGMT_P3
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
MY_SIMPLE_PLAN              MY_SIMPLE_GROUP1               0     80         0       0      80          0
MY_SIMPLE_PLAN              MY_SIMPLE_GROUP2               0     20         0       0      20          0
MY_SIMPLE_PLAN              OTHER_GROUPS                  0      0       100       0       0        100
MY_SIMPLE_PLAN              SYS_GROUP                100      0         0          100       0          0

SHAIKDB>exec   DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

PL/SQL procedure successfully completed.

SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN('MY_SIMPLE_PLAN');

PL/SQL procedure successfully completed.

SHAIKDB>EXEC   DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

PL/SQL procedure successfully completed.

SHAIKDB>show errors                                        
No errors.
SHAIKDB>exec dbms_resource_manager.submit_pending_Area();

PL/SQL procedure successfully completed.

SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan like '%SIMPLE%' order by 2;

no rows selected

SHAIKDB>



DEMO-2:

  • Creating:
  • Pending area
  • Create consumer groups CRITICAL,MEDIUM,LOW
  • Create PLAN - CRITICAL
  • Create PLAN directives
  • Validate PLAN
  • Submit PLAN.





SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan=’CRITICAL_PLAN’ order by 2;

no rows selected

SHAIKDB>exec dbms_resource_manager.create_pending_area();

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_consumer_group('CRITICAL','Group for Critical Apps');

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_plan('CRITICAL_PLAN','Plan fr critical queries');

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'CRITICAL',COMMENT=>'Allocate 60% CPU',MGMT_P1=>60);

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_consumer_group('MEDIUM','Medium critical Apps');

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_consumer_group('LOW','Low critical Apps');

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'MEDIUM', COMMENT=>'ALLOCATE CPU 20%',MGMT_P1=>20,PARALLEL_DEGREE_LIMIT_P1=>2,ACTIVE_SESS_POOL_P1=>2);

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'LOW', COMMENT=>'Allocate 10% CPU',MGMT_P1=>10,PARALLEL_DEGREE_LIMIT_P1=>1,ACTIVE_SESS_POOL_P1=>1);

PL/SQL procedure successfully completed.




SHAIKDB>exec dbms_resource_manager.create_plan_directive(PLAN=>'CRITICAL_PLAN',GROUP_OR_SUBPLAN=>'OTHER_GROUPS',COMMENT=>'ALLOCATE CPU 10% FOR OTHER GROUPS',MGMT_P1=>10);

PL/SQL procedure successfully completed.

SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

PL/SQL procedure successfully completed.

SHAIKDB>SHOW ERRORS
No errors.
SHAIKDB>EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA   

PL/SQL procedure successfully completed.

SHAIKDB>select plan,GROUP_OR_SUBPLAN,CPU_P1,CPU_P2,CPU_P3,mgmt_p1,mgmt_p2,mgmt_p3 from dba_rsrc_plan_directives where plan='CRITICAL_PLAN' order by 3 desc;

PLAN                  GROUP_OR_SUBPLAN          CPU_P1     CPU_P2    CPU_P3      MGMT_P1    MGMT_P2    MGMT_P3
------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CRITICAL_PLAN              CRITICAL                  60      0         0           60       0          0
CRITICAL_PLAN              MEDIUM               20      0         0           20       0          0
CRITICAL_PLAN              LOW                     10      0         0           10       0          0
CRITICAL_PLAN              OTHER_GROUPS           10      0         0           10       0          0


Assign consumer groups to USERS:

SHAIKDB>create user test1 identified by test1 default tablespace tbs1 temporary tablespace temp;

User created.

SHAIKDB>grant create session to test1;

Grant succeeded.

SHAIKDB>grant select any table to test1;

Grant succeeded.

SHAIKDB>select username,account_status,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username='TEST1';

USERNAME              ACCOUNT_STATUS           INITIAL_RSRC_CONSUMER_GROUP
------------------------------ -------------------------------- ------------------------------
TEST1                  OPEN               DEFAULT_CONSUMER_GROUP


SHAIKDB>exec dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'TEST1',consumer_group=>'LOW',grant_option=>TRUE);

PL/SQL procedure successfully completed.

SHAIKDB>exec dbms_resource_manager.set_initial_consumer_group(user=>'TEST1',consumer_group=>'LOW');

PL/SQL procedure successfully completed.


SHAIKDB>SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;

GRANTEE               GRANTED_GROUP             GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC                  DEFAULT_CONSUMER_GROUP          YES YES
SYSTEM                  SYS_GROUP             NO  YES
TEST1                  LOW                 YES YES
PUBLIC                  LOW_GROUP             NO  NO

SHAIKDB>select username,account_status,INITIAL_RSRC_CONSUMER_GROUP from dba_users where username='TEST1';

USERNAME              ACCOUNT_STATUS           INITIAL_RSRC_CONSUMER_GROUP
------------------------------ -------------------------------- ------------------------------
TEST1                  OPEN               LOW

SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';

no rows selected

SHAIKDB>/

      SID    SERIAL# USERNAME                RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
   50     2546 TEST1                OTHER_GROUPS

SHAIKDB>/


SWITCHING CONSUMER_GROUPS:


SHAIKDB>exec dbms_resource_manager.switch_consumer_group_for_sess('50','2546','MEDIUM');

PL/SQL procedure successfully completed.

SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';

      SID    SERIAL# USERNAME                RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
   50     2546 TEST1                MEDIUM


SHAIKDB>exec dbms_resource_manager.switch_consumer_group_for_user('TEST1','CRITICAL');

PL/SQL procedure successfully completed.

SHAIKDB>SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION where username='TEST1';

      SID    SERIAL# USERNAME                RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
   50     2546 TEST1                CRITICAL

Revoking Switch Privileges

SHAIKDB>exec dbms_resource_manager_privs.revoke_switch_consumer_group(revokee_name=>'TEST1',consumer_group=>'LOW');

PL/SQL procedure successfully completed.

 Documentation:
Oracle® Database PL/SQL Packages and Types Reference --> 116 DBMS_RESOURCE_MANAGER

Oracle® Database Administrator's Guide 11g Release 2 (11.2) -->
26 Managing Resource Allocation with Oracle Database Resource Manager


No comments: