ODA (Oracle Database Appliance): Howto Configuring and Monitoring Instance Caging

Asset ID:	1-71-1474290.1
Update Date:	2016-08-24
Keywords:

Solution Type Technical Instruction Sure

Solution 1474290.1 : ODA (Oracle Database Appliance): Howto Configuring and Monitoring Instance Caging

Applies to:

Oracle Database Appliance Software - Version 2.1.0.1 and later
Oracle Database Appliance - Version All Versions and later
Information in this document applies to any platform.

Goal

This article provides a step-by-step guide for configuring Instance Caging on ODA.

You can consolidate multiple databases using Oracle Database Appliance. 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 support the desired levels of service across multiple instances. Consolidation can minimize idle resources, maximize efficiency, and lower costs.

Oracle Database Appliance templates are designed by default to run on a specific number of cores. You can use instance caging and Oracle Database Appliance to deploy multiple databases on Oracle Database Appliance using Oracle Database Appliance templates that are pre-tuned for the size of each database instance workload. You can also select database template sizes that provide additional capacity for planned growth.

Note:

Oracle strongly recommends that you use the Oracle Database Appliance templates, because they implement best practices and are configured specifically for Oracle Database Appliance.
You can use either Oracle Appliance Manager or Database Configuration Assistant (DBCA) to create databases on Oracle Database Appliance. The templates are available with both utilities.

Solution

Determine Number of CPUs

The first step is to determine the number of CPUs on your ODA, using the following query. In this context, we need the number of CPU threads (not the number of cores).

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

i.e.: in this case the active cores are 12 for node, with the Hyper-Threading we have the NUM_CPUS=24

# oakcli show core_config_key
Optional core_config_key is not applied on this machine yet !

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

VALUE
----------
24

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, 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 an ODA with 16 CPU threads, 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.

Enabling Instance Caging for Oracle Database Appliance

By default, instance caging is not enabled on Oracle Database Appliance. To enable instance caging, enable Oracle Database Resource Manager by setting the RESOURCE_MANAGER_PLAN initialization parameter for each database on ODA, so that you allocate core resources among databases. The Resource Manager parameter specifies the top plan, identifying the plan to be used for the current instance. If no plan is specified with this parameter, then the Resource Manager is not enabled.

On Oracle Database Appliance, instance caging allocation of core resources is enabled in accordance with the Oracle Database Appliance database template size that you select for each database. The CPU_COUNT initialization parameter is set in the template. Use the CPU_COUNT setting that matches the size of each database to consolidate, and follow the standard instructions for configuring instance caging.

See Also:
Oracle Database Administrator's Guide for more information about enabling and configuring instance caging

To enable Instance Caging, set the cpu_count of each instance (or check the value of it, if you are using the ODA template) and then enable CPU Resource Manager.

show parameter cpu_count

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. If no rows are returned, Resource Manager is off or Resource Manager is on but not managing CPU. If a row is returned, it indicates the plan being used.

select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed = 'ON';
show parameter cpu_count;

i.e.:

SQL> select name from v$rsrc_plan where is_top_plan = 'TRUE' and cpu_managed = 'ON';

NAME
--------------------------------
DEFAULT_MAINTENANCE_PLAN

SQL> show parameter cpu_count;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 8

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:1362445.1> - Configuring and Monitoring Instance Caging

Attachments

This solution has no attachment