Oracle实例囚笼分析

共计 7391 个字符，预计需要花费 19 分钟才能阅读完成。

本篇内容介绍了“Oracle 实例囚笼分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

Oracle 实例囚笼（Instance Caging）

当多个实例运行在同一台服务器上时，为了避免实例间的相互影响，从 oracle 11gr2 开始推出了实例囚笼的概念。实例囚笼能够限制数据库实例使用的 CPU 资源。使用实例囚笼，只需要设置 CPU_COUT 和 resource_manager_plan 两个参数。该功能可以用于的数据库资源整合，而取代之前的虚拟化和分区等传统的资源分割方法

1，打开 swingbench 准备设置后进行压力测试（具体方法见前面文章）
2，查看服务器的 CPU 个数
select value from v$osstat where stat_name = NUM_CPUS
3, 开启 Instance Caging，只需设置两个参数即可
alter system set cpu_count = 4;
alter system set resource_manager_plan = default_plan  
备注：这个地方很奇怪，第一次使用报错 ORA-00450，经过一段时间后，设置竟然成功了

4，验证功能已经启用
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，查看功能使用情况

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 是一分钟内的活动 sessions 数，如果次数远小于 CPU_COUNT，这实例远没有达到限制。如果 AVG_WAITING_SESSIONS 很大，这系统基本达到最大限制了

6，可以动态的调整 CPU_COUNT 来调整实例使用的资源。下面是测试结果

a, 设置 cpu_count 为 32，即不设置限制。
SQL alter system set cpu_count =32;
开始压力测试，PC 服务器的 TPMC 达到 45 万 TPMC，CPU 利用率 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, 设置实例囚笼功能，即限制 CPU_cout 为 16，数据库出现大量 resmgr:cpu quantum 等待事件（这个和资源管理有关），此时系统利用率 65% 左右，但 %user 为 50% 左右，即 16 个 cpu.TPMC 为 20 万。能力受到限制
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 为 27%，基本保持在 8 个 CPU 数量，TPMC 10 万左右
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, 查看动态视图 avg_running_sessions 和 cpu_count 基本一致，说明已经达到最大限度了

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

参考文档

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

“Oracle 实例囚笼分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！