oracle實驗記錄(buffer_cache分析(3)cbc lru chain latch)

cache buffer lru chain latch
buffer header 除了在hash bucket中 cache buffer chains連結串列，還掛在 lru,lruw,ckptq連結串列lru含各種狀態的buffer,lruw含dirty buffer ,lru 與lruw互相排斥，一對lru+lruw叫做一個working set，每個working set由一個cache buffers lru chain latch保護 既working set數 由cache buffers lru chain latch確定
當server process在buffer cache中尋找free buffer時候訪問lru表
當後臺程式dbwr訪問lru 將dirty buffer從lru移入lruw，從lruw中清除dirty buffer時
上面2種 操作程式都需要獲得cache buffer lru chain latch

 

SQL> select name,latch#,addr from v$latch_children  where name like '%cache buff
ers lru chain%';

NAME                                                   LATCH# ADDR
-------------------------------------------------- ---------- --------
cache buffers lru chain                                   117 2024E7AC
cache buffers lru chain                                   117 2024EB5C
cache buffers lru chain                                   117 2024EF0C
cache buffers lru chain                                   117 2024F2BC
cache buffers lru chain                                   117 2024F66C
cache buffers lru chain                                   117 2024FA1C
cache buffers lru chain                                   117 2024FDCC
cache buffers lru chain                                   117 2025017C

已選擇8行。

 

SQL> select t.name,s.set_id,s.set_latch from x$kcbwds s left join v$latch_childr
en t on (s.set_latch=t.addr);

NAME                                                   SET_ID SET_LATC
-------------------------------------------------- ---------- --------
cache buffers lru chain                                     1 2024E7AC~~~~~~~~~~~set_id表示working set id號，latch表示latch address
cache buffers lru chain                                     2 2024EB5C
cache buffers lru chain                                     3 2024EF0C
cache buffers lru chain                                     4 2024F2BC
cache buffers lru chain                                     5 2024F66C
cache buffers lru chain                                     6 2024FA1C
cache buffers lru chain                                     7 2024FDCC
cache buffers lru chain                                     8 2025017C

已選擇8行。

x$kcbwds 表示核心cache buffer working set describute

SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> select
  2    x.ksppinm  name,
  3    y.ksppstvl  value,
  4    y.ksppstdf  isdefault,
  5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj,x. KSPPDESC
  7  from
  8    sys.x$ksppi x,
  9    sys.x$ksppcv y
 10  where
 11    x.inst_id = userenv('Instance') and
 12    y.inst_id = userenv('Instance') and
 13    x.indx = y.indx and
 14    x.ksppinm like '%_&par%'
 15  order by
 16    translate(x.ksppinm, ' _', ' ')
 17  /
輸入 par 的值:  lru_latch
原值   14:   x.ksppinm like '%_&par%'
新值   14:   x.ksppinm like '%_lru_latch%'

NAME                           VALUE                     ISDEFAULT ISMOD      IS
ADJ
------------------------------ ------------------------- --------- ---------- --
---
KSPPDESC
--------------------------------------------------------------------------------
----------------------------------------------------
_db_block_lru_latches          8                         TRUE      FALSE      FA           ~~~~受這個隱藏引數影響
LSE
number of lru latches

default 為
SQL> show parameter db_writer

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------------
db_writer_processes                  integer     1
SQL> show parameter cpu_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
cpu_count                            integer     2

db_writer_processes<=4 則為cpu_count*4

db_writer_processes>4 =db_writer_processes×cpu_count  

自己修改_db_block_lru_latches 按8的倍數修改，否則無效
SQL> alter system set "_db_block_lru_latches"=10 scope=spfile;

系統已更改。

SQL> startup force
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size              92275392 bytes
Database Buffers          188743680 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
資料庫已經開啟。
SQL> set linesize 132
SQL> column name format a30
SQL> column value format a25
SQL> select
  2    x.ksppinm  name,
  3    y.ksppstvl  value,
  4    y.ksppstdf  isdefault,
  5    decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE')  ismod,
  6    decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE')  isadj,x. KSPPDESC
  7  from
  8    sys.x$ksppi x,
  9    sys.x$ksppcv y
 10  where
 11    x.inst_id = userenv('Instance') and
 12    y.inst_id = userenv('Instance') and
 13    x.indx = y.indx and
 14    x.ksppinm like '%_&par%'
 15  order by
 16    translate(x.ksppinm, ' _', ' ')
 17  /
輸入 par 的值:  lru_latch
原值   14:   x.ksppinm like '%_&par%'
新值   14:   x.ksppinm like '%_lru_latch%'

NAME                           VALUE                     ISDEFAULT ISMOD      IS
ADJ
------------------------------ ------------------------- --------- ---------- --
---
KSPPDESC
--------------------------------------------------------------------------------
----------------------------------------------------
_db_block_lru_latches          8                         FALSE     FALSE      FA
LSE
number of lru latches
SQL> show parameter _db_block_lru_latches

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_db_block_lru_latches                integer     8

SQL> select name,latch#,addr from v$latch_children  where name like '%cache buff
ers lru chain%';

NAME                               LATCH# ADDR
------------------------------ ---------- --------
cache buffers lru chain               117 2024E7AC
cache buffers lru chain               117 2024EB5C
cache buffers lru chain               117 2024EF0C
cache buffers lru chain               117 2024F2BC
cache buffers lru chain               117 2024F66C~~~~~~~~~~~~~`還是8個
cache buffers lru chain               117 2024FA1C
cache buffers lru chain               117 2024FDCC
cache buffers lru chain               117 2025017C

已選擇8行。

SQL> alter system set "_db_block_lru_latches"=32 scope=spfile;

系統已更改。

SQL> startup force
ORACLE 例程已經啟動。

_db_block_lru_latches          32                        FALSE     FALSE      F
LSE
number of lru latches
SQL> show parameter db_block_lru

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_db_block_lru_latches                integer     32
SQL>

SQL> select count(*) from v$latch_children  where name like '%cache buffers lru
chain%';

  COUNT(*)
----------
        32
SQL> alter system reset "_db_block_lru_latches" scope=spfile sid='*';～～～改回來

系統已更改。

若cache buffers lru chain latch 爭用嚴重(若process 輪詢獲得不到lru latch 將出現latch free event)
1.考慮加大buffer cache,減少讀資料到buffer cache 掃描lru list的次數（buffer cache大 不用老讀資料 不會老換出去從buffer cache 中）
2.加大lru chain latch數量
3.alter table XX cache 讓常用的FTS表不換出，避免讀回buffer cache時搜lru list
4.優化sql，少讀資料

 

補充下
row cache object latch：保護oracle 資料字典 row cache(儲存為row  而不是block)
9i shared pool引入多pool後存多個row cache object latche 的副本（9I前只有一個）
10G分離出latH: row cache object latch

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