--Giới hạn Active session, parallel
/***** 1.CẤU HÌNH *****/
-- 1.1.Plan
begin
dbms_resource_manager.create_pending_area();
end;
begin
dbms_resource_manager.create_plan(
plan => 'USER1_PLAN',
comment => 'USER1_PLAN');
end;
/
--USER1_PLAN PENDING
select * from DBA_RSRC_PLANS
where plan in ('USER1_PLAN')
order by plan;
-- 1.2.Consumer groups
begin
dbms_resource_manager.create_consumer_group(
consumer_group => 'USER_GROUP',
comment => 'USER_GROUP');
end;
/
--USER_GROUP PENDING
select * from DBA_RSRC_CONSUMER_GROUPS
where consumer_group in ('USER_GROUP','OTHER_GROUPS')
order by consumer_group;
-- 1.3.Plan Directives
begin
dbms_resource_manager.create_plan_directive(
plan => 'USER1_PLAN'
,group_or_subplan => 'USER_GROUP'
,comment => 'Dieu tiet user ca nhan'
,active_sess_pool_p1 => 20
,queueing_p1 => 10
,parallel_degree_limit_p1 => 8);
end;
/
--ORA-29382: validation of pending area failed
--ORA-29377: consumer group OTHER_GROUPS is not part of top-plan USER1_PLAN
--ORA-06512: at "SYS.DBMS_RMIN", line 444
--ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 815
--ORA-06512: at line 2
begin
dbms_resource_manager.validate_pending_area();
end;
/
--Basically for each plan we need to define the plan directives for the OTHER_GROUPS also, what that means is determine limits for all other sessions other than those bound by the consumer group ETL_GROUP.
-- Fixed
begin
dbms_resource_manager.create_plan_directive ( plan => 'USER1_PLAN',
group_or_subplan =>'OTHER_GROUPS',
comment => 'Limit CPU resource',
parallel_degree_limit_p1 => 2);
end;
exec dbms_resource_manager.validate_pending_area();
--KIỂM TRA LẠI TRƯỚC KHI SUBMIT
--USER1_PLAN EMPHASIS PENDING
select plan, cpu_method, status from dba_rsrc_plans order by 1;
--USER_GROUP ROUND-ROBIN PENDING
select consumer_group,cpu_method, status from dba_rsrc_consumer_groups
order by 1;
--USER1_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 2
--USER1_PLAN USER_GROUP CONSUMER_GROUP 0 0 0 0 20 10 2
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, active_sess_pool_p1, queueing_p1,parallel_degree_limit_p1 "P_LIMIT", status
from dba_rsrc_plan_directives
where plan in ('USER1_PLAN')
order by 1,2,3,4,5,6;
begin
dbms_resource_manager.submit_pending_area(); --changed from PENDING to ACTIVE.
end;
----Rollback
--begin
-- dbms_resource_manager.clear_pending_area();
--end;
--/
/*****1.4.Grant switch privilege for resource consumer groups to users or roles *****/
begin
dbms_resource_manager.create_pending_area();
end;
begin
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'user2',
consumer_group => 'USER_GROUP',
grant_option => FALSE);
end;
/
exec dbms_resource_manager.set_initial_consumer_group(user => 'user2',consumer_group =>'USER_GROUP');
exec dbms_resource_manager.submit_pending_area();
--Now that we have implemented a Resource Manager strategy, we want to activate the day_plan immediately as our default plan.
alter system set resource_manager_plan='USER1_PLAN';
--Verifies if the day_plan is turned on:
--754948 USER1_PLAN TRUE OFF OFF 0 256 FIFO
--(Default INTERNAL_PLAN)
select * from v$rsrc_plan;
--And also, we must assign our default plan for the database at next startup by adding the following parameter to initSID.ora
--resource_manager_plan = 'USER1_PLAN'
--Rollback:
alter system set resource_manager_plan='';
select * from v$rsrc_plan;
/***** 2.MODIFY *****/
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
exec dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'USER_GROUP', new_comment => 'USER_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 2);
exec dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'OTHER_GROUPS', new_comment => 'USER_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 2);
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
--begin
-- dbms_resource_manager.create_plan_directive(
-- plan => 'USER1_PLAN'
-- ,group_or_subplan => 'USER_GROUP'
-- ,comment => 'Dieu tiet user CTKV, ca nhan'
-- ,active_sess_pool_p1 => 20
-- ,queueing_p1 => 10
-- ,parallel_degree_limit_p1 => 8);
--end;
--/
/*****3.MONITORING *****/
-- Plan
--754948 USER1_PLAN TRUE OFF OFF 0 256 FIFO
select * from v$rsrc_plan where is_top_plan = 'TRUE';
-- Plan Directive
--USER_GROUP 0 0 0 0 0 0 0 0
--OTHER_GROUPS 0 0 0 0 0 0 0 0
select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, cpu_p5, cpu_p6, cpu_p7, cpu_p8, active_sess_pool_p1, queueing_p1,parallel_degree_limit_p1 "P_LIMIT", status
from dba_rsrc_plan_directives where plan = (select name from v$rsrc_plan where is_top_plan = 'TRUE');
select * from DBA_RSRC_CONSUMER_GROUPS
where consumer_group in ('OTHER_GROUPS','USER_GROUP');
select * from DBA_RSRC_CONSUMER_GROUP_PRIVS order by grantee;
--select * from DBA_RSRC_GROUP_MAPPINGS;
--+Monitor CPU Usage and Waits by Consumer Group
select to_char(m.begin_time, 'HH:MI') time, m.consumer_group_name, m.cpu_consumed_time / 60000 avg_running_sessions, m.cpu_wait_time / 60000 avg_waiting_sessions, d.mgmt_p1*(select value from v$parameter where name = 'cpu_count')/100 allocation
from v$rsrcmgrmetric_history m, dba_rsrc_plan_directives d, v$rsrc_plan p where m.consumer_group_name = d.group_or_subplan and p.name = d.plan
order by m.begin_time desc, m.consumer_group_name ;
/**** 4.TEST ****/
-- Tao user test
create user test1 identified by test1123;
grant connect, resource, select any table to test1;
create user test2 identified by test2123;
grant connect, resource, select any table to test2;
-- Kiem tra Consumer Group cua user test1, test2, truong INITIAL_RSRC_CONSUMER_GROUP:DEFAULT_CONSUMER_GROUP
select * from dba_users
where username in ('TEST1','TEST2');
--Gan vao group USER_GROUP
begin
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'TEST1',consumer_group => 'USER_GROUP',grant_option => FALSE);
dbms_resource_manager.set_initial_consumer_group(user => 'TEST1',consumer_group =>'USER_GROUP');
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name => 'TEST2',consumer_group => 'USER_GROUP',grant_option => FALSE);
dbms_resource_manager.set_initial_consumer_group(user => 'TEST2',consumer_group =>'USER_GROUP');
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
end;
-- Kiem tra Consumer Group cua user test1, test2, truong INITIAL_RSRC_CONSUMER_GROUP:USER_GROUP
select * from dba_users
where username in ('TEST1','TEST2');
-- 4.1.Test thu parallel voi test1
--+ Login vao test1 bang SQL Navigator
select /*+ parallel(t,8) */ count(*) from IN_DATA.OG_ICC_HAN t;
--+ Login vao test2 bang SQL Navigator
select /*+ parallel(t,8) */ count(*) from IN_DATA.OG_ICC_HAN_CALL t;
--+ Montioring:
--++ Ket qua test1, test2 được chuyển từ Parallel 8 thành parallel 2
SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
--and s.username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
AND username in ('TEST1','TEST2')
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;
-- Thay đổi parallel từ 2 thành 4\
begin
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'USER_GROUP', new_comment => 'USER_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 4);
dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'OTHER_GROUPS', new_comment => 'USER_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 4);
dbms_resource_manager.validate_pending_area;
dbms_resource_manager.submit_pending_area;
end;
--+ Check lai
select * from DBA_RSRC_PLAN_DIRECTIVES;
-- Chạy lại câu lệnh select trên sẽ ăn theo 4 parallel mỗi user
SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
--and s.username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
AND username in ('TEST1','TEST2')
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY username,sql_id;
--4.2.Test active session
-- Thay doi Active_sess_pool_p1 tu 20 thanh 2 va queueing_p1 tu 10 thanh 1
-- Cau hinh ban dau
-- plan => 'USER1_PLAN'
-- ,group_or_subplan => 'USER_GROUP'
-- ,comment => 'Dieu tiet user CTKV, ca nhan'
-- ,active_sess_pool_p1 => 20
-- ,queueing_p1 => 10
-- ,parallel_degree_limit_p1 => 8
begin
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'USER_GROUP', new_comment => 'USER_GROUP Change the DOP to 4, active_sess_pool_p1 to 2', new_active_sess_pool_p1=> 2, new_queueing_p1=> 1);
--dbms_resource_manager.UPDATE_PLAN_DIRECTIVE( plan => 'USER1_PLAN', group_or_subplan => 'OTHER_GROUPS', new_comment => 'USER_GROUP Change the DOP to 8', new_parallel_degree_limit_p1 => 4);
dbms_resource_manager.validate_pending_area;
dbms_resource_manager.submit_pending_area;
end;
--+ Check lai dam bao Active_sess_pool_p1 la 2, queueing_p1 la 1
select * from DBA_RSRC_PLAN_DIRECTIVES where group_or_subplan in ('USER_GROUP','OTHER_GROUPS');
-- Chạy lại câu lệnh select trên sẽ ăn theo 4 parallel va co 3 tien trinh Active Session (P1TEXT-sleeptime.senerid la chính)
-- Active session cuối cùng bị reject khỏi hệ thống --ORA-07454: queue timeout, 1 second(s), exceeded
SELECT distinct s.inst_id i#, s.username, s.SID SID, s.osuser, s.machine,DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') ACTION,
s.sql_id, SUBSTR(DECODE(SS.SQL_TEXT, NULL, AA.NAME, SS.SQL_TEXT), 1, 1000) SQLTEXT,s.logon_time,s.p1text, S.P1, s.p2text, S.P2, s.p3text, S.P3
FROM GV$SESSION S, GV$SQLSTATS SS, AUDIT_ACTIONS AA
WHERE S.STATUS = 'ACTIVE' AND S.SQL_ID = SS.SQL_ID (+) AND AA.ACTION = S.COMMAND and s.type != 'BACKGROUND' AND S.TYPE = 'USER'
--and s.username NOT in ('SYS','SYSMAN','DBSNMP','GGATE','GOLDENGATE')
AND username in ('TEST1','TEST2')
--and DECODE(S.WAIT_TIME, 0, S.EVENT, 'CPU') like '%cell single block physical read%'
--and lower(ss.sql_text) like lower('%parallel%')
--and s.sid=4588
--and s.machine like '%BINHTV%'
--and s.sql_id ='ccwg0nqr1zbu7'
ORDER BY logon_time;
/***** 5.CLEAN UP *****/
--In order to delete the current plan, it has to be `switched off', or another plan has to be selected as current.
alter system set resource_manager_plan='';
begin
dbms_resource_manager.create_pending_area();
end;
/
-- Plan
select * from DBA_RSRC_PLANS order by plan;
begin
dbms_resource_manager.delete_plan(
plan => 'USER1_PLAN');
end;
/
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
select * from DBA_RSRC_PLANS order by plan;
-- Plan Directive
select * from DBA_RSRC_PLAN_DIRECTIVES order by plan;
EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>'USER1_PLAN',group_or_subplan=>'USER_GROUP');
EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE (plan=>'USER1_PLAN',group_or_subplan=>'OTHERS_GROUP');
EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
select * from DBA_RSRC_PLAN_DIRECTIVES order by plan;
--CONSUMBER_GROUP
select * from DBA_RSRC_CONSUMER_GROUPS order by consumer_group;
begin
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA;
dbms_resource_manager.delete_consumer_group(consumer_group => 'USER_GROUP');
--dbms_resource_manager.delete_consumer_group(consumer_group => 'BATCH_GROUP');
dbms_resource_manager.delete_consumer_group(consumer_group => 'OTHERS_GROUP');
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.clear_pending_area();
end;
/
select * from DBA_RSRC_CONSUMER_GROUPS order by consumer_group;
-- User
select * from dba_users
order by initial_rsrc_consumer_group;
