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;
Leave a comment