Oracle 10g的記憶體調整報告

SGA--(初始化引數 sga_target)

|

|__shared pool(初始化引數 shared_pool_size) chunk連結串列結構構成Bucket

| |

| |__library cache

| |

| |__dictionary cache

| |

| |__reserved size (初始化引數 shared_pool_reserved_size)

|

|__ DEFAULT buffer cache(初始化引數 db_cache_size) LRU連結串列結構

|

|__KEEP buffer cache (初始化引數 db_keep_cache_size)

|

|__RECYCLE buffer cache (初始化引數 db_recycle_cache_size)

|

|__ large pool (初始化引數 large_pool_size)

|

|__java pool (初始化引數 java_pool_size)

|

|__ streams pool (初始化引數 streams_pool_size)

|

|__log buffer (初始化引數 log_buffer)

PGA--(初始化引數 pga_aggregate_target)

|

|__ sort_area_size

|

|__ bitmap_merge_area_size

|

|__ hash_area_size

|

|__ open_cursors

|

|__ oracle 堆疊和TNS堆疊

PRE_PAGE_SGA

oracle例項啟動時，會只載入各個記憶體區最小的大小。而其他SGA記憶體只作為虛擬記憶體分配，只有當程式touch到相應的頁時，才會置換到實體記憶體中。但我們也許希望例項一啟動後，所有SGA都分配到實體記憶體。這時就可以通過設定PRE_PAGE_SGA引數來達到目的了。

這個引數的預設值為FALSE，即不將全部SGA置入實體記憶體中。當設定為TRUE時，例項啟動會將全部SGA置入實體記憶體中。它可以使例項啟動達到它的最大效能狀態，但是，啟動時間也會更長（因為為了使所有SGA都置入實體記憶體中，oracle程式需要touch所有的SGA頁）。

但是，要記住一點：PRE_PAGA_SGA只是在啟動時將實體記憶體分配給SGA，但並不能保證系統在以後的執行過程不會將SGA中的某些頁置換到虛擬記憶體中，也就是說，儘管設定了這個引數，還是可能出現Page In/Out。如果需要保障SGA不被換出，就需要由另外一個引數LOCK_SGA來控制了。

LOCK_SGA

上面提到，為了保證SGA都被鎖定在實體記憶體中，而不必頁入/頁出，可以通過引數LOCK_SGA來控制。這個引數預設值為FALSE，當指定為TRUE時，可以將全部SGA都鎖定在實體記憶體中。當然，有些系統不支援記憶體鎖定，這個引數也就無效了。

use_indirect_data_buffers

這個引數使32位平臺使用擴充套件緩衝快取基址，以支援支援4GB多實體記憶體。設定此引數，可以使SGA突破在32位系統中的2G最大限制。64位平臺中，這個引數被忽略。

SGA中，設定sga_target的值，並把statistics_level設定為typical或者all以啟動ASMM，啟動自動SGA管理後，Oracle可以自動為我們調整以下記憶體池的大小:

shared pool

buffer cache

large pool

java pool

streams pool

需要手動調整的引數

log buffer

db_nk_cache_size

db_keep_cache_size

db_recycle_cache_size

下面是初始化引數中定義的各個記憶體的值：

SQL> SELECT NAME,VALUE

2 FROM v$parameter

3 WHERE NAME IN('sga_max_size',

4 'db_cache_size',

5 'shared_pool_size',

6 'shared_pool_reserved_size',

7 'large_pool_size',

8 'java_pool_size',

9 'db_block_size',

10 'db_block_buffers',

11 'log_buffer',

12 'sort_area_size',

13 'sort_area_retained_size',

14 'hash_area_size',

15 'sessions',

16 'open_cursors',

17 'streams_pool_size',

18 'sga_target',

19 'pga_aggregate_target')

20 ORDER BY NAME;

NAME VALUE

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

db_block_buffers 0

db_block_size 32768

db_cache_size 0

hash_area_size 131072

java_pool_size 0

large_pool_size 0

log_buffer 14238720

open_cursors 500

pga_aggregate_target 1995440128

sessions 445

sga_max_size 4294967296

sga_target 4294967296

shared_pool_reserved_size 38587596

shared_pool_size 0

sort_area_retained_size 0

sort_area_size 65536

streams_pool_size 0

17 rows selected

通過以上語句可以看到SGA的大小是4294967296/1024/1024=4096MB 這4096MB是如何分配的呢，通過以下語句可以看到:

SQL> select t.COMPONENT,t.CURRENT_SIZE/1024/1024 "CURRENT_SIZE (MB)"from v$sga_dynamic_components t;

COMPONENT CURRENT_SIZE (MB)

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

shared pool 800

large pool 16

java pool 16

streams pool 0

DEFAULT buffer cache 3248

KEEP buffer cache 0

RECYCLE buffer cache 0

DEFAULT 2K buffer cache 0

DEFAULT 4K buffer cache 0

DEFAULT 8K buffer cache 0

DEFAULT 16K buffer cache 0

DEFAULT 32K buffer cache 0

ASM Buffer Cache 0

13 rows selected

SGA實際大小 = DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新記憶體池） + LOG_BUFFERS+11K(Redo Log Buffer的保護頁) + 1MB + 16M(SGA內部記憶體消耗，適合於9i及之前版本)

4096MB=3248+0+0+0+800+16+16+0+((14238720/1024)+11)/1024+1=4094.6MB

一般來說，設定1GB以上的shared pool不會給效能帶來明顯的提高，相反，這將給Oracle管理shared pool以及監控shared pool的過程中帶來較多的麻煩。我們可以在系統上線時，設定shared pool為SGA的10%，但是不要超過1GB，或者利用9i以後引入的顧問來幫助我們判斷shared pool 的設定是否合理。只要將初始化引數statistics_level設定為typical或者all就啟動對shared pool的建議，basic為關閉該功能。

SQL> select t.SHARED_POOL_SIZE_FOR_ESTIMATE "SP",

2 t.ESTD_LC_SIZE "EL",

3 t.ESTD_LC_MEMORY_OBJECTS "ELM",

4 t.ESTD_LC_TIME_SAVED "ELT",

5 t.ESTD_LC_TIME_SAVED_FACTOR "ELTS %",

6 t.ESTD_LC_MEMORY_OBJECT_HITS "ELMO"

7 from v$shared_pool_advice t

8 ;

SP EL ELM ELT ELTS % ELMO

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

320 88 5074 33933340 0.9864 1103895941

400 164 9091 34082404 0.9908 1103917266

480 243 12488 34212579 0.9946 1103936243

560 321 16246 34298181 0.997 1103950027

640 400 20958 34349935 0.9985 1103959377

720 479 25599 34381446 0.9995 1103965662

800 558 29274 34399956 1 1103969741

880 637 31830 34410211 1.0003 1103972287

960 716 36387 34415861 1.0005 1103973889

1040 771 38954 34419129 1.0006 1103974939

1120 810 41453 34421119 1.0006 1103975631

1200 889 45092 34422524 1.0007 1103976097

1280 969 48656 34423731 1.0007 1103976439

1360 1018 51779 34424784 1.0007 1103976714

1440 1057 54744 34425580 1.0007 1103976944

1520 1096 57024 34426021 1.0008 1103977142

1600 1175 63265 34426180 1.0008 1103977322

通過這兩個語句可以看出，Oracle建議我們shared pool的值為800MB，而且實際分配的值也是800MB。

ESTD_LC_SIZE:估計庫快取記憶體的使用數量（以兆位元組為單位）

ESTD_LC_MEMORY_OBJECTS: 估算共享池中庫快取的記憶體物件數

ESTD_LC_TIME_SAVED: 估算將可以節省的解析時間

ESTD_LC_TIME_SAVED_FACTOR: 估算的節省的解析時間與當前節省解析時間的比

ESTD_LC_MEMORY_OBJECT_HITS: 估算可以直接從共享池中命中庫快取的記憶體物件的命中次數

我們主要關注estd_lc_time_saved_factor列的值，當該列的值為1時表示再增加shared pool的大小對效能的提高沒有意義，對於上面例子來說，當shared pool為800MB時達到最佳。

在如何設定buffer cache的大小上，從Oracle 9i 開始通過初始化引數db_cache_advice,從而啟動buffer cache顧問，Oracle會監控default型別，keep型別和recycle型別的buffer cache的使用，以及其他5種不同資料庫尺寸（2，4，8，16或32KB）的buffer cache的使用，Oracle會根據當前所監控到的物理讀的速率，從而估算出在不同大小尺寸的buffer cache下，所產生的可能的物理讀的數量，Oracle將這些資訊放入v$db_cache_advice中，每種型別的buffer cache 都會有相應的若干條記錄來表示所建議的buffer cache的大小，下面是對於預設型別的，預設資料塊尺寸的buffer cache的建議大小應該是多少。

SQL> SELECT size_for_estimate "target M", buffers_for_estimate, estd_physical_read_factor "physical_read %", estd_physical_reads

2 FROM V$DB_CACHE_ADVICE

3 WHERE name = 'DEFAULT'

4 AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size')

5 AND advice_status = 'ON';

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS

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

320 10040 22.7562 146498585

640 20080 13.3366 85857536

960 30120 5.9642 38395958

1280 40160 2.8749 18507961

1600 50200 2.0151 12972443

1920 60240 1.6118 10376066

2240 70280 1.3775 8867754

2560 80320 1.2042 7752114

2880 90360 1.0749 6920255

3200 100400 1.0058 6474792

3248 101906 1 6437754

3520 110440 0.9674 6227738

3840 120480 0.9445 6080193

4160 130520 0.931 5993347

4480 140560 0.9196 5920463

4800 150600 0.9059 5832272

5120 160640 0.8762 5640700

5440 170680 0.8106 5218403

5760 180720 0.683 4397109

6080 190760 0.4802 3091367

target M BUFFERS_FOR_ESTIMATE physical_read % ESTD_PHYSICAL_READS

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

6400 200800 0.2046 1317289

21 rows selected

size_for_estimate : 預測buffer cache的尺寸

buffers_for_estimate: 預測buffer Cache大小（緩衝塊數）

estd_physical_read_factor: 這一緩衝大小時，物理讀因子，它是如果緩衝大小為SIZE_FOR_ESTIMATE時，建議器預測物理讀數與當前實際物理讀數的比率值。如果當前物理讀數為0，這個值為空。

estd_physical_reads: 如果緩衝大小為SIZE_FOR_ESTIMATE時，建議器預測物理讀數。

這裡的欄位estd_physical_read_factor在足夠記憶體的前提下，這個比值應該是越低越好，從上面的輸出可以看出，當前的buffer cache為3248MB，如果為320M，則估計產生的物理讀會是當前buffer cache尺寸下的22.7562倍，也就是增加了2275.62%左右的物理讀（22.7562-1），而如果增加buffer cache，將其設定為6400MB，會使得物理讀減少79.54%（1-0.2046）。

綜上所述，如果在記憶體許可的情況下建議將sga_target的值調整為7250MB-8000MB之間。

PGA中，設定workarea_size_policy引數的值為auto時，啟用PGA自動管理，pga設定多大合適呢，如果資料庫為OLTP應用的，則其應用一般都是小的短的程式，所需要的pga也相應較少，所以該值通常為總共分配給Oracle例項的20%，另外的80%給SGA，如果是OLAP的應用，建議各佔50%。而如果資料庫是混合型別的，一般會先分配給pga 40%的初始值，然後隨著應用不斷監控和調整PGA。

比如8GB的實體記憶體，按照Oracle的推薦，分配給Oracle例項的記憶體為實體記憶體的80%，對於OLAP應用來說，pga_aggregate_target的值大約就是1310MB（8192*80%*20%），而對於OLAP應用來說，則該值大約就是3276MB，而對於混合型資料庫，則該值大約就是2621MB。

當然這只是對於一個新的資料庫來說設定的初始值，這些值不一定準確，可以通過v$pga_target_advice這個檢視來確定pga的大小。

SQL> select round(t.PGA_TARGET_FOR_ESTIMATE/1024/1024) "target M",

t.ESTD_PGA_CACHE_HIT_PERCENTAGE "Est Cache Hit %",

round(t.ESTD_EXTRA_BYTES_RW/1024/1024) "Est RW M",

t.ESTD_OVERALLOC_COUNT "alloc" from v$pga_target_advice t;

target M Est Cache Hit % Est RW M alloc

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

238 89 20169 11429

476 100 67 0

952 100 67 0

1427 100 67 0

1903 100 67 0

2284 100 0 0

2664 100 0 0

3045 100 0 0

3425 100 0 0

3806 100 0 0

5709 100 0 0

7612 100 0 0

11418 100 0 0

15224 100 0 0

14 rows selected

該輸出告訴我們，按照系統目前的準轉情況，隨著PGA增加，estd_pga_cache_hit_percentage不斷增加，同時estd_extra_bytes_rw(sql語句與磁碟上臨時表空間互動讀寫的位元組數)不斷減小,由此可以知道，將PGA_aggregate_target設定為476M是最合理的。當然，如果可能的話設定為2284MB則更好。

因此Oracle的總的記憶體為SGA+PGA=7250+500=7750MB 或者8000+2284=10284MB

這樣實體記憶體至少要達到10-12GB才能滿足要求。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8554499/viewspace-622207/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 10g的記憶體調整報告

Oracle 10g的記憶體調整報告

相關文章