X $ BHSYS users can query bufferheader data, which can be obtained from the database data dictionary table. This dictionary table is x $ bh, where bh refers to bufferheaders, each buffer has a record in x $ bh. bufferheader stores important information such as the file number, block address, and status of the data block in each buffer. Based on this information,
X $ bh sys users can query the buffer header data, which can be obtained from the database data dictionary table. This dictionary table is x $ bh, where bh refers to buffer headers, each buffer has a record in x $ bh. the buffer header stores important information such as the file number, block address, and status of the data block in each buffer. Based on this information,
X $ bh sys users can query
Buffer header data, which can be queried from the database's data dictionary table. This dictionary table is x $ bh, where bh refers to buffer headers, each buffer has a record in x $ bh.
The buffer header stores important information such as the file number, block address, and status of data blocks in each buffer. Based on this information, combined with the dba_extents view, you can easily find the object information corresponding to each buffer:
There is also an important field TCH in x $ bh. TCH stands for Touch, indicating the number of accesses to a Buffer. The more times the buffer is accessed, the more popular the buffer is, there may also be hot block competition issues
Can be combined with dump bh to compare the field in x $ BH, see: http://blog.csdn.net/haibusuanyun/article/details/17525523
SYS @ bys3> desc x $ bh ---- A total of 57 fields: database version: 11.2.0.4
Name Type
-----------------------------
Addr raw (4) -- Hex address of the Buffer Header. address of a row in the view-hexadecimal address of the Buffer Header
Indx number -- Buffer Header number
INST_ID NUMBER
Hladdr raw (4) -- that is, the hash chain latch address can be associated with v $ latch_children.addr to associate latch with data blocks.
Blsiz number -- block size
NXT_HASH RAW (4) -- HASH value of the next BH
PRV_HASH RAW (4) -- HASH value of the previous BH
NXT_REPL RAW (4) -- HASH value of the next BH on the LRU chain
PRV_REPL RAW (4) -- HASH value of the previous BH on the LRU chain
Flag number --- block status, which may be buffer_dirty block_written_once redo_since_read in BH. For details, see the final section.
FLAG2 NUMBER
LOBID NUMBER
RFLAG NUMBER
SFLAG NUMBER
LRU_FLAG NUMBER --- LRU_FLAG. For details, refer to the final section.
TS # NUMBER -- tablespace number tablespace NUMBER
FILE # NUMBER -- absolute file number of a block in the database
Dbarfil number -- the relative file NUMBER of the block
Dbablk number -- block NUMBER -- On the data file
Class number -- too long, see the bottom.
State number -- too long, see the bottom.
MODE_HELD NUMBER --
CHANGES NUMBER
CSTATE NUMBER
LE_ADDR RAW (4) -- Lock Element address (OPS)
DIRTY_QUEUE NUMBER -- buffer on LRUW
SET_DS RAW (4) -- Buffer cache set this buffer is under
Obj number -- object NUMBER
Ba raw (4) -- buffer address -- ADDRESS in memory
CR_SCN_BAS NUMBER -- Consistent Read SCN base Consistent Read SCN low
CR_SCN_WRP NUMBER -- Consistent Read SCN wrap Consistent Read SCN high
CR_XID_USN NUMBER -- cr xid Undo segment no
CR_XID_SLT NUMBER -- cr xid slot
CR_XID_SQN NUMBER -- cr xid Sequence
CR_UBA_FIL NUMBER -- cr uba file
CR_UBA_BLK NUMBER -- cr uba Block
CR_UBA_SEQ NUMBER -- cr uba sequence
CR_UBA_REC NUMBER -- cr uba record
CR_SFL NUMBER --
CR_CLS_BAS NUMBER
CR_CLS_WRP NUMBER
LRBA_SEQ NUMBER -- Lowest RBA needed to recover block in cache
LRBA_BNO NUMBER
HSCN_BAS NUMBER --- low position of SCN
HSCN_WRP NUMBER -- high of SCN
HSUB_SCN NUMBER
US_NXT RAW (4)
US_PRV RAW (4)
WA_NXT RAW (4)
WA_PRV RAW (4)
OQ_NXT RAW (4) --- the previous HASH value of the object queue
OQ_PRV RAW (4) --- The next HASH value of the object queue
AQ_NXT RAW (4)
---The previous HASH value of the Secondary object queue.
AQ_PRV RAW (4)
---The next HASH value of the Secondary object queue.
OBJ_FLAG NUMBER
Tch number -- the abbreviation of Touch, indicating the NUMBER of accesses to a Buffer
Tim number -- Touch Time
CR_RFCNT NUMBER
SHR_RFCNT NUMBER
######################################## ###
Appendix:
In the flag, each member represents the following meanings:
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: indicates the type of the block corresponding to the buffer header:
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 is being exclusive by the current instance.
2, SCUR, a current mode block, shared with other instances is being shared by the current instance
3, CR, a consistent read (stale) block image consistent read
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 is in instance recovery mode
Lru_flag
0, 'free', 1, 'xcur', 2, 'scur ',
3, 'cr', 4, 'read', 5, 'mrec ',
6, 'c C', 7, 'write', 8, 'Pi ', 9, 'memory'
10, 'mwrite', 11, 'donated ', 12, 'protected ',
13, 'securefile', 14, 'siop ', 15, 'retries pt ',
16, 'flashfree', 17, 'flashrule', 18, 'flashnaca'
