Oracle實(shí)例囚籠分析

本篇內(nèi)容介紹了“Oracle實(shí)例囚籠分析”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

Oracle實(shí)例囚籠（Instance Caging）

當(dāng)多個(gè)實(shí)例運(yùn)行在同一臺(tái)服務(wù)器上時(shí)，為了避免實(shí)例間的相互影響，從oracle 11gr2開始推出了實(shí)例囚籠的概念。實(shí)例囚籠能夠限制數(shù)據(jù)庫(kù)實(shí)例使用的CPU資源。使用實(shí)例囚籠，只需要設(shè)置CPU_COUT和resource_manager_plan兩個(gè)參數(shù)。該功能可以用于的數(shù)據(jù)庫(kù)資源整合，而取代之前的虛擬化和分區(qū)等傳統(tǒng)的資源分割方法

1，打開swingbench準(zhǔn)備設(shè)置后進(jìn)行壓力測(cè)試（具體方法見(jiàn)前面文章）
2，查看服務(wù)器的CPU個(gè)數(shù)
select value from v$osstat where stat_name = 'NUM_CPUS';
3,開啟Instance Caging，只需設(shè)置兩個(gè)參數(shù)即可
alter system set cpu_count = 4;
alter system set resource_manager_plan = 'default_plan'; 
備注：這個(gè)地方很奇怪，第一次使用報(bào)錯(cuò)ORA-00450，經(jīng)過(guò)一段時(shí)間后，設(shè)置竟然成功了

4，驗(yàn)證功能已經(jīng)啟用
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是一分鐘內(nèi)的活動(dòng)sessions數(shù)，如果次數(shù)遠(yuǎn)小于CPU_COUNT，這實(shí)例遠(yuǎn)沒(méi)有達(dá)到限制。如果AVG_WAITING_SESSIONS很大，這系統(tǒng)基本達(dá)到最大限制了


6，可以動(dòng)態(tài)的調(diào)整CPU_COUNT來(lái)調(diào)整實(shí)例使用的資源。下面是測(cè)試結(jié)果

a, 設(shè)置cpu_count為32，即不設(shè)置限制。
SQL> alter system set cpu_count =32;
開始?jí)毫y(cè)試，PC服務(wù)器的TPMC達(dá)到45萬(wàn)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, 設(shè)置實(shí)例囚籠功能，即限制CPU_cout為16，數(shù)據(jù)庫(kù)出現(xiàn)大量resmgr:cpu quantum等待事件（這個(gè)和資源管理有關(guān)），此時(shí)系統(tǒng)利用率65%左右，但%user為50%左右，即16個(gè)cpu.TPMC為20萬(wàn)。能力受到限制
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個(gè)CPU數(shù)量，TPMC 10萬(wàn)左右
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,查看動(dòng)態(tài)視圖avg_running_sessions和cpu_count基本一致，說(shuō)明已經(jīng)達(dá)到最大限度了

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

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實(shí)例囚籠分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！