Resource Manager and Instance Caging

Brief: Excessive CPU load can destabilize the server and expose operating system bugs and can also prevent critical Oracle background processes from running in a timely manner,  resulting in failures such as database instance evictions on a RAC database.

Using Oracle Database Resource Manager, you can ensure that your database’s CPU  load is always healthy, thus avoiding all of these problems.

How it works?
At any time, many processes may be ready to run. However, the operating system can only run one process per CPU. Resource Manager manages CPU usage by controlling the database load to a very precise level.

By default, this level is set to the number of CPUs. That means that on a 4 CPU server, Resource Manager ensures that no more than 4 Oracle processes (more specifically, foregrounds and non-critical, CPU-intensive backgrounds) are running at a time.

Resource Manager allows one Oracle process per CPU to run at a given time. All other processes wait on an internal Resource Manager run queue, under the wait event "resmgr:cpu quantum". Resource Manager
allows an Oracle process to run for a small quantum of time (100 milliseconds). At the end of

this quantum or when the Oracle process starts a wait (e.g. for a lock or I/O), Resource Manager

selects a new Oracle process to run. Because Resource Manager uses a round-robin algorithm to
choose between all runnable processes, they all make forward progress.
Configuring Resource Manager
Configuring Resource Manager to manage the database load requires just one step

SQL> alter system set resource_manager_plan = ‘DEFAULT_PLAN‘ sid = ‘*‘;

By enabling Resource Manager with the out-of-box "DEFAULT_PLAN", you will get the
following benefits:

• Critical background processes, such as PMON and LMS, won’t be starved for CPU, due to excessive load from foreground processes.
• SYSTEM and SYS are scheduled at the highest priority. Their response time will not be affected by runaway CPU activity from normal users, allowing them to always be able to login and debug database problems.
• Automated maintenance tasks are scheduled at the lowest priority . These tasks include gathering optimizer statistics and running the automatic segment and sql tuning advisors. These tasks will therefore not compete with other sessions for CPU. However, when the database workload is low, these maintenance tasks will be scheduled to consume any remaining, otherwise unused CPU resources.

SQL> show parameter resource

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     3
resource_manager_plan                string
SQL> alter system set resource_manager_plan ='DEFAULT_PLAN' sid = '*';
System altered.
SQL> show parameter resource
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_limit                       boolean     FALSE
resource_manager_cpu_allocation      integer     3
resource_manager_plan                string      DEFAULT_PLAN
SQL>

Instance Caging cases

Resource Manager manages the database load so that all CPUs are utilized. In some circumstances, you might want a database instance to use less than 100% of the server’s CPU: Other applications or database instances are sharing the server. You want the database instance to use only a portion of the server’s CPUs. 

Your consolidated server is hosting multiple database instances. You want to limit a database instance’s CPU usage so the customer sees more consistent response times. Or you want to limit its CPU usage based on how much the customer paid. 

Your server is more stable at lower CPU utilization levels. You want the database instance’s load to stay at these lower levels. 

In these cases, you can lower the database load by setting the cpu_count parameter to the number of CPUs you want Resource Manager to use. For example, on an 8 CPU server, if you want Resource Manager to restrict the database load to no more than 6 active Oracle processes (and hence use no more than 6 CPUs), you would enable Resource Manager and set the cpu_count parameter to 6. 

SQL> alter system set cpu_count=6;