檢視buffer cache的大小：
SQL> SELECT component,current_size,min_size FROM v$sga_dynamic_components;

COMPONENT CURRENT_SIZE MIN_SIZE
---------------------------------------------------------------- ------------ ----------
shared pool 5570035712 5301600256
large pool 67108864 67108864
java pool 67108864 67108864
streams pool 67108864 67108864
DEFAULT buffer cache 1.1409E+10 1.1140E+10
KEEP buffer cache 0 0
RECYCLE buffer cache 0 0
DEFAULT 2K buffer cache 0 0
DEFAULT 4K buffer cache 0 0
DEFAULT 8K buffer cache 0 0
DEFAULT 16K buffer cache 0 0
DEFAULT 32K buffer cache 0 0
Shared IO Pool 0 0
ASM Buffer Cache 0 0
14 rows selected.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 16512M
sga_target big integer 16512M
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
db_cache_size的設定的值為預設值0，說明沒有設定大小，用的全是由SGA自動調整

這裡可以看到buffer cache是系統自己自動分配的，大概是10880MB。

舉個例子：

現在改一下
alter system set db_cache_size=200M scope=both;

SQL> alter system set db_cache_size=200M scope=both;
alter system set db_cache_size=200M scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient memory to grow cache
結果顯示沒有足夠的記憶體去增加

就是sga_target已經就這麼大了，都分完了
都分給各個池子了
這時要把DEFAULT buffer cache的值增加
要增加這個buffercache池子的大小的話
首先增加sga的大小

這地方首先說一下思路
首先增加sga_max_size大小
然後再增加sga_target
最後再增加db_cache_size

語句分別為

alter system set sga_max_size=500M scope=spfile;
alter system set sga_target=400M;
alter system set db_cache_size=200M;

其中的scope選項有三個可取值

scope=spfile
表示將引數值存入spfile檔案，即改變oracle的初始化引數檔案內容，在重啟資料庫後才能生效
scope=memory
表示改變正在記憶體中執行的例項的引數值，只在記憶體上修改，立即生效，
關閉例項此值就會丟失而被還原為原已儲存的值，因為這個修改並沒有寫入到初始化引數檔案
scope=both
表示將引數值同時存入spfile和memory，立即生效，因為存入了spfile重啟資料庫後仍然有效。

both是預設值，修改靜態引數必須使用spfile，動態引數三種方式都可以用。

spfile檔案的位置
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/10.2.0
                                                 /db_1/dbs/spfilejiagulun.ora

為了保護資料庫的安全有的引數進行了保護把它指定為靜態狀態
修改值時要加額外引數並且要資料庫重啟才能生效
但大部分引數是動態的可以隨時修改隨時生效
sga_max_size是靜態引數，sga_target和db_cache_size是動態引數

這三個引數目前值
sga_max_size                         big integer 272M
sga_target                           big integer 272M
db_cache_size                        big integer 0
因為sga_max_size和sga_target當前值相同
所以這裡必須先修改sga_max_size並重啟資料庫後
才能繼續修改sga_target的值使其增加
在sga有足夠空間時才能增加db_cache_size的值

我的修改過程
在sga_target把值增加後就會立即自動把剩餘的空間全部分配給db_cache_size
以為是因為db_cache_size使用預設值0，
但手工對db_cache_size分配值後效果仍然一樣。

在修改db_cache_size的值時

增加時是有限制的
sga要根據當前所有的狀態值分析有沒有足夠的空間滿足這個增大的值

減少時一般沒限制
因為這個值即使設定小了但sga仍使用自動分配空間分配給db_cache_size
一般真實值不會減少，仍保持在合理狀態

設定buffer cache的合理值：
SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size');
Cache Size (MB) SIZE_FACTOR Buffers EST_READ_FACTOR ESTD_PHY_RED EST_PHY_RED_T
--------------- ----------- ---------- --------------- ------------ -------------
1088 .1 128520 12.0303 5839773844 10813098
2176 .2 257040 4.7929 2326564251 3913685
3264 .3 385560 2.4136 1171610058 1645529
4352 .4 514080 1.5546 754629867 826643
5440 .5 642600 1.2682 615616449 553642
6528 .6 771120 1.1589 562544805 449417
7616 .7 899640 1.0981 533021196 391437
8704 .8 1028160 1.0541 511676762 349520
9792 .9 1156680 1.0238 496952824 320604
10880 1 1285200 1 485421183 297958
11968 1.1 1413720 .9812 476284469 280015
13056 1.2 1542240 .9644 468161067 264061
14144 1.3 1670760 .9495 460909360 249820
15232 1.4 1799280 .9358 454276548 236794
16320 1.5 1927800 .9253 449182455 226790
17408 1.6 2056320 .9128 443069856 214786
18496 1.7 2184840 .9015 437583045 204011
19584 1.8 2313360 .8913 432648923 194321
20672 1.9 2441880 .8659 420332933 170134
21760 2 2570400 .8412 408330158 146563

20 rows selected.

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

一個塊大小是8192
1088MB除以8K就是第一行的buffers列的值128520

EST_READ_FACTOR 為預估的物理讀因子
ESTD_PHY_RED 預估的物理讀的次數
EST_PHY_RED_T 預估的物理讀時間

我們可以以物理讀時間作為標準
因為buffercache最最突出的作用是快取block
目的是減少物理io
這樣就減少了物理io讀的時間

ESTD_PHY_RED和EST_PHY_RED_T基本上是同步的
次數多時間就長

在實際生產中我們就是找這個數值
它對應的這行最後邊三列的值，在此行以後的行這三列的數值變化不大的時候
找此行Cache Size (MB)值設定就可以了

相關文章：
http://blog.csdn.net/xinzhan0/article/details/52663078
http://blog.itpub.net/12679300/viewspace-1185623/

Oracle 11g buffer cache的設定

相關文章