【DB筆試面試527】在Oracle中，記憶體結構主要由什麼組成?
- 2019 年 10 月 10 日
- 筆記
♣
題目部分
在Oracle中，記憶體結構主要由什麼組成?
♣
答案部分
Oracle記憶體結構主要分共享記憶體區與非共享記憶體區，共享記憶體區主要包含SGA（System Global Area，系統全局區），非共享記憶體區主要由PGA（Program Global Area，程式全局區）組成，如下圖所示：
圖 3-5 Oracle實例結構
（一）SGA介紹
SGA（System Global Area，系統全局區）是Oracle實例的基本組成部分，是Oracle為一個實例分配的一組共享記憶體緩衝區，保存著Oracle系統與所有資料庫用戶的共享資訊，包括數據維護、SQL語句分析、Redo日誌管理等。SGA是實例的主要部分，它在實例啟動時分配。
SGA是動態的，由參數SGA_MAX_SIZE決定。查看當前系統的SGA可以使用的最大記憶體大小的命令是：SHOW PARAMETER SGA_MAX_SIZE。修改SGA大小的命令是：ALTER SYSTEM SET SGA_MAX_SIZE=1200M SCOPE=SPFILE。因為實例記憶體的分配是在資料庫啟動時進行的，所以，要讓修改生效，必須重啟資料庫。當Oracle運行在32位Linux上時，其默認SGA無法超過1.7GB。
在Oracle 10g中引入了ASMM（Automatic Shared Memory Management，自動共享記憶體管理），DBA只需設置SGA_TARGET，Oracle就會自動地對共享池、數據緩衝區、Redo日誌緩衝區、大池、Java池和流池進行自動調配，取消自動調配的方法為設置SGA_TARGET為0。
需要注意的是，Oracle分配記憶體的單位是granule，即粒度。最小的粒度為4M，設置大小不到一個粒度按一個粒度計算。在32位作業系統的平台上，粒度的最大值為16M。粒度的大小在資料庫實例周期內不能被修改。按照粒度為單位分配的組件包括：Shared Pool、Buffer Cache（以及不同大小塊的Buffer Cache）、Redo Log Buffer、Java Pool、Streams Pool和Large Pool。粒度的大小參考下表：
|
資料庫版本 |
SGA_MAX_SIZE或MEMORY_MAX_TARGET大小 |
粒度大小（GRANULE SIZE） |
|---|---|---|
|
9.2 |
<= 128MB |
4MB |
|
> 128MB |
16MB |
|
|
10.2 |
<= 1GB |
4MB |
|
> 1GB |
16MB |
|
|
11gR1 |
<= 1GB |
4MB |
|
>1Gb <= 4GB |
16MB |
|
|
>4Gb <= 16GB |
64MB |
|
|
>16Gb <= 64GB |
256MB |
|
|
> 64GB |
512MB |
|
|
11gR2、打了patch號為8813366的11gR1及其之後的版本 |
<= 1GB |
4MB |
|
>1GB <= 8GB |
16MB |
|
|
>8GB <= 16GB |
32MB |
|
|
>16GB <= 32GB |
64MB |
|
|
>32GB <= 64GB |
128MB |
|
|
>64GB <= 128GB |
256MB |
|
|
> 128GB |
512MB |
通過視圖V$SGAINFO可以查詢當前SGA分配的粒度大小，如下所示：
SYS@orclasm > SELECT * FROM V$SGAINFO WHERE NAME='Granule Size'; NAME BYTES RES -------------------------------- ---------- --- Granule Size 4194304 No
下面將對SGA的各個組成部分進行介紹。
（1）共享池（Shared Pool）：快取了各用戶間可共享的各種結構，例如，快取最近被執行的SQL語句和最近被使用的數據定義。共享池主要包括：庫快取（Library Cache）、數據字典快取（Data Dictionary Cache）、保留池（Reserved Pool）和結果快取（Result Cache）。
Ø 庫快取（Library Cache）是存放用戶SQL命令、解析樹和執行計劃的區域。對於庫快取來說，具體包含以下幾個部分：
l 共享SQL區（Shared SQL Area）：保存了SQL語句文本，編譯後的語法分析樹及執行計劃。查看共享SQL區的使用率命令為：SELECT(SUM(PINS-RELOADS))/SUM(PINS) "LIBRARY CACHE" FROM V$LIBRARYCACHE;。
l 私有SQL區（Private SQL Area）：包含當前會話的綁定資訊以及運行時記憶體結構。每個發出SQL語句的會話，都有一個Private SQL Area。當多個用戶執行相同的SQL語句，此SQL語句保存在共享SQL區。若是共享伺服器模式，則Private SQL Area位於SGA的Share Pool或Large Pool中。若是專用伺服器模式，則Private SQL Area位於PGA中。
l 共享PL/SQL區（Shared PL/SQL Area）：保存了分析與編譯過的PL/SQL塊（存儲過程、函數、包、觸發器和匿名PL/SQL塊）。
l 控制結構區（Control Structure Area）：保存鎖等控制資訊。
Ø 數據字典快取（Data Dictionary Cache）存放資料庫運行的動態資訊，例如，表和列的定義，數據字典表的許可權。查看數據字典緩衝區使用率的SQL為：「SELECT (SUM(GETS-GETMISSES-USAGE-FIXED))/SUM(GETS) "DATA DICTIONARY CACHE" FROM V$ROWCACHE;」。
Ø 保留池（Reserved Pool）也叫保留區域（Reserved Area），是指Shared Pool中配置的一個記憶體保留區域，這個保留區域用做當在普通的Shared Pool列表中的空間不能用來滿足Large Request的記憶體分配請求而分配大塊的連續記憶體塊。當一個記憶體請求大於隱含參數「_SHARED_POOL_RESERVED_MIN_ALLOC」（默認：4400 bytes，如果系統經常出現ORA-04031錯誤，基本上都是請求大於4400的記憶體塊，那麼就可能需要增加SHARED_POOL_RESERVED_SIZE參數設置。）的值時就是一個Large Request，反之當記憶體請求小於「_SHARED_POOL_RESERVED_MIN_ALLOC」時就是一個Small Request。另外關於Reserved Pool還有兩個參數需要關注一下，一個是SHARED_POOL_RESERVED_SIZE，另外一個是隱含參數「_SHARED_POOL_RESERVED_PCT」（默認：5%）。通過SHARED_POOL_RESERVED_SIZE可以為Reserved Pool指定一個大小，也可以通過「_SHARED_POOL_RESERVED_PCT」來為Shared Pool指定一個比例。如果這兩個參數同時設置了，那麼就會以「_SHARED_POOL_RESERVED_PCT」為準。參數SHARED_POOL_RESERVED_SIZE的預設值是SHARED_POOL_SIZE的5％，最小值為5000bytes，最大不得超過SHARED_POOL_SIZE的50％。通過視圖V$SHARED_POOL_RESERVED可以查到保留池的統計資訊。其中欄位REQUEST_MISSES記錄了沒有立即從空閑列表中得到可用的大記憶體段請求次數，這個值理想狀態下要為0。當REQUEST_FAILURES大於0時，則需要增加SHARED_POOL_SIZE和SHARED_POOL_RESERVED_SIZE的空間。當REQUEST_MISS等於0，或是FREE_MEMORY大於等於SHARED_POOL_RESERVED_SIZE的空間時，則增加SHARED_POOL_RESERVED_SIZE的空間。MAX_USED_SPACE欄位可以用來判斷保留池的大小是否合適。保留區使用Shared Pool的LRU鏈表來管理記憶體塊。可以通過如下的SQL語句來查詢保留池的命中率（Hit Ratio），查詢語句如下：
SELECT (REQUEST_MISSES / (REQUESTS + 0.0001)) * 100 "REQUEST MISSES RATIO", (REQUEST_FAILURES / (REQUESTS + 0.0001)) * 100 "REQUEST FAILURES RATIO" FROM V$SHARED_POOL_RESERVED;
以上結果應該都要小於1%，如果大於1，那麼應該考慮加大SHARED_POOL_RESERVED_SIZE。
Ø 結果快取（Result Cache）是存放SQL查詢結果和PL/SQL函數查詢結果的區域。
共享池的大小由參數SHARED_POOL_SIZE決定。只要將初始化參數STATISTICS_LEVEL設置為TYPICAL（默認值）或ALL，就能啟動對Shared Pool的建議功能，如果設置為BASIC，則關閉建議功能。使用如下的SQL語句可以查詢到Oracle所建議的Shared Pool的大小：
SYS@orclasm > SELECT SHARED_POOL_SIZE_FOR_ESTIMATE, 2 ESTD_LC_SIZE, 3 ESTD_LC_MEMORY_OBJECTS, 4 ESTD_LC_TIME_SAVED, 5 ESTD_LC_TIME_SAVED_FACTOR, 6 ESTD_LC_MEMORY_OBJECT_HITS 7 FROM V$SHARED_POOL_ADVICE; SHARED_POOL_SIZE_FOR_ESTIMATE ESTD_LC_SIZE ESTD_LC_MEMORY_OBJECTS ESTD_LC_TIME_SAVED ESTD_LC_TIME_SAVED_FACTOR ESTD_LC_MEMORY_OBJECT_HITS ----------------------------- ------------ ---------------------- ------------------ ------------------------- -------------------------- 180 9 596 15816 .9874 244830 184 13 876 15879 .9913 247845 188 17 996 15910 .9933 249132 192 21 1191 15937 .9949 249697 196 25 1329 15944 .9954 250167 200 29 1447 16018 1 254285 204 33 1630 16033 1.0009 255345 208 37 1810 16041 1.0014 255949 212 40 1999 16068 1.0031 256701 216 43 2209 16069 1.0032 257237 220 46 2428 16083 1.0041 257685 224 50 2549 16088 1.0044 258030 228 53 2639 16089 1.0044 258234 232 57 2833 16092 1.0046 258457 236 61 2985 16095 1.0048 258802 240 65 3061 16097 1.0049 259258 260 85 3709 16118 1.0062 259946 280 104 4411 16142 1.0077 260564 300 124 5084 16157 1.0087 260859 320 143 5990 16166 1.0092 260956 340 162 7155 16174 1.0097 260999 360 162 7155 16174 1.0097 261001 380 162 7155 16174 1.0097 261001 400 162 7155 16174 1.0097 261001
|
|
|---|
第一列表示Oracle所估計的Shared Pool的尺寸值，其他列表示在該估計的Shared Pool大小下所表現出來的指標值。可以主要關注ESTD_LC_TIME_SAVED_FACTOR列的值，當該列值為1時，表示再增加Shared Pool的大小對性能的提高沒有意義。對於上例來說，當Shared Pool為200MB時，達到最佳大小。對於設置比200MB更大的Shared Pool來說，就是浪費空間，沒有更多的好處了。
（1）數據緩衝區（Database Buffer Cache）：也叫資料庫緩衝區高速快取，用於快取從數據文件中檢索出來的數據塊，可以大大提高查詢和更新數據的性能，是資料庫實例的重要組成部分。參數DB_CACHE_SIZE可指定數據緩衝區的大小，需要在參數文件中靜態修改。Oracle在處理某個查詢時，伺服器進程會在Buffer Cache中查找它所需的所有數據塊。如果未在Buffer Cache中找到所需要的數據塊，那麼伺服器進程會從數據文件中讀取所需的數據塊，並在Buffer Cache中添加一個副本。因為關於同一數據塊的後續請求可能會在記憶體中找到該數據塊，因此，這些請求可能不需要進行物理讀操作。Buffer Cache中的記憶體塊有4種狀態：
① Pinned：當前塊正在被某個進程讀取到Cache或正寫到磁碟，即當前正在被訪問的數據塊，可防止多個會話同時對同一數據塊進行寫操作。此時，其他會話正等待訪問該塊。
② Clean：伺服器進程從數據文件中讀入的Block且還沒有被其它進程所修改或者後台進程DBWn將Dirty Buffer寫入到數據文件中的Buffer，該Buffer中的內容與數據文件中的Block一致。該狀態的數據塊是可以立即被移出的候選數據塊。
③ Free/Unused：Buffer內為空，為實例剛啟動時的狀態。Buffer Cache初始化時或者在執行alter system flush buffer_cache以後的Buffer，該Buffer中沒有存放任何內容。此狀態與「clean」狀態非常相似，不同之處在於「free/unused」狀態的緩衝區尚未使用。
④ Dirty：臟數據，數據塊已被修改，需要先被DBWn刷新到磁碟，才能執行過期處理（移出緩衝區）。在該狀態下，該Buffer的內容與數據文件中Block的內容不一致。
圖 3-6 Database Buffer Cache中數據塊的狀態轉變
資料庫高速緩衝區的主要功能是用來暫時存放最近讀取自資料庫中的數據，也就是數據文件（Data File）內的數據，而數據文件是以數據塊（Block）為單位，因此，資料庫高速緩衝區中的大小是以塊為基數。當用戶通過應用程式第一次向Oracle資料庫發出查詢請求時，Oracle會先在Buffer Cache內尋找該數據，如果有該請求所需要的數據，那麼就直接從Buffer Cache傳回給用戶，這稱為快取命中（Cache Hit），這樣就可以減少硬碟上的I/O次數。如果Oracle發現用戶要的數據並不在Buffer Cache里，就稱為快取失誤（Cache Miss），Oracle會從資料庫中讀取所需要的數據塊，先放入Buffer Cache中，再傳送給用戶。該區域內的數據塊通過LRU（Least Recently Used，最近最少使用）演算法管理。LRU將Buffer Cache中的所有的Clean和Free狀態的Buffer按照它們被讀取的頻率連接起來。（冷端：最少使用的；熱端：最常被使用的；在伺服器進程將磁碟的Block讀取到Buffer Cache時，會先覆蓋冷端的Buffer。）。
Buffer Cache可以分為多個緩衝池：
① 回收池（Recycle Pool）：放到回收池中的數據，只要空間不夠用，它們馬上就會被釋放出來，即回收池中的數據會最先被替換出記憶體，很少使用的數據放在該區。被放在回收池中的數據塊不會被反覆使用。也就是說，這些數據塊只在事務（Transaction）還存在時才會被用到，一旦事務結束，就會被釋放出來。回收池的大小最好是默認池的1/2，通過DB_RECYCLE_CACHE_SIZE參數指定回收池的大小。該快取不參與ASMM的動態管理，不能自動調整大小。默認未啟用，大小為0。手工修改指定值後，Default Pool的空間將被相應的減少。
② 保留池（Keep Pool）：當數據被放到保留池裡時，就代表這個數據是需要常常被重複使用的。保留池中的數據不會被替換出去，可以將常用的小表放置在該區可以降低I/O操作。可以通過DB_KEEP_CACHE_SIZE參數指定保留池的大小。該區域的大小不會被ASMM自動調節。默認未啟用，大小為0，當手工修改指定該值後，Default Pool的空間將被相應的減少。
③ 默認池（Default Pool）：當沒有指定對象存儲的緩衝池時，數據就會放在默認池中，相當於一個沒有Keep與Recycle池的實例的Buffer Cache。也就是說，放在默認池的數據利用的是LRU機制。通過DB_CACHE_SIZE參數指定默認池的大小。
BUFFER_POOL子句可以在對象的STORAGE子句中為對象指定使用具體的Buffer Pool。如果現有對象沒有明確指定Buffer Pool，那麼默認都指定為Default Buffer Pool。可以使用CREATE或ALTER語句指定對象存儲的緩衝池：
CREATE INDEX CUST_IDX ON TT(ID) STORAGE (BUFFER_POOL KEEP); ALTER TABLE OE.CUSTOMERS STORAGE (BUFFER_POOL RECYCLE); ALTER INDEX OE.CUST_LNAME_IX STORAGE (BUFFER_POOL KEEP);
在同一個資料庫中，支援多種大小的數據塊快取。通過DB_nK_CACHE_SIZE參數指定，如：
l DB_CACHE_SIZE（指定標準塊（這裡為8K）的快取區）
l DB_2K_CACHE_SIZE（指定塊大小為2K的快取區）
l DB_4K_CACHE_SIZE（指定塊大小為4K的快取區）
l DB_16K_CACHE_SIZE（指定塊大小為16K的快取區）
l DB_32K_CACHE_SIZE（指定塊大小為32K的快取區）
標準塊緩衝區大小由DB_CACHE_SIZE指定。如標準塊為nK，則不能通過DB_nK_CACHE_SIZE來指定標準塊緩衝區的大小，應由DB_CACHE_SIZE指定。
當資料庫高速緩衝區需要讀取或寫回數據到數據文件中時，都需要通過DBWn這個後台進程來協助處理，而參數DB_WRITER_PROCESSES主要設置要由幾個DBWn來協助處理。在此建議不要超過系統CPU的個數，如果設置的值超過了CPU的個數，那麼超過的那些是無法起作用的。
當參數DB_CACHE_ADVICE設置為ON（當STATISTICS_LEVEL為TYPICAL或ALL時，DB_CACHE_ADVICE參數值默認為ON）時，表示開啟DB_CACHE_ADVICE功能。當開啟參數DB_CACHE_ADVICE後，經過一段時間，Oracle就會自動收集足夠的相關統計數據，並預測出DB_CACHE_SIZE在不同大小情況下的性能數據，而這些數據就是通過V$DB_CACHE_ADVICE視圖來顯示的，因此，可以根據這些數據對DB_CACHE_SIZE做相關的調整，以達到最佳情況。
對視圖V$DB_CACHE_ADVICE的各列介紹如下表所示：
|
欄位名 |
數據類型 |
說明 |
|---|---|---|
|
ID |
NUMBER |
不同資料庫高速緩衝區的編號，一般來說，DB_CACHE_SIZE的編號是3 |
|
NAME |
VARCHAR2(20) |
資料庫高速緩衝區的名稱(Default、Keep、Recycle) |
|
BLOCK_SIZE |
NUMBER |
數據塊的大小(單位是K) |
|
ADVICE_STATUS |
VARCHAR2(3) |
開啟狀態：ON代表開啟，OFF代表關閉 |
|
SIZE_FOR_ESTIMATE |
NUMBER |
預測性能的Cache大小(以M為單位) |
|
SIZE_FACTOR |
NUMBER |
預測的Cache大小比例，也就是與目前大小的比例 |
|
BUFFERS_FOR_ESTIMATE |
NUMBER |
預測性能數據的數據塊個數 |
|
ESTD_PHYSICAL_READ_FACTOR |
NUMBER |
在資料庫高速緩衝區里物理讀取的因子，也就是說，當資料庫高速緩衝區大小為SIZE_FOR_ESTIMATE此欄位時，DB_CACHE_ADVICE預測的物理讀數與當前物理讀數的比率值。如果當前物理讀數為0，那麼，這個值為空 |
|
ESTD_PHYSICAL_READS |
NUMBER |
當資料庫高速緩衝區大小為SIZE_FOR_ESTIMATE時，DB_CACHE_ADVICE預測的實際讀數 |
|
ESTD_PHYSICAL_READ_TIME |
NUMBER |
當前物理讀取的時間 |
|
ESTD_PCT_OF_DB_TIME_FOR_READS |
NUMBER |
當前物理讀取的時間占所有時間的比例 |
查詢視圖V$DB_CACHE_ADVICE如下所示：
SYS@orclasm > SELECT ADVICE_STATUS, 2 SIZE_FOR_ESTIMATE, 3 ESTD_PHYSICAL_READ_FACTOR, 4 ESTD_PHYSICAL_READS 5 FROM V$DB_CACHE_ADVICE 6 WHERE NAME = 'DEFAULT'; ADV SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS --- ----------------- ------------------------- ------------------- ON 4 1.028 4028357 ON 8 1.0223 4006098 ON 12 1.0178 3988551 ON 16 1.0109 3961532 ON 20 1.0068 3945138 ON 24 1.0052 3939208 ON 28 1.0045 3936105 ON 32 1.004 3934171 ON 36 1.0033 3931620 ON 40 1.0023 3927591 ON 44 1.0013 3923578 ON 48 1 3918655 ON 52 .9992 3915665 ON 56 .9988 3913960 ON 60 .9984 3912432 ON 64 .9982 3911506 ON 68 .998 3910840 ON 72 .9979 3910450 ON 76 .9978 3910125 ON 80 .9925 3889182
由以上結果可以知道，當ESTD_PHYSICAL_READ_FACTOR為1時，最佳的DB_BUFFER_SIZE是48，因為在這之後再進行調整對降低I/O的影響有限，所以該系統最佳的Buffer Cache的大小為48M。
（1）Redo日誌緩衝區（Redo Log Buffer）：對資料庫進行修改的任何事務（Transaction）在記錄到Redo日誌文件之前都必須首先放到Redo日誌緩衝區中。Redo日誌緩衝區中的內容將被後台進程LGWR寫入聯機Redo日誌文件（Online Redo Log Files）中。Redo日誌緩衝區是一個循環快取區，在使用時從頂端向底端寫入數據，然後再返回到緩衝區的起始點循環寫入。Oracle中所有的DML和DDL操作都會記錄日誌，即便沒有提交的DML操作也會記錄日誌，在指定了NOLOGGING時，也會記錄一些日誌。Redo日誌緩衝區大小由參數LOG_BUFFER決定，需要在參數文件中靜態修改。伺服器進程（Server Process）及後台進程（Background Process）對Oracle的變更記錄會寫到Redo日誌緩衝區，這些變更的數據都在記憶體中的Redo日誌緩衝區中以Redo Entry（重做條目，也可稱為Redo Record）的方式存儲。Redo Entry是Oracle從用戶會話佔用的記憶體里將這些變更的記錄複製到Redo日誌緩衝區內，其在記憶體中是一段連續的記憶體塊，Oracle利用後台進程LGWR在適當的時機將Redo日誌緩衝區中的資訊（也就是Redo Entry）寫回到聯機Redo日誌文件內，以便萬一資料庫崩潰，可以進行必要的恢復。後台進程LGWR將Redo Entry寫回到聯機Redo日誌文件的時機如下：
① 用戶發出提交命令（COMMIT）
② 每隔3秒
③ Redo日誌緩衝區空間剩餘不到2/3
④ Redo日誌緩衝區內的數據達到1MB
⑤ 在發生聯機Redo日誌切換（Log Switch）時
⑥ 在DBWn進程將修改的緩衝區寫入磁碟時（如果相應的Redo日誌數據尚未寫入磁碟）
（2）大池（Large Pool）：SGA中一個可選的記憶體區域，大池用來分配大塊的記憶體，處理比共享池更大的記憶體，用來緩解Shared Pool的負擔。大池主要用在3種情況下，①若是共享伺服器模式時，則在Large Pool中分配UGA，若Large Pool沒有分配則在Shared Pool中分配。若是專用伺服器（多執行緒伺服器MTS，Multi-Threaded Server）連接，則UGA在PGA中創建；②語句的並行查詢（Parallel Executeion of Statements），允許進程間消息緩衝區的分配，用來協調並行查詢伺服器；③恢復管理器RMAN，用於RMAN磁碟I/O緩衝區。大池的大小由參數LARGE_POOL_SIZE決定，可以動態修改。大池也使用共享池的閂鎖機制，但和共享池不同的是，大池並不使用LRU機制，而是使用Large Memory Latch的保護，因此，大池中緩衝區內的數據不會被置換出來。大池內的數據會利用用戶的會話來控制分配和釋放大池的空間。如果大池的空間不足，那麼也會出現ORA-04031錯誤。
（3）Java池（Java Pool）：為Java命令的語法分析提供服務。Java池也是SGA中的一塊可選記憶體塊，大小由參數JAVA_POOL_SIZE決定。在Oracle 10g以後，提供了一個新的Java池的建議功能，以輔助調整Java池的大小，而建議的統計數據可以通過視圖V$JAVA_POOL_ADVICE來查詢。
（4）流池（Streams Pool）：被Oracle流所使用，主要提供專門的Streams複製功能，流池是可選用記憶體塊，它也屬於SGA中的可變區域。參數STREAMS_POOL_SIZE可以指定流池的大小。如果設置為0，那麼當第一次使用Streams複製功能時，Oracle會自動建立此塊區域，而自動建立的大小為共享池大小的10%。Oracle也提供了一個流池的建議功能，來協助調整流池的大小，而建議的統計數據可以通過視圖V$STREAMS_POOL_ADVICE來查詢。
（一）PGA介紹
PGA（Program Global Area，程式全局區）是單個Oracle進程使用的記憶體區域，為每個連接到Oracle資料庫的用戶進程保留的記憶體，不屬於實例的記憶體結構。它含有單個進程工作時需要的數據和控制資訊。PGA是非共享的，只有服務進程本身才能夠訪問它自己的PGA區。PGA在進程創建時分配，進程結束時釋放。PGA的內容隨伺服器的模式（專用模式/共享伺服器模式）不同而不同。PGA的大小由參數PGA_AGGREGATE_TARGET決定，可動態修改。
圖 3-7 PGA結構圖
PGA有如下幾個組件：
① Private SQL Area（私有SQL區）：參考Shared Pool部分的介紹。
② Cursor and SQL Areas（游標和SQL區）：Oracle Pro*C程式（Pro*C是Oracle提供的應用程式專用開發工具，它以C語言為宿主語言，能在C程式中嵌入SQL語句，進行資料庫操作。）的應用程式開發人員或Oracle調用介面（Oracle Call Interface，OCI）程式可以顯式打開游標或處理私有SQL區。
③ Session Memory（會話記憶體）：保存會話的變數（例如，登錄資訊）及其他與會話相關的資訊。在共享伺服器模式下，Session Memory是共享的。
④ Work Area（工作區）：PGA的一大部分被分配給Work Area，用來執行如下操作：
l 基於排序的操作，GROUP BY、ORDER BY、ROLLUP和窗口函數。由於排序需要記憶體空間，Oracle利用該記憶體排序數據，這部分空間稱為排序區。排序區存在於請求排序的用戶進程的記憶體中，該空間的大小為適應排序數據量的大小，可增長，但受參數SORT_AREA_SIZE所限制。
l HASH連接，大小受參數HASH_AREA_SIZE所限制
l 點陣圖合併，大小受參數BITMAP_MERGE_AREA_SIZE所限制
l 點陣圖創建，大小受參數CREATE_BITMAP_AREA_SIZE所限制
l 批量裝載操作使用的寫快取
PGA和SGA最明顯的差別在於，PGA不是共享記憶體，是私有不共享的。用戶對資料庫發起的無論查詢還是更新的任何操作，都是在PGA先預處理，然後接下來才進入實例區域，由SGA和系列後台進程共同完成用戶發起的請求。PGA起到的具體作用主要有三點：第一，保存用戶的連接資訊，如會話屬性、綁定變數等；第二，保存用戶許可權等重要資訊，當用戶進程與資料庫建立會話時，系統會將這個用戶的相關許可權查詢出來，然後保存在這個會話區內；第三，當發起的指令需要排序的時候，PGA正是這個排序區，如果在記憶體中可以放下排序的尺寸，就在記憶體PGA區內完成，如果放不下，超出的部分就在臨時表空間中完成排序，也就是在磁碟中完成排序。
（二）自動PGA記憶體管理（Automatic PGA Memory Management）
從Oracle9i開始，Oracle引入了PGA自動管理的特性。若設置參數PGA_AGGREGATE_TARGET為非0，則表示啟用PGA記憶體自動管理，並忽略所有*_AREA_SIZE的設置，例如SORT_AREA_SIZE、HASH_AREA_SIZE等。默認為啟用PGA的自動管理，Oracle根據SGA的20%來動態調整PGA中專用於Work Area部分的記憶體大小，最小為10MB。
設置WORKAREA_SIZE_POLICY參數，可以在PGA自動（AUTO，默認是AUTO）和PGA手動管理（MANUAL）之間進行選擇，然後通過設置初始化參數PGA_AGGREGATE_SIZE來設置PGA的記憶體總和。如果設置參數WORKAREA_SIZE_POLICY為MANUAL（默認值是AUTO），就代表此資料庫的PGA管理模式屬於手動管理模式，且在此模式下必須設置SORT_AREA_SIZE、HASH_AREA_SIZE等相關參數。需要注意的是，在Oracle 9i時，PGA自動管理只對Dedicate Server有效，對Shared Server無效，但是從Oracle 10g開始，PGA自動管理都有效。
對於OLTP系統，典型的PGA記憶體為：
PGA_AGGREGATE_SIZE = (total_memory * 80%) * 20%
對於OLAP系統，由於會運行一些很大的查詢：
PGA_AGGREATE_SIZE = (total_memoery * 80%) * 50%
80%是指，將機器總記憶體的80%分給Oracle使用。然後再將80%中的20%給PGA。
可以使用PGA相關的一些視圖來調整PGA_AGGREGATE_SIZE的大小，例如：V$PGASTAT、V$PGA_TARGET_ADVICE、V$PGA_TARGET_ADVICE_HISTOGRAM等。當自動PGA記憶體管理功能打開後，可以從V$PGA_TARGET_ADVICE中得到相關的指導數據，進而評估PGA_AGGREGATE_TARGE是否需要調整。該視圖的ESTD_OVERALLOC_COUNT列表示需要額外分配的PGA記憶體，如果此數值不是0，就表示PGA_AGGREGATE_TARGE設置得太小，需要調整。
SQL> SELECT PGA_TARGET_FOR_ESTIMATE / 1024 / 1024 PGAMB, 2 PGA_TARGET_FACTOR P_TR_FCT, 3 ESTD_PGA_CACHE_HIT_PERCENTAGE E_P_C_HIT_PRCT, 4 ESTD_OVERALLOC_COUNT E_OR_CNT 5 FROM V$PGA_TARGET_ADVICE; PGAMB P_TR_FCT E_P_C_HIT_PRCT E_OR_CNT ---------- ---------- -------------- ---------- 23.75 .125 82 1179 47.5 .25 82 1179 95 .5 95 233 142.5 .75 99 6 190 1 99 1 228 1.2 99 0 266 1.4 99 0 304 1.6 99 0 342 1.8 99 0 380 2 99 0 570 3 99 0 760 4 99 0 1140 6 99 0 1520 8 99 0
從上面的查詢中可以看出當設置PGA的大小為228MB時，可以消除PGA過載的情形。
（三）UGA介紹
UGA（User Global Area）保存了會話資訊，會話總能訪問這部分記憶體。UGA的位置取決於會話連接到Oracle的方式。如果是專用伺服器連接，那麼UGA在PGA中創建；如果是共享伺服器連接，那麼UGA在SGA的Large Pool中創建，若Large Pool沒有分配則在Shared Pool中分配。
PGA和UGA兩者間的區別跟一個進程和一個會話之間的區別是類似的。儘管說進程和會話之間一般都是一對一的關係，但實際上比這個更複雜。一個很明顯的情況是MTS配置，會話往往會比進程多得多。在這種配置下，每一個進程會有一個PGA，每一個會話會有一個UGA。PGA所包含的資訊跟會話是無任何關聯的，而UGA包含的資訊是以特定的會話為基礎的。
（四）SHOW SGA和V$SGA的結果區別
SHOW SGA的結果比V$SGA的結果多一行「Total System Global Area」數據。其實，SHOW SGA的結果來源於V$SGA視圖。運行命令「vi $ORACLE_HOME/bin/sqlplus」打開sqlplus文件，匹配SGA可以發現這麼一行程式碼：
SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA, SUM(VALUE), DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA FROM V$SGA UNION ALL SELECT NAME NAME_COL_PLUS_SHOW_SGA, VALUE, DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA FROM V$SGA;
該行程式碼的結果和執行SHOW SGA可以得到一樣的結果，如下所示：
SYS@omflhr> SHOW SGA Total System Global Area 1068937216 bytes Fixed Size 2253216 bytes Variable Size 771755616 bytes Database Buffers 289406976 bytes Redo Buffers 5521408 bytes SYS@omflhr> SELECT DECODE(NULL, '', 'Total System Global Area', '') NAME_COL_PLUS_SHOW_SGA, 2 SUM(VALUE), 3 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA 4 FROM V$SGA 5 UNION ALL 6 SELECT NAME NAME_COL_PLUS_SHOW_SGA, 7 VALUE, 8 DECODE(NULL, '', 'bytes', '') UNITS_COL_PLUS_SHOW_SGA 9 FROM V$SGA; NAME_COL_PLUS_SHOW_SGA SUM(VALUE) UNITS_COL_PLUS_ ------------------------ ---------- --------------- Total System Global Area 1068937216 bytes Fixed Size 2253216 bytes Variable Size 771755616 bytes Database Buffers 289406976 bytes Redo Buffers 5521408 bytes
在以上結果中，各部分的含義如下：
l Total System Global Area：顯示目前此SGA的大小，包括Fixed Size、Variable Size、 Database buffers和Redo Buffers的大小總和。
l Fixed Size：這裡存儲了SGA各部分組件的相關資訊，主要是作為引導SGA創建的區域，Oracle通過這個區找到SGA其它區，類似一個SGA各個組件的索引。這部分是Oracle內部使用的一個區，包括了資料庫與實例的控制資訊、狀態資訊、字典資訊等。當實例被打開時，此塊區域就被固定住而不能做任何變動，此區域也可稱為Fixed SGA。不同平台和不同版本下這部分的大小可能不一樣。
l Variable Size：包括Shared Pool、Java Pool、Large Pool、Streams Pool、游標區和其它結構。由於這些記憶體塊都是可動態分配的，所以統稱為Variable Size。
l Database Buffers：顯示資料庫高速緩衝區的大小，是SGA中最大的地方，決定資料庫性能。為DB_CACHE_SIZE、DB_KEEP_CACHE_SIZE、DB_RECYCLE_CACHE_SIZE、DB_NK_CACHE_SIZE的總大小，當然這是SGA_TARGET為0的情況，也就是手動SGA管理模式下，如果是自動SGA管理（SGA_TARGET>0），則這個值根據SGA的分配情況自動進行調整。
l Redo Buffers：顯示Redo日誌緩衝區的大小，這部分是實際分配的Redo Log Buffer的大小，由初始化參數LOG_BUFFER根據SGA的最小分配單位granule向上取整得到。
（五）和記憶體相關的比較有用的視圖
① V$SGASTAT主要記錄了有關SGA的統計資訊，以及記憶體分配的情況，對於發生ORA-04031錯誤有很重要的參考價值。其中的資訊由三個欄位組成，依序是：Name（SGA記憶體塊的名稱）、Bytes（記憶體塊的大小）、Pool（記憶體所屬的記憶體塊）。
以下的語句可查詢記憶體塊還剩餘多少使用空間。
SELECT POOL, NAME, BYTES / 1024 / 1024 MB FROM V$SGASTAT WHERE NAME = 'free memory';
以下的語句可查詢共享池的使用情況比率。
SELECT TO_NUMBER(V$PARAMETER.VALUE) VALUE, V$SGASTAT.BYTES / 1024 / 1024 "V$SGASTAT MB", (V$SGASTAT.BYTES / V$PARAMETER.VALUE) * 100 "PERCENT FREE" FROM V$SGASTAT, V$PARAMETER WHERE V$SGASTAT.NAME = 'free memory' AND V$PARAMETER.NAME = 'shared_pool_size' AND V$SGASTAT.POOL = 'shared pool';
② V$SGA_DYNAMIC_COMPONENTS記錄了SGA內各個組件的情況，包括SGA的各個記憶體塊的放大和縮小情況，且所提供的資訊是經過統計的，其欄位組成如下表所示：
|
欄位名 |
數據類型 |
說明 |
|---|---|---|
|
COMPONENT |
VARCHAR2(64) |
記憶體塊名稱 |
|
CURRENT_SIZE |
NUMBER |
目前的大小 |
|
MIN_SIZE |
NUMBER |
實例啟動後的最小值 |
|
MAX_SIZE |
NUMBER |
實例啟動後的最大值 |
|
OPER_COUNT |
NUMBER |
實例啟動後的調整次數 |
|
LAST_OPER_TYPE |
VARCHAR2(6) |
記錄最後一次的調整動作，值包括：GROW（增加），SHRINK（縮小） |
|
LAST_OPER_MODE |
VARCHAR2(6) |
最後一次完成調整動作的模式有兩種：MANUAL（手動）和AUTO（手動） |
|
LAST_OPER_TIME |
DATE |
最後一次完成的調整動作的開始時間 |
|
GRANULE_SIZE |
NUMBER |
記憶體粒度大小 |
③ V$LIBRARYCACHE的內容包含了關於庫快取的性能統計資訊，對於共享池的性能優化有很大幫助。V$LIBRARYCACHE的結構如下表所示。
|
欄位名 |
數據類型 |
說明 |
|---|---|---|
|
NAMESPACE |
VARCHAR2(15) |
庫快取的命名空間，例如INDEX、SQL AREA、OBJECT等 |
|
GETS |
NUMBER |
得到該對象的次數 |
|
GETHITS |
NUMBER |
在記憶體中得到該對象的次數，也就是鎖命中的次數 |
|
GETHITRATIO |
NUMBER |
得到該對象的命中率 |
|
PINS |
NUMBER |
要求PIN的次數 |
|
PINHITS |
NUMBER |
PIN實際命中的次數 |
|
PINHITRATIO |
NUMBER |
PIN命中率 |
|
RELOADS |
NUMBER |
在PIN過程中需要從硬碟中載入對象的次數 |
|
INVALIDATIONS |
NUMBER |
命名空間中的無效的對象 |
|
DLM_LOCK_REQUESTS |
NUMBER |
因為GET而導致的實例鎖的數量 |
|
DLM_PIN_REQUESTS |
NUMBER |
因為PIN請求導致的實例鎖的數量 |
|
DLM_PIN_RELEASES |
NUMBER |
請求釋放PIN鎖的次數 |
|
DLM_INVALIDATION_REQUESTS |
NUMBER |
GET請求非法鎖定實例的次數 |
|
DLM_INVALIDATIONS |
NUMBER |
從其他實例得到的非法PIN的數量 |
此表中必須特別注意PIN和GET的命中率或未命中率，當命中率小於99%或未命中率大於1%時，說明Hard Parse過多，可能需要加大共享池或是使用綁定變數等優化的動作。
& 說明：
關於Oracle的記憶體詳細解析可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2137064/。
真題1、Identify the memory component from which memory may be allocated for:
1.Session memory for the shared server
2.Buffers for I/O slaves
3.Oracle Database Recovery Manager(RMAN) backup and restore operations
A、Large Pool B、Redo Log Buffer
C、Database Buffer Cache D、Program Global Area(PGA)
答案：A。
根據本小節的講述，題目中的3項均是大池（Large Pool）的適用場景。
所以，本題的答案為A。
& 說明：
關於Oracle的記憶體詳細解析可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-2137064/。
真題2、You have executed this command to change the size of the database buffer cache:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582; System altered. To verify the change in size, you executed this command: SQL> SHOW PARAMETER DB_CACHE_SIZE NAME TYPE VALUE -------------- ------------ -------- db_cache_size big integer 4194304
Why is the value set to 4194304 and not to 2516582?
A、because 4194304 is the granule size
B、because 4194304 is the standard block size
C、because 4194304 is the largest nonstandard block size defined in the database
D、because 4194304 is the total size of data already available in the database buffer cache
答案：A。
Oracle分配記憶體的單位是granule，即粒度。最小的粒度為4M，設置大小不到一個粒度按一個粒度計算，通過如下的SQL可查看粒度的大小：
SELECT * FROM V$SGAINFO WHERE NAME='Granule Size';
在本題中，設置的DB_CACHE_SIZE約為2.5M，不足1個粒度，所以，Oracle自動調整為4M大小。
所以，本題的答案為A。
本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。
