otak-otak | favorite evening food

May 9, 2011

resource manager 80% cpu only

Filed under: troubleshoot — ora62 @ 7:53 am

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;
Advertisements

streams tuning on high batch jobs

Filed under: streams — ora62 @ 7:19 am
this apply parameter in streams environment is intended for the machine that has many cpus. 
the parallelis and commit_serialization will help when there are many insert, 
the streams will extremely fast enough to handle huge transaction.
but if your transaction is mandatory by update or delete, commit_serialization will depends on the scn order. 
so there are no impact to your business (but it should need careful test)

BEGIN
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => 'TXN_LCR_SPILL_THRESHOLD',
   value => '15000000');

DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_TXN_BUFFER_SIZE',
   value => '64');
 DBMS_APPLY_ADM.SET_PARAMETER (
   apply_name => 'A_NONAME',
   parameter => '_hash_table_size',
   value => '10000000');
dbms_apply_adm.set_parameter('A_NONAME','_SGA_SIZE','8192');
dbms_apply_adm.set_parameter('A_NONAME', '_RESTRICT_ALL_REF_CONS','N');
dbms_apply_adm.set_parameter('A_NONAME', 'COMMIT_SERIALIZATION','NONE');
dbms_apply_adm.set_parameter('A_NONAME', 'PARALLELISM','64');

END;
/

exec dbms_apply_adm.stop_apply('A_NONAME');

exec dbms_apply_adm.start_apply('A_NONAME');

Blog at WordPress.com.