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

成都創(chuàng)新互聯(lián)主要從事網(wǎng)站設(shè)計、成都網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)昆玉,十余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):13518219792

Oracle實例囚籠（Instance Caging）

---

---

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

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

4，驗證功能已經(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)的活動sessions數(shù)，如果次數(shù)遠小于CPU_COUNT，這實例遠沒有達到限制。如果AVG_WAITING_SESSIONS很大，這系統(tǒng)基本達到最大限制了


6，可以動態(tài)的調(diào)整CPU_COUNT來調(diào)整實例使用的資源。下面是測試結(jié)果

a, 設(shè)置cpu_count為32，即不設(shè)置限制。
SQL> alter system set cpu_count =32;
開始壓力測試，PC服務(wù)器的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, 設(shè)置實例囚籠功能，即限制CPU_cout為16，數(shù)據(jù)庫出現(xiàn)大量resmgr:cpu quantum等待事件（這個和資源管理有關(guān)），此時系統(tǒng)利用率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數(shù)量，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,查看動態(tài)視圖avg_running_sessions和cpu_count基本一致，說明已經(jīng)達到最大限度了

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

文章題目：Oracle實例囚籠分析
網(wǎng)頁鏈接：http://www.rwnh.cn/article40/jieseo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、網(wǎng)站收錄、手機網(wǎng)站建設(shè)、定制開發(fā)、定制網(wǎng)站、云服務(wù)器

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)