search
HomeDatabaseMysql Tutorial详解BufferHeader--DUMPbuffer结合X$BH视图各字段

Buffer Header结构图及简介 图1: buffer header:每一个数据块在被读入buffer cache时,都会先在buffer cache中构造一个buffer header,buffer header与数据块一一对应。buffer header包含的主要信息有: 1) 该数据块在buffer cache中实际的内存地址。就是

Buffer Header结构图及简介

图1:
\

buffer header:每一个数据块在被读入buffer cache时,都会先在buffer cache中构造一个buffer header,buffer header与数据块一一对应。buffer header包含的主要信息有:
1) 该数据块在buffer cache中实际的内存地址。就是上图中的虚线箭头所表示的意思。
2) 该数据块的类型,包括data、segment header、undo header、undo block等等。
3) 该buffer header所在的hash chain,是通过在buffer header里保存指向前一个buffer header的指针和指向后一个buffer header的指针的方式实现的。
4) 该buffer header所在的LRU、LRUW、CKPTQ等链表(这些链表我们后面都会详细说明)。也是通过记录前后buffer header指针的方式实现。
5) 当前该buffer header所对应的数据块的状态以及标记。
6) 该buffer header被访问(touch)的次数。
7) 正在等待该buffer header的进程列表(waiter list)和正在使用该buffer header的进程列表(user list)。

DUMP 指定的BUFFER BLOCK--含BH方法如下: --数据库版本:11.2.0.4

--补充下,如果是要DUMP整个BUFFER CACHE,可以参考:http://blog.csdn.net/haibusuanyun/article/details/17439845

BYS@ bys3>select dept.*,dbms_rowid.rowid_object(rowid) object#,dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#,dbms_rowid.rowid_row_number(rowid) row# from dept;
DEPTNO DNAME LOC OBJECT# FILE# BLOCK# ROW#
---------- -------------- ------------- ---------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 22327 4 251 0
20 RESEARCH DALLAS 22327 4 251 1
40 OPERATIONS BOSTON 22327 4 251 2
99 chedan bj 22327 4 251 3
BYS@ bys3>select dbms_utility.make_data_block_address(4,251) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(4,251)
-------------------------------------------
16777467
alter session set events 'immediate trace name set_tsn_p1 level 5';
alter session set events 'immediate trace name buffer level 16777467';
select value from v$diag_info where name like 'De%';

select * from x$bh where DBARFIL=4 and DBABLK=251; 用SYS用户从X$BH查看相应信息对照DUMP文件查看。
#########################################################

解读Buffer Header --结合X$BH

说明:DUMP BUFFER中一个块方法就是上一步的,可以先从多个会话对此块进行查询或DML之类,然后DUMP此块。下面的两段BH的内容是不是从同一个DUMP生成的文件中截取的已经记不清了。这里只是介绍了DUMP文件的BH中各个字段的意义,对已经知道的BH中各个字段与X$BH,V$BH中字段的对应也有写上。关于X$BH中字段,可以参考:X$BH中各字段意义
而对于像:什么情况下 lru-flags: hot_buffer或lru-flags: moved_to_tail,什么情况下st: CR或 st: XCURRENT,暂不在本文讨论范围了!
后面尽量按照DUMP的BH中一句一句的顺序进行解读,但是因为ru-flags: flags: obj-flags:这种并不在每一次DUMP中出现,所以这一块内容汇总在一起了--注意我下面的ru-flags: flags: obj-flags:等在下面贴出来的两个BH中可能没出现,是从其它DUMP文件中贴过来的。
最后,这方面参考资料较少,难免有疏漏之处,欢迎补充、指错!!
######
Dump of buffer cache at level 10 for tsn=4 rdba=16777467
1. BH (0x213e6ad0) file#: 4 rdba: 0x010000fb (4/251) class: 1 ba: 0x210b0000
2. set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
3. dbwrid: 0 obj: 22327 objn: 22327 tsn: 4 afn: 4 hint: f
4. hash: [0x2bbfddf4,0x213e7680] lru: [0x213e76b0,0x22ff07ec]
5. lru-flags: moved_to_tail
6. ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
7. st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
8. cr: [scn: 0x0.3a9dcc],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.3a9dcc],[sfl: 0x0],[lc: 0x0.359e7e]
9. flags: only_sequential_access
10. buffer tsn: 4 rdba: 0x010000fb (4/251)
11. scn: 0x0000.00359e7e seq: 0x01 flg: 0x04 tail: 0x9e7e0601
12. frmt: 0x02 chkval: 0x8cd6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
第二个
BH (0x21fe5dec) file#: 4 rdba: 0x010000fb (4/251) class: 1 ba: 0x21c92000
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 22327 objn: 22327 tsn: 4 afn: 4 hint: f
hash: [0x213e7680,0x233f2418] lru: [0x233f76c8,0x233f2448]
ckptq: [NULL] fileq: [NULL] objq: [0x233f76e0,0x240796ec] objaq: [0x233f76e8,0x240796e4]
use: [NULL] wait: [NULL]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch'tch: 2 txn: 0x293d0380
flags: private
LRBA: [0x0.0.0] LSCN: [0x0.3a9dcd] HSCN: [0x0.3a9dcd] HSUB: [65535]
buffer tsn: 4 rdba: 0x010000fb (4/251)
scn: 0x0000.00359e7e seq: 0x01 flg: 0x04 tail: 0x9e7e0601
frmt: 0x02 chkval: 0x8cd6 type: 0x06=trans data
Hex dump of corrupt header 3 = CHKVAL
Dump of memory from 0x21C92000 to 0x21C92014
#############################################

解读Buffer Header,也是对X$BH中相应字段的更详解读

第一句:
BH (0x213e6ad0) file#: 4 rdba: 0x010000fb (4/251) class: 1 ba: 0x210b0000
BH (0x213e6ad0) ,这是BH的HASH值
file#: 4 ,对应X$BH.FILE#,此块在4号文件里,通过v$dbfile.FILE#可以查出来对应的数据文件
rdba: 0x010000fb (4/251),rdba就是rowid中的相对文件号rfile#+block#块号。通过DBMS_ROWID查出块的rfile#+block#,使用select dbms_utility.make_data_block_address(4,251) from dual;这种方法可以计算出来十进制的rdba。这里的10000fb用to_number函数转换为10进制就是前面计算出来的:16777467。。0x010000fb转成二进制时,由相对文件号10位 block号22位。
class: 1,对应X$BH.class --表示buffer header对应block的类型,1=data block。详见本段Buffer Header解析最后。
ba: 0x210b0000 对应X$BH.BA,这是BUFFER中block address,是块在内存中的物理地址。
第二句:
set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
set: 3:对应X$BH.STATE,CR(3)=作为一致性读镜像的数据块,详见本段Buffer Header解析最后。
bsz: 8192,块大小8192KB。这个可以在 dba_tablespaces.BLOCK_SIZE 查询出数据文件的块大小。
第三句:
dbwrid: 0 obj: 22327 objn: 22327 tsn: 4 afn: 4 hint: f
obj: 22327,对应X$BH.OBJ ,也就是块上数据在哪个对象里,这个查询渠道很多。
第四句:
hash: [0x2bbfddf4,0x213e7680] lru: [0x213e76b0,0x22ff07ec]
hash: [0x2bbfddf4,0x213e7680] 一一对应x$bh.nxt_hash x$bh.prv_hash这里用链表,指出下一个及前一个BH的HASH值。
lru: [0x213e76b0,0x22ff07ec] 一一对应x$bh.nxt_repl x$bh.prv_repl这里用链表,指出下一个及前一个BH的在LRU链上HASH值。
第五句:
lru-flags: moved_to_tail 对应x$bh. LRU_FLAG 表示该数据块经历了依次全表扫描,它被移到LRU的冷端,随时都可能被age out。
lru-flags为0时,在DUMP BUFFER时可能不显示lru-flags: 这一行。
lru-flags: on_auxiliary_list
可能出现的:
obj-flags: object_ckpt_list 对应x$bh.OBJ_FLAG,,文件队列的对应对应x$bh.RFLAG
flags: only_sequential_access 对应x$bh. FLAG
flags: buffer_dirty block_written_once redo_since_read --在DML后可能会出现这种状态的 flags
flags: buffer_dirty redo_since_read
第六句:
ckptq: [NULL] fileq: [NULL] objq: [0x22ff8054,0x24839390] objaq: [0x22ff805c,0x24839388]
ckptq: [NULL] 在检查点队列上的HASH值,这里为空
fileq: [NULL] 在文件队列上的HASH值
objq: [0x22ff8054,0x24839390] 对应x$bh.oq_nxt x$bh.oq_prv .对象队列HASH值
objaq: [0x22ff805c,0x24839388] 对应x$bh.aq_nxt x$bh.aq_prv.辅助对象队列HASH值

第七句
st: CR md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
st: CR :对应x$bh.state CR, a consistent read (stale) block image 一致读。详见本段Buffer Header解析最后。
tch: 1 对应X$BH.TCH,Touch的缩写,表示一个Buffer的访问次数--不过不是绝对,3秒内访问同一块,TCH不增加。与此相关的一个字段是:X$BH.tim --Touch Time

第八句
cr: [scn: 0x0.3a9dcc],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.3a9dcc],[sfl: 0x0],[lc: 0x0.359e7e]
[scn: 0x0.3a9dcc] 产生此CR块时的SCN

第九句:
buffer tsn: 4 rdba: 0x010000fb (4/251)
这个块的TSN 表空间号和RDBA
第十句:
scn: 0x0000.00359e7e seq: 0x01 flg: 0x04 tail: 0x9e7e0601
scn: 0x0000.00359e7e,SCN直接转换为用to_number函数转换为10进制就是数据库内查出的SCN了,是这个块的状态改变时的SCN。详见:http://blog.csdn.net/haibusuanyun/article/details/17029517
seq: 0x01
flg: 0x04 flg:0x01 (新建块)0x2(数据块延迟清洗推进scn和seq) 0X04(设置校验和) 0x08(临时块)
第十一句:
frmt: 0x02 chkval: 0x8cd6 type: 0x06=trans data
type:0x06(表/索引块)
frmt: 0x01(v7) 0x02(v8)
##############################
第二个块BH与不同的有:
LRBA: [0xe3.e8e5.0] LSCN: [0x0.3a9dcd] HSCN: [0x0.3a9dcd] HSUB: [65535]
LSCN: [0x0.3a9dcd] HSCN: [0x0.3a9dcd] 修改时的SCN--如记录有修改时的SCN,可以转换此十六进制为SCN进行对比
LRBA: [0xe3.e8e5.0] 应该是最低REDO BYTE ADDRES,[0xe3.e8e5.0]对应日志号,块号,第几字节起。也可能会有HRBA ,recovery rba 。
use: [NULL] wait: [NULL] 对应BH中的 users list ; waiters list
################################################################

附:

flag中,每位代表如下含义:

bit bit
0 buffer_dirty 14 stale
1 notify_after_change 15 deferred_ping
2 mod_started 16 direct_access
3 block_has_been_logged 17 hash_chain_dump
4 temp_data 18 ignore_redo
5 being_written 19 only_sequential_access
6 waiting_for_write 20 prefetched_block
7 multiple_waiters 21 block_written_once
8 recovery_reading 22 logically_flushed
9 unlink_from_lock 23 resilvered_already
10 down_grade_lock 25 redo_since_read
11 clone_being_written 29 plugged_from_foreign_db
12 reading_as_CR 30 flush_after_writing
13 gotten_in_current_mode
class:表示buffer header对应block的类型:
1=data block, 9=2nd level bmb,
2=sort block, 10=3rd level bmb,
3=save undo block, 11=bitmap block,
4=segment header, 12=bitmap index block,
5=save undo header, 13=unused,
6=free list, 14=undo header,
7=extent map, 15=undo block
state:
0, FREE, no valid block image
1, XCUR, a current mode block, exclusive to this instance 正在被当前的instance独占。
2, SCUR, a current mode block, shared with other instances正在被当前的instance共享
3, CR, a consistent read (stale) block image 一致读
4, READ, buffer is reserved for a block being read from disk 正在从磁盘上读取块
5, MREC, a block in media recovery mode 处于介质恢复模式
6, IREC, a block in instance (crash) recovery mode处于实例恢复模式
lru_flag
0,'free',1,'xcur',2,'scur',
3,'cr', 4,'read',5,'mrec',
6,'irec',7,'write',8,'pi', 9,'memory'
10,'mwrite',11,'donated', 12,'protected',
13,'securefile', 14,'siop',15,'recckpt',
16, 'flashfree', 17, 'flashcur', 18, 'flashna'


Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL handle data replication?How does MySQL handle data replication?Apr 28, 2025 am 12:25 AM

MySQL processes data replication through three modes: asynchronous, semi-synchronous and group replication. 1) Asynchronous replication performance is high but data may be lost. 2) Semi-synchronous replication improves data security but increases latency. 3) Group replication supports multi-master replication and failover, suitable for high availability requirements.

How can you use the EXPLAIN statement to analyze query performance?How can you use the EXPLAIN statement to analyze query performance?Apr 28, 2025 am 12:24 AM

The EXPLAIN statement can be used to analyze and improve SQL query performance. 1. Execute the EXPLAIN statement to view the query plan. 2. Analyze the output results, pay attention to access type, index usage and JOIN order. 3. Create or adjust indexes based on the analysis results, optimize JOIN operations, and avoid full table scanning to improve query efficiency.

How do you back up and restore a MySQL database?How do you back up and restore a MySQL database?Apr 28, 2025 am 12:23 AM

Using mysqldump for logical backup and MySQLEnterpriseBackup for hot backup are effective ways to back up MySQL databases. 1. Use mysqldump to back up the database: mysqldump-uroot-pmydatabase>mydatabase_backup.sql. 2. Use MySQLEnterpriseBackup for hot backup: mysqlbackup--user=root-password=password--backup-dir=/path/to/backupbackup. When recovering, use the corresponding life

What are some common causes of slow queries in MySQL?What are some common causes of slow queries in MySQL?Apr 28, 2025 am 12:18 AM

The main reasons for slow MySQL query include missing or improper use of indexes, query complexity, excessive data volume and insufficient hardware resources. Optimization suggestions include: 1. Create appropriate indexes; 2. Optimize query statements; 3. Use table partitioning technology; 4. Appropriately upgrade hardware.

What are views in MySQL?What are views in MySQL?Apr 28, 2025 am 12:04 AM

MySQL view is a virtual table based on SQL query results and does not store data. 1) Views simplify complex queries, 2) Enhance data security, and 3) Maintain data consistency. Views are stored queries in databases that can be used like tables, but data is generated dynamically.

What are the differences in syntax between MySQL and other SQL dialects?What are the differences in syntax between MySQL and other SQL dialects?Apr 27, 2025 am 12:26 AM

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

What is MySQL partitioning?What is MySQL partitioning?Apr 27, 2025 am 12:23 AM

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How do you grant and revoke privileges in MySQL?How do you grant and revoke privileges in MySQL?Apr 27, 2025 am 12:21 AM

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor