DATABASE RESOURCE MANAGER
The Database Resource Manager allows the DBA to have more control over certain resources utilization than is normally possible through operating system resource management alone. With Oracle 9i, it is possible to have control over CPU utilization and degree of parallelism.
DATABASE RESOURCE MANAGER COMPONENTS:
• Resource Consumer Group (User Groups with similar needs)
• Resource Plan ( Describes Resources allocated to Resource Consumer Group)
• Resource Plan Directive (Allocates Resources among Resource Consumer Groups)
RESOURCE ALLOCATION METHOD:
This is the method, which is used by Database Resource Manager to allocate any particular resource to any Resource Consumer Group or Plan.
Currently, the method which is used to allocate CPU among Resource Consumer Group is Emphasis Method.
Currently, the method which is used to limiting the degree of parallelism is Absolute Method.
Every Consumer Group may contain multiple numbers of users and the method which is used to distribute CPU Resources inside each Consumer Group that is Round Robin Method.
CPU usage is assigned in levels from 1 to 8, with level 1 having the highest priority.
ADMINISTERING DATABASE RESOURCE MANAGER:
In order to administer database resource manager, required privilege is administer_resource_manager (By default SYS having)
Then it is possible to execute all procedures in the package dbms_resource_manager.
The administer_resource_manager privilege is granted and revoked through the package dbms_resource_manager_privs. It can not be granted and revoked through the sql statement (Grant and Revoke).
EXAMPLE: Grant System Privilege Procedure
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (grantee_name => 'scott', privilege_
name => 'administer_resource_manager', admin_option => false);
It grants the administrative privilege to Scott but does not grant sys with admin option privilege.
Admin_option => false: Scott can execute all procedures in dbms_resource_manager package but Scott can not use the grant_system_privilege procedure to grant the administrative privilege to others.
EXAMPLE: Revoke System Privilege Procedure
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SYSTEM_PRIVILEGE ('scott');
NOTE: DBMS_RESOURCE_MANAGER_PRIVS package also contains another two procedures such as
1. GRANT SWITCH_CONSUMER_GROUP
2. REVOKE_SWITCH_CONSUMER_GROUP
EXAMPLE: Grant Switch Consumer Group Procedure
SQL> EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
'scott', 'mail_maintenance_group', true);
EXAMPLE: Revoke Switch Consumer Group Procedure
SQL> EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
'scott', 'mail_maintenance_group');
PENDING AREA:
For creating or modifying any resource plan, we need an area, where we can stage our changes and validate them, before those are made active and this area is called as pending area.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
VALIDATING CHANGES:
After making any change in the pending area, we have to ensure that changes have been made are valid
SQL> EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
SUBMITTING CHANGES:
After validating changes, we have to activate all those changes.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
CLEARING PENDING AREA:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
Everything will be cleared from the pending area but we must have to call the create_pending_area procedure before we can again attempt to make changes.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
CREATING RESOURCE CONSUMER GROUP:
There are two special consumer groups always present in the data dictionary, which can not be modified or deleted by any means and those are
1. Default_Consumer_Group
2. Other_Groups
It is possible to create resource consumer group by using the procedure create_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA', COMMENT => 'THIS IS DBA GROUP');
UPDATING RESOURCE CONSUMER GROUP:
Existing resource consumer group can be updated by using the procedure update_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA', NEW_COMMENT => 'THIS IS NEW DBA GROUP');
NOTE: new_cpu_mth parameter also can be specified in update_consumer_group procedure.
DELETING RESOURCE CONSUMER GROUP:
Existing resource consumer group can be deleted by using the procedure delete_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA');
CREATING RESOURCE PLAN:
New Resource plan can be created by using the procedure create_plan.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => 'DBA_PLAN', COMMENT => 'THIS IS DBA PLAN');
UPDATING RESOURCE PLAN:
Existing Resource plan can be updated by using the procedure update_plan.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN (PLAN => 'DBA_PLAN', NEW_COMMENT => 'THIS IS A NEW DBA PLAN');
DELETING RESOURCE PLAN:
Existing Resource plan can be deleted by using the procedure delete_plan.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN => 'DBA_PLAN');
CREATING RESOURCE PLAN DIRECTIVE:
It is used to assign consumer group to resource plan and can be created by using the procedure create_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', COMMENT => 'ASSIGNING DBA GROUP TO DBA_PLAN', CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 20);
UPDATING RESOURCE PLAN DIRECTIVE:
It is used to modify or update an existing a resource plan directive and can be updated by using the procedure update_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', NEW_CPU_P1 => 70, NEW_PARALLEL_DEGREE_LIMIT_P1 => 100);
DELETING RESOURCE PLAN DIRECTIVE:
It is used to delete an existing a resource plan directive and can be deleted by using the procedure delete_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA');
ASSIGNING USERS TO RESOURCE CONSUMER GROUP:
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (GRANTEE_NAME => 'SAAN', CONSUMER_GROUP => 'DBA', GRANT_OPTION => FALSE);
DISPLAYING INITIAL CONSUMER GROUP OF A USER:
SQL> SELECT USERNAME, INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS WHERE USERNAME='SAAN';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SAAN DEFAULT_CONSUMER_GROUP
CHANGING INITIAL CONSUMER GROUP FOR A USER:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (USER => 'SAAN', CONSUMER_GROUP => 'DBA');
DISPLAYING INITIAL CONSUMER GROUP OF A USER AFTER CHANGING THE INITIAL CONSUMER GROUP:
SQL> SELECT USERNAME, INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS WHERE USERNAME='SAAN';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SAAN DBA
DISPLAYING AND SETTING RESOURCE PLAN FOR INSTANCE:
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DBA_PLAN';
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string DBA_PLAN
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string
CHANGING A RESOURCE CONSUMER GROUP (BY DBA FOR ALL SESSIONS):
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
SQL>EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN=>'NORMAL_PLAN', COMMENT=>'NORMAL PLAN');
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP=>'NORMAL', COMMENT=>'NORMAL');
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN=>'NORMAL_PLAN', GROUP_OR_SUBPLAN=>'OTHER_GROUPS', COMMENT=>'NORMAL PLAN DIRECTIVE', CPU_P1=>10, PARALLEL_DEGREE_LIMIT_P1=>10);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
SQL> SELECT USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
SAAN DBA
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DBA_PLAN;
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (USER => 'SAAN', CONSUMER_GROUP => 'NORMAL');
SQL> SELECT USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
SAAN NORMAL
CHANGING A RESOURCE CONSUMER GROUP (BY DBA FOR SPECIFIC SESSIONS):
SQL> SELECT SID, SERIAL#, USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
12 23 SAAN NORMAL
16 260 SAAN DBA
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (SESSION_ID => 12, SESSION_SERIAL=> 23, CONSUMER_GROUP => 'DBA');
SQL> SELECT SID, SERIAL#, USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
12 23 SAAN DBA
16 260 SAAN DBA
ACTIVE SESSION POOL
Generally CPU resources are allocated at the beginning of a transaction or query and not freed until the transaction is completed or query is finished. There is no alternative way to free resources associated with such operations until the operation completes.
ACTIVE SESSION POOL is used to limit the number of concurrent active sessions per resource consumer group.
After the active session pool is filled, the database resource manager will queue all subsequent results and run them only after the existing active session complete its operation.
There is only one queue per resource consumer group and the queuing method is FIRST IN FIRST OUT (FIFO).
QUEUE INFORMATION:
SQL> SELECT USERNAME, CURRENT_QUEUE_DURATION FROM V$SESSION WHERE USERNAME='SAAN';
NOTE: CURRENT_QUEUE_LENGTH describes how long sessions have been queued and this value will be 0 if no sessions are currently queued.
SQL> SELECT NAME, ACTIVE_SESSIONS, QUEUE_LENGTH FROM V$RSRC_CONSUMER_GROUP;
NOTE: QUEUE_LENGTH describes number of sessions waiting in the queue.
ACTIVE SESSION POOL PARAMETER:
The active session pool parameters can be defined in either CREATE_PLAN_DIRECTIVE or UPDATE_PLAN_DIRECTIVE in the DBMS_RESOURCE_MANAGER package.
ACTIVE_SESS_POOL_P1 (CREATE_PLAN_DIRECTIVE)
NEW_ACTIVE_SESS_POOL_P1 (UPDATE_PLAN_DIRECTIVE)
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'NEW_DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', COMMENT => 'ASSIGNING DBA GROUP TO DBA_PLAN', CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 =>
20, ACTIVE_SESS_POOL_P1 => 100);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN =>'DBA', NEW_ACTIVE_SESS_POOL_P1 => 100);
NOTE: If nothing is defined, then the default value is 1000000
QUEUEING TIME:
This will define how long a session will wait in the queue, after that the operation will abort with an error message. Default value is 1000000 seconds.
The Queuing time parameters can be defined in either CREATE_PLAN_DIRECTIVE or UPDATE_PLAN_DIRECTIVE in the DBMS_RESOURCE_MANAGER package.
QUEUEING_P1 (CREATE_PLAN_DIRECTIVE)
NEW_QUEUEING_P1 (UPDATE_PLAN_DIRECTIVE)
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'NEW_DBA_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS NEW', QUEUEING_P1 => 100);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', NEW_QUEUEING_P1 => 100);
MAXIMUM ESTIMATED EXECUTION TIME:
The maximum estimated execution time for any SQL statement or PL/SQL block is calculated using the statistics from the cost based optimizer.
When a transaction is initiated, Database Resource Manager makes an estimate of the processing time required to complete the transaction. If that estimated time in seconds exceeds the value of this parameter, the transaction is aborted.
Parameter is MAX_EST_EXEC_TIME for CREATE_PLAN_DIRECTIVE and NEW_MAX_EST_EXEC_TIME for UPDATE_PLAN_DIRECTIVE.
Default value is 1000000 seconds.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'MY_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', MAX_EST_EXEC_TIME => 2000);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000);
AUTOMATIC CONSUMER GROUP SWITCHING:
If any session is active more than the time specified in SWITCH_TIME parameter, then it will be automatically switched to another consumer group specified in SWITCH_GROUP parameter.
When the session finished its operation it is switched back to its original state/group.
Parameter is SWITCH_TIME for CREATE_PLAN_DIRECTIVE and NEW_SWITCH_TIME for UPDATE_PLAN_DIRECTIVE. Similarly the other parameter is SWITCH_GROUP for CREATE_PLAN_DIRECTIVE and NEW_SWITCH_GROUP for UPDATE_PLAN_DIRECTIVE.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', MAX_EST_EXEC_TIME => 2000, SWITCH_TIME => 100, SWITCH_GROUP => ‘OTHER_GROUPS’);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000, NEW_SWITCH_TIME => 5000, NEW_SWITCH_GROUP => ‘OTHER_GROUPS’);
UNDO QUOTA FOR RESOURCE CONSUMER GROUP:
IT IS DEFINED AS QUOTA OF undo space per resource consumer group.
When this value is exceeded, it prevents DML operation but SELECT statement is allowed.
Default value is 1000000 Bytes.
It is applicable for both Automatic UNDO Management and Manual UNDO Management.
Parameter is UNDO_POOL for CREATE_PLAN_DIRECTIVE and NEW_UNDO_POOL for UPDATE_PLAN_DIRECTIVE.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'MY_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', UNDO_POOL => 6000);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000, NEW_SWITCH_TIME => 5000,NEW_SWITCH_GROUP => 'OTHER_GROUPS', NEW_UNDO_POOL => 3000);
DATABASE RESOURCE MANAGER INFORMATION:
• DBA_RSRC_PLANS
• DBA_RSRC_PLAN_DIRECTIVES
• DBA_RSRC_CONSUMER_GROUPS
• DBA_RSRC_CONSUMER_GROUP_PRIVS
• DBA_USERS
• DBA_RSRC_MANAGER_SYSTEM_PRIVS
• V$SESSION
• V$RSRC_PLAN
• V$RSRC_CONSUMER_GROUP
The Database Resource Manager allows the DBA to have more control over certain resources utilization than is normally possible through operating system resource management alone. With Oracle 9i, it is possible to have control over CPU utilization and degree of parallelism.
DATABASE RESOURCE MANAGER COMPONENTS:
• Resource Consumer Group (User Groups with similar needs)
• Resource Plan ( Describes Resources allocated to Resource Consumer Group)
• Resource Plan Directive (Allocates Resources among Resource Consumer Groups)
RESOURCE ALLOCATION METHOD:
This is the method, which is used by Database Resource Manager to allocate any particular resource to any Resource Consumer Group or Plan.
Currently, the method which is used to allocate CPU among Resource Consumer Group is Emphasis Method.
Currently, the method which is used to limiting the degree of parallelism is Absolute Method.
Every Consumer Group may contain multiple numbers of users and the method which is used to distribute CPU Resources inside each Consumer Group that is Round Robin Method.
CPU usage is assigned in levels from 1 to 8, with level 1 having the highest priority.
ADMINISTERING DATABASE RESOURCE MANAGER:
In order to administer database resource manager, required privilege is administer_resource_manager (By default SYS having)
Then it is possible to execute all procedures in the package dbms_resource_manager.
The administer_resource_manager privilege is granted and revoked through the package dbms_resource_manager_privs. It can not be granted and revoked through the sql statement (Grant and Revoke).
EXAMPLE: Grant System Privilege Procedure
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE (grantee_name => 'scott', privilege_
name => 'administer_resource_manager', admin_option => false);
It grants the administrative privilege to Scott but does not grant sys with admin option privilege.
Admin_option => false: Scott can execute all procedures in dbms_resource_manager package but Scott can not use the grant_system_privilege procedure to grant the administrative privilege to others.
EXAMPLE: Revoke System Privilege Procedure
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SYSTEM_PRIVILEGE ('scott');
NOTE: DBMS_RESOURCE_MANAGER_PRIVS package also contains another two procedures such as
1. GRANT SWITCH_CONSUMER_GROUP
2. REVOKE_SWITCH_CONSUMER_GROUP
EXAMPLE: Grant Switch Consumer Group Procedure
SQL> EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
'scott', 'mail_maintenance_group', true);
EXAMPLE: Revoke Switch Consumer Group Procedure
SQL> EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
'scott', 'mail_maintenance_group');
PENDING AREA:
For creating or modifying any resource plan, we need an area, where we can stage our changes and validate them, before those are made active and this area is called as pending area.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
VALIDATING CHANGES:
After making any change in the pending area, we have to ensure that changes have been made are valid
SQL> EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
SUBMITTING CHANGES:
After validating changes, we have to activate all those changes.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
CLEARING PENDING AREA:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
Everything will be cleared from the pending area but we must have to call the create_pending_area procedure before we can again attempt to make changes.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
CREATING RESOURCE CONSUMER GROUP:
There are two special consumer groups always present in the data dictionary, which can not be modified or deleted by any means and those are
1. Default_Consumer_Group
2. Other_Groups
It is possible to create resource consumer group by using the procedure create_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA', COMMENT => 'THIS IS DBA GROUP');
UPDATING RESOURCE CONSUMER GROUP:
Existing resource consumer group can be updated by using the procedure update_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA', NEW_COMMENT => 'THIS IS NEW DBA GROUP');
NOTE: new_cpu_mth parameter also can be specified in update_consumer_group procedure.
DELETING RESOURCE CONSUMER GROUP:
Existing resource consumer group can be deleted by using the procedure delete_consumer_group
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP (CONSUMER_GROUP => 'DBA');
CREATING RESOURCE PLAN:
New Resource plan can be created by using the procedure create_plan.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN => 'DBA_PLAN', COMMENT => 'THIS IS DBA PLAN');
UPDATING RESOURCE PLAN:
Existing Resource plan can be updated by using the procedure update_plan.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN (PLAN => 'DBA_PLAN', NEW_COMMENT => 'THIS IS A NEW DBA PLAN');
DELETING RESOURCE PLAN:
Existing Resource plan can be deleted by using the procedure delete_plan.
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN => 'DBA_PLAN');
CREATING RESOURCE PLAN DIRECTIVE:
It is used to assign consumer group to resource plan and can be created by using the procedure create_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', COMMENT => 'ASSIGNING DBA GROUP TO DBA_PLAN', CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 => 20);
UPDATING RESOURCE PLAN DIRECTIVE:
It is used to modify or update an existing a resource plan directive and can be updated by using the procedure update_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', NEW_CPU_P1 => 70, NEW_PARALLEL_DEGREE_LIMIT_P1 => 100);
DELETING RESOURCE PLAN DIRECTIVE:
It is used to delete an existing a resource plan directive and can be deleted by using the procedure delete_plan_directive.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA');
ASSIGNING USERS TO RESOURCE CONSUMER GROUP:
SQL>EXECUTE DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (GRANTEE_NAME => 'SAAN', CONSUMER_GROUP => 'DBA', GRANT_OPTION => FALSE);
DISPLAYING INITIAL CONSUMER GROUP OF A USER:
SQL> SELECT USERNAME, INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS WHERE USERNAME='SAAN';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SAAN DEFAULT_CONSUMER_GROUP
CHANGING INITIAL CONSUMER GROUP FOR A USER:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP (USER => 'SAAN', CONSUMER_GROUP => 'DBA');
DISPLAYING INITIAL CONSUMER GROUP OF A USER AFTER CHANGING THE INITIAL CONSUMER GROUP:
SQL> SELECT USERNAME, INITIAL_RSRC_CONSUMER_GROUP FROM DBA_USERS WHERE USERNAME='SAAN';
USERNAME INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
SAAN DBA
DISPLAYING AND SETTING RESOURCE PLAN FOR INSTANCE:
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DBA_PLAN';
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string DBA_PLAN
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='';
SQL> SHOW PARAMETER RESOURCE_MANAGER_PLAN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string
CHANGING A RESOURCE CONSUMER GROUP (BY DBA FOR ALL SESSIONS):
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
SQL>EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN=>'NORMAL_PLAN', COMMENT=>'NORMAL PLAN');
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP=>'NORMAL', COMMENT=>'NORMAL');
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN=>'NORMAL_PLAN', GROUP_OR_SUBPLAN=>'OTHER_GROUPS', COMMENT=>'NORMAL PLAN DIRECTIVE', CPU_P1=>10, PARALLEL_DEGREE_LIMIT_P1=>10);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
SQL> SELECT USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
SAAN DBA
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DBA_PLAN;
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (USER => 'SAAN', CONSUMER_GROUP => 'NORMAL');
SQL> SELECT USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
SAAN NORMAL
CHANGING A RESOURCE CONSUMER GROUP (BY DBA FOR SPECIFIC SESSIONS):
SQL> SELECT SID, SERIAL#, USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
12 23 SAAN NORMAL
16 260 SAAN DBA
SQL> EXECUTE DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (SESSION_ID => 12, SESSION_SERIAL=> 23, CONSUMER_GROUP => 'DBA');
SQL> SELECT SID, SERIAL#, USERNAME, RESOURCE_CONSUMER_GROUP FROM V$SESSION WHERE USERNAME='SAAN';
SID SERIAL# USERNAME RESOURCE_CONSUMER_GROUP
---------- ---------- ------------------------------ --------------------------------
12 23 SAAN DBA
16 260 SAAN DBA
ACTIVE SESSION POOL
Generally CPU resources are allocated at the beginning of a transaction or query and not freed until the transaction is completed or query is finished. There is no alternative way to free resources associated with such operations until the operation completes.
ACTIVE SESSION POOL is used to limit the number of concurrent active sessions per resource consumer group.
After the active session pool is filled, the database resource manager will queue all subsequent results and run them only after the existing active session complete its operation.
There is only one queue per resource consumer group and the queuing method is FIRST IN FIRST OUT (FIFO).
QUEUE INFORMATION:
SQL> SELECT USERNAME, CURRENT_QUEUE_DURATION FROM V$SESSION WHERE USERNAME='SAAN';
NOTE: CURRENT_QUEUE_LENGTH describes how long sessions have been queued and this value will be 0 if no sessions are currently queued.
SQL> SELECT NAME, ACTIVE_SESSIONS, QUEUE_LENGTH FROM V$RSRC_CONSUMER_GROUP;
NOTE: QUEUE_LENGTH describes number of sessions waiting in the queue.
ACTIVE SESSION POOL PARAMETER:
The active session pool parameters can be defined in either CREATE_PLAN_DIRECTIVE or UPDATE_PLAN_DIRECTIVE in the DBMS_RESOURCE_MANAGER package.
ACTIVE_SESS_POOL_P1 (CREATE_PLAN_DIRECTIVE)
NEW_ACTIVE_SESS_POOL_P1 (UPDATE_PLAN_DIRECTIVE)
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'NEW_DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', COMMENT => 'ASSIGNING DBA GROUP TO DBA_PLAN', CPU_P1 => 60, PARALLEL_DEGREE_LIMIT_P1 =>
20, ACTIVE_SESS_POOL_P1 => 100);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN =>'DBA', NEW_ACTIVE_SESS_POOL_P1 => 100);
NOTE: If nothing is defined, then the default value is 1000000
QUEUEING TIME:
This will define how long a session will wait in the queue, after that the operation will abort with an error message. Default value is 1000000 seconds.
The Queuing time parameters can be defined in either CREATE_PLAN_DIRECTIVE or UPDATE_PLAN_DIRECTIVE in the DBMS_RESOURCE_MANAGER package.
QUEUEING_P1 (CREATE_PLAN_DIRECTIVE)
NEW_QUEUEING_P1 (UPDATE_PLAN_DIRECTIVE)
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'NEW_DBA_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS NEW', QUEUEING_P1 => 100);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN => 'DBA', NEW_QUEUEING_P1 => 100);
MAXIMUM ESTIMATED EXECUTION TIME:
The maximum estimated execution time for any SQL statement or PL/SQL block is calculated using the statistics from the cost based optimizer.
When a transaction is initiated, Database Resource Manager makes an estimate of the processing time required to complete the transaction. If that estimated time in seconds exceeds the value of this parameter, the transaction is aborted.
Parameter is MAX_EST_EXEC_TIME for CREATE_PLAN_DIRECTIVE and NEW_MAX_EST_EXEC_TIME for UPDATE_PLAN_DIRECTIVE.
Default value is 1000000 seconds.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'MY_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', MAX_EST_EXEC_TIME => 2000);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'DBA_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000);
AUTOMATIC CONSUMER GROUP SWITCHING:
If any session is active more than the time specified in SWITCH_TIME parameter, then it will be automatically switched to another consumer group specified in SWITCH_GROUP parameter.
When the session finished its operation it is switched back to its original state/group.
Parameter is SWITCH_TIME for CREATE_PLAN_DIRECTIVE and NEW_SWITCH_TIME for UPDATE_PLAN_DIRECTIVE. Similarly the other parameter is SWITCH_GROUP for CREATE_PLAN_DIRECTIVE and NEW_SWITCH_GROUP for UPDATE_PLAN_DIRECTIVE.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', MAX_EST_EXEC_TIME => 2000, SWITCH_TIME => 100, SWITCH_GROUP => ‘OTHER_GROUPS’);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000, NEW_SWITCH_TIME => 5000, NEW_SWITCH_GROUP => ‘OTHER_GROUPS’);
UNDO QUOTA FOR RESOURCE CONSUMER GROUP:
IT IS DEFINED AS QUOTA OF undo space per resource consumer group.
When this value is exceeded, it prevents DML operation but SELECT statement is allowed.
Default value is 1000000 Bytes.
It is applicable for both Automatic UNDO Management and Manual UNDO Management.
Parameter is UNDO_POOL for CREATE_PLAN_DIRECTIVE and NEW_UNDO_POOL for UPDATE_PLAN_DIRECTIVE.
EXAMPLE:
SQL> EXECUTE DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN => 'MY_PLAN', GROUP_OR_SUBPLAN=> 'DBA', COMMENT => 'THIS IS MY PLAN DIRECTIVE', UNDO_POOL => 6000);
SQL> EXECUTE DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (PLAN => 'OUR_PLAN', GROUP_OR_SUBPLAN=>‘DBA', NEW_MAX_EST_EXEC_TIME => 2000, NEW_SWITCH_TIME => 5000,NEW_SWITCH_GROUP => 'OTHER_GROUPS', NEW_UNDO_POOL => 3000);
DATABASE RESOURCE MANAGER INFORMATION:
• DBA_RSRC_PLANS
• DBA_RSRC_PLAN_DIRECTIVES
• DBA_RSRC_CONSUMER_GROUPS
• DBA_RSRC_CONSUMER_GROUP_PRIVS
• DBA_USERS
• DBA_RSRC_MANAGER_SYSTEM_PRIVS
• V$SESSION
• V$RSRC_PLAN
• V$RSRC_CONSUMER_GROUP
No comments:
Post a Comment