oracle控制文件与数据库启动的关系

控制文件

ckpt的一项任务是更新数据文件头和控制文件,记录检查点信息,这些信息对于数据库的恢复和完整性校验都至关重要.下面来看一下控制文件和数据文件头都记录了哪些信息.通过以下内部命令可以转储oracle的数据文件头信息:
alter session set events ‘immediate trace name file_hdrs level 10’;
首先以immediate方式关闭数据库,在mount状态下执行该命令,研究一下此时转储的文件头信息:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4606.trc

查看跟踪文件信息,选取一个文件的信息(这里选择userso1.dbf文件),这类trace文件的信息包含两个部分,一部分来自控制文件,另一部分来自数据文件:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 ......
 V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)
 .....

其中”FILE HEADER”开始的信息就是来自数据文件头,之前的相关内容来自控制文件,在mount状态下将users01.dbf文件移除,重新转储数据文件头:

With the Partitioning, OLAP and Data Mining options
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 18 06:37:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ! mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4620.trc

检查现在生成的跟踪文件可以看到,由于users01.dbf文件丢失,”FILE HEADER”部分信息将无法获得:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
 ......
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'
*** Error 1157 in open/read file # 4 ***

此时报出的错误信息是,文件无法找到,也就是说当执行trace file_hdrs时需要读取数据文件头,获得相关信息,回过头来看一下来自控制文件部分的信息,其中包含:
Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
在”FILE HEADER”部分信息中包含了如下部分:
status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30
其中控制文件中记录的scn指最后一次成功完成的检查点scn;数据文件头中记录的checkpointed at scn指数据文件头中记录的最后一次成功完成的检查点scn;这两者在正常情况下是相等的.此外在控制文件和数据文件头都记录一个检查点计数(chkpt cnt或checkpoint cnt)而且数据文件头还记录了一个控制文件检查点计数(ctl cnt),在以上输出中ctl cnt:191比控制文件中的checkpoint cnt192要小1,这是因为当检查点更新控制文件和数据文件头上的chkpt cnt/checkpoint cnt信息时,在更新控制文件之前,可以获得当前的控制文件的clt cnt,这个信息被记入到数据文件头中,也就是ctlcnt:191,为什么要写这个ctl cnt到数据文件头了.是因为不能保证当前更新控制文件上的checkpoint cnt一定会成功(数据库可能突然crash了),记录之前成功的ctl cnt可以确保上一次的checkpoint是成功完成的,从而节了校验步骤.

数据库的启动验证
在数据库启动过程中的检验包含以下两个步骤;
第一步检查数据文件头中的checkpoint cnt是否与对应的控制文件中的checkpoint cnt一致.如果相等,则进行第二步检查.

第二步检查数据文件头的开始scn和对应的控制文件中的结束scn是否一致,如果控制文件中的结束scn等于数据文件头中的开始scn,则不需要对那个文件进行恢复.

对于每个数据文件都要完成检查后才打开数据库,同时将每个数据文件的结束的scn设置为无穷大也就是0xFFFFFFF.FFFFF

当使用alter session set events ‘immediate trace name file_hdrs level 10’来转储数据文件头信息时,oracle会转储两部分信息,一部分来自控制文件,另一部分来自数据文件,在数据库启动过程中,这两部分信息要用来进行启动验证.通过以下过程来进一步深入探讨一下这部分内容.

首先来看一下来自mount状态控制文件部分转储;

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
这部分中包含的重要信息有检查点计数(ckeckpoint cnt:192),检查点scn
(scn: 0x0000.0011601b 01/18/2013 06:31:30)和数据文件Stop scn(
top scn: 0x0000.0011601b 01/18/2013 06:31:30).

接下来再来看来自数据文件头的信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)

这部分中包含的重要信息有检查点scn(Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30)
和检查点计数库(chkpt cnt: 192 ctl cnt:191),这两者都和控制文件中所记录的一致.如果这两者一致.数据库启动时就能通过验证,启动数据库.

那么如果不一致,oracle则会请求进行恢复;以下是从崩溃中进行恢复users01.dbf文件.首先第一部分从控制文件中获得的信息是相同的:
先复制users01.dbf

[oracle@jingyong udump]$ cp /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

使用shutdown abort来模拟崩溃故障

SQL> shutdown abort;
ORACLE instance shut down.

再来删除users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.del

再从备份的users01.dbf.bak文件中还原users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf

再启动到mount状态下转储数据文件头信息

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.

再业转储数据文件头信息

Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4923.trc


DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:201 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120188 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

得到的控制文件中记录检查点计数(Checkpoint cnt:201),检查点scn

(scn: 0x0000.00120188 01/18/2013 07:15:10),和数据文件Stop scn
(Stop scn: 0x0000.00120188 01/18/2013 07:15:10)

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4545=0x11c1, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:12:48
 status:0x4 root dba:0x00000000 chkpt cnt: 199 ctl cnt:198
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120027 01/18/2013 07:12:49
 thread:1 rba:(0xb.2.10)

而从文件头中获得的备份文件信息则是:检查点是Checkpointed at scn: 0x0000.00120027 01/18/2013 07:12:49
检查点计数为:chkpt cnt: 199 ctl cnt:198

数据文件头中的检查计数为chkpt cnt: 199小于控制文件中的记录的Checkpoint cnt:201oracle可以判断文件是从备份中恢复的,或者文件故障,需要进行介质恢复.如果此时试图打开数据库,则oracle提示文件需要介质恢复.而且控制文件中的检查点scn: 0x0000.00120188与数据文件头中的Checkpointed at scn: 0x0000.00120027也不相同

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'

执行介质恢复

SQL> recover datafile 4;
Media recovery complete.

再来对数据文件头信息进行转储,来看一下恢复完成之后,控制文件和数据文件头的变化.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4940.trc

首先看控制文件的变化:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:202 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120187 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

检查点计数据Checkpoint cnt:202,执行了恢复之后,检查点计数较前增加了1,此时检查点scn是scn: 0x0000.00120188 01/18/2013 07:15:10,数据文件的Stop scn为
Stop scn: 0x0000.00120187 01/18/2013 07:15:10,说明数据文件stop scn和数据文件进行了同步.

以下是数据文件头信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4554=0x11ca, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x0 root dba:0x00000000 chkpt cnt: 202 ctl cnt:201
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120187 01/18/2013 07:15:10
 thread:1 rba:(0xb.15a.10)

此时数据文件头信息显示检查点(Checkpointed at scn: 0x0000.00120187 01/18/2013 07:15:10)
和控制文件中记录的Stop scn(Stop scn: 0x0000.00120187 01/18/2013 07:15:10)一致,数据库启动可以顺利进行.检查点计数为(chkpt cnt: 202 ctl cnt:201)

打开数据库,看一看open阶段的变化:

SQL> alter database open;

Database altered.


SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4968.trc

此时数据库恢复正常运行,控制文件信息如下:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:203 scn: 0x0000.00120189 01/18/2013 07:28:46
 Stop scn: 0xffff.ffffffff 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

此时Stop scn被置为无穷大(Stop scn: 0xffff.ffffffff)
数据文件头信息如下,其中检查点信息和控制文件中记录的checkpoint信息一致:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4557=0x11cd, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x4 root dba:0x00000000 chkpt cnt: 203 ctl cnt:202
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120189 01/18/2013 07:28:46
 thread:1 rba:(0xb.15a.10)

X$BH与Buffer Header

X$BH与Buffer Header

buffer header数据,可以从数据库的数据字典表中查询得到,这张字典表就是x$bh,x$bh中的bh就是
指buffer headers,每个buffer在x$bh中都存在一条记录

buffer header中存储每个buffer容纳的数据块的文件号,块地址,状态等重要信息,根据这些信息,
结合dba_extents视图,可以很容易地找到每个buffer对应的对象信息:

x$bh中还有一个重要的字段TCH,TCH为Touch的缩写,表示一个Buffer的访问次数,buffer被访问的次
数越多,说明该buffer就越抢手,也就可能存在热块竞争的问题

通过对下查询获得当前数据库最繁忙的buffer

SQL> select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21;

ADDR                    TS#      FILE#    DBARFIL     DBABLK        TCH
---------------- ---------- ---------- ---------- ---------- ----------
9FFFFFFFBF5AACA8          7         12         12     638244       1722
9FFFFFFFBF5AACA8          7         13         13     598707       1636
9FFFFFFFBF5AAFF8          7         11         11     261996       1629
9FFFFFFFBF5AAFF8          6          8          8     135404       1614
9FFFFFFFBF5AAFF8          7         11         11     655501       1587
9FFFFFFFBF5AAFF8          7         11         11     269628       1568
9FFFFFFFBF5AAFF8          7         11         11    2742315       1568
9FFFFFFFBF5AAFF8          7         11         11     269612       1562
9FFFFFFFBF5AACA8          7         13         13     601755       1538
9FFFFFFFBF5AAFF8          7         13         13     599052       1514
9FFFFFFFBF5AACA8          7         13         13     254900       1506
9FFFFFFFBF5AACA8          7         12         12     261898       1504
9FFFFFFFBF5AAFF8          7         11         11     662797       1491
9FFFFFFFBF5AACA8          7         13         13     610957       1487
9FFFFFFFBF5AAFF8          6          8          8     715684       1465
9FFFFFFFBF5AAFF8          7         11         11     665204       1462
9FFFFFFFBF5AAFF8          7         11         11     132492       1461
9FFFFFFFBF5AACA8          6          8          8    1766500       1458
9FFFFFFFBF5AACA8          7         11         11     273549       1445
9FFFFFFFBF5AAFF8          7         11         11     266099       1441

20 rows selected

再结合dba_extents中的信息,可以查询得到这些热点buffer都来自哪些对象;

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

除了查询x$bh之外,也可以从buffer cache的转储信息中,看到buffer header的具体内容

BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
      set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
      dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
      hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
      lru-flags:
      ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
      st: XCURRENT md: NULL tch: 1
      flags:
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040b894 (1/47252)
      scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
      frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data

在oracle10g以前,数据库的等待事件中,所有的latch等待都被归纳为latch free等待
在statspack的report中,如果在top5等待事件中看到latch free这一等待处于较高的位置
那么就要地行研究和解决了

由于latch free是一个汇总等待事件,我们需要从v$latch视图中获得具体的latch竞争主要
是由哪些latch引起的

如果需要具体确定热块对象,可以从v$latch_children中查询到具体的子latch信息

SQL> select * from (
  2  select addr,child#,gets,misses,sleeps,immediate_gets,immediate_misses, spin_gets
  3  from v$latch_children
  4  where name='cache buffers chains'
  5  order by sleeps desc)
  6  where rownum<6;


ADDR                 CHILD#       GETS     MISSES     SLEEPS IMMEDIATE_GETS IMMEDIATE_MISSES  SPIN_GETS
---------------- ---------- ---------- ---------- ---------- -------------- ---------------- ----------
C00000001888BF70      14784  126455972    1469279        636           2832                1    1468752
C0000000187F9940      11786   48496473     345817        216           2948                0     345623
C000000018645EF0       2864   63718005     332144        211           2645                1     331967
C0000000187CEDA8      10911   48514435     329026        207           3791                0     328844
C00000001877CDF0       9232   63325754     341420        202           3223                0     341256

在x$bh中还存在另外一个关键字段HLADDR,即hash chain latch address,这个字段可以和v$latch_children.addr
进行关联,这样就可以把具体的latch竞争和数据块关联起来,再结合dba_extents视图,就可以找到具体的热块竞争
对象,找到具体热点对象后,可以结合v$sqlarea或v$sqltext,找到频繁操作这些对象的sql,然后对其进行优化,
就可以缓解或解决热块竞争的问题了

SQL> select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
  2  select * from (
  3  select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
  4  where rownum<6) a,
  5  (select addr,gets,misses,sleeps from v$latch_children
  6  where name='cache buffers chains') b
  7  where a.hladdr=b.addr
  8  /

ADDR                    TS#    DBARFIL     DBABLK        TCH       GETS     MISSES     SLEEPS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C00000001886F4D8          7         12     638244       2273     656301         13          0
C00000001876C900          7         11     261996       2320    2972787         89          0
C0000000186BC960          7         11     269628       2220     495549         10          0
C0000000186A9FB8          6          8     135404       2303    3257192        101          0
C000000018634678          7         11     655501       2257    3640151      30614         26

可以使用下面的语句来找到这些热点buffer的对象信息

select a.owner,a.segment_name,a.segment_type from dba_extents a,
(select * from (select addr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc)
where rownum<21) b
where a.relative_fno=b.dbarfil
and a.block_id< =b.dbablk
and a.block_id+a.blocks>b.dbablk;

select b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps from (
select * from (
select addr,ts#,dbarfil,dbablk,tch,hladdr from x$bh order by tch desc)
where rownum<6) a,
(select addr,gets,misses,sleeps from v$latch_children
where name='cache buffers chains') b,
dba_extents c
where a.hladdr=b.addr
and c.relative_fno=a.dbarfil
and c.block_id< =a.dbablk
and c.block_id+c.blocks>a.dbablk

再与v$sqltext或v%sqlarea视图关联可以找到操作这些热块对象的相关sql

select  hash_value,sql_fulltext from v$sqlarea
where sql_id  in(
select a.sql_id from v$sqltext a,
(select distinct b.owner,b.segment_name,b.segment_type from dba_extents b,
(select dbarfil,dbablk from(
select dbarfil,dbablk from x$bh order by tch desc)
where rownum<11) c
where b.relative_fno=c.dbarfil
and b.block_id< =c.dbablk
and b.block_id+b.blocks>c.dbablk) d
where a.sql_text like '%'||d.segment_name||'%'
and d.segment_type='TABLE')

找到这些sql语句后,就可以通过优化sql减少数据的访问,避免或优化某些容易引起的争用操作
来减少热块竞争

cache buffer chain

buffer cache的管理有两个重要的数据结构:

hash bucket和cache buffer chain

1. hash bucket和cache buffer chain
可以想象,如果所有的buffer cache中的所有buffer都通过同一个结构来进行管理,当需要确定某个
block在buffer中是否存在时,将需要遍历整个结构,性能会相当低下.

为了提高效率,oracle引入了bucket的数据结构,oracle把管理的所有buffer通过一个内部的hash算法
运算后,存放到不同的hash bucket中,这样通过hash bucket进行分割之后,众多的buffer被分布到一
定数量的bucket之中,当用户需要在buffer中定位数据是否存在时,只需要通过同样的算法来获得hash
值然后到相应的bucket中查找少理的buffer即可确定.每个buffer存放的bucket由buffer的数据块
地址(DBA,Data Block Address)运算决定.

bucket内部,通过cache buffer chain(cache buffer chain是一个双向链表)将所有的buffer通过
buffer header信息联系起来

buffer header存放的是对应数据块的概要信息,包括数据块的文件号,块地址,状态等.要判断数据块
在buffer中是否存在,通过检查buffer header即可确定.

如果多个会话同时对相同的cache buffer chain进行读取时就会产生cache buffer chain的竞争.
先来进行读取的会话持有这个cache buffer chain的latch

从oracle9i开始,对于cache buffer chain的只读访问,其latch可以被共享,也就是说,如果多个会话
都只是来查阅这个cache buffer chain那么大家可以同时进行查阅,但是如果有会话要对这个
cache buffer chian中的buffer header对应的数据块进行操作时那么就只能独享这个latch了.

这就是buffer cache 与latch的竞争

由于buffer根据buffer header进行散列,从而最终决定存入哪一个hash bucket,那么hash bucket的
数量在一定程度上就决定了每个bucket中buffer数量的多少,也就间接影响了搜索buffer header的
性能.

所以在不同版本中,oracle一直在修改算法,优化hash bucket的数量,可以想象,bucket的数量多一些
那么在同一时间就可以有更多的会话可以对不同的hash bucket进行读取.但是更多的hash bucket,
显然需要更多的存放空间,更多的管理成本,所以优化在什么时候都不是简单的一元方程.

hash bucket的设置受一个隐含参数_db_block_hash_buckets的影响.

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
  7  ;

NAME                         VALUE            DESCRIB
---------------------------- ---------------- ----------------------------------------
_db_block_hash_buckets       4194304          Number of database block hash buckets

对于每个hash bucket,只会有一个cache buffer chain ,当用户试图搜索cache buffer chain时
必须先获得cache buffer chain latch.那么cache buffer chain latch的设置就同样值得研究

在oracle8i之前,对于每一个hash bucket,oracle使用一个独立的hash latch来维护,其缺省的
bucket数量为next_prime(db_block_buffers/4)

由于过于严重的热块竞争,从oracle8i开始,oracle改变了这个算法,先是bucket数量开始增加,
_db_block_hash_bucket增加到了2*db_block_buffers,而_db_block_hash_latchs的数量也发生
变化

当cache buffers少于2052个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-1))

当cache buffers多于131075个buffers时:
_db_block_hash_latches=power(2,trunc(log(2,db_block_buffers-4)-6))

当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024

从oracle8i开始,_db_block_hash_buckets的数量较以前增加了8倍,而_db_block_hash_latchs的
数量增加比较有限,这意味着,每个latch需要管理多个bucket,但是由于bucket数量的成倍增加,
每个bucket中的block的数量得以减少,从而使用少量latch管理更多bucket成为可能

从oracle8i开始,bucket的数量比以前大大增加;通过增加bucket的数量来使得每个bucket上的
buffer的数量大大减少.

在oracle8i之前,_db_block_hash_latches的数量和hash bucket的数量是一致的,每一个latch管理
一个bucket,从oracle8i开始每个lath可以管理多个bucket,由于每个bucket中的buffer header数量
大大降低所以latch的性能反而得到提高

每个bucket存在一条cache buffer chain

buffer header上存在指向具体buffer的指针

下面是测试的一情景
db_cache_size为88MB,此时的_db_block_hash_buckets为32768

SQL> show parameter db_cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_size                        big integer 88M

SQL>
SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_buckets%'
  7  ;

NAME                             VALUE               DESCRIB
-------------------------------- ------------------- --------------------------------------------
_db_block_hash_buckets           32768                Number of database block hash buckets


SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

计算db_cache_size=88MB,有多少个db_block_buffer=11264

SQL> select 88*1024/8 from dual;

 88*1024/8
----------
     11264

查询一下_db_block_hash_latches=1024所以应证了
当cache buffers在2052与131075个buffers之间时:
_db_block_hash_latches=1024

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from  sys.x$ksppi x,sys.x$ksppcv y
  3  where
  4  x.inst_id=userenv('Instance') and
  5  y.inst_id=userenv('Instance') and
  6  x.indx=y.indx and x.ksppinm like '%_db_block_hash_latches%'
  7  ;

NAME                                   VALUE               DESCRIB
-------------------------------------- ------------------- -----------------------------------------
_db_block_hash_latches                 1024                Number of database block hash latches

计算一下每个bucket中有多少个buffer header
db_cache_size为88MB,此时的_db_block_hash_buckets为32768
db_block_size=8Kb

SQL> select 88*1024/8/32768 from dual;

88*1024/8/32768
---------------
        0.34375

那么说明有的bucket中没有buffer header

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3341.trc

跟踪文件中的cache buffer chain的数量正好是

[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc | wc -l
32768

某些chain上可能没有buffer header信息(被标记为null),这些chain的数据类似如下:

[oracle@jingyong udump]$ grep CHAIN jingyong_ora_3341.trc|head -20
CHAIN: 0 LOC: 0x290c0838 HEAD: [NULL]
CHAIN: 1 LOC: 0x290c0840 HEAD: [NULL]
CHAIN: 2 LOC: 0x290c0848 HEAD: [NULL]
CHAIN: 3 LOC: 0x290c0850 HEAD: [NULL]
CHAIN: 4 LOC: 0x290c0858 HEAD: [NULL]
CHAIN: 5 LOC: 0x290c0860 HEAD: [NULL]
CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c]
CHAIN: 7 LOC: 0x290c0870 HEAD: [NULL]
CHAIN: 8 LOC: 0x290c0878 HEAD: [24fe6dcc,24fe6dcc]
CHAIN: 9 LOC: 0x290c0880 HEAD: [NULL]
CHAIN: 10 LOC: 0x290c0888 HEAD: [NULL]
CHAIN: 11 LOC: 0x290c0890 HEAD: [NULL]
CHAIN: 12 LOC: 0x290c0898 HEAD: [233f8c7c,233f8c7c]
CHAIN: 13 LOC: 0x290c08a0 HEAD: [NULL]
CHAIN: 14 LOC: 0x290c08a8 HEAD: [NULL]
CHAIN: 15 LOC: 0x290c08b0 HEAD: [NULL]
CHAIN: 16 LOC: 0x290c08b8 HEAD: [NULL]
CHAIN: 17 LOC: 0x290c08c0 HEAD: [NULL]
CHAIN: 18 LOC: 0x290c08c8 HEAD: [21fed2dc,21fee82c]
CHAIN: 19 LOC: 0x290c08d0 HEAD: [NULL]

查看一下chain 6的数据

CHAIN: 6 LOC: 0x290c0868 HEAD: [25feb12c,25feb12c]
    BH (0x25feb12c) file#: 1 rdba: 0x0040b894 (1/47252) class: 1 ba: 0x25cec000
      set: 3 blksize: 8192 bsi: 0 set-flg: 0 pwbcnt: 75
      dbwrid: 0 obj: 181 objn: 183 tsn: 0 afn: 1
      hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]
      lru-flags:
      ckptq: [NULL] fileq: [NULL] objq: [25feb124,25feb284]
      st: XCURRENT md: NULL tch: 1
      flags:
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x0040b894 (1/47252)
      scn: 0x0000.00074869 seq: 0x01 flg: 0x04 tail: 0x48690601
      frmt: 0x02 chkval: 0xa2b2 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x25CEC000 to 0x25CEE000
25CEC000 0000A206 0040B894 00074869 04010000  [......@.iH......]
25CEC010 0000A2B2 00000001 000000B5 00074869  [............iH..]
25CEC020 1FE80000 00031F02 0040B88B 00140001  [..........@.....]
25CEC030 0000006D 00805685 0022002D 00008000  [m....V..-.".....]
25CEC040 00039FF0 0011000A 000000C7 00800A9E  [................]
25CEC050 002600D7 00008000 0007481D 00050400  [..&......H......]
25CEC060 0028FFFF 1E791E70 00001E79 00010001  [..(.p.y.y.......]
25CEC070 00020001 00020000 1F790003 1E701F33  [..........y.3.p.]
25CEC080 1EA21ED5 00000000 00000000 00000000  [................]
25CEC090 00000000 00000000 00000000 00000000  [................]
        Repeat 482 times
25CEDEC0 00000000 00000000 00000000 000D006C  [............l...]
25CEDED0 02444902 001002C1 00000000 00000000  [.ID.............]
25CEDEE0 00000000 02190000 02FF02C1 C20303C1  [................]
25CEDEF0 C1023509 02C20302 FFFFFF1D 006C8001  [.5............l.]
25CEDF00 500B000D 41424F52 494C4942 C1025954  [...PROBABILITY..]
25CEDF10 00001004 00000000 00000000 00000000  [................]
25CEDF20 C1020F00 C102FF02 FFFFFF03 01FFFFFF  [................]
25CEDF30 0D006C80 43530500 0245524F 001003C1  [.l....SCORE.....]
25CEDF40 00000000 00000000 00000000 020F0000  [................]
25CEDF50 02FF02C1 FFFF03C1 FFFFFFFF 006C8001  [..............l.]
25CEDF60 4902000D 02C10244 00000010 00000000  [...ID...........]
25CEDF70 00000000 00000000 02C10219 03C102FF  [................]
25CEDF80 0202C102 C20302C1 FFFF1D02 6C8001FF  [...............l]
25CEDF90 04001500 302E3824 5ECEFA10 4AAA6474  [....$8.0...^td.J]
25CEDFA0 5730E0F6 1016058C 02C20365 05C10209  [..0W....e.......]
25CEDFB0 0104C102 FFFFFF80 FFFFFFFF FFFFFFFF  [................]
25CEDFC0 11FFFFFF F99F5921 C8031661 E625EF53  [....!Y..a...S.%.]
25CEDFD0 03CF388F 0200AC3D 00040004 94B84000  [.8..=........@..]
25CEDFE0 40000000 000094B8 5ECEFA10 4AAA6474  [...@.......^td.J]
25CEDFF0 5730E0F6 1016058C 02C10265 48690601  [..0W....e.....iH]
Block header dump:  0x0040b894
 Object id on Block? Y
 seg/obj: 0xb5  csc: 0x00.74869  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x40b88b ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.014.0000006d  0x00805685.002d.22  C---    0  scn 0x0000.00039ff0
0x02   0x000a.011.000000c7  0x00800a9e.00d7.26  C---    0  scn 0x0000.0007481d

data_block_dump,data header at 0x25cec05c
===============
tsiz: 0x1fa0
hsiz: 0x28
pbl: 0x25cec05c
bdba: 0x0040b894
     76543210
flag=--------
ntab=4
nrow=5
frre=-1
fsbo=0x28
fseo=0x1e70
avsp=0x1e79
tosp=0x1e79
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=0 offs=2
0x1a:pti[3] nrow=3 offs=2
0x1e:pri[0] offs=0x1f79
0x20:pri[1] offs=0x1f33
0x22:pri[2] offs=0x1e70
0x24:pri[3] offs=0x1ed5
0x26:pri[4] offs=0x1ea2
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: K-H-FL-- lb: 0x0  cc: 2
curc: 4 comc: 4 pk: 0x0040b894.0 nk: 0x0040b894.0
col  0: [16]  fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col  1: [ 2]  c1 02
tab 1, row 0, @0x1f33
tl: 70 fb: -CH-FL-- lb: 0x0  cc: 21 cki: 0
col  0: [ 4]  24 38 2e 30
col  1: [16]  fa ce 5e 74 64 aa 4a f6 e0 30 57 8c 05 16 10 65
col  2: [ 3]  c2 02 09
col  3: [ 2]  c1 05
col  4: [ 2]  c1 04
col  5: [ 1]  80
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: *NULL*
col 13: *NULL*
col 14: *NULL*
col 15: *NULL*
col 16: *NULL*
col 17: *NULL*
col 18: *NULL*
col 19: *NULL*
col 20: [17]  21 59 9f f9 61 16 03 c8 53 ef 25 e6 8f 38 cf 03 3d
tab 3, row 0, @0x1e70
tl: 50 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [ 2]  49 44
col  1: [ 2]  c1 02
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 19
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: [ 3]  c2 09 35
col  7: [ 2]  c1 02
col  8: [ 3]  c2 02 1d
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
tab 3, row 1, @0x1ed5
tl: 45 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [ 5]  53 43 4f 52 45
col  1: [ 2]  c1 03
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
tab 3, row 2, @0x1ea2
tl: 51 fb: -CH-FL-- lb: 0x0  cc: 13 cki: 0
col  0: [11]  50 52 4f 42 41 42 49 4c 49 54 59
col  1: [ 2]  c1 04
col  2: [16]  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0f
col  3: [ 2]  c1 02
col  4: *NULL*
col  5: [ 2]  c1 03
col  6: *NULL*
col  7: *NULL*
col  8: *NULL*
col  9: *NULL*
col 10: *NULL*
col 11: *NULL*
col 12: [ 1]  80
end_of_block_dump

这个chain中存一个BH信息,其中包含”hash: [290c0868,290c0868] lru: [25feb230,25feb0d0]”
“hash: [290c0868,290c0868] “中的两个数据分别代表X$BH中的NXT_HASH和PRV_HASH,也就是指
同一个hash chain上的下一个BH地址和上一个buffer地址.如果某个chain只包含一个BH,
那么这两个值将同时指向该chain地址

“lru: [25feb230,25feb0d0]”中的两个数据分别代表X$BH中的NXT_REPL和PRV_REPL也就是LRU上的
下一个buffer和上一个buffer

cache buffers lru chain

cache buffers lru chain闩锁竞争与解决

当用户进程需要读数据到buffer cache时,或cache buffer根据lru算法进行管理时,就不可避免地要扫描
lru list获取可用buffer或更改buffer的状态,我们知道,oracle的buffer cache是共享内存,可以为众多
并发进程并发访问,所以在搜索的过程中必须获取latch(latch是oracle的一种串行锁机制,用于保护共享内存结构)
,锁定内存结构,防止并发访问损坏内存中的数据.

这个用于锁定lru的latch就是经常见到的cache buffers lru chain.

SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses
  2  from v$latch where name='cache buffers lru chain';

ADDR                 LATCH# NAME                          GETS     MISSES       IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- ----------------------------- ---------- ---------- -------------- ----------------
C00000000BE23B10        117 cache buffers lru chain        2601887       8060      106765296           597096

cache buffers lru chain latch存在多个子latch,它的数量受隐含参数_db_block_lru_latches控制

SQL> select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
  2  from sys.x$ksppi x,sys.x$ksppcv y
  3  where x.inst_id=USERENV('Instance')
  4  and y.inst_id=USERENV('Instance')
  5  and x.indx=y.indx
  6  and x.ksppinm like '%_db_block_lru_latches%';

NAME                         VALUE          DESCRIB
---------------------------- -------------  -------------------------------
_db_block_lru_latches        32             number of lru latches

可以从v$latch_children视图查看当前各子latch使用的情况:

SQL> select addr,latch#,name,gets,misses,immediate_gets,immediate_misses
  2  from v$latch_children where name='cache buffers lru chain';

ADDR                 LATCH# NAME                                GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- -------------------------  -------------- ---------- -------------- ----------------
C00000047AB14E80        117 cache buffers lru chain                68          0              0                0
C00000047AB14928        117 cache buffers lru chain                68          0              0                0
C00000047AB143D0        117 cache buffers lru chain                68          0              0                0
C00000047AB13E78        117 cache buffers lru chain                68          0              0                0
C00000047AB13920        117 cache buffers lru chain                68          0              0                0
C00000047AB133C8        117 cache buffers lru chain                68          0              0                0
C00000047AB12E70        117 cache buffers lru chain                68          0              0                0
C00000047AB12918        117 cache buffers lru chain                68          0              0                0
C00000047AB123C0        117 cache buffers lru chain                68          0              0                0
C00000047AB11E68        117 cache buffers lru chain                68          0              0                0
C00000047AB11910        117 cache buffers lru chain                68          0              0                0
C00000047AB113B8        117 cache buffers lru chain                68          0              0                0
C00000047AB10E60        117 cache buffers lru chain                68          0              0                0
C00000047AB10908        117 cache buffers lru chain                68          0              0                0
C00000047AB103B0        117 cache buffers lru chain                68          0              0                0
C00000047AB0FE58        117 cache buffers lru chain                68          0              0                0
C00000047AB0F900        117 cache buffers lru chain                68          0              0                0
C00000047AB0F3A8        117 cache buffers lru chain                68          0              0                0
C00000047AB0EE50        117 cache buffers lru chain                68          0              0                0
C00000047AB0E8F8        117 cache buffers lru chain                68          0              0                0

ADDR                 LATCH# NAME                                 GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
---------------- ---------- -------------------------  -------------- ---------- -------------- ----------------
C00000047AB0E3A0        117 cache buffers lru chain            658582       2096       27333396           148252
C00000047AB0DE48        117 cache buffers lru chain            659346       2092       27372470           148770
C00000047AB0D8F0        117 cache buffers lru chain            660582       2168       27373352           148620
C00000047AB0D398        117 cache buffers lru chain            657057       1824       27227832           152743
C00000047AB0CE40        117 cache buffers lru chain                68          0              0                0
C00000047AB0C8E8        117 cache buffers lru chain                68          0              0                0
C00000047AB0C390        117 cache buffers lru chain                68          0              0                0
C00000047AB0BE38        117 cache buffers lru chain                68          0              0                0
C00000047AB0B8E0        117 cache buffers lru chain                70          0              2                0
C00000047AB0B388        117 cache buffers lru chain                70          0              3                0
C00000047AB0AE30        117 cache buffers lru chain                70          0              3                0
C00000047AB0A8D8        117 cache buffers lru chain                70          0              2                0

32 rows selected

如果该latch竞争激烈,通常有如下方法可以采用.

适当的增大buffer cache,这样可以减少读数据到buffer cache的机会,减少扫描lru list的竞争

可以适当增加lru latch的数量,修改_db_block_lru_latches参数可以实现,但是该参数通常来说
是足够的,除非在oracle support的建议下或确知是该参数带来的影响,否则不推荐修改

通过多缓冲池技术,可以减少不希望的数据老化和全表扫描等操作对于default池的冲击,
从而可以减少竞争

转储buffer cache的内容

通过如下命令来转储buffer cache的内容,从而清晰地看到它的数据结构

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d;

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3294.trc

不同的level转储的内容详细程度不同,此命令的可用级别主要有1-10级,其中和级别
的含义如下:
level 1:仅包含buffer headers信息
level 2:包含buffer headers和buffer概要信息转储
level 3:包含buufer headers和完整的buffer内容转储
level 4: level 1+ latch转储+ lru队列
level 5: level 4+ buffer概要信息转储
level 6和level 7:level 4+完整的buffer内容转储
level 8:level 4+显示users/waiters信息
level 9:level 5+显示users/waiters信息
level 10:level 6+显示users/waiters信息

转储仅限于在测试环境中使用,转储的跟踪文件可能非常巨大,为获取完整的跟踪
文件,建议设置初始化参数max_dump_file_size为UNLIMITED

** 2013-12-27 23:18:36.317
*** SERVICE NAME:(SYS$USERS) 2013-12-27 23:18:36.315
*** SESSION ID:(144.29) 2013-12-27 23:18:36.315
Dump of buffer cache at level 10 for tsn=2147483647, rdba=0
  (WS) size: 0 wsid:  1 state: 0
    (WS_REPL_LIST) main_prev: 29142bbc main_next: 29142bbc aux_prev: 29142bc4 aux_next: 29142bc4curnum: 0 auxnum: 0
cold: 29142bbc hbmax: 0 hbufs: 0
    (WS_WRITE_LIST) main_prev: 29142bd8 main_next: 29142bd8 aux_prev: 29142be0 aux_next: 29142be0curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 29142bf4 main_next: 29142bf4 aux_prev: 29142bfc aux_next: 29142bfccurnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29142c10 main_next: 29142c10 aux_prev: 29142c18 aux_next: 29142c18curnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29142c2c main_next: 29142c2c aux_prev: 29142c34 aux_next: 29142c34curnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 0 sumwrt:  0
  (WS) pwbcnt: 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]
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]
  (WS) size: 0 wsid:  2 state: 0
    (WS_REPL_LIST) main_prev: 29142f40 main_next: 29142f40 aux_prev: 29142f48 aux_next: 29142f48curnum: 0 auxnum: 0
cold: 29142f40 hbmax: 0 hbufs: 0
    (WS_WRITE_LIST) main_prev: 29142f5c main_next: 29142f5c aux_prev: 29142f64 aux_next: 29142f64curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 29142f78 main_next: 29142f78 aux_prev: 29142f80 aux_next: 29142f80curnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29142f94 main_next: 29142f94 aux_prev: 29142f9c aux_next: 29142f9ccurnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29142fb0 main_next: 29142fb0 aux_prev: 29142fb8 aux_next: 29142fb8curnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 0 sumwrt:  0
  (WS) pwbcnt: 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]
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]
  (WS) size: 10479 wsid:  3 state: 2
    (WS_REPL_LIST) main_prev: 23fedc20 main_next: 247ee1a0 aux_prev: 207f6bd0 aux_next: 23fedcd0curnum: 10479  auxnum: 1105
cold: 207f6b20 hbmax: 5200 hbufs: 2848
    (WS_WRITE_LIST) main_prev: 291432e0 main_next: 291432e0 aux_prev: 291432e8 aux_next: 291432e8curnum: 0 auxnum: 0
    (WS_XOBJ_LIST) main_prev: 291432fc main_next: 291432fc aux_prev: 29143304 aux_next: 29143304curnum: 0 auxnum: 0
    (WS_XRNG_LIST) main_prev: 29143318 main_next: 29143318 aux_prev: 29143320 aux_next: 29143320curnum: 0 auxnum: 0
    (WS_REQ_LIST) main_prev: 29143334 main_next: 29143334 aux_prev: 2914333c aux_next: 2914333ccurnum: 0 auxnum: 0
  (WS) fbwanted: 0
  (WS) bgotten: 11481 sumwrt:  4081
  (WS) pwbcnt: 112
MAIN RPL_LST Queue header (NEXT_DIRECTION)[247ee1a0,23fedc20]
0x247ee14c=>0x23feee5c=>0x23feedac=>0x23fee61c=>0x23fee1fc=>0x23fef53c=>0x23fef1cc=>0x23fee77c
0x23fee56c=>0x23fee82c=>0x23fee8dc=>0x23fee98c=>0x23feea3c=>0x23feeaec=>0x23fef06c=>0x243eefbc
0x23fef11c=>0x23fef32c=>0x23fef3dc=>0x23fef48c=>0x23fef5ec=>0x23fef74c=>0x23fef7fc=>0x23fef95c
0x23fefa0c=>0x23fefabc=>0x23fefb6c=>0x23fefc1c=>0x23fefccc=>0x23fefd7c=>0x23fefe2c=>0x23fefedc
0x23feff8c=>0x23ff003c=>0x23ff00ec=>0x23ff019c=>0x23ff024c=>0x23ff03ac=>0x23ff045c=>0x23ff050c
0x23ff05bc=>0x23ff066c=>0x23ff071c=>0x23ff07cc=>0x23ff087c=>0x23ff092c=>0x23ff09dc=>0x23ff0a8c
0x23ff0b3c=>0x23ff0bec=>0x23ff0c9c=>0x23ff0d4c=>0x23ff0dfc=>0x23ff0eac=>0x23ff0f5c=>0x23ff10bc
0x23ff116c=>0x23ff12cc=>0x23ff137c=>0x23ff142c=>0x23ff14dc=>0x23ff158c=>0x23ff163c=>0x23ff16ec
Proudly powered by WordPress | Indrajeet by Sus Hill.