Below are the steps to setup the DRM ( Database
Resource Manager)....
here we are creating a basic CPU based Plan....
SQL> show parameter resource;
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
resource_limit
boolean
FALSE
resource_manager_cpu_allocation
integer 8
resource_manager_plan
string
<<<< No Plan exists
step 1:- create a plan that you want to
name...
begin
dbms_resource_manager.create_plan(
plan => 'CPU_LIMIT_TEST_PLAN',
comment => 'Resource plan/method for
Single level sample');
end;
/
step 2:- create a group to which the
particular users will be assigned...
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'ASSIGNED_GROUP',
comment => 'Resource consumer
group/method for online users sessions');
end;
/
step 3:- In this step we will be creating sub plan
or assigning cpu resources to the group that we created in step 2 along with
the other users( which fall in OTHER groups)... here we are alloting 20% of CPU
resources to the ASSIGNED_GROUP and 80% to OTHER_GROUPS in the database.
begin
dbms_resource_manager.create_plan_directive(
plan => 'CPU_LIMIT_TEST_PLAN',
group_or_subplan =>
'ASSIGNED_GROUP',
comment => 'Online day users
sessions at level 1',
cpu_p1 => 20,
parallel_degree_limit_p1 => 0);
dbms_resource_manager.create_plan_directive(
plan => 'CPU_LIMIT_TEST_PLAN',
group_or_subplan =>
'OTHER_GROUPS',
comment => 'OTHER_GROUPS day
users sessions at level 1',
cpu_p1 => 80,
parallel_degree_limit_p1 => 0);
end;
/
Step 5:- Validating the Plan
begin
dbms_resource_manager.validate_pending_area();
end;
/
step 6:- submitting the plan
begin
dbms_resource_manager.submit_pending_area();
end;
/
once the Setup is completed...... we need to set
this plan in the Database..
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN =
'CPU_LIMIT_TEST_PLAN';
SQL> show parameter resource;
NAME
TYPE VALUE
------------------------------------ -----------
------------------------------
resource_limit
boolean FALSE
resource_manager_cpu_allocation
integer 8
resource_manager_plan
string CPU_LIMIT_TEST_PLAN
once this is completed. we need to assign the
users to the required plans that we have created....
here we are assiging REPORT_USER to the
ASSIGNED_GROUP in order to limit the CPU resources to this group.
step 7:- create a pending area where the changes
are first made with out effecting the original or existing plan.
begin
dbms_resource_manager.create_pending_area();
end;
/
step 8:-
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'REPORT_USER',
consumer_group =>
'ASSIGNED_GROUP',
grant_option => FALSE);
end;
/
step 9:- Validating the Plan
begin
dbms_resource_manager.validate_pending_area();
end;
/
step 10:- once this is done we will again submit
the plan.
begin
dbms_resource_manager.submit_pending_area();
end;
/
This completes the DRM setup for your
environment...
TO Delete/alter the existing plan and create a
new one ...follow these steps:-
step 1:- create pending area
begin
dbms_resource_manager.create_pending_area();
end;
/
step 2:-
SQL> EXEC
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>'CPU_LIMIT_TEST_PLAN',group_or_subplan=>'ASSIGNED_GROUP');
PL/SQL procedure successfully completed.
and follow steps above mentioned step up
steps......
TO Delete DRM completely from the database:-
step 1:-
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
step 2:- create pending area
begin
dbms_resource_manager.create_pending_area();
end;
/
step 3:-
step 9:- Validating the Plan
begin
dbms_resource_manager.validate_pending_area();
end;
/
step 10:-
begin
DBMS_RESOURCE_MANAGER.DELETE_PLAN('CPU_LIMIT_TEST_PLAN'
end;
/
step 11:- once this is done we will again submit
the plan.
begin
dbms_resource_manager.submit_pending_area();
end;
/
--- Clearing any Pending Area….
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/
No comments:
Post a Comment