Oracle體系結構之內存結構（SGA、PGA）

發布時間：2020-08-05 02:58:50 來源：ITPUB博客閱讀：513 作者：煙花丶易冷欄目：關系型數據庫

Oracle體系結構之內存結構（SGA、PGA）

一、內存結構
Oracle體系結構之內存結構（SGA、PGA）
SGA(System Global Area)：由所有服務進程和后臺進程共享；
PGA(Program Global Area)：由每個服務進程、后臺進程專有；每個進程都有一個PGA。

二、SGA
包含實例的數據和控制信息，包含如下內存結構：
1）Database buffer cache：緩存了從磁盤上檢索的數據塊。
2）Redo log buffer：緩存了寫到磁盤之前的重做信息。
3）Shared pool：緩存了各用戶間可共享的各種結構。
4）Large pool：一個可選的區域，用來緩存大的I/O請求，以支持并行查詢、共享服務器模式以及某些備份操作。
5）Java pool：保存java虛擬機中特定會話的數據與java代碼。
6）Streams pool：由Oracle streams使用。
7）Keep buffer cache：保存buffer cache中存儲的數據，使其盡時間可能長。
8）Recycle buffer cache：保存buffer cache中即將過期的數據。
9）nK block size buffer：為與數據庫默認數據塊大小不同的數據塊提供緩存。用來支持表空間傳輸。

database buffer cache, shared pool, large pool, streams pool與Java pool根據當前數據庫狀態，自動調整；
keep buffer cache,recycle buffer cache,nK block size buffer可以在不關閉實例情況下，動態修改。

三、PGA
每個服務進程私有的內存區域，包含如下結構：
1）Private SQL area：包含綁定信息、運行時的內存結構。每個發出sql語句的會話，都有一個private SQL area（私有SQL區）
2）Session memory：為保存會話中的變量以及其他與會話相關的信息，而分配的內存區。

四、SGA COMPONENT
(一)、Buffer Cache
1、DB_CACHE_SIZE
　　　　通過參數DB_CACHE_SIZE可指定DB buffer cache的大小
　　　　ALTER SYSTEM SET DB_CACHE_SIZE=20M scope=both;
　　　　服務進程從數據文件讀數據到buffer cache；DBWn從buffer cache寫數據到數據文件。
　　　　buffer cache的四種狀態：
　　　　　　　　1）pinned：當前塊正在讀到cache或正寫到磁盤，其他會話等待訪問該塊。
　　　　　　　　2）clean：
　　　　　　　　3）free/unused：buffer內為空，為實例剛啟動時的狀態。
　　　　　　　　4）dirty：臟數據，數據塊被修改，需要被DBWn刷新到磁盤，才能執行過期處理。

同一個數據庫中，支持多種大小的數據塊緩存。通過DB_nK_CACHE_SIZE參數指定，如
　　　　? DB_2K_CACHE_SIZE
　　　　? DB_4K_CACHE_SIZE
　　　　? DB_8K_CACHE_SIZE
　　　　? DB_16K_CACHE_SIZE
　　　　? DB_32K_CACHE_SIZE
標準塊緩存區大小由DB_CACHE_SIZE指定。如標準塊為nK，則不能通過DB_nK_CACHE_SIZE來指定標準塊緩存區的大小，應由DB_CACHE_SIZE指定。
例，標準塊為8K，則數據庫可以設置的塊緩存大小的參數如下：
　　　　? DB_CACHE_SIZE （指定標準塊（這里為8K）的緩存區）
　　　　? DB_2K_CACHE_SIZE （指定塊大小為2K的緩存區）
　　　　? DB_4K_CACHE_SIZE （指定塊大小為4K的緩存區）
　　　　? DB_16K_CACHE_SIZE （指定塊大小為16K的緩存區）
　　　　? DB_32K_CACHE_SIZE （指定塊大小為32K的緩存區）

2、多種緩沖池（buffer pool）
　　　　1）Keep：通過db_keep_cache_size參數指定。
　　　　該buffer內的數據可能被重用，以降低I/O操作。該池的大小要大于指定到該池的段的總和。
　　　　　　讀入到keep buffer的塊不需要過期操作。
　　　　2）Recycle：通過db_recycle_cache_size參數指定。
　　　　該池中的數據被重用機會較小，該池大小要小于分配到該池的段的總和。讀入該池的塊需要經常執行過期處理。
　　　　3）Default：相當于一個沒有Keep與Recycle池的實例的buffer cache，通過db_cache_size參數指定。

3、為對象明確指定buffer pool
　　　　buffer_pool子句，用來為對象指定默認的buffer pool，是storage子句的一部分。
　　　　　　對create與alter table、cluster、index語句有效。
　　　　如果現有對象沒有明確指定buffer pool，則默認都指定為default buffer pool，大小為DB_CACHE_SIZE參數設置的值。
語法：
　　　　a.CREATE INDEX cust_idx ON tt(id) STORAGE (BUFFER_POOL KEEP);
　　　　b.ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE);
　　　　c.ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);
(二)、Share Pool
1、SHARE_POOL_SIZE
　 1）Share Pool可通過SHARE_POOL_SIZE參數指定：
　　SQL> alter system set shared_pool_size=20M scope=both;
　 2）Share Pool保存的信息被多個會話共享，類型包括：
  a.Library Cache
Library Cache又包含共享SQL區與PL/SQL區：
a).共享SQL區保存了分析與編譯過的SQL語句。
b).PL/SQL區保存了分析與編譯過的PL/SQL塊（過程和函數、包、觸發器與匿名PL/SQL塊）。
  b.Data Dictionary Cache
保存了數據字典對象的定義。
  c.UGA(User Global Area)
UGA內包含了共享服務器模式下的會話信息。
共享服務器模式時，如果large pool沒有配置，則UGA保存在Share Pool中。

(三)、Large Pool Oracle體系結構之內存結構（SGA、PGA）
1）Large Pool大小通過LARGE_POOL_SIZE參數指定：
SQL> alter system set large_pool_size=20m scope=both;
2）作用:
a.為I/O服務進程分配內存
b.為備份與恢復操作分配內存
c.為Oracle共享服務器模式與多個數據庫間的聯機事務分配內存。
通過從large pool中為共享服務器模式分配會話內存，可以減少share pool因頻繁為大對象分配和回收內存而產生的碎片。將大的對象從share pool中分離出來，可以提高shared pool的使用效率，使其可以為新的請求提供服務或者根據需要保留現有的數據。

(四)、Java Pool     Oracle體系結構之內存結構（SGA、PGA）
1、JAVA_POOL_SIZE
   　　通過JAVA_POOL_SIZE參數指定java pool大小。
   保存了jvm中特定會話的java code和數據。
2、在編譯數據庫中的java代碼和使用數據庫中的java資源對象時，都會用到share pool。
   java的類加載程序對每個加載的類會使用大約8K的空間。
   系統跟蹤運行過程中，動態加載的java類，也會使用到share pool。

(五)、Redo Log Buffer
1、服務進程從用戶空間拷貝每條DML/DDL語句的redo條目到redo log buffer中。
2、redo log buffer是一個可以循環使用的buffer，服務進程拷貝新的redo覆蓋掉redo log buffer中已通過LGWR寫入磁盤（online redo log）的條目。
3、導致LGWR執行寫redo log buffer到online redo log的條件
a.用戶執行事務提交commit
b.每3秒鐘或redo log buffer內已達到1/3滿或包含1MB數據
c.DBWn進程將修改的緩沖區寫入磁盤時（如果相應的重做日志數據尚未寫入磁盤）

（六）、ASMM(Automatic Shared Memory Management)
1、SGA_TARGET
   1）SGA_TARGET默認值為0，即ASMM被禁用。需要手動設置SGA各中各組件的大小。
   2）當SGA_TARGET為非0時，則啟用ASMM，自動調整以下各組件大小：
　　　　DB buffer cache（default pool）
　　　　shared pool
　　　　large pool
　　　　streams pool
　　　　java pool
   但ASSM中，以下參數仍需要手動指定：
　　　　log buffer
　　　　keep、recycle、以及非標準塊緩沖區
　　　　固定SGA以及其他內部分配。
2、啟用ASMM需要將STATISTICS_LEVEL設置成TYPICAL或ALL
3、啟用ASMM，自動調整SGA內部組件大小后。若手動指定某一組件值，則該值為該組件的最小值。如
手動設置SGA_TARGET=8G,SHARE_POOL_SIZE=1G，則ASMM在自動調整SGA內部組件大小時，保證share pool不會低于1G。
SQL> SELECT component, current_size/1024/1024 size_mb FROM v$sga_dynamic_components;
4、SGA_MAX_SIZE
SGA_MAX_SIZE指定內存中可以分配給SGA的最大值。
SGA_TARGET是一個動態參數，其最大值為SGA_MAX_SIZE指定的值。

五、PGA
Oracle體系結構之內存結構（SGA、PGA）

（一）Private SQL Area
1、保存了當前會話的綁定信息以及運行時內存結構。這些信息
2、每個執行sql語句的會話，都有一個private sql area。
3、當多個用戶執行相同的sql語句，此sql語句保存在一個稱為shared sql area。此share sql area被指定給這些用戶的private sql area
4、共享服務器模式：private sql area位于SGA的share pool或large pool中
   專用服務器模式：private sql area位于PGA中
（二）Cursor、SQL Areas
（三）Work Area
PGA的一大部分被分配給Work Area，用來執行如下操作：
　　　　a.基于操作符的排序，group by、order by、rollup和窗口函數。
  　　　　參數為sort_area_size
　　　　b.hash散列連接，
  　　　　參數為hash_area_size
　　　　c.位圖合并，
  　　　　參數為bitmap_merge_area_size
　　　　d.位圖創建，
  　　　　參數為create_bitmap_area_size
　　　　e.批量裝載操作使用的寫緩存
（四）Session memory
保存了會話的變量，如登錄信息及其他與會話相關的信息，共享服務器模式下，Session memory是共享的。

（五）自動PGA管理
設置PGA_AGGREGATE_TARGET為非0，則啟用PGA自動管理，并忽略所有*_area_size的設置。如sort_area_size,hash_area_size等。
默認為啟用PGA的自動管理，Oracle根據SGA的20%來動態調整PGA中專用與Work Area部分的內存大小，最小為10MB。
用于實例中各活動工作區（work area）的PGA總量，為PGA_AGGREGATE_TARGET減去其他組件分配的PGA內存。得到的結果，按照特定需求動態分配給對應的工作區。
1）設置PGA_AGGREGATE_TARGET大小的步驟
a.設置PGA_AGGREGATE_TARGET為SGA的20%，對于DSS系統，此值可能過低。
b.運行典型的負載，通過oracle收集的pga統計信息來調整PGA_AGGREGATE_TARGET的值。
c.根據oracle的pga建議調整PGA_AGGREGATE_TARGET大小。
2）禁用自動pga管理
為向后兼容，設置PGA_AGGREGATE_TARGET為0，即禁用pga的自動管理。可使用關聯的*_area_size參數調整對應工作區的最大大小。
bitmap_merge_area_size
create_bitmap_area_size
hash_area_size
sort_area_size

Oracle的內存結構,除了SGA(System Global Area)之外,Oracle進程還使用下面三個全局區:
PGA: process global area

UGA: user global area

CGA: call global area

1. PGA與UGA

很多人都搞不清楚PGA和UGA兩者之間的區別,實際上兩者之間的區別跟一個進程和一個會話之間的區別是類似的.盡管說進程和會話之間一般都是一對一的關系,但實際上比這個更復雜.一個很明顯的情況是MTS配置,會話往往會比進程多得多.在這種配置下,每一個進程會有一個PGA,每一個會話會有一個UGA. PGA所包含的信息跟會話是無任何關聯的, 而UGA包含的信息是以特定的會話為基礎的.

2. PGA

進程全局區(PGA)即可以理解為Process Global Area,也可以理解為Program Global Area.它的內存段是在進程私有區(Process Private Memory)而不是在共享區(Shared Memory). 每個Oracle的server process都有屬于自己的PGA,它只包含了本進程的相關特定信息.PGA中的結構不需要由latches來保護,因為其它的進程是不能進入到這里面來訪問的.

PGA包含兩個主要區域:Fixed PGA和Variable PGA或稱為PGA Heap.

Fixed PGA的作用跟Fixed SGA是類似的,都包含原子變量(不可分的),小的數據結構和指向Variable PGA的指針.
Variable PGA是一個堆.它的Chunks可以從Fixed Table X$KSMPP查看得到,這個表的結構跟前面有提到的X$KSMSP是相同的. PGA HEAP包含了一些有關Fixed Table的永久性內存,它跟某些參數的設置有依賴關系.這些參數包含DB_FILES,LOG_FILES,CONTROL_FILES.

3. UGA
UGA(User Global Area)包含的是特定會話的信息。

跟PGA一樣,UGA也由兩區組成:Fixed UGA和Variable UGA,也稱為UGA HEAP. Fixed UGA包含了大約70個原子變量,小的數據結構和指向Variable UGA的指針.　

UGA HEAP中的Chunks可以從它們自己的會話中通過查看表X$KSMUP獲得相關信息,這個表的結構跟X$KSMSP是一樣的.UGA HEAP包含了一些有關fixed tables的永久性內存段,跟一些參數的設置有依賴關系.這些參數有OPEN_CURSORS,OPEN_LINKS,和MAX_ENABLE_ROLES.　

UGA在內存中的位置依賴于會話的配置方式.

如果會話連接的配置方式是專用服務器模式(DDS)即是一個會話對應一個進程,則UGA是放在PGA中的. 在PGA中, Fixed UGA是其中的一個Chunk, 而UGA HEAP是PGA的一個子堆(Subheap).

如果會話連接是配置為共享服務器模式(MTS), Fixed UGA是SHARED POOL或者Large pool中的一個Chunk, 而UGA HEAP則是SHARED POOL或者large pool中的子堆(Subheap).　

4. CGA

跟其它的全局區不同,Call Global Area是短暫性存在的.它只有在函數調用期間存在,一般是在對實例的最低級別的調用時才需要CGA,比如:分析一個SQL語句，執行一個SQL語句，取出一個SELECT語句的輸出

一個單獨的CGA在遞歸調用時是需要的.在SQL語句的分析過程中,對數據字典信息的遞歸調用是需要的,因為要對SQL語句進行語法分析,還有在語句的優化期間要計算執行計劃.執行PL/SQL塊時在處理SQL語句的執行時也是需要遞歸調用的,在DML語句的執行時要處理觸發器執行也是需要遞歸調用的.

不管UGA是放在PGA中還是在SGA中,CGA都是PGA的一個子堆(Subheap).這個事實的一個重要推論是在一個調用的期間會話必須是一個進程.對于在一個MTS的Oracle數據庫進程應用開發時關于這一點的理解是很重要的.如果相應的調用較多,就得增加processes的數量以適應調用的增加.　

沒有CGA中的數據結構,CALLS是沒法工作的. 而實際上跟一次CALL相關的數據結構一般都是放在UGA中,如SQL AREA,PL/SQL AREA, SORT AREA, hash area, bitmap area它們都必須在UGA中, 因為它們要在各CALLS之間要一直存在并且可用. 而CGA中所包含的數據結構是要在一次CALL結束后能夠釋放的. 例如CGA包含了關于遞歸調用的信息,直接I/O BUFFER等還有其它的一些臨時性的數據結構.　

5. 總結一下

PGA包括：
1）Fixed PGA
2）UGA (dedicated server)，當系統是shared server時，UGA位于shared pool或者large pool。
3）CGA

而UGA和CGA都屬于Variable PGA(PGA heap)

UGA包括：

1）Fixed UGA
2）private SQL area
3）session memory
4）SQL Work Areas

private SQL area, session memory, SQL work ares都屬于variable UGA(UGA heap).

6. private SQL area
可以分為兩部分：
1）永久內存區域：存放相同SQL語句多次執行時都需要的一些游標信息，比如綁定變量等。這部分內存只有在游標被關閉時才釋放。所以稱為：永久內存區域。

2）運行時區域：處理SQL語句時的第一步要創建運行時區域，這里存放了當SQL運行時所使用的信息。對于DML語句，SQL執行完畢就釋放該區域；對于select語句，當所有數據行返回給用戶時釋放。

7. session area
保存該session的一些參數，比如：修改的NLS參數，修改的優化器參數optimizer_mode，alter session命令所啟用的跟蹤信息，可以使用的roles，所打開的db links，真正使用的package等。

8. SQL work area
SQL工作區是UGA中最重要的部分，占UGA的大部分內存。主要是在排序sort(ordr by, group by)，hash-join時使用。SQL工作區的大小對性能的影響很大。

一般大數量的排序，是不太可能完全在SQL work area中完成的，因為沒有那么大的內存，所以一般都會使用臨時表空間，將排序的中間結果寫入到temp表空間中(磁盤排序)。根據SQL work area的大小，使用臨時表空間的次數可以是一次，也可能是多次。所以有三種情況：

1）optimal: 排序完全在內存中完成；
2）onepass: 排序完成，需要使用磁盤一次；
3）multipass: 排序的完成，需要使用磁盤多次；

optimal一般是不太現實的，我們一般盡量保證onepass，如果導致了multipass，則性能會下降很多。我們先來看看排序的過程：

排序算法采用的是堆排序；排序方式是分批排序，比如10000條記錄排序，把它分成100次，每次對100條記錄排序；當100條排序完成之后，寫入到temp表空間；再對下100條排序，排序完之后在寫入到temp表空間；最后對temp表空間的100個局部排序的隊列進行merge操作。從而完成對10000條記錄的排序。這樣是比較理想的情況，因為只對磁盤讀寫一次，屬于onepass。如果排序涉及到磁盤的多次讀寫，則屬于multipass。一般我們盡量調整sort_area_size的大小，來避免multipass排序。

SQL work area可以分為下面幾個部分：

排序區(sort_area_size)，hash區(hash_area_size)，bitmap區(create_bitmap_area_size,bitmap_merge_area_size)等，他們深刻的影響著排序、hash-join、bitmap等的性能。

9. PGA的自動管理
在Oracle9i之前，我們是通過設置sort_area_size, hash_area_size等參數來管理PGA。

SQL> show parameter 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

workarea_size_policy string AUTO

SQL> show parameter sort_area;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

sort_area_retained_size integer 0

sort_area_size integer 65536

而從Oracle9i開始，Oracle引入了PGA的自動管理。不需要我們單獨地設置大小了。

設置參數workarea_size_policy參數，可以在PGA自動(auto默認是auto)和PGA手動管理(manual)之間進行選擇。

然后通過初始化參數pga_aggregate_size來設置PGA的內存總和(相當于SGA的sga_max_size)。注意在Oracle9i時，PGA自動管理只對dedicate server有效，對shared server無效，但是在Oracle10g時，PGA自動管理都有效。

10. 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

使用v$pgastat查看當前PGA的統計信息(類似于v$sgastat和SGA)：

SQL> select * from v$pgastat;

NAME VALUE UNIT

---------------------------------------- ---------- ------------

aggregate PGA target parameter 71303168 bytes

aggregate PGA auto target 27297792 bytes

global memory bound 14260224 bytes

total PGA inuse 40966144 bytes

total PGA allocated 88008704 bytes

maximum PGA allocated 125034496 bytes

total freeable PGA memory 4259840 bytes

process count 23

max processes count 40

PGA memory freed back to OS 492634112 bytes

total PGA used for auto workareas 0 bytes

maximum PGA used for auto workareas 6105088 bytes

total PGA used for manual workareas 0 bytes

maximum PGA used for manual workareas 0 bytes

over allocation count 0

bytes processed 6275959808 bytes

extra bytes read/written 0 bytes

cache hit percentage 100 percent

recompute count (total) 33814

19 rows selected.
maximum PGA allocated：PGA曾今達到的最大值

over allocation count: Over allocating PGA memory can happen if the value ofPGA_AGGREGATE_TARGET is too small. When this happens, the Oracle Database cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated. If over allocation occurs, then increase the value of PGA_AGGREGATE_TARGET using the information provided by the V$PGA_TARGET_ADVICE view.

extra bytes read/written: Number of bytes processed during extra passes of the input data, cumulated since instance startup. When a work area cannot run optimal, one or more of these extra passes is performed. (即磁盤排序的讀寫磁盤的字節數)

使用v$pga_target_advice來估測PGA的大小，即PGA的建議值(類似于v$sga_target_advice和SGA)：
V$PGA_TARGET_ADVICE predicts how the cache hit percentage and over allocation count statistics displayed by the V$PGASTAT performance view would be impacted if the value of the PGA_AGGREGATE_TARGET parameter is changed. The prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

SQL> select round(pga_target_for_estimate/(1024*1024)) target_size_M,

2 estd_pga_cache_hit_percentage est_cache_hit_percentage,

3 round(estd_extra_bytes_rw/(1024*1024)) est_extra_read_write_M,

4 estd_overalloc_count est_over_alloc from v$pga_target_advice;

TARGET_SIZE_M EST_CACHE_HIT_PERCENTAGE EST_EXTRA_READ_WRITE_M EST_OVER_ALLOC

------------------ --------------------------- ---------------------- --------------

17 97 186 31

34 97 186 31

51 100 0 2

68 100 0 0

82 100 0 0

95 100 0 0

109 100 0 0

122 100 0 0

136 100 0 0

204 100 0 0

272 100 0 0

408 100 0 0

544 100 0 0

13 rows selected.

使用v$pga_target_advice_histogram來估測PGA的大小(類似于v$sga_target_advice和SGA)：

V$PGA_TARGET_ADVICE_HISTOGRAM predicts how statistics displayed by the V$SQL_WORKAREA_HISTOGRAM dynamic view would be impacted if the value of thePGA_AGGREGATE_TARGET parameter is changed. This prediction is performed for various values of the PGA_AGGREGATE_TARGET parameter, selected around its current value. The advice statistic is generated by simulating the past workload run by the instance.

該視圖可以通過對不同工作區大小的采樣評估提供統計信息共分析使用SQL在工作區中以3種方式執行：

Optimal（優化方式）：指所有處理可以在內存中完成；Onepass：大部分操作可以在內存中完成，但是需要使用到磁盤排序；Multipass：大量操作需要產生磁盤交互，性能極差。

About Me

...............................................................................................................................

● 本文整理自網絡

● 本文在itpub（http://blog.itpub.net/26736162）、博客園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 數據庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯系我請加QQ好友(646634621)，注明添加緣由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章內容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的數據庫技術。