当多个实例运行在同一台服务器上时，为了避免实例间的相互影响，从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
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.
This section contains:
About Instance Caging
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.
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. 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:
-
Over-provisioning—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-provisioned 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.
-
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. 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 Maximum Utilization Limit
If you enable instance caging and set a maximum 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 maximum utilization limit of 50%, then the consumer group can use a maximum of 50% of 4 CPUs, which is 2 CPUs.
Enabling Instance Caging
To enable instance caging, do the following for each instance on the server:
-
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.
See "Enabling Oracle Database Resource Manager and Switching Plans" for instructions.
-
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;
Enabling Oracle Database Resource Manager and Switching Plans
You enable Oracle Database Resource Manager (the Resource Manager) by setting the RESOURCE_MANAGER_PLAN initialization parameter. This parameter specifies the top plan, identifying the plan to be used for the current instance. If no plan is specified with this parameter, the Resource Manager is not enabled.
By default the Resource Manager is not enabled, except during preconfigured maintenance windows, described later in this section.
The following statement in a text initialization parameter file activates the Resource Manager upon database startup and sets the top plan as mydb_plan.
RESOURCE_MANAGER_PLAN = mydb_plan
You can also activate or deactivate the Resource Manager, or change the current top plan, using the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure or the ALTER SYSTEM statement.
The following SQL statement sets the top plan to mydb_plan, and activates the Resource Manager if it is not already active:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';
An error message is returned if the specified plan does not exist in the data dictionary.
Automatic Enabling of the Resource Manager by Oracle Scheduler Windows
The Resource Manager automatically activates if an Oracle Scheduler window that specifies a resource plan opens. When the Scheduler window closes, the resource plan associated with the window is disabled, and the resource plan that was running before the Scheduler window opened is reenabled. (If no resource plan was enabled before the window opened, then the Resource Manager is disabled.) In an Oracle Real Application Clusters environment, a Scheduler window applies to all instances, so the window's resource plan is enabled on every instance.
Note that by default a set of automated maintenance tasks run during maintenance windows, which are predefined Scheduler windows that are members of the MAINTENANCE_WINDOW_GROUP window group and which specify the DEFAULT_MAINTENANCE_PLAN resource plan. Thus, the Resource Manager activates by default during maintenance windows. You can modify these maintenance windows to use a different resource plan, if desired.
Note:
If you change the plan associated with maintenance windows, then ensure that you include the subplan ORA$AUTOTASK_SUB_PLAN and the consumer group ORA$DIAGNOSTICS in the new plan.Disabling Plan Switches by Oracle Scheduler Windows
In some cases, the automatic change of Resource Manager plans at Scheduler window boundaries may be undesirable. For example, if you have an important task to finish, and if you set the Resource Manager plan to give your task priority, then you expect that the plan will remain the same until you change it. However, because a Scheduler window could activate after you have set your plan, the Resource Manager plan might change while your task is running.
To prevent this situation, you can set the RESOURCE_MANAGER_PLAN initialization parameter to the name of the plan that you want for the system and prepend "FORCE:" to the name, as shown in the following SQL statement:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan';
Using the prefix FORCE: indicates that the current resource plan can be changed only when the database administrator changes the value of the RESOURCE_MANAGER_PLAN initialization parameter. This restriction can be lifted by rerunning the command without preceding the plan name with "FORCE:".
The DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure has a similar capability.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information on DBMS_RESOURCE_MANAGER.SWITCH_PLAN.Disabling the Resource Manager
To disable the Resource Manager, complete the following steps:
-
Issue the following SQL statement:
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
-
Disassociate the Resource Manager from all Oracle Scheduler windows.
To do so, for any Scheduler window that references a resource plan in its resource_plan attribute, use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set resource_plan to the empty string (''). Qualify the window name with the SYS schema name if you are not logged in as user SYS. You can view Scheduler windows with the DBA_SCHEDULER_WINDOWS data dictionary view. See "Altering Windows" and Oracle Database PL/SQL Packages and Types Reference for more information.
Note:
By default, all maintenance windows reference the DEFAULT_MAINTENANCE_PLAN resource plan. To completely disable the Resource Manager, you must alter all maintenance windows to remove this plan. However, use caution, because resource consumption by automated maintenance tasks will no longer be regulated, which may adversely affect the performance of your other sessions. See Chapter 26, "Managing Automated Database Maintenance Tasks" for more information on maintenance windows.
参数CPU_COUNT指定了Oracle实例可以同时使用的CPU的数量，数据库的部分功能配置依赖于CPU_COUNT参数，比如查询优化器，并行查询和资源管理器.
Instance caging是Oracle Database 11gR2企业版的新特性，是对CPU资源使用的一个简单管理方法. 如果要启动Instance caging，需要为数据库实例
设置CPU_COUNT参数和启动一个资源管理计划。
通常有两种方法来设置instance caging:
Partitioning:
在这种方法中,所有实例的CPU_COUNT的总和小于或等于系统的CPU数目，实例之间互不干扰。
Over-provisioning:
在这种方法中, 所有实例的CPU_COUNT的总和超过系统的CPU数目，实例的性能会相互影响。
1. CPU_COUNT参数的默认值是系统上最大可用的CPU数量
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 64
通过alter system限定实例可用的CPU数量
SQL> alter system set cpu_count=2;
2. 可以设置自己的资源管理计划，在CPU指令中使用mgmt_p1,mgmt_p2,...,mgmt_p8来限定消费者组的CPU资源利用率。
最简单的方法是启动默认的DEFAULT_PLAN.
SQL> alter system set resource_manager_plan=DEFAULT_PLAN;
在11gR2中，还不能为数据库实例指定特定的CPU，同一系统上的不同实例的进程可能会运行在相同的CPU上。
Configuring and Monitoring Instance Caging (文档 ID 1362445.1)
In this Document
| Purpose |
| Details |
| References |
| 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).
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 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.
show parameter cpu_count;
To monitor Instance Caging on an instance, monitor the average number of running and waiting sessions.
"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 CagingNOTE:1484302.1 - Master Note: Overview of Oracle Resource Manager and DBMS_RESOURCE_MANAGER
NOTE:1339769.1 - Master Note for Oracle Database Resource Manager
About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
