instance caging – a sample

Instance caging was introduced in 11.2 and desinged to limit the cpu-usage of an instance.

This makes sence when you run several instances on one machine.

In the past, while one instance makes trouble in cpu usage, all others instance got problems as well 😦

Instance caging based on the oracle resource manager.

It makes no sense to throttle a instance permanent down – the system should be well balanced and this feature should be an emergency button.

here’s an example:

-- generate resource plan "caged"
--
begin
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  dbms_resource_manager.create_plan( plan => 'caged', comment => 'instance caging to limit cpu' );
  dbms_resource_manager.create_plan_directive(PLAN => 'caged', GROUP_OR_SUBPLAN => 'SYS_GROUP',COMMENT => '',CPU_P1=> 50);
  dbms_resource_manager.create_plan_directive(PLAN => 'caged', GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT => '' ,CPU_P1=> 0,CPU_P2=>90, ACTIVE_SESS_POOL_P1 => 1);
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/

This plan will give you as an sys_group participant (sys or system) the chance to run queries or kill the crazy sessions.

The “normal” sessions run in other_groups (except you added your application owner to the sys_group).

-- show number of cpus
select stat_name, value from v$osstat where stat_name  like '%CPU%';

show parameters cpu

show parameters resource_manager_plan

alter system set cpu_count = 2;

alter system set resource_manager_plan = 'caged';

show parameters resource_manager_plan

select instance_caging from v$rsrc_plan where is_top_plan = 'TRUE'; 

set lines 180
select * from v$rsrc_plan where is_top_plan = 'TRUE'; 

-- monitor
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, m.consumer_group_name;

COL name                      FORMAT A12     HEADING 'Resource|Consumer|Group'
COL active_sessions           FORMAT 9999    HEADING 'Act|Sess'
COL execution_waiters         FORMAT 9999    HEADING 'Exec|Wtrs'
COL requests                  FORMAT 9999    HEADING 'Reqs'
COL cpu_wait_time             FORMAT 9999999 HEADING 'CPU|Wait|Time'
COL cpu_waits                 FORMAT 9999999 HEADING 'CPU|Waits'
COL consumed_cpu_time         FORMAT 9999999 HEADING 'CPU|Time|Used'
COL yields                    FORMAT 9999    HEADING 'Ylds'
COL queue_length              FORMAT 99999   HEADING 'Queue|Len'
COL current_undo_consumption  FORMAT 99999   HEADING 'Curr|UNDO|Used'

SELECT name , active_sessions ,execution_waiters, requests, cpu_wait_time,
       cpu_waits, consumed_cpu_time, yields, queue_length,
       current_undo_consumption
  FROM v$rsrc_consumer_group;

The waitevent “resmgr:cpu quantum” is the indicator that instance caging is used and throtteling down

 
-- show active plan some details
set lines 120
select group_or_subplan, mgmt_p1, mgmt_p2, mgmt_p3, max_utilization_limit ,
       ACTIVE_SESS_POOL_P1
from dba_rsrc_plan_directives
 where plan = (select name from v$rsrc_plan where is_top_plan = 'TRUE');

-- disable instance caging
alter system set resource_manager_plan = '';

-- change plan
begin
  DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  dbms_resource_manager.UPDATE_PLAN_DIRECTIVE (plan => 'caged', GROUP_OR_SUBPLAN => 'OTHER_GROUPS', new_cpu_p2 => 90);
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
end;
/

-- drop resource plan
EXECUTE dbms_resource_manager.delete_plan('caged');

see also oracle documentation

Oracle® Database Administrator’s Guide 11g Release 2 (11.2)