oracle學習筆記 buffer_cache大小的設定及依據
一）先回顧一下
前面講的buffercache的工作過程和大體的結構原理
磁盤有一堆dbf檔案
記憶體有buffercache
第一步：一個sql語句進來以後
如：select * from t2 where id=1;
首先是serverprocess會拿着這個sql語句去解析
解析的時候會用到cpu資源還會通路rowcache
解析完成以後生成執行計劃再存儲到librarycache裡面去
這時sql語句的第一步解析執行完了
第二步：要執行語句
執行時要通路資料
buffercache結構中一部分為鍊區，剩下的為buffer
這裡面的鍊單元有位址指向裡面的一些buffer
這時oracle首先通過一系列的判斷以後
發現要通路的表t2裡面id等于1的行
先判斷應該在哪個鍊上
它計算完了以後它發現這個行所在的塊應該在某個鍊上但不見得在這個鍊上
然後對這個鍊進行周遊
第一種結果
在這個鍊上找到了這個block對應的buffer
既然找到了
這時候這個serverprocess直接讀這個buffer
不需要到磁盤再去讀了
第二種情況
在這個鍊上沒有找到這個block對應的buffer
這時serverprocess就會在buffer裡面找到一個可用的塊
假設一個塊可用
serverprocess把這個block讀到可用的buffer塊裡面去
同時把可用buffer塊的相關位址寫到對應鍊單元内
然後把資料接着讀出來
整個的，從鍊上找buffer，找不到時把block讀到記憶體去寫到buffer裡面去，然後再讀buffer
整個過程都是serverprocess做的
再看另外一個語句
update t2 set name=某個值 where id=1；
要修改某個block的某一行
這時同樣serverprocess經過解析以後要執行
執行時同樣判斷
這行所在的塊所在的block應該挂在某個鍊上
這裡在鍊上周遊發現周遊以後确實找到對應的buffer
直接在記憶體裡面修改buffer不須到磁盤上修改
修改完成以後buffer和block就不一緻了
這個buffer就成了一個髒的buffer
這個buffer會被挂在LRUW上和CHECKPOINT隊列上
而前面select語句結果通路的塊是幹淨的是可用的
它挂在LRU上
剛才最基本的知識都需要掌握
這是我們回顧的内容
二）DBWn程序和buffercache
接下來我們關心很多内容
某個髒塊髒了serverprocess并沒有把它寫回到磁盤上
是一個程序DBWn負責寫的，它是系統的一個程序
使用
[[email protected] ~]$ ps -ef|grep ora
查詢目前系統程序中與ora相關的程序
結果中有
oracle 3161 1 0 03:48 ? 00:00:00 ora_dbw0_jiagulun
的一個程序
此程序會把我們的髒塊寫到磁盤上
dbwn是一個背景程序
serverprocess是一個前台程序
我們的使用者送出一個sql語句
serverprocess接到sql語句
整個的從解析到執行到擷取這個過程都是serverprocess工作
serverprocess工作時使用者一直在等着，工作完了把結果返給使用者
使用者所執行的sql語句所等待的時間就是serverprocess工作的時間
是以serverprocess是直接面向使用者的叫前台程序
我們希望它越快越好
我們就希望serverprocess所做的事情越少越好
将髒塊寫回磁盤我們沒有讓serverprocess去做
而是交給一個背景程序DBWn這個程序
它的運作和使用者沒有關系，它并不影響使用者
它執行時使用者沒有在等待
它在背景周期性執行
1）髒塊和DBWn的觸發
有很多條件會觸發DBWn
1、每隔一段時間DBWn會觸發
一般觸發不會将整個的所有的髒塊全寫回磁盤
它會找LRUW鍊
将冷端的幾個髒塊寫回磁盤
因為冷端意味着不怎麼經常被使用
這是第一個每隔一個時間段
每次寫的時候寫一批不是寫所有的
2、沒有了幹淨塊
對于幹淨的和free的塊我們可以覆寫可以來使用
現在有一個block要調到記憶體裡面去
找了半天沒有找到幹淨塊沒有找到可用塊
這時也會觸發DBWn
DBWn會批量的寫一批buffer到磁盤上
寫過來的buffer和磁盤的block就一緻了
一緻以後就可用，這也是一種條件
3、資料庫關閉以後
DBWn會把所有的buffer全部寫回磁盤
1和2兩種情況最多
資料庫正常運作期間
1周期性DBWn觸發
2當我們的髒緩沖區到一定程度的時候會觸發DBWn
還有一種情況
我要在buffer裡面找一個可用塊的時候
半天都沒找着
就是找可用塊找的時間太長
雖然鍊還有剩餘，很多剩餘塊還沒有搜尋也會中途退出尋找
這裡有一個時間的門檻值我們叫閥值也可以叫門檻值
它也會觸發DBWn
總之DBWn是保證我們的髒塊能夠相對及時的合理的寫回到磁盤上
DBWn是背景
它的工作不影響前台不影響serverprocess
它在背景默默的工作
我們用資料庫的時候并不怎麼關心DBWn
2）DBWn的個數
系統程序查詢時
結果有程序名為ora_dbw0_jiagulun
其中dbw0有個數字0
既然有0，就可以有1
也就是DBWn程序可以是多個
但是我們不要去設定過多的DBWn
我們可以看一個參數
SQL> show parameter writer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_writer_processes integer 1
db_writer_processes參數目前的值是1
也就是隻有一個程序
一般的它的預設值DBWn的個數是cpu數量除上8
如果你16顆cpu的話就應該是兩個DBWn程序
一般DBWn程序是夠的
老師有一次去使用者那裡
看到有一個很奇怪的現象
那麼多的DBWn而且DBWn占用cpu很高
最後發現它改過這個參數
老師問：你為什麼要改它？
他說：我覺得多一些資料庫更快！
老師的忠告是：其實不見得！
改這個參數也很容易
SQL> alter system set db_writer_processes=2;
alter system set db_writer_processes=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
結果提示這個參數不能被修改意味着它是靜态參數要使用scope=spfile選項靜态改
SQL> alter system set db_writer_processes=2 scope=spfile;
System altered.
然後重新開機資料庫
SQL> startup force;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 104859240 bytes
Database Buffers 171966464 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
測試環境下startup force可以重新開機資料庫
但實際環境這樣重新開機很危險的
因為這樣重新開機
整個過程中的關閉資料庫過程會被強制執行
而不管有沒有正在運作的程序和正在連接配接的使用者
非常可能造成資料的丢失，和引起資料庫故障。
再去查一下
[[email protected] ~]$ ps -ef|grep ora
結果中
oracle 21914 1 0 09:09 ? 00:00:00 ora_dbw0_jiagulun
oracle 21916 1 0 09:09 ? 00:00:00 ora_dbw1_jiagulun
有這麼兩行
說明目前DBWn有兩個程序
ora_dbw0_jiagulun和ora_dbw1_jiagulun
因為我們把db_writer_processes參數值設成2了
這次教給大家DBWn程序的數量到底設幾個不要亂設
三）buffercache大小
buffercache很重要，目前為止到底該設多大呢
1）buffercache大小的查詢和修改
buffercache的設定和sharedpool的設定很相似
一種情況我們單獨的去設定
如buffercache設定1個g或2個g
另外可以把buffercache的設定放在SGA target裡面去
oracle自動去設定buffercache大小
在實際的生産中我們還是經常的去手工設buffercache大小
這句
SELECT component,current_size,min_size FROM v$sga_dynamic_components;
是查一下目前我們sga裡面的各個元件到底是多大
buffer設定大小
alter system set db_cache_size=20M scope=memory;
也可以scope=both
設定buffercache大小這裡設的是20M
一般的有一個原則
DB_CACHE_SIZE = SGA_MAX_SIZE/2～ SGA_MAX_SIZE*2/3
dbcache設的大小一般是
SGA_MAX_SIZE的二分之一到三分之二
也就是整個的SGA的一半到三分之二的空間
我們建議大家直接設成三分之二
當然這個值不是很準确
我們到時候去通過相關的指令去設定去查
先查一下sga的狀态
SQL> SELECT component,current_size,min_size FROM v$sga_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE
---------------------------------------------------------------- ------------ ----------
shared pool 100663296 96468992
large pool 4194304 4194304
java pool 4194304 4194304
streams pool 0 0
DEFAULT buffer cache 167772160 167772160
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
ASM Buffer Cache 0 0
13 rows selected.
這一行
DEFAULT buffer cache 167772160 167772160
是buffercache的大小
目前是100多M
sga總體大小為200多M
buffercache占了二分之一要多
仔細查一下設定的值
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 272M
sga_target big integer 272M
sga設定的總大小sga_target為272M
SQL> show parameter db_cache_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
db_cache_size的設定的值為預設值0，說明沒有設定大小，用的全是由SGA自動調整
我們可以自己設定大小
我們設大小隻能往大了設，往小了設沒有意義
設的值比oracle系統配置設定給它的值小了
oracle不會理會這個自己設的值仍會采用自動配置設定的值
現在改一下
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
一般真實值不會減少，仍保持在合理狀态
2）buffercache的合理值
有一個指令
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');
可以通過這個指令查一下目前資料庫buffercache該設多大
執行一下：
SQL> SELECT size_for_estimate "Cache Size (MB)",size_factor,buffers_for_estimate "Buffers",
estd_physical_read_factor est_read_factor,
2 estd_physical_reads estd_phy_red,estd_physical_read_time est_phy_red_t
3 4 FROM v$db_cache_advice
WHERE NAME='DEFAULT' AND block_size=(SELECT VALUE FROM v$parameter WHERE NAME='db_block_size'); 5
Cache Size (MB) SIZE_FACTOR Buffers EST_READ_FACTOR ESTD_PHY_RED EST_PHY_RED_T
--------------- ----------- ---------- --------------- ------------ -------------
16 .1 1996 1.7523 14677 77
32 .2 3992 1.1516 9646 48
48 .3 5988 1 8376 40
64 .4 7984 1 8376 40
80 .5 9980 1 8376 40
96 .6 11976 1 8376 40
112 .7 13972 1 8376 40
128 .8 15968 1 8376 40
144 .9 17964 1 8376 40
160 1 19960 1 8376 40
176 1.1 21956 1 8376 40
192 1.2 23952 1 8376 40
208 1.3 25948 1 8376 40
224 1.4 27944 1 8376 40
240 1.5 29940 1 8376 40
256 1.6 31936 1 8376 40
272 1.7 33932 1 8376 40
288 1.8 35928 1 8376 40
304 1.9 37924 1 8376 40
320 2 39920 1 8376 40
20 rows selected.
我們看一下這些列
Cache Size (MB)是假設的大小
如第一行結果
16 .1 1996 1.7523 14677 77
設16MB的話會有1996個buffer
目前資料庫設定的塊的大小
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
是以一個buffer是8K
16MB除以8K就是第一行Buffers列的值
EST_READ_FACTOR 為預估的實體讀因子
ESTD_PHY_RED 預估的實體讀的次數
EST_PHY_RED_T 預估的實體讀時間
我們可以以實體讀時間作為标準
因為buffercache最最突出的作用是緩存block
目的是減少實體io
這樣就減少了實體io讀的時間
如果設16M的話
預測的實體讀次數14677，系統的實體讀時間是77
如果設32M的話
預測的實體讀次數9646，實體讀時間是48
ESTD_PHY_RED和EST_PHY_RED_T基本上是同步的
次數多時間就長
對我們目前資料庫來講實際上
因為我的資料庫沒有任何負載48M就可以了
因為你設的再怎麼大
它對實體讀的次數和實體讀的時間的減少沒有任何意義
在實際生産中我們就是找這個數值
它對應的這行最後邊三列的值，在此行以後的行這三列的數值變化不大的時候
找此行Cache Size (MB)值設定就可以了
視訊課程中我們可以看到
老師個人比較喜歡
把平時要做的很多操作的一些sql語句提前給它找出來
找好了以後給它寫到txt檔案裡面去
以後幹活的時候在txt檔案中找直接複制出來用就行了
這樣它就成為了老師的一個工具
老師希望學生回去以後也要在這方面要去好好的去積累
積累以後你幹活的時候有工具
你幹活的時候就會非常的好
是以這些收集的語句都很重要
這個地方我們講了buffercache該設多大怎麼設
還教大家怎麼看目前大小
這是buffercache
知道這個以後基本的操作基本上都會了
這節課主要内容講了buffercache的設定
後面的課是block的狀态
block狀态其實應該是buffer的狀态
裡面好多東西可能大家不太了解需要詳細的講一下
内容還有一堆sql語句
2016年9月25日
文字：韻筝
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)