Oracle 11g – Managing CPU Usage with Instance Caging

oracle-11g-r2

I am doing a little test of the cpu utilization case, got to know that i can use Instance Caging for the same to improve my resource utilization. Can somebody let me know how to do this? i m using oracle 11g.

Best Answer

Instance caging is a feature in the Enterprise Edition of Oracle Database 11g Release 2 (11.2) that simplifies the management of CPU usage in consolidation environments. By enabling Resource Manager and setting the CPU_COUNT parameter in each instance, you can limit the maximum amount of CPUs/Cores the instance can use. You have to notice three points for this.

1. Enabling Resource Manager
2. Setting CPU_COUNT
3. Monitoring Instance Caging

1. Enabling Resource Manager Resource Manager has been available in the Oracle database. Resource Manager is not enabled by default, so it must be enabled by specifying a resource plan before instance caging can take effect.

 SELECT plan FROM dba_rsrc_plans;

  PLAN
 ------------------------------
 DEFAULT_PLAN
 INTERNAL_QUIESCE
 INTERNAL_PLAN
 APPQOS_PLAN
 DEFAULT_MAINTENANCE_PLAN
 ETL_CRITICAL_PLAN
 MIXED_WORKLOAD_PLAN
 ORA$AUTOTASK_SUB_PLAN
 ORA$AUTOTASK_HIGH_SUB_PLAN
 DSS_PLAN

 10 rows selected.
 SQL>

If you have no specific resource management needs within the instance, the simplest solution is to use the default plan.

   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = default_plan;

2. Setting CPU_COUNT

ALTER SYSTEM SET cpu_count = 2;

3. Monitoring Instance Caging

The throttling effect of Resource Manager can be displayed using the CONSUMED_CPU_TIME and CPU_WAIT_TIME columns of the following views. The CONSUMED_CPU_TIME is the number of milliseconds of CPU time consumed by the consumer group, while the CPU_WAIT_TIME is the time waiting for CPU due to Resource Manager throttling.

SELECT name, consumed_cpu_time, cpu_wait_time
FROM v$rsrc_consumer_group;

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SET LINESIZE 100
SELECT begin_time,
   consumer_group_name,
   cpu_consumed_time,
   cpu_wait_time
FROM   v$rsrcmgrmetric_history
ORDER BY begin_time;
Related Question