buffer pool 和shared pool 詳解（一），buffershared

buffer pool 和shared pool 詳解（一），buffershared

【深入解析--eygle】學習筆記

1.1 buffer pool原理

Buffer Cache是Oracle SGA中一個重要部分，通常的資料訪問和修改都需要通過BufferCache來完成。當一個進程需要訪問資料時，首先需要確定資料在記憶體中是否存在，如果資料在Buffer中存在，則需要根據資料的狀態來判斷是否可以直接存取還是需要構造一致性讀取；如果資料在Buffer中不存在，則需要在Buffer Cache中尋找足夠的空間以裝載需要的資料，如果Buffer  Cache中找不到足夠的記憶體空間，則需要觸發DBWR去寫出髒資料，釋放Buffer空間。

1.1.1 LRU 與LRUW List

在Buffer Cache中，Oracle通過幾個鏈表進行記憶體管理，其中最為熟知的是LRU List和LRUW List（也經常被稱為Write/Dirty List），各種List上存放的是指向具體的Buffer的指標等資訊。

從Oracle8開始，為了實施增量檢查點，Oracle還引入了檢查點隊列- Checkpoint Queue和檔案隊列  –  File Queue；從Oracle8i開始，由於非同步DBWn的引入，現在關於各種List以及Queue的更為精確的概念是工作集（WS - Working Sets）， 在 每 個WS中包含幾個不同功能的List，每 個List都通過Cache Buffers LRU CHAIN Latch進行保護，當使 用 多 個DBWR進程時（通過DB_WRITER_PROCESSES參數可以設定資料庫使用多個DBWR進程），資料庫中會存在多個WS，同時當使用Buffer Cache的多緩衝池技術時，每個獨立的緩衝池也會存在各自獨立的WS。

LRU List用於維護記憶體中的Buffer，按照LRU演算法進行管理（在不同版本中，管理方式有所不同），資料庫初始化時，所有的Buffer都被Hash到LRUList上管理。當需要從資料檔案上讀取資料時，首先要在LRU List上尋找Free的Buffer，然 後 讀 取 數 據 到Buffer Cache中；當資料被修改之後，狀態變為Dirty，就可以被移動至LRUW List，LRUW List上的都是候選的可以被DBWR寫出到資料檔案的Buffer，一個Buffer要麼在LRU List上，要麼在LRUW List上存在，不能同時在這兩個List上存在。

是Buffer Cache中LRU及LRUW List的簡要：

 

 

檢查點隊列（Checkpoint Queue）則負責按照資料區塊的修改順序記錄資料區塊，同時將RBA和資料區塊關聯起來，這樣在進行增量檢查點時，資料庫可以按照資料區塊修改的先後順序將其寫出，從而在進行恢複時，可以根據最後寫出資料區塊及其相關的RBA開始進行快速恢複。在檢查點觸發時DBWR根據檢查點隊列執行寫出，在其他條件觸發時，DBWR由Dirty List執行寫出。檢查點隊列的記憶體在Shared Pool記憶體中分配：

 

同樣具有相關Latch對其進行保護：

15:48:38 sys@felix SQL>select name ,gets,missesfrom v$latch where name like '%checkpoint queue%';

 

NAME                                          GETS     MISSES

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

active checkpoint queue latch                 10247          0

checkpoint queue latch                       145659          1

 

15:48:39 sys@felix SQL>

 

 

可以通過來詳細介紹一下BufferCache的原理及使用：

 

1．當一個Server進程需要讀資料到Buffer Cache中時，首先必須判斷該資料在Buffer

中是否存在（圖中①所示過程），如 果 存 在 且 可 用 ，則擷取該資料，同時根據LRU

演算法增進其訪問計數；如果Buffer中不存在該資料，則需要從資料檔案上進行讀取。

2．在讀取資料之前，Server進程需要掃描LRU List尋找Free的Buffer，掃描過程中Server進程會把發現的所有已經被修改過的Buffer註冊到LRUW List上（圖中②所示過程），這些Dirty Buffer隨後可以被寫出到資料檔案。

3． 如果LRUW  Queue超過了閥值，Server進程就會通知DBWn去寫出髒資料（圖中③所示過程）；

 

這也是觸發DBWn寫的一個條件，這個閥值曾經提到是25%，也就是當Dirty Queue超過25%滿就會觸發DBWn的寫操作：

 

16:13:58 sys@felix SQL>select kvittag, kvitval,kvitdsc from x$kvit where kvittag ='kcbldq';

 

KVITTAG                 KVITVAL KVITDSC

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

kcbldq                       25 large dirty queue ifkcbclw reaches this

 

16:14:04 sys@felix SQL>

 

如果Server進程掃描LRU超過一個閥值仍然不能找到足夠的Free Buffer，將停止尋找，轉而通知DBWn去寫出髒資料，釋放記憶體空間。

 

同樣這個閥值可以從以上字典表中查詢得到，這個數字是40%，也就是說當Server進程掃描LRU超過40%還沒能找到足夠的Free  Buffer就會停止搜尋，通知DBWn執行寫出，這是進程會處於free buffer wait等待

 

 

16:18:03 sys@felix SQL>col  KVITDSC for a60

16:18:20 sys@felix SQL>select kvittag, kvitval,kvitdsc from x$kvit where kvittag = 'kcbfsp';

 

KVITTAG                 KVITVAL KVITDSC

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

kcbfsp                       40 Max percentage of LRUlist foreground can scan for free

 

16:18:24 sys@felix SQL>

 

 

同時我們知道，由於增量檢查點的引入，DBWn也會主動掃描LRU  List，將發現的Dirty  Buffer註冊到Dirty  List以及Checkpoint  Queue，這個掃描也受一個內部約束，在Oracle9iR2中，這個比例是25%：

4．  找到足夠的Buffer之後，Server進程就可以將Buffer從資料檔案讀入Buffer Cache（圖中④所示過程）

5．  如果讀取的Block不滿足讀一致性需求，則Server進程需要通過當前Block版本和復原段構造前鏡像返回給使用者。

從Oracle 8i開始，LRU List和LRUW List又分別增加了輔助List（AUXILIARY List），用於提高管理效率。引入了輔助List之後，當資料庫初始化時，Buffer首先存放在LRU的輔助List上（AUXILIARY  RPL_LST），當被使用後移動到LRU主List上（MAIN  RPL_LST），這樣當使用者進程搜尋FreeBuffer時就可以從LRU-AUX List 開始，而DBWR搜尋Dirty Buffer時，則可以從LRU-Main List開始，從而提高了搜尋效率和資料庫效能。

可以通過如下命令轉儲Buffer Cache的內容，從而清晰的看到以上描述的資料結構：

 

 

alter session set events 'immediate trace name buffers level4';

 

16:33:14 sys@felix SQL>select value from v$diag_info;

 

VALUE

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

/u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_7187.trc

 

 

 

不同level轉儲的內容詳細程度不同，此命令的可用層級主要有1～10級，其中各層級的含義如下。

（1）  Level 1：僅包含BufferHeaders資訊。

（2）  Level 2：包含BufferHeaders和Buffer概要資訊轉儲。

（3）  Level 3：包含BufferHeaders和完整Buffer內容轉儲。

（4）  Level 4：Level 1 +Latch轉儲  + LRU隊列。

（5）  Level 5：Level 4 +Buffer概要資訊轉儲。

（6）  Level 6和Level 7：Level 4 + 完整的Buffer內容轉儲。

（7）  Level 8：Level 4 + 顯示users/waiters資訊。

（8）  Level 9：Level 5 + 顯示users/waiters資訊。

（9）  Level 10：Level 6 + 顯示users/waiters資訊

 

轉儲僅限於在測試環境中使用，轉儲的追蹤檔案可能非常巨大，為擷取完整的追蹤檔案，建議設定初始化參數max_dump_file_size為UNLIMITED。

 

16:33:27 sys@felix SQL>show parameter max_dump

 

NAME                                 TYPE                   VALUE

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

max_dump_file_size                   string                 unlimited

16:47:37 sys@felix SQL>show parametermemory_target

 

NAME                                 TYPE                   VALUE

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

memory_target                        big integer            400M

16:48:46 sys@felix SQL>

 

 

 

查看檔案 /u01/app/oracle/diag/rdbms/felix/felix/trace/felix_ora_7187.trc資訊：

從Level 4級追蹤檔案的開頭部分可以獲得如下資訊，這是 記 錄 的 不 同List的Prev和Next定位資訊。其中WS就是指WorkingSets，注意WSID指不同WS的編號：

 

*** 2014-07-22 16:33:13.185

*** SESSION ID:(41.237) 2014-07-22 16:33:13.185

*** CLIENT ID:() 2014-07-22 16:33:13.185

*** SERVICE NAME:(SYS$USERS) 2014-07-2216:33:13.185

*** MODULE NAME:(sqlplus@felix (TNS V1-V3))2014-07-22 16:33:13.185

*** ACTION NAME:() 2014-07-22 16:33:13.185

 

Dump of buffer cache at level 4 for tsn=2147483647rdba=0

  (WS) size: 0 (0) wsid: 1 state: 0 pool: 1

   (WS_REPL_LIST) main_prev: 0x774b2f18 main_next: 0x774b2f18 aux_prev:0x774b2f28 aux_next: 0x774b2f28

    curnum:0 auxnum: 0

    cold:774b2f18 hbmax: 0 hbufs: 0

   (WS_WRITE_LIST) main_prev: 0x774b2f48 main_next: 0x774b2f48 aux_prev:0x774b2f58 aux_next: 0x774b2f58

    curnum:0 auxnum: 0

   (WS_XOBJ_LIST) main_prev: 0x774b2f78 main_next: 0x774b2f78 aux_prev:0x774b2f88 aux_next: 0x774b2f88

    curnum:0 auxnum: 0

   (WS_XRNG_LIST) main_prev: 0x774b2fa8 main_next: 0x774b2fa8 aux_prev:0x774b2fb8 aux_next: 0x774b2fb8

    curnum:0 auxnum: 0

   (WS_REQ_LIST) main_prev: 0x774b2fd8 main_next: 0x774b2fd8 aux_prev:0x774b2fe8 aux_next: 0x774b2fe8

    curnum:0 auxnum: 0

   (WS_L2WRT_LIST) main_prev: 0x774b3008 main_next: 0x774b3008 aux_prev:0x774b3018 aux_next: 0x774b3018

    curnum:0 auxnum: 0

   (WS_L2REPL_LIST) main_prev: 0x774b3038 main_next: 0x774b3038 aux_prev:0x774b3048 aux_next: 0x774b3048

    curnum:0 auxnum: 0

   (WS_L2KEEP_LIST) main_prev: 0x774b3068 main_next: 0x774b3068 aux_prev:0x774b3078 aux_next: 0x774b3078

    curnum:0 auxnum: 0

  (WS) fbwanted: 0

  (WS) bgotten: 0 sumwrt: 0

  (WS) pwbcnt: 0, last: 0

MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]

 

接下來是具體的List鏈表資訊，注意這裡存在多條NULL列表，這是為Buffer  Cache不同部分（Keep池、Recycle池以及不同block_size大小的記憶體使用量）預分配的List：

 

MAIN RPL_LST Queue header(NEXT_DIRECTION)[NULL]

MAIN RPL_LST Queue header(PREV_DIRECTION)[NULL]

AUXILIARY RPL_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY RPL_LST Queue header(PREV_DIRECTION)[NULL]

MAIN WRT_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN WRT_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY WRT_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY WRT_LST Queue header(PREV_DIRECTION)[NULL]

MAIN XOBJ_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN XOBJ_LST Queue header(PREV_DIRECTION)[NULL]

AUXILIARY XOBJ_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY XOBJ_LST Queue header(PREV_DIRECTION)[NULL]

MAIN XRNG_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN XRNG_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY XRNG_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY XRNG_LST Queue header(PREV_DIRECTION)[NULL]

MAIN REQ_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN REQ_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY REQ_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY REQ_LST Queue header(PREV_DIRECTION)[NULL]

MAIN L2W_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN L2W_LST Queue header(PREV_DIRECTION)[NULL]

AUXILIARY L2W_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY L2W_LST Queue header(PREV_DIRECTION)[NULL]

MAIN L2R_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN L2K_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN L2R_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY L2R_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY L2K_LST Queue header(NEXT_DIRECTION)[NULL]

MAIN L2K_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY L2K_LST Queue header(NEXT_DIRECTION)[NULL]

AUXILIARY L2K_LST Queue header(PREV_DIRECTION)[NULL]

  (WS) size:0 (0) wsid: 2 state: 0 pool: 2

   (WS_REPL_LIST) main_prev: 0x774ce4b0 main_next: 0x774ce4b0 aux_prev:0x774ce4c0 aux_next: 0x774ce4c0

    curnum:0 auxnum: 0

    cold:774ce4b0 hbmax: 0 hbufs: 0

   (WS_WRITE_LIST) main_prev: 0x774ce4e0 main_next: 0x774ce4e0 aux_prev:0x774ce4f0 aux_next: 0x774ce4f0

    curnum:0 auxnum: 0

   (WS_XOBJ_LIST) main_prev: 0x774ce510 main_next: 0x774ce510 aux_prev:0x774ce520 aux_next: 0x774ce520

    curnum:0 auxnum: 0

   (WS_XRNG_LIST) main_prev: 0x774ce540 main_next: 0x774ce540 aux_prev:0x774ce550 aux_next: 0x774ce550

    curnum:0 auxnum: 0

   (WS_REQ_LIST) main_prev: 0x774ce570 main_next: 0x774ce570 aux_prev:0x774ce580 aux_next: 0x774ce580

    curnum:0 auxnum: 0

   (WS_L2WRT_LIST) main_prev: 0x774ce5a0 main_next: 0x774ce5a0 aux_prev:0x774ce5b0 aux_next: 0x774ce5b0

    curnum:0 auxnum: 0

   (WS_L2REPL_LIST) main_prev: 0x774ce5d0 main_next: 0x774ce5d0 aux_prev:0x774ce5e0 aux_next: 0x774ce5e0

    curnum:0 auxnum: 0

   (WS_L2KEEP_LIST) main_prev: 0x774ce600 main_next: 0x774ce600 aux_prev:0x774ce610 aux_next: 0x774ce610

    curnum:0 auxnum: 0

  (WS)fbwanted: 0

  (WS)bgotten: 0 sumwrt: 0

  (WS)pwbcnt: 0, last: 0

MAIN RPL_LST Queue header (NEXT_DIRECTION)[NULL]

MAIN RPL_LST Queue header (PREV_DIRECTION)[NULL]

AUXILIARY RPL_LST Queue header (NEXT_DIRECTION)[NULL]

 

從以上輸出還可以看到，Buffer  Cache中除了RPL_LST和WRT_LST外還存在其他分類的List，作用各不相同。Buffer Cache的多緩衝池以及多WS結構如下所示

 

 

同時在Level  4級的轉儲中，再向下可以看到主要RPL_LST的隊列資訊，這也是鏈表的一個最直觀表現：

 

  (WS)bgotten: 17019 sumwrt: 6954

  (WS)pwbcnt: 0, last: 25

MAIN RPL_LST Queue header(NEXT_DIRECTION)[0x6a7e6170,0x6bbe17d0]

0x6a7e6088=>0x6a7e61b8=>0x6a7e62e8=>0x6a7e6418=>0x6a7e6548=>0x6a7e6678=>0x6a7e67a8=>0x6a7e68d8

0x6a7e6a08=>0x6b3e8c78=>0x6bfd8608=>0x6cbf9c68=>0x6bbf4358=>0x69bdf5a8=>0x6cbd8278=>0x6abfc268

0x6abfc398=>0x6abfc4c8=>0x6abfc5f8=>0x6afd8018=>0x6afd8148=>0x6afd8278=>0x6afd83a8=>0x6afd84d8

0x6afd8738=>0x6afd8868=>0x6afd8f88=>0x6afd8e58=>0x6afd8998=>0x6b7f38a8=>0x6afdc038=>0x6afdc168

0x6afdc4f8=>0x6afdc628=>0x6afdc758=>0x6afdc888=>0x6afdd208=>0x6afdd338=>0x6afdd468=>0x6afdd6c8

0x6afdd7f8=>0x6afdd928=>0x6afdda58=>0x6afddb88=>0x6afddcb8=>0x6afddde8=>0x6afddf18=>0x6afde048

0x6afde178=>0x6afde2a8=>0x6afde3d8=>0x6afde508=>0x6afde898=>0x6afde9c8=>0x6afdeaf8=>0x6afdec28

0x6afded58=>0x6afdf0e8=>0x6afdf218=>0x6afdf478=>0x6afdf5a8=>0x6afdf6d8=>0x6afdfb98=>0x6afe0e98

0x6afe36f8=>0x6afe54a8=>0x6afe55d8=>0x6afe5708=>0x6afe5968=>0x6afe5bc8=>0x6afe5cf8=>0x6afe5e28

0x6afe62e8=>0x6afe6418=>0x6b7f2478=>0x6afe6a08=>0x6afe6b38=>0x6afe6c68=>0x6afe6d98=>0x6afe6ec8

0x6afe6ff8=>0x6afe7128=>0x6afe7258=>0x6afe7f68=>0x6afe8b48=>0x6afe8c78=>0x6afe9988=>0x6afe9ab8

0x6afe9be8=>0x6afe9f78=>0x6afea0a8=>0x6afea1d8=>0x6afea8f8=>0x6afeb3a8=>0x6afeb998=>0x6afebac8

0x6afebbf8=>0x6afebf88=>0x6afec0b8=>0x6afec1e8=>0x6afec318=>0x6afec578=>0x6afec6a8=>0x6afec7d8

0x6afec908=>0x6afeca38=>0x6afecdc8=>0x6afecef8=>0x6afed028=>0x6afed158=>0x6afed288=>0x6afed3b8

 

誰佔用了我的Buffer Pool?(sql server緩衝之

比方說，能否知道是哪個資料庫，哪個表，哪個index佔用了buffer Pool嗎？”當時我沒有找到這個問題的答案，但是我一直記著這個問題。直到SQL server 2005 版本出現，這個問題迎刃而解。答案就是使用動態視圖（DMV）sys.dm_os_buffer_descriptors。這個DMV非常強大。根據SQL Server 聯機叢書，這個視圖的作用是 “返回有關 SQL Server 緩衝池中當前所有資料頁的資訊。可以使用該視圖的輸出，根據資料庫、對象或類型來確定緩衝池內資料庫頁的分布”。具體點說，這個視圖能夠返回buffer pool裡面一個8K 的data page的下列屬性：(1)該頁屬於哪個資料庫(2)該頁屬於資料庫哪個檔案(3)該頁的Page_ID(4)該頁的類型。可以根據這個來判斷此頁時索引頁還是資料頁(5)該頁內有多少行資料(6)該頁有多少可用空間。(7)該頁從磁碟讀取以來是否修改過。有了上面的資訊，我們就可以很方便的統計出幾種很有用的資料，如下。1. Buffer Pool的記憶體主要是由那個資料庫佔了？SELECTcount(*)*8 as cached_pages_kb,CASE database_id WHEN 32767 THEN'ResourceDb' ELSEdb_name(database_id) ENDAS Database_nameFROMsys.dm_os_buffer_descriptorsGROUPBYdb_name(database_id),database_idORDERBY cached_pages_kb DESC;結果如下：從上面的結果可以看到資料庫AdventureWorks佔用了大概30MB左右的緩衝池空間。注意該DMV 並不返回Buffer Pool裡面有關非資料頁(如執行計畫的緩衝等)的資訊。也就是說這個DMV並沒有返回Buffer Pool裡面所有頁面的資訊。2. 再具體一點，當前資料庫的哪個表或者索引佔用Pool緩衝空間最多? SELECTcount(*)*8 AS cached_pages_kb ,obj.name ,obj.index_id,b.type_desc,b.nameFROMsys.dm_os_buffer_descriptorsAS bd INNERJOIN ( SELECTobject_name(object_id)AS name ,index_id ,allocation_unit_id,object_id FROMsys.allocation_unitsAS au INNERJOINsys.partitionsAS p ON au.container_id = p.hobt_id AND(au.type= 1 OR au.type= 3) UNIONALL SELECTobject_name(object_id)AS name ,index_id, allocation_unit_id,object_id FROMsys.allocation_unitsAS au INNERJOINsys.partitionsAS p ON au.container_id = p.partition_id AND au.type= 2 )AS obj ON bd.allocation_unit_id = obj.allocation_unit_id LEFTJOINsys.indexes b on b.object_id= obj.object_idAND......餘下全文>>
 
Oracle 的 shared_pool_size的value 怎更改變大小 我想讓它有值 現在它一直是0

額