主頁(yè) > 知識(shí)庫(kù) > Oracle9i 動(dòng)態(tài)SGA,PGA特性探索

Oracle9i 動(dòng)態(tài)SGA,PGA特性探索

“In the latest release 10.2 Oracle changed these default values. The memory allocated to a single SQL operator (_smm_max_size) is limited to:
for P_A_T = 500MB the parameter _smm_max_size = 20% of P_A_T
for P_A_T between 500MB and 1000MB the parameter _smm_max_size = 100M
for P_A_T betweeen 1001MB and 2560MB (2.5GB) the parameter _smm_max_size = 10% of P_A_T
I have seen even cases when these values were even bigger after the instance was restarted with P_A_T set at 4GB.
The maximum value for parallel operations changed from 30% to 50% PGA_AGGREGATE_TARGET/DOP. Also the DOP changed. When DOP =5 then _smm_max_size is used, otherwise _smm_px_max_size/DOP limits the maximum memory usage. . .
轉(zhuǎn)載:http://www.1to2.us/ORACLE-a117737.htm
oracle中SGA的設(shè)置
關(guān)鍵詞： sga
關(guān)于SGA設(shè)置的一點(diǎn)總結(jié)
本總結(jié)不針對(duì)特例，僅對(duì)服務(wù)器只存在OS + ORACLE 為例，如果存在其他應(yīng)用請(qǐng)酌情考慮
寫(xiě)這個(gè)也是因?yàn)榻鼇?lái)這種重復(fù)性的問(wèn)題發(fā)生的太多所導(dǎo)致的
首先不要迷信STS,SG,OCP,EXPERT 等給出的任何建議、內(nèi)存百分比的說(shuō)法
基本掌握的原則是， data buffer 通?？梢员M可能的大，shared_pool_size 要適度，log_buffer 通常大到幾百K到1M就差不多了
設(shè)置之前，首先要明確2個(gè)問(wèn)題
1：除去OS和一些其他開(kāi)銷(xiāo)，能給ORACLE使用的內(nèi)存有多大
2：oracle是64bit or 32 bit,32bit 通常 SGA有 1.7G 的限制(某些OS的處理或者WINDOWS上有特定設(shè)定可以支持到2G以上甚至達(dá)到3.7G，本人無(wú)這方面經(jīng)驗(yàn))
下面是我的windows2000下的oracle :
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
SQL>
windows上存在32bit的限制，如AIX、HP UNIX 等有明確的64BIT OS and ORACLE的版本，32bit oracle可以裝在64bit os 上，64 bit oracle不能裝在32 bit OS上
不管oracle是32 bit ORACLE還是 64 bit 的,假定應(yīng)用存在沒(méi)有很好的使用bind var 的情況，也不能設(shè)置 shared_pool_size 過(guò)大，通常應(yīng)該控制在200M--300M,如果是 ORACLE ERP 一類(lèi)的使用了很多存儲(chǔ)過(guò)程函數(shù)、包，或者很大的系統(tǒng)，可以考慮增大shared_pool_size ,但是如果超過(guò)500M可能是危險(xiǎn)的，達(dá)到1G可能會(huì)造成CPU的嚴(yán)重負(fù)擔(dān)，系統(tǒng)甚至癱瘓。所以shared_pool_size 如果超過(guò)300M還命中率不高，那么應(yīng)該從應(yīng)用上找原因而不是一味的增加內(nèi)存，shared_pool_size 過(guò)大主要增加了管理負(fù)擔(dān)和latch 的開(kāi)銷(xiāo)。
log_buffer : 128K ---- 1M 之間通常問(wèn)題不大，不應(yīng)該太大
large_pool_size :如果不設(shè)置MTS，通常在 RMAN 、OPQ 會(huì)使用到，但是在10M --- 50M 應(yīng)該差不多了。假如設(shè)置 MTS,則由于 UGA 放到large_pool_size 的緣故，這個(gè)時(shí)候依據(jù) session最大數(shù)量和 sort_ares_size 等參數(shù)設(shè)置，必須增大large_pool_size 的設(shè)置，可以考慮為 session * (sort_area_size + 2M)。這里要提醒一點(diǎn)，不是必須使用MTS，我們都不主張使用MTS，尤其同時(shí)在線用戶數(shù)小于500的情況下。
java_pool_size : 若不使用java，給30M通常就夠了
data buffer ,在做了前面的設(shè)置后，凡可以提供給oracle的內(nèi)存，都應(yīng)該給data buffer = (db_block_size * db_block_buffers)
在9i 中可以是 db_cache_size
還有2個(gè)重要參數(shù)我們需要注意
sort_area_size and hash_area_size
這兩個(gè)參數(shù)在非MTS下都是屬于PGA ，不屬于SGA,是為每個(gè)session單獨(dú)分配的，在我們的服務(wù)器上除了OS + SGA,一定要考慮這兩部分
(****) : OS 使用內(nèi)存+ SGA + session*(sort_area_size + hash_area_size + 2M) 總物理RAM 為好
這樣歸結(jié)過(guò)來(lái)，假定oracle是 32 bit ,服務(wù)器RAM大于2G ，注意你的PGA的情況，,則建議
shared_pool_size + data buffer +large_pool_size + java_pool_size 1.6G
再具體化，注意滿足上面(****) 的原則的基礎(chǔ)上可以參考如下設(shè)置
如果512M RAM
建議 shared_pool_size = 50M, data buffer = 200M
如果1G RAM
shared_pool_size = 100M , data buffer = 500M
如果2G
shared_pool_size = 150M ,data buffer = 1.2G
物理內(nèi)存再大已經(jīng)跟參數(shù)沒(méi)有關(guān)系了
假定64 bit ORACLE
內(nèi)存4G
shared_pool_size = 200M , data buffer = 2.5G
內(nèi)存8G
shared_pool_size = 300M , data buffer = 5G
內(nèi)存 12G
shared_pool_size = 300M-----800M , data buffer = 8G
以上僅為參考值，不同系統(tǒng)可能差異比較大，需要根據(jù)具體情況調(diào)整。建議在設(shè)置參數(shù)的同時(shí)，init中使用 lock_sga ，在不同的平臺(tái)上可能有不同的方式，使得SGA鎖定在物理內(nèi)存中而不被放入 SWAP 中，這樣對(duì)效率有好處關(guān)于內(nèi)存的設(shè)置，要再進(jìn)行細(xì)致的調(diào)整，起的作用不大，但可根據(jù)statspack信息和v$system_event,v$sysstat,v$sesstat,v$latch 等view信息來(lái)考慮微調(diào)
pga設(shè)置
hash_area_size用于在內(nèi)存中進(jìn)行排序的區(qū)域
hash_value不會(huì)影響PGA，他是系統(tǒng)自動(dòng)計(jì)算HASH運(yùn)算，以定位內(nèi)存地址。
增大hash_area_size,可以減少物理磁盤(pán)的排序，所以提高排序的速度。
9I管理PGA有兩種方式，同時(shí)影響到hash_area_size
自動(dòng)配置PGA
手動(dòng)配置PGA
如果設(shè)置了auto，那么sort area, hash area自動(dòng)分配大小，pga_aggregate_target參數(shù)被使用。
如果設(shè)置為 manual，那么參數(shù)sort area, hash area參數(shù)被使用，對(duì)于某些特別耗資源的可以指定manual，然后設(shè)置合理的sort area,hash area。
設(shè)置10104事件來(lái)判斷hash_area_size是否要增大，如果Number of rows left to be
iterated over的值為非0，表示讀了臨時(shí)表，要兩階段不能在內(nèi)存中一次完成
在Oracle9i之前，PGA的計(jì)算和控制都是比較復(fù)雜的事情，從Oracle9i開(kāi)始，Oracle提供了一種SQL內(nèi)存管理的新方法：自動(dòng)化SQL執(zhí)行內(nèi)存管理（Automated SQL Execution Memory Management），使用這個(gè)新特性，Oracle可以自動(dòng)調(diào)整S Q L內(nèi)存區(qū)，而不用關(guān)閉數(shù)據(jù)庫(kù)，這一改進(jìn)大大簡(jiǎn)化了DBA的工作，同時(shí)也提高了Oracle數(shù)據(jù)庫(kù)的性能。
為實(shí)現(xiàn)自動(dòng)的PGA管理，Oracle引入了幾個(gè)新的初始化參數(shù)：
1．PGA_AGGREGATE_TARGET-此參數(shù)用來(lái)指定所有session總計(jì)可以使用最大PGA內(nèi)存。這個(gè)參數(shù)可以被動(dòng)態(tài)的更改,取值范圍從10M -- （4096G-1 ）bytes。
2。WORKAREA_SIZE_POLICY-此參數(shù)用于開(kāi)關(guān)PGA內(nèi)存自動(dòng)管理功能，該參數(shù)有兩個(gè)選項(xiàng)：AUTO 和 MANUAL，當(dāng)設(shè)置為AUTO時(shí)，數(shù)據(jù)庫(kù)使用Oracle9i提供的自動(dòng)PGA管理功能，當(dāng)設(shè)置為MANUAL時(shí)，則仍然使用Oracle9i前手工管理的方式。
缺省的，Oracle9i中WORKAREA_SIZE_POLICY被設(shè)置為AUTO。
需要注意的是，在Oracle9i中，PGA_AGGREGATE_TARGET參數(shù)僅對(duì)專(zhuān)用服務(wù)器模式下（Dedicated Server）的專(zhuān)屬連接有效，但是對(duì)共享服務(wù)器（Shared Server）連接無(wú)效；從Oracle10g開(kāi)始PGA_AGGREGATE_TARGET對(duì)專(zhuān)用服務(wù)器連接和共享服務(wù)器連接同時(shí)生效。
PGA_AGGREGATE_TARGET 參數(shù)同時(shí)限制全局PGA分配和私有工作區(qū)內(nèi)存分配：
1．對(duì)于串行操作，單個(gè)SQL操作能夠使用的PGA內(nèi)存按照以下原則分配：
MIN（5% PGA_AGGREGATE_TARGET，100MB）
2．對(duì)于并行操作
30% PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）
要理解PGA的自動(dòng)調(diào)整，還需要區(qū)分可調(diào)整內(nèi)存（TUNABLE MEMORY SIZE）與不可調(diào)整內(nèi)存（UNTUNABLE MEMORY SIZE）?？烧{(diào)整內(nèi)存是由SQL工作區(qū)使用的，其余部分是不可調(diào)整內(nèi)存。
啟用了自動(dòng)PGA調(diào)整之后， Oracle仍然需要遵循以下原則：
UNTUNABLE MEMORY SIZE + TUNABLE MEMORY SIZE = PGA_AGGREGATE_TARGET
數(shù)據(jù)庫(kù)系統(tǒng)只能控制可調(diào)整部分的內(nèi)存分配，如果可調(diào)整的部分過(guò)小，則Oracle永遠(yuǎn)也不會(huì)強(qiáng)制啟用這個(gè)等式。
另外，PGA_AGGREGATE_TARGET參數(shù)在CBO優(yōu)化器模式下，對(duì)于SQL的執(zhí)行計(jì)劃會(huì)產(chǎn)生影響。Oracle在評(píng)估執(zhí)行計(jì)劃時(shí)會(huì)根據(jù)PGA_AGGREGATE_TARGET參數(shù)評(píng)估在Sort，HASH-JOIN或Bitmap操作時(shí)能夠使用的最大或最小內(nèi)存，從而選擇最優(yōu)的執(zhí)行計(jì)劃。
對(duì)于PGA_AGGREGATE_TARGET參數(shù)的設(shè)置，Oracle提供這樣一個(gè)建議方案
1．對(duì)于OLTP系統(tǒng)
PGA_AGGREGATE_TARGET = (Total Physical Memory > * 80%) * 20%
2．對(duì)于DSS系統(tǒng)
PGA_AGGREGATE_TARGET = (Total Physical Memory > * 80%) * 50%
也就是說(shuō)，對(duì)于一個(gè)單純的數(shù)據(jù)庫(kù)服務(wù)器，通常我們需要保留20%的物理內(nèi)存給操作系統(tǒng)使用，剩余80%可以分配給Oracle使用。Oracle使用的內(nèi)存分為兩部分SGA和PGA，那么PGA可以占用Oracle消耗總內(nèi)存的20%（OLTP系統(tǒng)）至50%（DSS系統(tǒng)）。
這只是一個(gè)建議設(shè)置，更進(jìn)一步的我們應(yīng)該根據(jù)數(shù)據(jù)庫(kù)的具體性能指標(biāo)來(lái)調(diào)整和優(yōu)化PGA的使用。
對(duì)于PGA_AGGREGATE_TARGET參數(shù)的設(shè)置，Oracle提供這樣一個(gè)建議方案
1．對(duì)于OLTP系統(tǒng)
PGA_AGGREGATE_TARGET = (Total Physical Memory > * 80%) * 20%
2．對(duì)于DSS系統(tǒng)
PGA_AGGREGATE_TARGET = (Total Physical Memory > * 80%) * 50%
也就是說(shuō)，對(duì)于一個(gè)單純的數(shù)據(jù)庫(kù)服務(wù)器，通常我們需要保留20%的物理內(nèi)存給操作系統(tǒng)使用，剩余80%可以分配給Oracle使用。Oracle使用的內(nèi)存分為兩部分SGA和PGA，那么PGA可以占用Oracle消耗總內(nèi)存的20%（OLTP系統(tǒng)）至50%（DSS系統(tǒng)）。
Oracle9i數(shù)據(jù)庫(kù)在內(nèi)部特性方面有著非常大的增強(qiáng)，其中一個(gè)最令Oracle DBA興奮的莫過(guò)于可以動(dòng)態(tài)設(shè)置全部的Oracle SGA控制參數(shù)。與8i不同的是，原來(lái)都將初始化參數(shù)放到一個(gè)文本文件中，并且在數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候讀取，Oracle9i卻可以通過(guò)ALTER DATABASE和ALTER SYSTEM命令復(fù)位全部的Oracle參數(shù)。
　　在9i前，如果想對(duì)Oracle數(shù)據(jù)庫(kù)的處理模式作一些改變的話，Oracle管理員必須關(guān)閉數(shù)據(jù)庫(kù)并且重新設(shè)置INIT.ORA文件中的參數(shù)，然后重新啟動(dòng)數(shù)據(jù)庫(kù)。對(duì)于白天使用OLTP模式運(yùn)作，晚上切換到數(shù)據(jù)倉(cāng)庫(kù)模式的Oracle數(shù)據(jù)庫(kù)來(lái)說(shuō)，這種重新設(shè)置是經(jīng)常做的。
　　對(duì)于需要停止和重新啟動(dòng)Oracle數(shù)據(jù)庫(kù)來(lái)修改參數(shù)來(lái)說(shuō)，Oracle9i在這方面有明顯的加強(qiáng)，它令實(shí)現(xiàn)數(shù)據(jù)庫(kù)連續(xù)可用的目標(biāo)變得更加簡(jiǎn)單。
　　這種可以在Oracle SGA中動(dòng)態(tài)增加和縮小不同區(qū)域的能力為Oracle數(shù)據(jù)庫(kù)管理員提供了一些令人激動(dòng)的新特性。SGA每個(gè)區(qū)域的數(shù)據(jù)庫(kù)活動(dòng)都可以獨(dú)立地被監(jiān)視，而且也可以在Oracle數(shù)據(jù)庫(kù)中，根據(jù)使用的模式分配和取回資源。
　　我們首先來(lái)看以下Oracle9i數(shù)據(jù)庫(kù)和Oracle8i數(shù)據(jù)庫(kù)的一些區(qū)別。Oracle9i的一個(gè)最重要的加強(qiáng)是對(duì)于連接到Oracle數(shù)據(jù)庫(kù)的全部專(zhuān)用連接，都無(wú)需要擁有一個(gè)獨(dú)立的PGA空間。在Oracle8i中，對(duì)于專(zhuān)用的Oracle連接，我們都需要在內(nèi)存中分配一個(gè)獨(dú)立的區(qū)域，稱(chēng)為Program Global Area或者PGA。PGA空間中包含有SORT_AREA_SIZE和額外的RAM控制結(jié)構(gòu)以用來(lái)維護(hù)連接任務(wù)的狀態(tài)。在Oracle9i中，PGA空間已經(jīng)被Oracle SGA中的一個(gè)新內(nèi)存空間代替，它是通過(guò)PGA_AGGREGATE_TARGET參數(shù)來(lái)設(shè)置的
　　由于全部的內(nèi)存使用都在Oracle SGA中分配，所以O(shè)racle數(shù)據(jù)庫(kù)管理員可以將分配給Oracle服務(wù)器的內(nèi)存加大，可以分配至直到Orace服務(wù)器全部?jī)?nèi)存的80%。Oracle建議將服務(wù)器其余的20%內(nèi)存保留給操作系統(tǒng)的任務(wù)。
　　當(dāng)用戶連接到Oracle9i數(shù)據(jù)庫(kù)時(shí)，排序工作所需要的內(nèi)存將會(huì)在Oracle9i的PGA_AGGREGATE_TARGET區(qū)域中分配。這可以令Oracle9i比Oracle8i跑得更快，這是由于內(nèi)存只在需要的期間才分配，并且在完成后就可以馬上釋放給其它連接的Oracle任務(wù)使用。
　　動(dòng)態(tài)修改SGA區(qū)域
　　由于Oracle管理員現(xiàn)在可以增加和減少SGA的全部區(qū)域，因此我們可以快速地查看一下SGA區(qū)域是怎樣的，這樣我們就可以知道Oracle DBA如何監(jiān)視這些區(qū)域的使用并且為Oracle數(shù)據(jù)庫(kù)更有效地重新分配內(nèi)存。SGA的區(qū)域可以分為以下的部分。
　　數(shù)據(jù)緩沖（Data buffers）--Oracle9i擁有多達(dá)7個(gè)獨(dú)立的數(shù)據(jù)緩沖來(lái)保存磁盤(pán)送來(lái)的數(shù)據(jù)塊。這些包含有傳統(tǒng)的KEEP pool，RECYCLE pool和DEFAULT pool，還有為每個(gè)Oracle數(shù)據(jù)庫(kù)支持的塊大小(2K, 4K, 8K, 16K和32K) 而建立的獨(dú)立數(shù)據(jù)緩沖池
　　我們可以監(jiān)視這7個(gè)數(shù)據(jù)緩沖區(qū)域的命中率，如果緩沖的命中率保持在百分之九十以上，我們可以減少分配給這些數(shù)據(jù)緩沖的內(nèi)存，并且將它們重新分配給其它Oracle實(shí)例中需要額外內(nèi)存的地方。
　　當(dāng)數(shù)據(jù)緩沖的命中率（DBHR）下降時(shí)，我們可以將內(nèi)存由一個(gè)數(shù)據(jù)緩沖中分離出來(lái)，并且將它重新分配給其它的數(shù)據(jù)緩沖
　　共享池（Shared pool）--Oracle9i的共享池有一個(gè)很重要的作用是分析和執(zhí)行Oracle SQL語(yǔ)句。低的library cache命中率表示分配給library cache的內(nèi)存不足，當(dāng)shared pool需要對(duì)SQL語(yǔ)句進(jìn)行大量的分析和執(zhí)行時(shí)，Oracle9i的數(shù)據(jù)庫(kù)管理員可以使用ALTER SYSTEM來(lái)為shared pool加入額外的內(nèi)存。
　　PGA區(qū)域--分配給PGA_AGGREGATE_TARGET的內(nèi)存是用來(lái)讓Oracle連接維護(hù)與連接相關(guān)的信息（例如游標(biāo)的狀態(tài)），并且對(duì)SQL的結(jié)果集進(jìn)行排序。
　　Log buffer--對(duì)于Oracle redo log緩沖是否有大量活動(dòng)，我們可以在log switch（日志轉(zhuǎn)換）的頻率上看出來(lái)。Oracle管理員可以監(jiān)視r(shí)edo log區(qū)域的活動(dòng)，并且在Oracle數(shù)據(jù)庫(kù)需要額外的內(nèi)存為原始的緩沖區(qū)域服務(wù)時(shí)，動(dòng)態(tài)地增加內(nèi)存。
　　現(xiàn)在就讓我們來(lái)仔細(xì)看以下這些內(nèi)存區(qū)域之間是如何作用的。
改變PGA的內(nèi)存分配
　　當(dāng)以下的其中一個(gè)條件是真時(shí)，我們將需要?jiǎng)討B(tài)地修改PGA_AGGREGATE_TARGET參數(shù)。
　　。當(dāng)V$SYSSTAT中對(duì)"estimated PGA memory for one-pass" 的統(tǒng)計(jì)值超出PGA_AGGREGATE_TARGET時(shí)，我們就需要增加PGA_AGGREGATE_TARGET的值。
　　。當(dāng)V$SYSSTAT中對(duì)"workarea executions - multipass" 的統(tǒng)計(jì)值超過(guò)百分之一時(shí)，數(shù)據(jù)庫(kù)將會(huì)由更多的內(nèi)存中得到好處
　　。你可能過(guò)高地估計(jì)了PGA內(nèi)存的空間，當(dāng)V$SYSSTAT中"workarea executions - optimal"的值一直是100%時(shí)，可以考慮減少PGA_AGGREGATE_TARGET的值。
　　我們可以通過(guò)一個(gè)簡(jiǎn)單的腳本來(lái)查看shared pool是否需要更多的內(nèi)存。
　　量度Library Cache的丟失率
set lines 80;
set pages 999;
column mydate heading 'Yr. Mo Dy Hr.' format a16
column c1 heading "execs" format 9,999,999
column c2 heading "Cache Misses|While Executing" format 9,999,999
column c3 heading "Library Cache|Miss Ratio" format 999.99999
break on mydate skip 2;
select
to_char(snap_time,'yyyy-mm-dd HH24') mydate,
sum(new.pins-old.pins) c1,
sum(new.reloads-old.reloads) c2,
sum(new.reloads-old.reloads)/
sum(new.pins-old.pins) library_cache_miss_ratio
from
stats$librarycache old,
stats$librarycache new,
stats$snapshot sn
where
new.snap_id = sn.snap_id
and
old.snap_id = new.snap_id-1
and
old.namespace = new.namespace
group by
to_char(snap_time,'yyyy-mm-dd HH24')

Cache Misses
Yr. Mo Dy Hr. execs While Executing LIBRARY_CACHE_MISS_RATIO
---------------- ---------- --------------- ------------------------
2001-12-11 10 10,338 3 .00029
2001-12-12 10 182,477 134 .00073
2001-12-14 10 190,707 202 .00106
2001-12-16 10 2,803 11 .00392
　　由上面的例子看到，在每天的9：00AM到10：AM之間，shared pool明顯缺少內(nèi)存。我們就可以在這段期間動(dòng)態(tài)地重新設(shè)置shared_pool參數(shù)以由db_cache_size中分配額外的內(nèi)存。
　　SGA的閥值摘要
　　由下面的表一可以看到，在監(jiān)視SGA的內(nèi)存使用時(shí)，有幾個(gè)明顯的閥值可以利用。我們可以寫(xiě)一些腳本并在其中集成一些智能，這樣就可以在處理的需求變化時(shí)重新設(shè)置SGA。
RAM Area Too-small Condition Too-Large Condition
Shared pool Library cache misses No misses
Data buffer cache Hit ratio 90% Hit ratio > 95%
PGA aggregate high multi-pass executions 100% optimal executions
　　表1：SGA中的異常條件指示
　　查看SGA內(nèi)存區(qū)域的負(fù)載
　　Oracle9i使用了一些新的內(nèi)部視圖或者在現(xiàn)有的視圖中加入新的列來(lái)幫助查看Oracle9i中的內(nèi)部?jī)?nèi)存分配情況。以下的新V$視圖可以幫助監(jiān)視Oracle9i連接的內(nèi)存使用。
　　V$PROCESS -在Oracle9i中加入了三個(gè)新的列以監(jiān)視PGA內(nèi)存的使用，新列的名字是pga_used_mem, pga_alloc_mem 和pga_max_mem。
　　V$SYSSTAT -加入了很多新的統(tǒng)計(jì)行，包括有area statistics for optimal, one-pass和multi-pass。
　　V$PGASTAT -該新視圖展示了全部后臺(tái)進(jìn)程和專(zhuān)用連接的PGA內(nèi)存使用
　　V$SQL_PLAN--這個(gè)新視圖包含了全部當(dāng)前執(zhí)行的SQL的執(zhí)行計(jì)劃信息。對(duì)于需要最優(yōu)化的SQL語(yǔ)句的性能調(diào)整專(zhuān)家來(lái)說(shuō)這是非常吸引人的。
　　V$WORKAREA -這個(gè)新視圖提供了Oracle9i連接的累積內(nèi)存統(tǒng)計(jì)的詳細(xì)信息。
　　V$WORKAREA_ACTIVE - 這個(gè)新的視圖提供了當(dāng)前全部正在執(zhí)行的SQL語(yǔ)句的內(nèi)部?jī)?nèi)存使用信息。
　　它們的目的是通過(guò)這些V$視圖來(lái)監(jiān)視SGA中的內(nèi)存使用，然后根據(jù)Oracle實(shí)例的處理要求，通過(guò)ALTER SYSTEM命令重新分配內(nèi)存。我們以下來(lái)看一些這些新的Oracle9i特性和腳本，它可以幫助我們查看詳細(xì)的內(nèi)存使用。
　　當(dāng)然我們不可能在這里詳述所有的技術(shù)，以下就讓我們看一個(gè)簡(jiǎn)單的例子，它通過(guò)使用V$SYSSTAT視圖來(lái)決定何時(shí)重新設(shè)置PGA_AGGREGATE_TARGET參數(shù)。
　　以下的查詢(xún)可以得到自數(shù)據(jù)庫(kù)實(shí)例啟動(dòng)后work areas被執(zhí)行的全部數(shù)目和百分比。
work_area.sql
select
name profile,
cnt,
decode(total, 0, 0, round(cnt*100/total)) percentage
from
(
select
name,
value cnt,
(sum(value) over ()) total
from
v$sysstat
where
name like 'workarea exec%'
);
　　這個(gè)查詢(xún)的輸出可能如下：
PROFILE CNT PERCENTAGE
----------------------------------- ---------- ----------
workarea executions - optimal 5395 95
workarea executions - onepass 284 5
workarea executions - multipass 0 0
　　這個(gè)查詢(xún)的輸出是用來(lái)告訴DBA何時(shí)動(dòng)態(tài)調(diào)整PGA_AGGREGATE_TARGET參數(shù)。在通常的情況下，如果multi-pass的執(zhí)行大于0，就需要增加PGA_AGGREGATE_TARGET的值，并且在optimal executions是100%時(shí)減少它的值。
　　我們還可以使用V$PGASTAT視圖來(lái)決定我們的Oracle實(shí)例的內(nèi)存使用。V$PGASTAT視圖提供了PGA使用和自動(dòng)內(nèi)存管理的實(shí)例級(jí)摘要統(tǒng)計(jì)信息。以下的腳本提供了全部Oracle9i連接的整體內(nèi)存使用的統(tǒng)計(jì)信息。
　　以下是一個(gè)用來(lái)檢測(cè)Oracle9i中PGA內(nèi)存使用的簡(jiǎn)單腳本。
check_pga.sql
column name format a30
column value format 999,999,999
select
name,
value
from
v$pgastat

The output of this query might look like the following:
NAME VALUE
------------------------------------------------------ ----------
aggregate PGA auto target 736,052,224
global memory bound 21,200
total expected memory 141,144
total PGA inuse 22,234,736
total PGA allocated 55,327,872
maximum PGA allocated 23,970,624
total PGA used for auto workareas 262,144
maximum PGA used for auto workareas 7,333,032
total PGA used for manual workareas 0
maximum PGA used for manual workareas 0
estimated PGA memory for optimal 141,395
maximum PGA memory for optimal 500,123,520
estimated PGA memory for one-pass 534,144
maximum PGA memory for one-pass
　　在上面的v$pgastat顯示中我們可以看到以下的統(tǒng)計(jì)。
　　Aggregate PGA auto target -該列給出了可用于Oracle9i連接的全部?jī)?nèi)存。我們已經(jīng)提過(guò)，這個(gè)值是由PGA_AGGREGATE_TARGET設(shè)置的。
　　Global memory bound -該統(tǒng)計(jì)表示work area的最大值，Oracle建議在該統(tǒng)計(jì)值下降到1M時(shí)，你應(yīng)該增加PGA_AGGREGATE_TARGET的值。
　　Total PGA allocated - 這個(gè)統(tǒng)計(jì)顯示了數(shù)據(jù)庫(kù)中全部PGA內(nèi)存使用的高水位線。當(dāng)使用增加時(shí)，你應(yīng)該看到這個(gè)值接近PGA_AGGREGATE_TARGET的值。
　　Total PGA used for auto workareas - 這個(gè)統(tǒng)計(jì)監(jiān)視內(nèi)存的使用或者全部運(yùn)行在自動(dòng)內(nèi)存模式中的全部連接。要記住的是，并不是全部的內(nèi)部進(jìn)程使用自動(dòng)內(nèi)存特性。例如，Java和PL/SQL 將分配內(nèi)存，但是這部分將不會(huì)統(tǒng)計(jì)到這個(gè)值中。因此我們可使用整體PGA的值來(lái)減去該值，以得到連接和Java and PL/SQL使用的內(nèi)存。
　　Estimated PGA memory for optimal/one-pass - 該統(tǒng)計(jì)估計(jì)optimal模式下執(zhí)行全部的連接任務(wù)所需要的內(nèi)存。要記住的是，如果Oracle9i遇到內(nèi)存不足時(shí)，它就會(huì)調(diào)用multi-pass操作。這個(gè)統(tǒng)計(jì)對(duì)于監(jiān)視Oracle9i中的內(nèi)存使用是非常重要的，大多數(shù)的Oracle DBA將會(huì)增加PGA_AGGREGATE_TARGET到這個(gè)值。
　　現(xiàn)在我們已經(jīng)了解了這個(gè)概念，以下就讓我們來(lái)看一下自動(dòng)重新配置SGA有哪些方法。
　　總述
　　在一個(gè)UNIX環(huán)境中，在處理需求改變時(shí)通過(guò)定時(shí)任務(wù)來(lái)修改內(nèi)存配置是非常簡(jiǎn)單的。例如，許多Oracle數(shù)據(jù)庫(kù)在一般的工作時(shí)間以O(shè)LTP模式運(yùn)作，在晚上的時(shí)候則運(yùn)行對(duì)內(nèi)存需求很大的批量報(bào)告。
　　我們知道在一個(gè)OLTP數(shù)據(jù)庫(kù)中應(yīng)該將DB_CACHE_SIZE設(shè)置為一個(gè)較大的值，而在需求內(nèi)存很大的批量任務(wù)中則需要給PGA_AGGREGATE_TARGET分配額外的內(nèi)存。
　　以下的UNIX腳本可以用來(lái)重新設(shè)置OLTP和DSS的SGA值而無(wú)需將實(shí)例停下來(lái)。在這個(gè)例子中，我們假定有一個(gè)孤立的帶有8GB內(nèi)存的的Oracle服務(wù)器。我們還假定保留20%的內(nèi)存供UNIX使用，而剩下的6GB內(nèi)存則用作Oracle和Oracle連接。這些腳本是在HP/UX或者Solaris中使用的，并且接受$ORACLE_SID作為一個(gè)參數(shù)。
　　DSS_CONFIG.KSH腳本將在每晚的6：00 p.m運(yùn)行，以重新設(shè)置Oracle在晚上運(yùn)行對(duì)內(nèi)存需求很大的批量任務(wù)。
dss_config.ksh
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
#ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus -s /nologin
connect system/manager as sysdba;
ALTER SYSTEM set db_cache_size=1500m;
ALTER SYSTEM set shared_pool_size=500m;
ALTER SYSTEM set pga_aggregate_target=400m;
exit
!
　　現(xiàn)在我們已經(jīng)知道了一個(gè)常見(jiàn)的方式來(lái)修改Oracle的配置，我們也很容易地看到可以很簡(jiǎn)單地開(kāi)發(fā)一個(gè)技術(shù)來(lái)連續(xù)地監(jiān)控Oracle的處理需求，并且根據(jù)現(xiàn)有的數(shù)據(jù)庫(kù)需求來(lái)使用ALTER SYSTEM作修改。
　　結(jié)論
　　雖然Oracle9i中的內(nèi)存管理仍然需要很多的手工操作，不過(guò)大部分的Oracle管理員可以使用工具來(lái)連續(xù)地監(jiān)控Oracle SGA中的內(nèi)存使用，并且可以根據(jù)Oracle instance中現(xiàn)在的使用情況來(lái)自動(dòng)地重新分配內(nèi)存。這樣就可以令Oracle 管理員根據(jù)系統(tǒng)的變化來(lái)靈活地重新設(shè)置他們的系統(tǒng)。

標(biāo)簽：湖南黃山蘭州湘潭崇左銅川衡水仙桃

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《Oracle9i 動(dòng)態(tài)SGA,PGA特性探索》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。

佳木斯湛栽影视文化发展公司

Oracle9i 動(dòng)態(tài)SGA,PGA特性探索

QQ咨詢(xún)

電話咨詢(xún)