Latch cache buffers chains大约是Oracle中child latch数量最多,使用最为频繁的闩锁了。其子闩总数受到初始化参数(8i中的db_block_buffers/4)的影响,Oracle中有大量kernel函数有机会接手持有cache buffer chains latch:
col parent_name for a25
col location for a40

SELECT t1.ksllasnam "parent_name",
       t2.ksllwnam  "location"
  FROM x$ksllw t2, x$kslwsc t1
 WHERE t2.indx = t1.indx
   AND ksllasnam = 'cache buffers chains';

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbw_activate_granule
cache buffers chains      kcbw_first_buffer_free
cache buffers chains      kcbwxb
cache buffers chains      kcbw_examine_granule
cache buffers chains      kcbw_next_free
cache buffers chains      kcbw_first_buffer_free_2
cache buffers chains      kcbbckb
cache buffers chains      kcbbioe
cache buffers chains      kcbbic1
cache buffers chains      kcbbcwd
cache buffers chains      kcbbxsv

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbbwdb
cache buffers chains      kcbbic2
cache buffers chains      kcbkzs
cache buffers chains      kcbrmf2so
cache buffers chains      kcbget: exchange rls
cache buffers chains      kcbralloc
cache buffers chains      kcbgcur: deadlock
cache buffers chains      kcbgcur: buf pinned
cache buffers chains      kcbgtcr
cache buffers chains      kcbchg: no fast path
cache buffers chains      kcbchg: apply change

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbrra: buf exists
cache buffers chains      kcbrra: update buf flags
cache buffers chains      kcbema: find buf
cache buffers chains      kcbtema: find buf
cache buffers chains      kcbget: prewarm wait
cache buffers chains      kcbrfrebuf
cache buffers chains      kcbsod1
cache buffers chains      kcbrbrl
cache buffers chains      kcbgcur: kslbegin
cache buffers chains      kcbgtcr: kslbegin shared
cache buffers chains      kcbrls: kslbegin

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbchg: kslbegin: bufs not pinned
cache buffers chains      kcbchg: kslbegin: call CR func
cache buffers chains      kcbnlc
cache buffers chains      kcbget: exchange
cache buffers chains      kcbget: pin buffer
cache buffers chains      kcbnew: new latch again
cache buffers chains      kcbgkcbcr
cache buffers chains      kcbget: in cur_read
cache buffers chains      kcbesc: escalate
cache buffers chains      kcblbi
cache buffers chains      kcbcge

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbfrl
cache buffers chains      kcbzsc
cache buffers chains      kcbibr
cache buffers chains      kcbnew_1
cache buffers chains      kcbema
cache buffers chains      kcbsrbd
cache buffers chains      kcbso1: set no access
cache buffers chains      kcbtema
cache buffers chains      kcbso1: in done_clr
cache buffers chains      kcbsod2
cache buffers chains      kcbzcg

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzre1
cache buffers chains      kcbrlb1
cache buffers chains      kcbchkrsod
cache buffers chains      kcbxbh
cache buffers chains      kcbzsck
cache buffers chains      kcbgtcr: fast path
cache buffers chains      kcbgtcr: kslbegin excl
cache buffers chains      kcbgtcrf
cache buffers chains      kcbfdgd
cache buffers chains      kcbdng
cache buffers chains      kcbbufaddr2hdr

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbo_ivd_process
cache buffers chains      kcbo_write_process
cache buffers chains      kcbo_exam_buf
cache buffers chains      kcb_pre_apply: kcbhq61
cache buffers chains      kcb_post_apply: kcbhq62
cache buffers chains      kcb_post_apply: kcbhq63
cache buffers chains      kcbnew : new esc failed
cache buffers chains      kcbesc : escalate failed
cache buffers chains      kcb_private_owner
cache buffers chains      kcb_is_private
cache buffers chains      kcb_unprivatize

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcb_restore_block_headers
cache buffers chains      kcb_flush_undo_buffers
cache buffers chains      kcbgcur - DEADL
cache buffers chains      kcbtbd
cache buffers chains      kcbzwc
cache buffers chains      kcbzwx
cache buffers chains      kcbrmflx
cache buffers chains      kcbzwb
cache buffers chains      kcbzgb: get latch after post
cache buffers chains      kcbzgb: scan from tail. nowait
cache buffers chains      kcbzgb: exit_loop

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzib: multi-block read: nowait
cache buffers chains      kcbzib: finish free bufs
cache buffers chains      kcbzcb
cache buffers chains      kcbzdh
cache buffers chains      kcbdpr
cache buffers chains      kcbcxx
cache buffers chains      kcbzrn
cache buffers chains      kcbdpd: for specific dba
cache buffers chains      kcbdpd: dump all buffers
cache buffers chains      kcbzib: exchange rls
cache buffers chains      kcbzpnd: dump buffers

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbzhngcbk1: get hash chain latch no wai
                          t

cache buffers chains      kcbo_cxx
cache buffers chains      kcbz_check_obj_reuse_sanity
cache buffers chains      kcbzib_grlk
cache buffers chains      kcbz_force_maps
cache buffers chains      kcbrldflx: recover in-flux bufs
cache buffers chains      kcbra1fbuf: recover in-flux bufs
cache buffers chains      kcbrafb: flashback bufs:1
cache buffers chains      kcbrafb: flashback bufs:2

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kcbr_media_apply: find buffer
cache buffers chains      kcbr_issue_read: alloc buffer
cache buffers chains      kcbr_issue_read: retry alloc
cache buffers chains      kcbr_validate_read: mark corrupt
cache buffers chains      kcbr_apply_change: after apply
cache buffers chains      kcbr_mapply_change
cache buffers chains      kcbr_mrcv_clear_fgda
cache buffers chains      kclwlr
cache buffers chains      kclebs_1
cache buffers chains      kclcls
cache buffers chains      kclcsr_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclpred
cache buffers chains      kclcls_1
cache buffers chains      kclple_1
cache buffers chains      kclple_2
cache buffers chains      kclcls_2
cache buffers chains      kcllwr
cache buffers chains      kclwcrs
cache buffers chains      kclcrs_1
cache buffers chains      kclcsr
cache buffers chains      kclrls
cache buffers chains      kclwcrs_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclfbst_1
cache buffers chains      kclpdc_1
cache buffers chains      kclwcrs_2
cache buffers chains      kclwcrs_3
cache buffers chains      kclfpdb
cache buffers chains      kclfpdb_2
cache buffers chains      kclpdc_2
cache buffers chains      kcllkopb
cache buffers chains      kclgrantlk
cache buffers chains      kclwrt
cache buffers chains      kcllkopb_1

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclwcrs_4
cache buffers chains      kcllkopb_2
cache buffers chains      kclcls_4
cache buffers chains      kclpred_1
cache buffers chains      kclrclr_2
cache buffers chains      kclrecbst
cache buffers chains      kclgrantlk_1
cache buffers chains      kclcls_5
cache buffers chains      kclrwrite_1
cache buffers chains      kclrwrite_2
cache buffers chains      kclcopy

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclswrite
cache buffers chains      kclchash
cache buffers chains      kclcfusion
cache buffers chains      kclfchk_1
cache buffers chains      kclcfusion_1
cache buffers chains      kclblkdone
cache buffers chains      kclcfusion_2
cache buffers chains      kclrenounce
cache buffers chains      kclbla
cache buffers chains      kclpto_1
cache buffers chains      kclgrantlk_2

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclcomplete
cache buffers chains      kclshrshr
cache buffers chains      kclclaim
cache buffers chains      kclhngcbk1
cache buffers chains      kclblkdone_1
cache buffers chains      kclgvlk
cache buffers chains      kclblkdone_2
cache buffers chains      kclcclaim
cache buffers chains      kclrechk_1
cache buffers chains      kclrechk_2
cache buffers chains      kclbr

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclpto
cache buffers chains      kclpdcl
cache buffers chains      kclpdc_3
cache buffers chains      kclpdc_4
cache buffers chains      kclgcr_1
cache buffers chains      kclcls_6
cache buffers chains      kclevict
cache buffers chains      kcldle
cache buffers chains      kclrcopy
cache buffers chains      kclenter
cache buffers chains      kclrbast

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclexpand
cache buffers chains      kclcls_3
cache buffers chains      kclverify
cache buffers chains      kclaffinity
cache buffers chains      kclassert
cache buffers chains      kclobj
cache buffers chains      kclobj_1
cache buffers chains      kclobj_2
cache buffers chains      kclgclk
cache buffers chains      kclwcrs_5
cache buffers chains      kclscrs

parent_name               location
------------------------- ----------------------------------------
cache buffers chains      kclstalepi
cache buffers chains      kclstalepi_1
cache buffers chains      kclstalepi_2
cache buffers chains      kclgvlk_1
cache buffers chains      kclgclk_1
cache buffers chains      kclgclk_2
cache buffers chains      kclcsr_2
cache buffers chains      kcbvm

204 rows selected.
我们一般认为Latch结构是Mostly exclusive access的,也就是极少会有共享访问闩的机会。但Oracle一般对外宣称读取数据时服务进程是以共享模式使用cache buffers chains闩,这就造成了许多人误以为读读是不会出现latch: cache buffers chains争用的。 但是实际上查询语句大部分情况下仍需要以exclusive模式持有该类子闩(有时会以SHARED模式持有,这取决于读取时是使用kcbgtcr: kslbegin shared还是kcbgtcr: kslbegin excl;kcbgtcr是Oracle rdbms中重要的获取一致性读的函数,其含义为Kernal Cache Buffer GeT Cosistents Read,显然该函数存在两种获取cache buffers chains的方式即kslbegin shared和excl。与之相对应的是kcbgcur: kslbegin,kcbgcur的含义为Kernel Cache Buffer Get Current,该函数用以获取当前块以便修改,也就是"写";很显然kcbgcur: kslbegin函数只有以excl排他方式持有child cache buffers chains latch的必要),原因在于虽然是查询语句但同样需要修改buffer header结构,譬如修改tch抢手度、holder list的hash变量us_nxt、us_prv以及waiter list的hash变量wa_prv、wa_nxt等。换而言之读读是会引起Latch free:cache buffers chains等待的,而非许多人认为的仅有读写、写写会导致缓存链闩争用。 这个问题我们再用实验证明一遍:
SQL> drop table maclean;
drop table maclean
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table maclean tablespace users as select * from dba_objects;
Table created.

SQL> select count(*) from maclean;

  COUNT(*)
----------
     51944

SQL> /

  COUNT(*)
----------
     51944

SQL> /

  COUNT(*)
----------
     51944

SQL> select spid from v$process where addr =(select paddr from v$session where sid=(select distinct sid from v$mystat));

SPID
------------
6023

另开一个命令行窗口:

[maclean@rh2 ~]$ gdb $ORACLE_HOME/bin/oracle 6023
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-23.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
...
Reading symbols from /s01/10gdb/bin/oracle...(no debugging symbols found)...done.
Attaching to program: /s01/10gdb/bin/oracle, process 6023
Reading symbols from /s01/10gdb/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxp10.so
Reading symbols from /s01/10gdb/lib/libhasgen10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libhasgen10.so
Reading symbols from /s01/10gdb/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libskgxn2.so
Reading symbols from /s01/10gdb/lib/libocr10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocr10.so
Reading symbols from /s01/10gdb/lib/libocrb10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrb10.so
Reading symbols from /s01/10gdb/lib/libocrutl10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libocrutl10.so
Reading symbols from /s01/10gdb/lib/libjox10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libjox10.so
Reading symbols from /s01/10gdb/lib/libclsra10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libclsra10.so
Reading symbols from /s01/10gdb/lib/libdbcfg10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libdbcfg10.so
Reading symbols from /s01/10gdb/lib/libnnz10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/10gdb/lib/libnnz10.so
Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
0x00000039f280d8e0 in __read_nocancel () from /lib64/libpthread.so.0
(gdb) break kcbgtcr
Breakpoint 1 at 0x108c72c

回到原sqlplus窗口再次执行查询语句,会hang住:
SQL> select count(*) from maclean;

在gdb窗口:
(gdb) break kslfre
Breakpoint 2 at 0x7a133c

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 1, 0x000000000108c72c in kcbgtcr ()
(gdb) c
Continuing.

Breakpoint 2, 0x00000000007a133c in kslfre ()
(gdb) c
Continuing.

多次continue直到出现kslfre内核函数,开一个新的sqlplus窗口:
SQL> set autotrace on;
SQL> select count(*) from v$latchholder;

  COUNT(*)
----------
         3

Execution Plan
----------------------------------------------------------
Plan hash value: 1575818826

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    13 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |    13 |            |          |
|*  2 |   FIXED TABLE FULL| X$KSUPRLAT |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

v$latchholder的数据来源于X$KSUPRLAT内部视图,因为v$latchholder不带mode字段,所以我们直接观察X$KSUPRLAT

SQL> select KSUPRLAT "address",KSUPRLNM "name",KSUPRLMD "mode" from X$KSUPRLAT;

address          name                      mode
---------------- ------------------------- ---------------
00000000FCE40040 cache buffers chains      EXCLUSIVE
00000000FA696978 simulator lru latch       EXCLUSIVE
00000000FA6CDCE0 simulator hash latch      EXCLUSIVE

/* 可以看到即便是查询语句也是以EXCLUSIVE mode持有child cache buffers chains latch */

(gdb) quit
A debugging session is active.

        Inferior 1 [process 6235] will be detached.

Quit anyway? (y or n) y
Detaching from program: /s01/10gdb/bin/oracle, process 6235

/* 可以通过以下statistics可以看到以上读取为纯粹的逻辑读,没有物理读取的部分干扰*/

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        719  consistent gets
          0  physical reads
          0  redo size
        517  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed