Determine "cpu_count" for All Instances
The next step is to determine how the database instances on your server will share the CPU. With Instance Caging, each instance's cpu_count specifies the maximum number of CPUs you want it to use at any time. The sum of the cpu_counts across all database instances determines the amount of isolation between the database instances and the efficiency of the server.
For maximum isolation between the database instances, use the "partition" approach. With the partition approach, the sum of the cpu_counts is less than or equal to the number of CPUs, as determined in step 1. With hyper-threaded or CMT processors, you can achieve even more resource isolation if the sum of the cpu_counts is less than or equal to 75% of the number of CPUs. The partition approach is suitable for critical production databases that need very predictable performance.
For example, suppose the total number of CPUs (i.e. CPU threads) is 16. Using the partition approach, we could set cpu_count=8 for database A, cpu_count=4 for database B, and cpu_count=4 for database C. The sum of the cpu_counts is 16, which equals the number of CPUs.
The disadvantage of the partition approach is that any CPU unused by one database instance cannot be used by another. Therefore, for non-critical databases where you also want to achieve better CPU utilization efficiency, use the "over-subscribe" approach. With the over-subscribe approach, the sum of the cpu_counts is less than or equal to 3x the number of CPUs, as determined in step 1.
For example, for a server with 16 CPUs, you could use the over-subscribe approach and set cpu_count=8 for database A, cpu_count=8 for database B, and cpu_count=8 for database C. The sum of the cpu_counts is 24, which is greater than the number of CPUs. Therefore, if all databases are using their full CPU allocation, there will be some CPU contention.
An acceptable over-subscription factor is based on the expected total load of all databases. For example, if every database is expected to always use its full CPU allocation, the server should be partitioned. If every database is expected to run at 50% of its full CPU allocation, then the server can be over-subscribed by a factor of 2. For many over-subscribed deployments, an over-subscription factor of 3 seems to be a reasonable starting point.
Enable Instance Caging
To enable Instance Caging, set the cpu_count of each instance and then enable CPU Resource Manager.
alter system set cpu_count = 4;
alter system set resource_manager_plan = 'default_plan';
Monitor Instance Caging
To verify that Instance Caging is enabled, check that "instance_caging" equals "ON" and that "cpu_count" is set appropriately.
select instance_caging from v$rsrc_plan where is_top_plan = 'TRUE';
show parameter cpu_count;
To monitor Instance Caging on an instance, monitor the average number of running and waiting sessions.
select to_char(begin_time, 'HH24:MI') time, sum(avg_running_sessions) avg_running_sessions, sum(avg_waiting_sessions) avg_waiting_sessions from v$rsrcmgrmetric_history group by begin_time order by begin_time;
"avg_running_sessions" is the average number of running sessions for this minute. If avg_running_sessions is much smaller than cpu_count, the instance is not fully utilizing its cpu_count allocation. cpu_count could be decreased without affecting performance.
"avg_waiting_sessions" is the average number of sessions waiting to be scheduled for this minute. If avg_waiting_sessions is consistently bigger than 0, the performance of the instance could be improved by increasing cpu_count by this amount.
Tuning Instance Caging
You can dynamically tune Instance Caging by adjusting the value of cpu_count. Changes will take effect within seconds.
We do not recommend that you change cpu_count too frequently, since changing its value has some overhead. We also don't recommend that you set it to 1 or change the value from a very small number to an extremely large value.
REFERENCES
NOTE:1340172.1 - Recommended Patches for Instance Caging
NOTE:1484302.1 - Master Note: Overview of Oracle Resource Manager and DBMS_RESOURCE_MANAGER
NOTE:1339769.1 - Master Note for Oracle Database Resource Manager
|
