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 🙂
ORACLE-LEARN 