共計(jì) 7391 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。
本篇內(nèi)容介紹了“Oracle 實(shí)例囚籠分析”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
Oracle 實(shí)例囚籠（Instance Caging）
當(dāng)多個(gè)實(shí)例運(yùn)行在同一臺(tái)服務(wù)器上時(shí)，為了避免實(shí)例間的相互影響，從 oracle 11gr2 開(kāi)始推出了實(shí)例囚籠的概念。實(shí)例囚籠能夠限制數(shù)據(jù)庫(kù)實(shí)例使用的 CPU 資源。使用實(shí)例囚籠，只需要設(shè)置 CPU_COUT 和 resource_manager_plan 兩個(gè)參數(shù)。該功能可以用于的數(shù)據(jù)庫(kù)資源整合，而取代之前的虛擬化和分區(qū)等傳統(tǒng)的資源分割方法
1，打開(kāi) 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, 開(kāi)啟 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;
開(kāi)始?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
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 實(shí)例囚籠分析”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
