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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-622151/