Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Oracle instance cage analysis". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Oracle instance cage (Instance Caging)

When multiple instances are running on the same server, in order to avoid the interaction between instances, the concept of instance cage is introduced from oracle 11gr2. Instance cages can limit the CPU resources used by database instances. To use an instance cage, you only need to set two parameters, CPU_COUT and resource_manager_plan. This function can be used for database resource integration, replacing previous traditional resource segmentation methods such as virtualization and partitioning

1. Open swingbench to prepare the settings for stress test (see previous article for specific methods)

2. Check the number of CPU on the server

Select value from v$osstat where stat_name = 'NUM_CPUS'

3. To enable Instance Caging, just set two parameters.

Alter system set cpu_count = 4

Alter system set resource_manager_plan = 'default_plan'

Note: this place is very strange, the first time to use the error ORA-00450, after a period of time, the setting was successful

4, the verification function has been enabled

SQL > select instance_caging from v$rsrc_plan where is_top_plan = 'TRUE'

INS

-

ON

SQL > show parameter cpu_count

NAME TYPE VALUE

-

Cpu_count integer 4

5. Check the usage of the feature

SQL > 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

TIME AVG_RUNNING_SESSIONS AVG_WAITING_SESSIONS

14:48. 82905. 000083333

14:49. 536. 40295

14:50. 334233333. 060016667

17:30 8.53193333 4.39328333

17:31 15.85885. 0001

17:32 9.46965 22.3486667

Avg_running_sessions is the number of active sessions in a minute. If the number of times is much less than CPU_COUNT, this instance is far from reaching the limit. If the AVG_WAITING_SESSIONS is large, the system has basically reached the maximum limit.

6. You can dynamically adjust the CPU_COUNT to adjust the resources used by the instance. Here are the test results

A, set cpu_count to 32, that is, no limit is set.

SQL > alter system set cpu_count = 32

Starting the stress test, the TPMC of the PC server reaches 450000 TPMC,CPU utilization about 75%.

09:44:17 all 69.73 0.00 5.65 2.83 0.00 21.79

09:44:27 all 71.52 0.00 5.81 2.69 0.00 19.99

09:44:37 all 61.98 0.00 5.12 2.91 0.00 29.99

09:44:47 all 69.76 0.00 5.66 3.58 0.00 21.00

B, set the instance cage function, that is, limit CPU_cout to 16, and there are a large number of resmgr:cpu quantum waiting events in the database (this is related to resource management). At this time, the system utilization is about 65%, but the% user is about 50%, that is, 16 cpu.TPMC is 200000. Capacity is limited

SQL > alter system set cpu_count=16

09:49:28 CPU user nice system iowait steal idle

09:49:38 all 53.91 0.00 8.78 1.81 0.00 35.50

09:49:48 all 52.15 0.00 8.66 2.88 0.00 36.31

09:49:58 all 53.91 0.00 8.37 1.85 0.00 35.87

09:50:08 all 50.98 0.00 8.76 2.66 0.00 37.60

09:50:18 all 53.24 0.00 8.42 1.91 0.00 36.43

C, cpu_count=8;%User is 27%, basically keep at 8 CPU quantity, TPMC 100000 or so

09:57:38 CPU user nice system iowait steal idle

09:57:48 all 27.96 0.00 4.99 3.01 0.00 64.03

09:57:58 all 27.82 0.00 4.47 2.49 0.00 65.21

09:58:08 all 27.97 0.00 4.54 2.31 0.00 65.18

09:58:18 all 27.90 0.00 4.50 2.25 0.00 65.34

D, check the dynamic view avg_running_sessions and cpu_count are basically the same, indicating that the maximum has been reached

SQL > 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

09:44 18.4489333. 017666667

09:45 14.9326833 34.1877333

09:46 14.5135167 44.6346167

09:47 13.7069167 41.3688333

09:48 14.3363833 43.9001667

09:49 14.3411 43.345

09:50 14.2703333 43.2445

09:51 8.04406667 58.9471667

09:52 1.86445 15.7961833

09:53 7.1256 62.3546667

09:54 7.32335 64.64055

09:55 7.30835 64.3774

09:56 7.2753 64.0636333

09:57 7.35958333 65.0054

09:58 7.23883333 64.4193333

09:59 7.06161667 62.3264833

10:00 7.3477 66.1179333

10:01 7.3673 66.7519

10:02 5.44061667 48.0556167

10:03. 009183333 0

10:04. 006833333 0

10:05. 00545 0

10:06. 0062 0

10:07 1.5357 12.9266833

10:08 7.35653333 65.4692333

10:09 7.36343333 65.6357833

10:10 7.1894 63.24075

Reference documentation

Configuring and Monitoring Instance Caging [ID 1362445.1]

Http://www.oracle.com/technetwork/database/performance/instance-caging-wp-166854.pdf

Http://www.dbi-services.com/index.php/blog/entry/oracle-11g-instance-caging-limit-database-cpu-consumption

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.APPLIES TO:

Oracle Database-Enterprise Edition-Version 11.2.0.1 to 11.2.0.4 [Release 11.2]

Information in this document applies to any platform.

* Checked for relevance on 05-Apr-2016 * *

PURPOSE

This document provides a step-by-step guide for configuring Instance Caging. Instance Caging is an RDBMS feature for limiting the CPU usage of a database instance. Instance Caging is a valuable tool for database consolidation.

DETAILS

Determine Number of CPUs

The first step is to determine the number of CPUs on your server, 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'

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.

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

This is the end of the content of "Oracle instance Cage Analysis". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.