Control CPU use for Oracle Instances

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Control CPU use for Oracle Instances                                                                          [2] HOL

[1] Control CPU use for Oracle Instances 

27.8 Managing Multiple Database Instances on a Single Server

Oracle Database provides a method for managing CPU allocations on a 
multi-CPU server running multiple database instances. 
This method is called instance caging. Instance caging and Oracle Database 
Resource Manager (the Resource Manager) work together 
to support desired levels of service across multiple instances.

27.8.1 About Instance Caging

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.

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.

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.

There are two typical approaches to instance caging for a server:

[1] Over-subscribing
You would use this approach for non-critical databases such as development 
and test systems, or low-load non-critical production systems.
In this approach, the sum of the CPU limits for each instance exceeds the 
actual number of CPUs on the system.

For example, on a 4-CPU system with four database instances, you might limit 
each instance to three CPUs. 
When a server is over-subscribed in this way, the instances can impact 
each other's performance. However, instance caging limits the impact and 
helps provide somewhat predictable performance. 
However, if one of the instances has a period of high load, the CPUs are 
available to handle it. 
This is a reasonable approach for non-critical systems, because one or 
more of the instances may frequently be idle or at a very low load.

[2] Partitioning
This approach is for critical production systems, where you want to prevent 
instances from interfering with each other.

You allocate CPUs such that the sum of all allocations is equal to the number 
of CPUs on the server. 
For example, on a 16-server system, you might allocate 8 CPUs to the first 
instance, 4 CPUs to the second, and 2 each to the remaining two instances.

[2] HOL

select value,stat_name from v$osstat where stat_name='NUM_CPUS';

VALUE STAT_NAME
---------- --------------------
1 NUM_CPUS

SQL> ALTER SYSTEM SET CPU_COUNT=1;

System altered.

SQL>

SQL> ALTER SYSTEM SET CPU_COUNT=2;
ALTER SYSTEM SET CPU_COUNT=2
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02097: parameter cannot be modified because specified value is invalid


SQL> !oerr ora 02097
02097, 00000, "parameter cannot be modified because specified value is invalid"
// *Cause: Though the initialization parameter is modifiable, the modified
// value is not acceptable to the parameter.
// *Action: Check the DBA guide for range of acceptable values for this
// parameter.

By dedicating CPU resources to each database instance, the load on one 
instance cannot affect another's, and each instance performs predictably.

--------------------------------------------
Using Instance Caging with Utilization Limit
--------------------------------------------
If you enable instance caging and set a utilization limit in your resource 
plan, then the absolute limit is computed as a percentage of the allocated 
CPU resources.

For example, if you enable instance caging and set the CPU_COUNT to 4, 
and a consumer group has a utilization limit of 50%, then the consumer 
group can use a maximum of 50% of 4 CPUs, which is 2 CPUs.

27.8.2 Enabling Instance Caging

You can enable instance caging using by creating a resource plan with 
CPU directives and setting the CPU_COUNT initialization parameter.

To enable instance caging, do the following for each instance on the server:

[1] Enable the Resource Manager by assigning a resource plan, and ensure 
that the resource plan has CPU directives, using the MGMT_P1 through 
MGMT_P8 parameters.

[2] Set the cpu_count initialization parameter.
This is a dynamic parameter, and can be set with the following statement:

ALTER SYSTEM SET CPU_COUNT = 4;


HOL
====
To set "Instance Caging" we must do two things:

1. Enable the Resource Manager by assigning a plan.

2. Define the CPU_COUNT parameter. This parameter is dynamic and can 
be changed with 
ALTER SYSTEM .. SCOPE = MEMORY.

Keep in mind that when we activated the Resource Manager, 
the CPU resources are divided roughly and propocional so that the load 
does not exceed the limit imposed by CPU_COUNT.

3. In the exercise of "Administer Resource Manager" already defined several 
Resource Plan. In fact, we have all the time slot 24h Indoor or by the 
DAYLIGHT_PLAN or NIGHT_PLAN plan. So you only need to modify the parameter 
CPU_COUNT for our test.

- Validate that we have active any of the Resource Plan
- Depending on the time we have active DAYLIGHT_PLAN or NIGHT_PLAN
SHOW PARAMETER RESOURCE_MANAGER_PLAN

- Reviewed the current value of CPU_COUNT
SHOW PARAMETER CPU_COUNT

- Modify the limit
- In my case, I have increased my number of CPU virtual machine 4, 
which appears CPU_COUNT = 4
ALTER SYSTEM SET CPU_COUNT = 2 SCOPE = MEMORY;

4. We can see that the CPU is limited by the following test. 
We will launch three sessions 
with the siguiento code for several minutes.

- Run the following code to force the CPU usage on the machine
- First we create a table for testing
CREATE TABLE KILL_CPU (N PRIMARY KEY)
ORGANIZATION INDEX AS 
SELECT ROWNUM FROM ALL_OBJECTS WHERE ROWNUM <= 50; 

We executed in parallel in three sessions for one minute 
ALTER SESSION SET "_old_connect_by_enabled" = true; 

SELECT COUNT (*) FROM KILL_CPU X CONNECT BY N > PRIOR N 
PRIOR START WITH N = 1;

- We canceled the execution of the three sessions CTRL-C

We can see that the CPU usage is limited OEM if we go to the Performance tab. 
The green area (CPU ) does not exceed two units and we put the limit of 2 CPUs. 
We also see light green ( Scheduler) an event that represents the Resource Manager 
CPU limiting machine ( resmgr : cpu quantum)

- We can also see how long the CPU has been limited in view V$RSRC_CONSUMER_GROUP 
(CPU_WAIT_TIME column)
SELECT NAME , CONSUMED_CPU_TIME , CPU_WAIT_TIME
FROM V$RSRC_CONSUMER_GROUP;

SELECT BEGIN_TIME,CONSUMER_GROUP_NAME,CPU_CONSUMED_TIME,CPU_WAIT_TIME
FROM V$RSRCMGRMETRIC_HISTORY
ORDER BY BEGIN_TIME; 

- Leaving the limit we had before
ALTER SYSTEM SET CPU_COUNT= 4 SCOPE = MEMORY;

======================
Path to Documentation:
======================
Database Administrator’s Guide
27 Managing Resources with Oracle Database Resource Manager
27.8 Managing Multiple Database Instances on a Single Server
27.8.1 About Instance Caging

Thank you for visiting this blog 🙂