Oracle例項囚籠（Instance Caging）

路途中的人2012發表於2017-09-24
Oracle

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.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:

  1. 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 for instructions.

  2. 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.

See Also:

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:

for more information on DBMS_RESOURCE_MANAGER.SWITCH_PLAN.

Disabling the Resource Manager

To disable the Resource Manager, complete the following steps:

  1. Issue the following SQL statement: 

    ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

  2. 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 and 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 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).

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



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群，學習最實用的資料庫技術。

Oracle例項囚籠（Instance Caging）
 DBA筆試面試講解
 歡迎與我聯絡

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-2145384/，如需轉載，請註明出處，否則將追究法律責任。

相關文章