resource manager 80% cpu only

here the steps of configuring resource manager to only gives oracle 80% cpu only for all the user sessions belongs to GROUP_OLTP.

set serverout on size 5555
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
exec dbms_resource_manager.clear_pending_area();
exec dbms_resource_manager.create_pending_area();
exec dbms_resource_manager.DELETE_PLAN('PLAN_OLTP');
exec dbms_resource_manager.DELETE_CONSUMER_GROUP('GROUP_OLTP');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
begin
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_consumer_group(consumer_group=>'GROUP_OLTP',comment=>'oltp rsrc');
dbms_resource_manager.create_plan(plan=>'PLAN_OLTP',comment=>'batch plan');
dbms_resource_manager.create_plan_directive(plan=>'PLAN_OLTP',group_or_subplan=>'GROUP_OLTP',comment=>'plan dir night',cpu_p1=>80,parallel_degree_limit_p1=>16);
dbms_resource_manager.create_plan_directive(plan=>'PLAN_OLTP',group_or_subplan=>'OTHER_GROUPS',comment=>'plan dir night',cpu_p3=>80,parallel_degree_limit_p1=>8);
dbms_resource_manager.validate_pending_area();
dbms_resource_manager.submit_pending_area();
end;
/
commit
/
begin
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SCOTT',consumer_group=>'GROUP_OLTP',grant_option=>false);
dbms_resource_manager_privs.grant_switch_consumer_group(grantee_name=>'SH',consumer_group=>'GROUP_OLTP',grant_option=>false);
dbms_resource_manager.set_initial_consumer_group(user=>'SCOTT',consumer_group=>'GROUP_OLTP');
dbms_resource_manager.set_initial_consumer_group(user=>'SH',consumer_group=>'GROUP_OLTP');
end;
/
alter system set resource_manager_plan=PLAN_OLTP scope=both
/
alter session set resource_manager_plan=PLAN_OLTP scope=both
/

set lines 200
COL consumer_group      FORMAT A24  HEADING 'Consumer Group'
COL cpu_method          FORMAT A18  HEADING 'CPU Method'
COL status              FORMAT A10  HEADING 'Status'
COL mandatory           FORMAT A06  HEADING 'Manda-|tory?'
COL comments            FORMAT A32  HEADING 'Comments'
SELECT
     consumer_group
    ,cpu_method
    ,status
    ,mandatory
    ,comments
  FROM dba_rsrc_consumer_groups;

COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL num_plan_directives       FORMAT 9999 HEADING '# of|Plan|Dirs'
COL cpu_method                FORMAT A18  HEADING 'CPU Method'
COL active_sess_pool_mth      FORMAT A32  HEADING 'Active|Session|Pool|Method'
COL parallel_degree_limit_mth FORMAT A32  HEADING 'Parallel|Limit|Method'
COL queueing_mth              FORMAT A18  HEADING 'Queueing|Method'
COL status                    FORMAT A10  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A32  HEADING 'Comments'
SELECT
     plan
    ,num_plan_directives
    ,cpu_method
    ,active_sess_pool_mth
    ,parallel_degree_limit_mth
    ,queueing_mth
    ,status
    ,mandatory
  FROM dba_rsrc_plans
;

COL plan                      FORMAT A18  HEADING 'Resource Plan'
COL group_or_subplan          FORMAT A12  HEADING 'Group or|SubPlan'
COL type                      FORMAT A15  HEADING 'Type'
COL cpu_p1                    FORMAT 999  HEADING 'CPU|1 %'
COL cpu_p2                    FORMAT 999  HEADING 'CPU|2 %'
COL cpu_p3                    FORMAT 999  HEADING 'CPU|3 %'
COL status                    FORMAT A08  HEADING 'Status'
COL mandatory                 FORMAT A06  HEADING 'Manda-|tory?'
COL comments                  FORMAT A24  HEADING 'Comments'

SELECT
     plan
    ,group_or_subplan
    ,type
    ,cpu_p1
    ,cpu_p2
    ,cpu_p3
    ,status
  FROM dba_rsrc_plan_directives
 WHERE plan = 'PLAN_OLTP'
;

COL initial_rsrc_consumer_group  FORMAT A24  HEADING 'Resource|Consumer|Group'
COL username                     FORMAT A12  HEADING 'User Name'
SELECT
	 initial_rsrc_consumer_group,
    username
  FROM dba_users
 ORDER BY 1,2;

SELECT se.sid sess_id, co.name consumer_group,
 se.state, se.consumed_cpu_time cpu_time, se.cpu_wait_time, se.queued_time
 FROM v$rsrc_session_info se, v$rsrc_consumer_group co
 WHERE se.current_consumer_group_id = co.id;

select username,resource_CONSUMER_GROUP,count(*) from v$session group by username,resource_CONSUMER_GROUP;

Advertisement