l??????
前言
對于
oracle
的內存的管理，截止到
9iR2
，都是相當重要的環節，管理不善，將可能給數據庫帶來嚴重的性能問題。下面我們將一步一步就內存管理的各個方面進行探討。
?
?
?
l??????
概述
oracle
的內存可以按照共享和私有的角度分為系統全局區和進程全局區，也就是
SGA
和
PGA(process global area or private global area)
。對于
SGA
區域內的內存來說，是共享的全局的，在
UNIX
上，必須為
oracle
設置共享內存段（可以是一個或者多個），因為
oracle
在
UNIX
上是多進程；而在
WINDOWS
上
oracle
是單進程（多個線程），所以不用設置共享內存段。
PGA
是屬于進程（線程）私有的區域。在
oracle
使用共享服務器模式下（
MTS
）
,PGA
中的一部分，也就是
UGA
會被放入共享內存
large_pool_size
中。
對于
SGA
部分，我們通過
sqlplus
中查詢可以看到：
SQL> select * from v$sga;
?
?
?
NAME????????????????????? VALUE
-------------------- ?????????????----------
Fixed Size?????????????? ????454032
Variable Size???????? ????????109051904
Database Buffers????? ????????385875968
Redo Buffers???????????? ?????667648
?
?
?
Fixed Size
oracle
的不同平臺和不同版本下可能不一樣，但對于確定環境是一個固定的值，里面存儲了
SGA
各部分組件的信息，
可以看作引導建立SGA的區域
。
?
?
?
Variable Size
包含了
shared_pool_size
、
java_pool_size
、
large_pool_size
等內存設置和用于管理數據緩沖區等內存結構的
hash table
、塊頭信息
(
比如
x$bh
消耗內存
)
等
?
?
?
Database Buffers
???
指數據緩沖區，在
8i
中包含
default pool
、
buffer_pool_keep
、
buffer_pool_recycle
三部分內存。在
9i
中包含
db_cache_size
、
db_keep_cache_size
、
db_recycle_cache_size
、
db_nk_cache_size
。這里要注意在
8i
中三部分內存總和為
db_block_buffers*db_block_size
。
?
?
?
Redo Buffers
指日志緩沖區，
log_buffer
。在這里要額外說明一點的是，對于
v$parameter
、
v$sgastat
、
v$sga
查詢值可能不一樣。
v$parameter
里面的值，是指用戶在初始化參數文件里面設置的值，
v$sgastat
是
oracle
實際分配的日志緩沖區大小（因為緩沖區的分配值實際上是離散的，也不是以
block
為最小單位進行分配的），
v$sga
里面查詢的值，是在
oracle
分配了日志緩沖區后，為了保護日志緩沖區，設置了一些保護頁，通常我們會發現保護頁大小大約是
11k(
不同環境可能不一樣
)
。參考如下內容
?
?
?
SQL>? select substr(name,1,10) name,substr(value,1,10) value
? 2? from v$parameter where name = 'log_buffer';
?
?
?
NAME???????????????? VALUE
-------------------- ????--------------------
log_buffer
??????????
????
524288
?
?
?
?
?
?
SQL> select * from v$sgastat ;
?
?
?
POOL ?NAME???????????? BYTES
----------- -------------------
fixed_sga????????????????? ?454032
buffer_cache??????????????? 385875968
log_buffer
?????????????????
656384
?
?
?
SQL> select * from v$sga;
?
?
?
NAME???????????????????? VALUE
--------------------???????????? ?----------
Fixed Size?????????????? ???454032
Variable Size???????? ???????109051904
Database Buffers????? ???????385875968
Redo Buffers
???????????
???
?
667648
?
?
?
關于各部分內存的作用，參考
oracle
體系結構，在此不再敘述。
?
?
?
l??????
SGA
的大小
那么我們現在來考察內存參數的設置。實際上，對于特定的環境，總是存在著不同的最優設置的，沒有任何一種普遍適用的最優方案。但為什么在這里我們還要來談設置這個話題呢，那僅僅是出于一個目的，避免過度的犯錯誤。事實上，在任何一個生產系統正式投入使用之前，我們不擁有任何系統運行信息讓我們去調整，這樣就只有兩種可能，一是根據文檔推薦設置，另外一種就是根據經驗設置。相對來說，根據經驗的設置比根據文檔的設置要可靠一些。尤其是那些
24*7
的系統，我們更要減少錯誤的發生。那么我們嘗試去了解不同的系統不同的應用的具體設置情況，從而提供一個參照信息給大家。
為了得出一個參照設置，我們就必須假定一個參照環境。以下所有設置我們基于這樣一個假定，那就是硬件服務器上只考慮存在操作系統和數據庫，在這個單一的環境中，我們來考慮內存的設置。
在設置參數之前呢，我們首先要問自己幾個問題
一：物理內存多大
二：操作系統估計需要使用多少內存
三：數據庫是使用文件系統還是裸設備
四：有多少并發連接
五：應用是
OLTP
類型還是
OLAP
類型
根據這幾個問題的答案，我們可以粗略地為系統估計一下內存設置。那我們現在來逐個問題地討論，首先物理內存多大是最容易回答的一個問題，然后操作系統估計使用多少內存呢？從經驗上看，不會太多，通常應該在
200M
以內（不包含大量進程
PCB
）。
接下來我們要探討一個重要的問題，那就是關于文件系統和裸設備的問題，這往往容易被我們所忽略。操作系統對于文件系統，使用了大量的
buffer
來緩存操作系統塊。這樣當數據庫獲取數據塊的時候，雖然
SGA
中沒有命中，但卻實際上可能是從操作系統的文件緩存中獲取的。而假如數據庫和操作系統支持異步
IO
，則實際上當數據庫寫進程
DBWR
寫磁盤時，操作系統在文件緩存中標記該塊為延遲寫，等到真正地寫入磁盤之后，操作系統才通知
DBWR
寫磁盤完成。對于這部分文件緩存，所需要的內存可能比較大，作為保守的估計，我們應該考慮在
0.2
——
0.3
倍內存大小。但是如果我們使用的是裸設備，則不考慮這部分緩存的問題。這樣的情況下
SGA
就有調大的機會。
關于數據庫有多少并發連接，這實際上關系到
PGA
的大小（
MTS
下還有
large_pool_size
）。事實上這個問題應該說還跟
OLTP
類型或者
OLAP
類型相關。對于
OLTP
類型
oracle
傾向于可使用
MTS,
對于
OLAP
類型使用獨立模式，同時
OLAP
還可能涉及到大量的排序操作的查詢，這些都影響到我們內存的使用。那么所有的問題綜合起來，實際上主要反映在
UGA
的大小上。
UGA
主要包含以下部分內存設置
SQL> show parameters area_size
NAME???????????????????????????????? TYPE?? ??????VALUE
------------------------------------ ??????????????------- ?????????-------------
bitmap_merge_area_size?????????????? ????integer ????????1048576
create_bitmap_area_size????????????? ?????integer ????????8388608
hash_area_size?????????????????????? ????integer ????????131072
sort_area_size?????????????????????? ?????integer ????????65536
SQL>
?
?
?
在這部分內存中我們最關注的通常是
sort_area_size
，這是當查詢需要排序的時候，數據庫會話將使用這部分內存進行排序，當內存大小不足的時候，使用臨時表空間進行磁盤排序。由于磁盤排序效率和內存排序效率相差好幾個數量級，所以這個參數的設置很重要。這四個參數都是針對會話進行設置的，是單個會話使用的內存的大小，而不是整個數據庫使用的。偶爾會看見有人誤解了這個參數以為是整個數據庫使用的大小，這是極其嚴重的錯誤。假如設置了
MTS
，則
UGA
被分配在
large_pool_size
，也就是說放在了共享內存里面，不同進程（線程）之間可以共享這部分內存。在這個基礎上，我們假設數據庫存在并發執行
server? process
為
100
個，根據上面我們
4
個參數在
oracle8.1.7
下的默認值，我們來計算獨立模式下
PGA
的大致大小。由于會話并不會經常使用
create_bitmap_area_size
、
bitmap_merge_area_size
，所以我們通常不對四個參數求和。在考慮到除這四個參數外會話所保存的變量、堆棧等信息，我們估計為
2M
，則
100
個進程最大可能使用
200M
的
PGA
。
現在，根據上面這些假定，我們來看
SGA
實際能達到多少內存。在
1G
的內存的服務器上，我們能分配給
SGA
的內存大約為
400—500M
。若是
2G
的內存，大約可以分到
1G
的內存給
SGA
，
8G
的內存可以分到
5G
的內存給
SGA
。當然我們這里是以默認的排序部分內存
sort_area_size=64k
進行衡量的，假如我們需要調大該參數和
hash_area_size
等參數，然后我們應該根據并發的進程的數量，來衡量考慮這個問題。
事實上，通常我們更習慣通過直觀的公式化來表達這樣的問題：
OS
使用內存
+SGA+
并發執行進程數
*(sort_area_size+hash_ara_size+2M) < 0.7*
總內存
???
??? (
公式是死的，系統是活的，實際應用的調整不必框公式，這不過是一個參考建議
)
???
在我們的實際應用中，假如采用的是裸設備，我們可適當的增大
SGA(
如果需要的話
)
。由于目前幾乎所有的操作系統都使用虛擬緩存，所以實際上如果就算
SGA
設置的比較大也不會導致錯誤，而是可能出現頻繁的內存頁的換入與換出
(page in/out)
。在操作系統一級如果觀察到這個現象，那么我們就需要調整內存的設置。
?
?
?
l??????
SGA
內參數設置
Log_buffer
對于日志緩沖區的大小設置，通常我覺得沒有過多的建議，因為參考
LGWR
寫的觸發條件之后，我們會發現通常超過
3M
意義不是很大。作為一個正式系統，可能考慮先設置這部分為
log_buffer=1—3M
大小，然后針對具體情況再調整。
?
?
?
Large_pool_size
對于大緩沖池的設置，假如不使用
MTS
，建議在
20—30M
足夠了。這部分主要用來保存并行查詢時候的一些信息，還有就是
RMAN
在備份的時候可能會使用到。如果設置了
MTS
，則由于
UGA
部分要移入這里，則需要具體根據
server process
數量和相關會話內存參數的設置來綜合考慮這部分大小的設置。
?
?
?
Java_pool_size
假如數據庫沒有使用
JAVA
，我們通常認為保留
10—20M
大小足夠。事實上可以更少，甚至最少只需要
32k
，但具體跟安裝數據庫的時候的組件相關
(
比如
http server)
。
?
?
?
shared_pool_size
這是迄今為止最具有爭議的一部分內存設置。按照很多文檔的描述，這部分內容應該幾乎和數據緩沖區差不多大小。但實際上情況卻不是這樣的。首先我們要考究一個問題，那就是這部分內存的作用，它是為了緩存已經被解析過的
SQL
，而使其能被重用，不再解析。這樣做的原因是因為，對于一個新的
SQL
（
shared_pool
里面不存在已經解析的可用的相同的
SQL
），數據庫將執行硬解析，這是一個很消耗資源的過程。而若已經存在，則進行的僅僅是軟分析（在共享池中尋找相同
SQL
），這樣消耗的資源大大減少。所以我們期望能多共享一些
SQL
，并且如果該參數設置不夠大，經常會出現
ora-04031
錯誤，表示為了解析新的
SQL
，沒有可用的足夠大的連續空閑空間，這樣自然我們期望該參數能大一些。但是該參數的增大，卻也有負面的影響，因為需要維護共享的結構，內存的增大也會使得
SQL
的老化的代價更高，帶來大量的管理的開銷，所有這些可能會導致
CPU
的嚴重問題。
在一個充分使用綁定變量的比較大的系統中，
shared_pool_size
的開銷通常應該維持在
300M
以內。除非系統使用了大量的存儲過程、函數、包，比如
oracle erp
這樣的應用，可能會達到
500M
甚至更高。于是我們假定一個
1G
內存的系統，可能考慮設置該參數為
100M
，
2G
的系統考慮設置為
150M,8G
的系統可以考慮設置為
200—300M
。
對于一個沒有充分使用或者沒有使用綁定變量系統，這可能給我們帶來一個嚴重的問題。所謂沒有使用
bind var
的
SQL
，我們稱為
Literal SQL
。也就是比如這樣的兩句
SQL
我們認為是不同的
SQL,
需要進行
2
次硬解析：
select * from EMP where name = ‘TOM’;
select * from EMP where name = ‘JERRY’;
假如把
’TOM’
和
‘JERRY’
換做變量
V
，那就是使用了
bind var
，我們可以認為是同樣的
SQL
從而能很好地共享。共享
SQL
本來就是
shared_pool_size
這部分內存存在的本意，
oracle
的目的也在于此，而我們不使用
bind var
就是違背了
oracle
的初衷，這樣將給我們的系統帶來嚴重的問題。當然，如果通過在操作系統監控，沒有發現嚴重的
cpu
問題，我們如果發現該共享池命中率不高可以適當的增加
shred_pool_size
。但是通常我們不主張這部分內存超過
800M
（特殊情況下可以更大）。
事實上，可能的話我們甚至要想辦法避免軟分析，這在不同的程序語言中實現方式有差異。我們也可能通過設置
session_cached_cursors
參數來獲得幫助（這將增大
PGA
）。
?
?
?
Data buffer
現在我們來談數據緩沖區，在確定了
SGA
的大小并分配完了前面部分的內存后，其余的，都分配給這部分內存。通常，在允許的情況下，我們都嘗試使得這部分內存更大。這部分內存的作用主要是緩存
DB BLOCK
，減少甚至避免從磁盤上獲取數據，在
8i
中是由
db_block_buffers*db_block_size
來決定大小的（包含
default
、
keep
、
recycle
）。如果我們設置了
buffer_pool_keep
和
buffer_pool_recycle
，這兩部分內存的大小包含在前面設置中（
db_block_buffers*db_block_size
）。
buffer_pool_keep
是用來取代
8i
版本以前的緩存頻繁小表于
LUR
的
MOST? USED
端的。通過開辟一段獨立的內存用于緩存頻繁的小表，在創建表的時候可以指定存儲參數，或者也可以動態修改表的存儲參數（
alter table t storage(buffer_pool ?keep);
）。
Buffer_pool_recycle
作為一塊單獨開辟出來的內存，主要用于很少執行的大表全表掃描的查詢，使得這些大表掃描不會影響到
default
里面
LRU
而沖擊整個數據庫緩沖區的性能。雖然這樣有可能降低大表的全表掃描的性能，但是保護了整體性能不間歇性的受到較大的沖擊。同樣，除了設置參數外還需要在創建表的過程中使用存儲參數或者動態修改表的存儲參數（
alter table t storage(buffer_pool? recycle);
）
?
?
?
l??????
9i
下參數的變化
oracle
的版本的更新，總是伴隨著參數的變化，并且越來越趨向于使得參數的設置更簡單，因為復雜的參數設置使得
DBA
們經常焦頭爛額。關于內存這部分的變化，我們可以考察下面的參數。事實上在
9i
中數據庫本身可以給出一組適合當前運行系統的
SGA
相關部分的參數調整值（參考
V$DB_CACHE_ADVICE
、
V$SHARED_POOL_ADVICE
），關于
PGA
也有相關視圖
V$PGA_TARGET_ADVICE
等。
?
?
?
Data buffer
9i
中保留了
8i
中的參數，如設置了新的參數，則忽略舊的參數。
9i
中用
db_cache_size
來取代
db_block_buffers
，用
db_keep_cache_size
取代
buffer_pool_keep,
用
db_recycle_cache_size
取代
buffer_pool_recycle
；這里要注意
9i
中設置的是實際的緩存大小而不再是塊的數量。另外
9i
新增加了
db_nk_cache_size
，這是為了支持在同一個數據庫中使用不同的塊大小而設置的。對于不同的表空間，可以定義不同的數據塊的大小，而緩沖區的定義則依靠該參數的支持。其中
n
可以為
2
、
4
、
6
、
8
、
16
等不同的值。在這里順便提及的一個參數就是
db_block_lru_latches
，該參數在
9i
中已經成為了保留參數，不推薦手工設置。
?
?
?
PGA
???
在
9i
里面這部分也有了很大的變化。在獨立模式下，
9i
已經不再主張使用原來的
UGA
相關的參數設置，而代之以新的參數。假如
workarea_size_policy=AUTO
（缺省），則所有的會話的
UGA
共用一大塊內存，該內存在
pga_aggregate_target
設置以內分配。在我們根據前面介紹的方法評估了所有進程可能使用的最大
PGA
內存之后，我們可以通過在初始化參數中設置這個參數，從而不再關心其他
”*_area_size”
參數。
?
?
?
SGA_MAX_SIZE
在
9i
中若設置了
SGA_MAX_SIZE
，則在總和小于等于這個值內，可以動態的調整數據緩沖區和共享池的大小
SQL> show parameters sga_max_size
?
?
?
NAME???????????????????????????????? TYPE??? VALUE
------------------------------------ ------- -------------
sga_max_size
???????????????????????? unknown 193752940
SQL>
?
?
?
SQL>? alter system set db_cache_size = 30000000;
?
?
?
System altered.
?
?
?
SQL>? alter system set shared_pool_size = 20480000;
?
?
?
System altered.
?
?
?
l??????
Lock_sga = true
的問題
由于幾乎所有的操作系統都支持虛擬內存，所以即使我們使用的內存小于物理內存，也不能避免操作系統將
SGA
換到虛擬內存（
SWAP
）。所以我們可以嘗試使得
SGA
鎖定在物理內存中不被換到虛擬內存中，這樣減少頁面的換入和換出，從而提高性能。但在這里遺憾的是，
windows
是無法避免這種情況的。下面我們來參考在不同的幾個系統下怎么實現
lock_sga
?
?
?
AIX 5L（AIX 4.3.3以上）
?
?
?
logon aix as root
cd /usr/samples/kernel
./vmtune (信息如下) v_pingshm已經是1
./vmtune -S 1
然后oracle用戶修改initSID.ora 中 lock_sga = true
重新啟動數據庫
?
?
?
HP UNIX
?
?
?
Root身份登陸
Create the file "/etc/privgroup":? vi /etc/privgroup
Add line "dba MLOCK" to file
As root, run the command "/etc/setprivgrp -f /etc/privgroup":
$/etc/setprivgrp -f /etc/privgroup
oracle用戶修改initSID.ora中lock_sga=true
重新啟動數據庫
?
?
?
SOLARIS (solaris2.6
以上
)
8i版本以上數據庫默認使用隱藏參數 use_ism = true ，自動鎖定SGA于內存中,不用設置lock_sga,
如果設置 lock_sga =true 使用非 root 用戶啟動數據庫將返回錯誤。
WINDOWS
不能設置
lock_sga=true,
可以通過設置
pre_page_sga=true,
使得數據庫啟動的時候就把所有內存頁裝載，這樣可能起到一定的作用。
?
?
?
?
?
?
?
?
?
l??????
關于內存參數的調整
關于參數調整，是
oracle
的復雜性的一個具體體現。通常來講，我們更傾向于讓客戶做
statspack
報告，然后告訴我們
os
監控的狀況，在這些的信息的基礎上，再向客戶索取具體的詳細信息以診斷問題的所在。系統的調整，現在我們通常采用從等待事件入手的方法。因為一個系統感覺到慢，必然是在某個環節上出現等待，那么我們從等待最多的事件入手逐步診斷并解決問題。
對于內存的調整，相對來說簡單一些，我們首先可以針對數據緩沖區的大小來看。首先觀察命中率
?
?
?
數據緩沖區命中率
SQL> select value from? v$sysstat where name ='physical reads';
???? VALUE
----------
???? 14764
SQL>? select value from? v$sysstat where name ='physical reads direct';
???? VALUE
----------
??????? 50
SQL> select value from? v$sysstat where name ='physical reads direct (lob)';
???? VALUE
----------
???????? 0
SQL> select value from? v$sysstat where name ='consistent gets';
???? VALUE
----------
167763
?
?
?
SQL> select value from v$sysstat where name = 'db block gets';
?
?
?
???? VALUE
----------
???? 14305
這里命中率的計算應該是
令 x = physical reads direct + physical reads direct (lob)
命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100
通常如果發現命中率低于
90%,
則應該調整應用可可以考慮是否增大數據緩沖區
?
?
?
?
?
?
共享池的命中率
?
?
?
SQL> select sum(pinhits)/sum(pins)*100 "hit radio" from v$librarycache;
?
?
?
?hit radio
----------
?99.809291
?
?
?
假如共享池的命中率低于
95%,
就要考慮調整應用（通常是沒使用
bind var
）或者增加內存
?
?
?
?
?
?
關于排序部分
SQL>? select name,value from v$sysstat where name like '%sort%';
NAME????????????????????????????????????????????????????????????????? VALUE
---------------------------------------------------------------- ----------
sorts (memory)??????????????????????????????????????????????????????? 67935
sorts (disk)????????????????????????????????????????????????????????????? 1
sorts (rows)???????????????????????????? ??????????????????????????????7070
?
?
?
SQL>
假如我們發現
sorts (disk)/ (sorts (memory)+ sorts (disk))
的比例過高，則通常意味著
sort_area_size
部分內存較小，可考慮調整相應的參數。
?
?
?
關于log_buffer
SQL>? select name,value from v$sysstat
? 2??? where name in('redo entries','redo buffer allocation retries');
?
?
?
NAME????????????????????????????? ????????????????????????????????????VALUE
---------------------------------------------------------------- ----------
redo entries??????????????????????????????????????????????????????? 2325719
redo buffer allocation retries??????????????????????????????? ???????????10
?
?
?
假如
redo buffer allocation retries/ redo entries
的比例超過
1%
我們就可以考慮增大
log_buffer
?
?
?
通常來說，內存的調整的焦點就集中在這幾個方面，更多更詳細的內容，建議從
statspack
入手來一步一步調整。最后關于內存的調整，再強調這一點，一定要結合操作系統來衡量，任何理論都必須要實踐來檢驗。在操作系統中觀察
page? in/out
狀況，發現問題嚴重，應該考慮調小
SGA
。
?
?
?
?
?
?
l??????
32bit
和
64bit
的問題
對于
oracle
來說，存在著
32bit
與
64bit
的問題。這個問題影響到的主要是
SGA
的大小。在
32bit
的數據庫下，通常
oracle
只能使用不超過
1.7G
的內存，即使我們擁有
12G
的內存，但是我們卻只能使用
1.7G
，這是一個莫大的遺憾。假如我們安裝
64bit
的數據庫
,
我們就可以使用很大的內存，幾乎不可能達到上限。但是
64bit
的數據庫必須安裝在
64bit
的操作系統上，可惜目前
windows
上只能安裝
32bit
的數據庫，我們通過下面的方式可以查看數據庫是
32bit
還是
64bit
：
?
?
?
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
???
但是在特定的操作系統下，可能提供了一定的手段，使得我們可以使用超過
1.7G
的內存，達到
2G
以上甚至更多。
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=497173