Tuesday, November 16, 2010

Instance Caging in Oracle database

About Instance Caging

You might decide to run multiple Oracle database instances on a single multi-CPU server. A typical reason to do so would be server consolidation—using available hardware resources more efficiently. When running multiple instances on a single server, the instances compete for CPU. One resource-intensive database instance could significantly degrade the performance of the other instances. For example, on a 16-CPU system with four database instances, the operating system might be running one database instance on the majority of the CPUs during a period of heavy load for that instance. This could degrade performance in the other three instances. CPU allocation decisions such as this are made solely by the operating system; the user generally has no control over them.

A simple way to limit CPU consumption for each database instance is to use instance caging. Instance caging is a method that uses an initialization parameter to limit the number of CPUs that an instance can use simultaneously. In the previous example, if you use instance caging to limit the number of CPUs to four for each of the four instances, there is less likelihood that one instance can interfere with the others. When constrained to four CPUs, an instance might become CPU-bound. This is when the Resource Manager begins to do its work to allocate CPU among the various database sessions according to the resource plan that you set for the instance. Thus, instance caging and the Resource Manager together provide a simple, effective way to manage multiple instances on a single server.

Examples:



Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn donghua/donghua
Connected.
SQL> set echo on
SQL> @resource_plan.sql
SQL> BEGIN
2 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
3
4 DBMS_RESOURCE_MANAGER.CREATE_PLAN(
5 PLAN => 'MAXCAP_PLAN',
6 COMMENT => 'Limit overall database CPU');
7
8 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
9 PLAN => 'MAXCAP_PLAN',
10 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
11 COMMENT => 'This group is mandatory',
12 MAX_UTILIZATION_LIMIT => 90);
13
14 DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
15 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
16 END;
17 /

PL/SQL procedure successfully completed.

SQL> alter system set resource_manager_plan='';

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
INTERNAL_PLAN OFF OFF

SQL> alter system set resource_manager_plan=MAXCAP_PLAN;

System altered.

SQL> alter system set cpu_count=2;

System altered.

SQL> select name,cpu_managed,instance_caging from v$rsrc_plan;

NAME CPU INS
-------------------------------- --- ---
MAXCAP_PLAN ON ON