Oracle - 資料庫的記憶體調整
Oracle資料庫將資訊儲存在記憶體快取和磁碟上，記憶體訪問比磁碟訪問快得多；與記憶體訪問相比，磁碟訪問（物理I/O）佔用大量時間，也會增加所需的CPU資源，因此頻繁訪問的物件的資料請求通過記憶體執行會更有效率，而不是需要磁碟訪問。
影響效能的主要Oracle資料庫記憶體快取包括：資料庫緩衝區快取、重做日誌緩衝區、共享池（含庫快取、資料字典快取和伺服器結果快取等）、Java池、流池、大池、程式專用記憶體、記憶體列儲存（IM列儲存）等。
一、自動記憶體管理
自動記憶體管理使Oracle資料庫能夠自動管理和調優資料庫記憶體。在自動記憶體管理模式下，對共享全域性區域(SGA)和程式全域性區域(例項PGA)記憶體的管理完全由Oracle資料庫處理。這個方法是最自動化且被Oracle強烈推薦的。
如果禁用了自動記憶體管理，則Oracle資料庫使用自動共享記憶體管理來管理SGA記憶體。在這種模式下，Oracle資料庫會根據總SGA記憶體設定的目標大小自動將記憶體分配給各個SGA元件。
如果禁用了自動記憶體管理和自動共享記憶體管理，就必須通過在SGA中調整單個記憶體池來手動管理SGA記憶體。這種模式能夠完全控制SGA記憶體的分佈，但它需要更大的工作量。
如果禁用了自動記憶體管理，則Oracle資料庫將使用自動PGA記憶體管理來管理PGA記憶體。在這種模式下，Oracle資料庫根據總PGA記憶體設定的目標大小自動將記憶體分配到例項PGA中的工作區域。
如果自動記憶體管理和自動PGA記憶體管理都被禁用，就必須通過調整PGA記憶體的部分來手動管理PGA記憶體。這種方法可能非常困難，因為工作負載總是在變化，Oracle強烈建議使用自動記憶體管理或自動PGA記憶體管理。
使用自動記憶體管理，需要設定MEMORY_TARGET和MEMORY_MAX_TARGET 初始化引數。在MEMORY_TARGET初始化引數中指定目標記憶體大小，再根據需要在SGA和例項PGA之間重新分配記憶體；此引數是動態的，因此可以隨時更改其值，而無需重新啟動資料庫。而MEMORY_MAX_TARGET初始化引數指定的最大記憶體大小，該值用作MEMORY_TARGET值的限制；該引數是靜態的，因此例項啟動後無法更改其值。
二、自動SGA記憶體管理
受自動管理的元件
自動共享記憶體管理簡化了SGA的配置，自動分配記憶體用於以下記憶體池：資料庫緩衝區快取（預設池）、共享池、大型游泳池、Java池和流池，由 SGA_TARGET 引數控制。SGA_TARGET引數的值的變化會自動調整這些記憶體池的大小；如果將這些記憶體池設定為非零值，則自動共享記憶體管理將這些值作為最小值。
不自動管理的元件
以下記憶體快取是手動大小的元件，不受自動共享記憶體管理控制:
重做日誌緩衝區
重做日誌緩衝區使用 LOG_BUFFER 初始化引數，該引數的值以位元組表示，例項啟動後，重做日誌緩衝區的大小不能修改。
指標 REDO BUFFER ALLOCATION RETRIES 統計資料反映了在重做日誌緩衝區中使用者程式等待空間的次數。這個統計量的值應該在一個區間上接近於零，如果此值持續增加則意味著使用者程式必須等待重做日誌緩衝區中的空間可用。
-- 使用V$SYSSTAT效能檢視查詢此統計資料。
SQL> SELECT name, value FROM V$SYSSTAT WHERE name = 'redo buffer allocation retries';
NAME VALUE
--------------------------------------------- ----------
redo buffer allocation retries 2465
其他快取快取（例如KEEP、RECYCLE等)
KEEP池的大小使用 DB_KEEP_CACHE_SIZE 初始化引數，保持池的目的是在記憶體中保留物件，從而避免I/O操作。儲存在保持池中的典型段是小的、經常使用的引用表。
RECYCLE池的大小使用 db_restcle_cache_size 初始化引數，回收池的目的是保留被掃描很少或不經常被引用的片段
固定SGA和其他內部分配
固定的SGA和其他內部分配使用DB_nK_CACHE_SIZE初始化引數。
當自動共享記憶體管理計算自動調優記憶體池的值時，分配給這些快取的記憶體將從SGA_TARGET引數的值中扣除。修改SGA_TARGET引數的值可以使用 ALTER SYSTEM 命令。
啟用自動共享記憶體管理需要設定初始化引數 STATISTICS_LEVEL 的值為 TYPICAL 或 ALL，並將 SGA_TARGET 的值設定為非零的小於或等於SGA_MAX_SIZE初始化引數值的值，SGA_MAX_SIZE引數的值預設為所有SGA元件的聚合設定，且不能動態調整；禁用自動共享記憶體管理時將SGA_TARGET在例項啟動時將引數值動態設定為零。
三、手動共享記憶體管理
如果系統沒有使用自動記憶體管理或自動共享記憶體管理，那麼您必須手動配置以下SGA元件的大小:
資料庫緩衝區快取
資料庫緩衝區快取的大小使用DB_CACHE_SIZE初始化引數
-- 通過 V$DB_CACHE_ADVICE 諮詢檢視以返回緩衝池在各種大小的的I/O要求的預測
SQL> COLUMN size_for_estimate FORMAT 999,999,999,999 heading 'Cache Size (MB)'
SQL> COLUMN buffers_for_estimate FORMAT 999,999,999 heading 'Buffers'
SQL> COLUMN estd_physical_read_factor FORMAT 999.90 heading 'Estd Phys|Read Factor'
SQL> COLUMN estd_physical_reads FORMAT 999,999,999 heading 'Estd Phys| Reads'
SQL> SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND advice_status = 'ON'
AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') ;
Estd Phys Estd Phys
Cache Size (MB) Buffers Read Factor Reads
---------------- ----------- ------------ ------------
30 3,802 18.70 192,317,943 -- 10% of Current Size
60 7,604 12.83 131,949,536
91 11,406 7.38 75,865,861
121 15,208 4.97 51,111,658
152 19,010 3.64 37,460,786
182 22,812 2.50 25,668,196
212 26,614 1.74 17,850,847
243 30,416 1.33 13,720,149
273 34,218 1.13 11,583,180
304 38,020 1.00 10,282,475 -- Current Size
334 41,822 .93 9,515,878
364 45,624 .87 8,909,026
395 49,426 .83 8,495,039
424 53,228 .79 8,116,496
456 57,030 .76 7,824,764
486 60,832 .74 7,563,180
517 64,634 .71 7,311,729
547 68,436 .69 7,104,280
577 72,238 .67 6,895,122
608 76,040 .66 6,739,731 -- 200% of Current Size
在本例中，當前快取大小為204MB；如果縮小為212MB時物理讀取的估計數量將增加到1.74倍即增加74％，因此不建議減少到212MB；如果增加到334MB可能會將讀取量減少到0.93倍即減少7％，如果系統上有額外的記憶體可用且SGA_MAX_SIZE引數的值允許增量，則建議將預設緩衝區快取池大小增加到334MB。
共享池
共享池的大小使用 SHARED_POOL_SIZE 初始化引數來調整大小。資料庫使用共享池來快取包括PL/SQL塊和SQL語句、字典快取資料、結果快取資料和其他資料的文字和可執行形式。在調整共享池的大小時，目標是在庫快取中快取多次執行的SQL語句，而不會過度分配記憶體。
庫快取
使用 $LIBRARYCACHE 檢視來監視反映庫快取活動的統計資訊。這些統計資訊反映了最近資料庫例項啟動後的所有庫快取記憶體活動。每一行描述的項由NAMESPACE列的值標識
SQL> SELECT namespace, pins, pinhits, reloads, invalidations FROM V$LIBRARYCACHE ORDER BY namespace;
NAMESPACE PINS PINHITS RELOADS INVALIDATIONS
----------------------------- ---------- ---------- ---------- -------------
SQL AREA 68042183 59896737 757840 1339997
TABLE/PROCEDURE 26946789 26923471 9349 0
BODY 306051 305853 3 0
TRIGGER 166104 166005 0 0
INDEX 16875 14608 676 0
CLUSTER 3077 3054 3 0
DIRECTORY 27 7 0 0
QUEUE 16368 16360 1 0
JAVA SOURCE 10 5 0 0
JAVA RESOURCE 10 5 0 0
APP CONTEXT 22 14 0 0
計算庫快取的命中率：LibraryCacheHitRatio = sum(pinhits) / sum(pins)
檢視庫快取可用的記憶體量：
SQL> SELECT * FROM V$SGASTAT WHERE name = 'free memory' AND pool = 'shared pool';
POOL NAME BYTES
------------ -------------------------- ----------
shared pool free memory 4841986312
字典快取
資料庫例項啟動時，資料字典快取不包含任何資料。因此，發出的任何SQL語句都可能導致快取未命中。隨著更多資料被讀入快取，快取未命中的可能性降低。最終，資料庫達到穩定狀態，其中最常用的字典資料在快取中
-- 查詢以檢視視應用程式執行期間的統計資料
SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates
FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES
-------------------------------- ---------- -------------- ------------- ----------
dc_constraints 1888 633 66.4724576 1888
outstanding_alerts 17075 16149 5.42313324 11
dc_awr_control 30396 486 98.4011054 452
sch_lj_objs 166 32 80.7228916 0
sch_lj_oids 2433 58 97.6161118 0
dc_objects 103102954 27213 99.973606 6167
dc_table_scns 178 178 0 0
qmtmrciq_cache_entries 1 1 0 0
qmtmrctq_cache_entries 1824 1198 34.3201754 0
dc_users 55585406 398 99.999284 0
qmrc_cache_entries 7 7 0 0
查詢結果快取
如果使用SGA_TARGET初始化引數即自動共享池管理，資料庫預設將SGA_TARGET引數值的0.50％分配給結果快取，
使用SHARED_POOL_SIZE初始化引數即手動共享池管理，則資料庫預設將1％的共享池大小分配給結果快取，可以通過初始化引數RESULT_CACHE_MAX_SIZE設定為所需的大小，禁用伺服器結果快取時將此引數設定為0即可
-- 通過執行 DBMS_RESULT_CACHE.MEMORY_REPORT 檢視結果快取的使用情況
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE DBMS_RESULT_CACHE.MEMORY_REPORT
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 671104K bytes (671104 blocks)
Maximum Result Size = 33555K bytes (33555 blocks)
[Memory]
Total Memory = 12768 bytes [0.000% of the Shared Pool]
... Fixed Memory = 12768 bytes [0.000% of the Shared Pool]
... Dynamic Memory = 0 bytes [0.000% of the Shared Pool]
PL/SQL 過程已成功完成。
保留池
資料庫例項使用保留池來儲存大型物件。保留池的大小使用 SHARED_POOL_RESERVED_SIZE 初始化引數來設定，該引數的預設值是 SHARED_POOL_SIZE 引數的5%，通常不需要更改這個預設值，而一般的調整修改到10%即足夠。
調整引數時使用 V$SHARED_POOL_RESERVED 檢視的統計資訊做參考，在具有足夠空閒記憶體的系統上 REQUEST_MISSES 的統計值應該為零，在受作業系統記憶體的限制的系統上目標是使REQUEST_FAILURES的統計值為0或不增加，否則應增大SHARED_POOL_RESERVED_SIZE的值
SQL> select REQUEST_FAILURES,REQUEST_MISSES from V$SHARED_POOL_RESERVED;
REQUEST_FAILURES REQUEST_MISSES
---------------- --------------
0 0
記憶體中列儲存
記憶體列儲存（IM列儲存）是12C開始的一個可選的靜態SGA池，可以為表、物化檢視、分割槽和表空間等資料庫物件啟用記憶體列儲存，使之儲存為針對快速掃描進行優化的特殊的列狀格式。使用 INMEMORY_SIZE 初始化引數設定它的大小，該引數的預設值為0即不使用記憶體中的列儲存，要啟用時設定該引數為非零值。在設定記憶體中列儲存的大小之後，必須重新啟動資料庫例項，以便將資料庫物件儲存在其中。
使用記憶體中的列儲存需要大量的記憶體，為減少記憶體需求可以為要儲存在記憶體列儲存中的每個資料庫物件指定壓縮方法，搞壓縮可以減少記憶體但不會提高更大的效能優勢，需要平衡效能的優勢與可用的記憶體。記憶體中的列儲存支援的壓縮方法包括 不壓縮資料（NO MEMCOMPRESS）、優化DML操作的資料並最少壓縮記憶體列儲存資料（MEMCOMPRESS FOR DML）以及逐級更高壓縮的四個（MEMCOMPRESS FOR QUERY LOW、MEMCOMPRESS FOR QUERY HIGH、MEMCOMPRESS FOR CAPACITY LOW、MEMCOMPRESS FOR CAPACITY HIGH）壓縮方法。
-- 將記憶體列儲存的大小設定為100GB的示例
SQL> ALTER SYSTEM SET INMEMORY_SIZE = 100G;
-- 為oe.product_information表啟用列存並指定MEMCOMPRESS FOR CAPACITY HIGH壓縮方法
SQL> ALTER TABLE oe.product_information INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;
大池
資料庫例項使用大池來儲存共享伺服器體系結構的客戶端程式的會話資訊、並行查詢的並行執行訊息緩衝區和恢復管理器的快取I/O緩衝區。
大型池的大小使用 LARGE_POOL_SIZE 初始化引數。
Java池
資料庫例項使用Java池來儲存所有會話特定的Java程式碼和和Java虛擬機器中的資料。
Java池的大小使用 JAVA_POOL_SIZE 初始化引數來設定。
流池
資料庫例項使用流池來儲存緩衝佇列訊息併為Oracle流捕獲程式和應用程式提供記憶體。
流池的大小使用 STREAMS_POOL_SIZE 初始化引數來設定。
這些引數的值可以使用ALTER SYSTEM語句動態配置，除了INMEMORY_SIZE引數。INMEMORY_SIZE引數是一個靜態引數，因此在使用ALTER SYSTEM語句更改後必須重新啟動資料庫例項以使更改生效。
四、自動PGA記憶體管理
自動PGA記憶體管理簡化並改進了PGA記憶體的分配方式，預設情況下，啟用PGA記憶體管理。在這種模式下，Oracle資料庫會根據SGA記憶體大小的20％，動態調整專用於工作區域的PGA記憶體部分，從而自動調整PGA的大小。
可以通過將 PGA_AGGREGATE_TARGET 初始化引數的值設定為0來禁用自動PGA記憶體管理。禁用自動PGA記憶體管理時，工作區的最大大小可以使用相關 *_AREA_SIZE 引數進行調整。
檢視 V$PGASTAT 提供有關PGA記憶體使用情況和自動PGA記憶體管理器的例項級的統計資訊。
SQL> SELECT * FROM V$PGASTAT;
NAME VALUE UNIT
----------------------------------------- ---------- -------
aggregate PGA target parameter 1.0491E+10 bytes -- 引數 PGA_AGGREGATE_TARGET 的當前值
aggregate PGA auto target 8221344768 bytes -- 在自動模式下可用於執行工作區域的PGA記憶體量
global memory bound 1049098240 bytes -- 自動模式下執行的工作區域的最大大小
total PGA inuse 1356447744 bytes
total PGA allocated 1845280768 bytes -- 例項分配的當前的PGA記憶體量
maximum PGA allocated 8022375424 bytes
total freeable PGA memory 263258112 bytes -- 可以被釋放的分配了的PGA記憶體量
process count 478
max processes count 506
PGA memory freed back to OS 4.5025E+12 bytes
total PGA used for auto workareas 456704 bytes -- 自動模式下執行的工作區域當前消耗的PGA記憶體量
maximum PGA used for auto workareas 4897979392 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 1085440 bytes
over allocation count 0
bytes processed 6.6557E+12 bytes -- 例項啟動以來記憶體密集型SQL操作符處理的位元組數
extra bytes read/written 4.0586E+12 bytes -- 工作區域無法最佳執行時額外傳遞過程中處理的位元組數
cache hit percentage 62.11 percent -- 快取命中的百分比
recompute count (total) 477574
檢視 V$PROCESS 監視連線到資料庫例項的每個Oracle程式，每一行一個程式。
SQL> select PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM from V$PROCESS;
PROGRAM PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
程式 使用的記憶體 已分配的記憶體 可回收的記憶體 分配過的最大記憶體
------------------------------- ------------ ------------- ---------------- ----------------
PSEUDO 0 0 0 0
oracle@tyzf-db2 (PMON) 1005330 1133794 0 1133794
oracle@tyzf-db2 (PSP0) 984538 1117378 0 1117378
oracle@tyzf-db2 (VKTM) 980450 1117378 0 1117378
oracle@tyzf-db2 (GEN0) 1006826 1117378 0 1117378
oracle@tyzf-db2 (DIAG) 6242618 6425794 0 6425794
oracle@tyzf-db2 (PING) 1058762 1182914 0 1182914
oracle@tyzf-db2 (ACMS) 988562 1117378 0 1117378
oracle@tyzf-db2 (DIA0) 33028618 38652450 4521984 38652450
oracle@tyzf-db2 (LMON) 7581842 8588482 0 8588482
oracle@tyzf-db2 (LMD0) 12101250 12455106 0 12455106
oracle@tyzf-db2 2154602 2493634 0 2493634
oracle@tyzf-db2 (TNS V1-V3) 1008010 1265186 0 1265186
oracle@tyzf-db2 (J001) 927898 1903810 524288 1903810
oracle@tyzf-db2 3615346 4852930 983040 7867586
oracle@tyzf-db2 2086209 2886850 196608 2886850
oracle@tyzf-db2 1630090 2804258 917504 5163554
檢視 V$PROCESS_MEMORY 按每個Oracle程式的命名元件類別顯示動態PGA記憶體使用情況，組包含SQL、PL/SQL、Freeable、Other等
SQL> select * from V$PROCESS_MEMORY;
PID SERIAL# CATEGORY ALLOCATED USED MAX_ALLOCATED
---------- ---------- --------------- ---------- ---------- -------------
2 1 PL/SQL 2008 224 2008
2 1 Other 1131786 1131786
3 1 PL/SQL 2008 224 2008
3 1 Other 1115370 1115370
4 1 PL/SQL 2008 224 2008
4 1 Other 1115370 1115370
51 149 SQL 25064 7560 254728
51 149 PL/SQL 2008 224 2008
51 149 Freeable 131072 0
51 149 Other 2383970 2383970
檢視 V$SQL_WORKAREA 為每個載入的遊標維護累積的工作區域統計資料。通過該檢視查詢出最耗記憶體的工作區，通過關聯v$sql即可關聯出遊標的具體資訊：
SQL> SELECT * FROM (
SELECT sql_id, workarea_address, operation_type, policy, estimated_optimal_size FROM V$SQL_WORKAREA ORDER BY estimated_optimal_size DESC)
WHERE ROWNUM <= 10;
SQL_ID WORKAREA_ADDRESS OPERATION_TYPE POLICY ESTIMATED_OPTIMAL_SIZE
------------- ---------------- ----------------- --------- ----------------------
2kkw0w4ss3rk3 070000196D7E9C28 SORT (v2) AUTO 4.1737E+10
a0vcv0jnh19t2 0700001D7EA6B108 SORT (v2) AUTO 1.1912E+10
2kkw0w4ss3rk3 0700001ABCD9D1E8 SORT (v2) AUTO 8235171840
cmdhha47fgpxs 0700001ADDE0B378 SORT (v2) AUTO 5638367232
3ms76daaz787y 070000194EE8EDE0 SORT (v2) AUTO 5349998592
9n29nxtvtnphb 0700001DAFE2E358 HASH-JOIN AUTO 2147482624
cmdhha47fgpxs 0700001ADDE0B3E0 HASH-JOIN AUTO 2147482624
51gvt76q05956 0700001D5F851CA0 SORT (v2) AUTO 2086549504
gxyddq0kt2rfg 0700001C05293AB8 SORT (v2) AUTO 2086549504
gxyddq0kt2rfg 0700001AACE5E130 SORT (v2) AUTO 2086549504
相關文章
- oracle for aix記憶體調整OracleAI記憶體
- Oracle記憶體分配與調整Oracle記憶體
- oracle記憶體調整相關Oracle記憶體
- 對oracle例項的記憶體（SGA和PGA）進行調整，優化資料庫性Oracle記憶體優化資料庫
- oracle資料庫的效能調整Oracle資料庫
- Oracle 10g的記憶體調整報告Oracle 10g記憶體
- weblogic 記憶體調整Web記憶體
- oracle例項記憶體（SGA和PGA）調整Oracle記憶體
- Oracle調整緩衝區快取記憶體Oracle快取記憶體
- oracle資料庫的效能調整(轉)Oracle資料庫
- Oracle - 資料庫的記憶體結構Oracle資料庫記憶體
- Oracle 資料庫引數調整Oracle資料庫
- Oracle效能最佳化調整--調整緩衝區快取記憶體Oracle快取記憶體
- 調整資料庫的資料檔案記錄資料庫
- 【大頁記憶體】Oracle資料庫配置大頁記憶體記憶體Oracle資料庫
- Oracle資料庫記憶體引數調優技術的個人總結Oracle資料庫記憶體
- Oracle資料庫記憶體引數調優技術的個人總結 （2）Oracle資料庫記憶體
- Oracle資料庫記憶體引數調優技術的個人總結 （1）Oracle資料庫記憶體
- 記憶體資料庫記憶體資料庫
- MySQL記憶體引數及調整MySql記憶體
- Tomcat記憶體引數調整Tomcat記憶體
- (三) MdbCluster分散式記憶體資料庫——節點狀態變化及分片調整分散式記憶體資料庫
- oracle資料庫記憶體分配（sga和pga）Oracle資料庫記憶體
- AIX 下oracle 資料庫記憶體優化AIOracle資料庫記憶體優化
- 如何配置oracle資料庫伺服器的記憶體Oracle資料庫伺服器記憶體
- Mongodb記憶體資料庫MongoDB記憶體資料庫
- 達夢記憶體調整及修改方法記憶體
- solaris記憶體引數調整及管理記憶體
- KVM之十一：調整cpu和記憶體記憶體
- 將altibase記憶體庫的表匯出到oracle資料庫記憶體Oracle資料庫
- Oracle整體資料庫複製解決方案調查報告Oracle資料庫
- 自動記憶體調整中真正決定自動調整的引數記憶體
- 從Oracle資料庫故障到AIX記憶體管理Oracle資料庫AI記憶體
- Oracle資料庫記憶體監控及意義Oracle資料庫記憶體
- oracle 記憶體引數調整優化相關傾力整理Oracle記憶體優化
- 調整資料庫引數資料庫
- TOMCAT記憶體溢位及大小調整Tomcat記憶體溢位
- 【記憶體資料庫】TimesTen記憶體資料庫