首页 > 代码库 > x$bh视图
x$bh视图
首先，这篇文章是基于如下ORACLE版本。
BANNER------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64biPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Solaris: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production
下面是x$bh这个视图(也许是表 :) ) 的结构。
X$BH Fixed Table Buffer Cache DiagramColumn Type Description~~~~~~ ~~~~~ ~~~~~~~~~~~ADDR RAW(4) Hex address of the Buffer Header.INDX NUMBER Buffer Header numberBUF# NUMBERHLADDR RAW(4) Hash Chain Latch AddressSee . ADDRLRU_FLAG NUMBER 8.1+ LRU flag KCBBHLDF 0x01 8.1 LRU Dump Flag used in debug print routine KCBBHLMT 0x02 8.1 moved to tail of lru (for extended stats) KCBBHLAL 0x04 8.1 on auxiliary list KCBBHLHB 0x08 8.1 hot buffer - not in cold portion of lruFLAG NUMBER KCBBHFBD 0x00001 buffer dirty KCBBHFAM 0x00002 7.3 about to modify; try not to start io KCBBHFAM 0x00002 8.0 about to modify; try not to start io KCBBHNAC 0x00002 8.1 notify dbwr after change KCBBHFMS 0x00004 modification started, no new writes KCBBHFBL 0x00008 block logged KCBBHFTD 0x00010 temporary data - no redo for changes KCBBHFBW 0x00020 being written; can‘t modify KCBBHFWW 0x00040 waiting for write to finish KCBBHFCK 0x00080 7.3 checkpoint asap 0x00080 8.0 not used KCBBHFMW 0x00080 8.1 multiple waiters when gc lock acquired KCBBHFRR 0x00100 recovery reading, do not reuse, being read KCBBHFUL 0x00200 unlink from lock element - make non-current KCBBHFDG 0x00400 write block & stop using for lock down grade KCBBHFCW 0x00800 write block for cross instance call KCBBHFCR 0x01000 reading from disk into KCBBHCR buffer KCBBHFGC 0x02000 has been gotten in current mode KCBBHFST 0x04000 stale - unused CR buf made from current 0x08000 7.3 Not used. KCBBHFDP 0x08000 8.0 deferred ping KCBBHFDP 0x08000 8.1 deferred ping KCBBHFDA 0x10000 Direct Access to buffer contents KCBBHFHD 0x20000 Hash chain Dump used in debug print routine KCBBHFIR 0x40000 Ignore Redo for instance recovery KCBBHFSQ 0x80000 sequential scan only flag KCBBHFNW 0x100000 7.3 Set to indicate a buffer that is NEW 0x100000 8.0 Not used KCBBHFBP 0x100000 8.1 Indicates that buffer was prefetched KCBBHFRW 0x200000 7.3 re-write if being written (sort) 0x200000 8.0 Not used KCBBHFFW 0x200000 8.1 Buffer has been written once KCBBHFFB 0x400000 buffer is "logically" flushed KCBBHFRS 0x800000 ReSilvered already - do not redirty KCBBHFKW 0x1000000 7.3 ckpt writing flag to avoid rescan */ 0x1000000 8.0 Not used KCBBHDRC 0x1000000 8.1 buffer is nocache 0x2000000 7.3 Not used KCBBHFRG 0x2000000 8.0 Redo Generated since block read KCBBHFRG 0x2000000 8.1 Redo Generated since block read KCBBHFWS 0x10000000 8.0 Skipped write for checkpoint. KCBBHFDB 0x20000000 8.1 buffer is directly from a foreign DB KCBBHFAW 0x40000000 8.0 Flush after writing KCBBHFAW 0x40000000 8.1 Flush after writingTS# NUMBER 8.X Tablespace numberDBARFIL NUMBER 8.X Relative file number of blockDBAFIL NUMBER 7.3 File number of blockDBABLK NUMBER Block number of blockCLASS NUMBER See Note 33434.1 1,‘data block‘, 2,‘sort block‘, 3,‘save undo block‘, 4,‘segment header‘, 5,‘save undo header‘, 6,‘free list‘, 7,‘extent map‘, 8,‘1st level bmb‘, 9,‘2nd level bmb‘, 10,‘3rd level bmb‘, 11,‘bitmap block‘, 12,‘bitmap index block‘, 13,‘file header block‘, 14,‘unused‘, 15,‘system undo header‘, 16,‘system undo block‘, 17,‘undo header‘, 18,‘undo block‘ -- since 10gSTATE NUMBER KCBBHFREE 0 buffer free KCBBHEXLCUR 1 buffer current (and if DFS locked X) KCBBHSHRCUR 2 buffer current (and if DFS locked S) KCBBHCR 3 buffer consistant read KCBBHREADING 4 Being read KCBBHMRECOVERY 5 media recovery (current & special) KCBBHIRECOVERY 6 Instance recovery (somewhat special)MODE_HELD NUMBER Mode buffer held in (MODE pre 7.3) 0=KCBMNULL, KCBMSHARE, KCBMEXCLCHANGES NUMBERCSTATE NUMBERX_TO_NULL NUMBER Count of PINGS out (OPS)DIRTY_QUEUE NUMBER You wont normally see buffers on the LRUWLE_ADDR RAW(4) Lock Element address (OPS)SET_DS RAW(4) Buffer cache set this buffer is under OBJ NUMBER Data object numberTCH NUMBER 8.1 Touch CountTIM NUMBER 8.1 Touch TimeBA RAW(4)CR_SCN_BAS NUMBER Consistent Read SCN baseCR_SCN_WRP NUMBER Consistent Read SCN wrapCR_XID_USN NUMBER CR XID Undo segment noCR_XID_SLT NUMBER CR XID slotCR_XID_SQN NUMBER CR XID SequenceCR_UBA_FIL NUMBER CR UBA fileCR_UBA_BLK NUMBER CR UBA BlockCR_UBA_SEQ NUMBER CR UBA sequenceCR_UBA_REC NUMBER CR UBA recordCR_SFL NUMBERLRBA_SEQ NUMBER } Lowest RBA needed to recover block in cacheLRBA_BNO NUMBER }LRBA_BOF NUMBER }HRBA_SEQ NUMBER } Redo RBA to be flushed BEFORE this blockHRBA_BNO NUMBER } can be written outHRBA_BOF NUMBER }RRBA_SEQ NUMBER } Block recovery RBARRBA_BNO NUMBER }RRBA_BOF NUMBER }NXT_HASH NUMBER Next buffer on this hash chainPRV_HASH NUMBER Previous buffer on this hash chainNXT_LRU NUMBER Next buffer on the LRUPRV_LRU NUMBER Previous buffer on the LRUUS_NXT RAW(4)US_PRV RAW(4)WA_NXT RAW(4)WA_PRV RAW(4)ACC RAW(4)MOD RAW(4)
看不懂没关系，这个视图就是oracle data buffer的一个窗口。里面的每一条记录代表了data buffer中的一个记录。
下面的查询可以查到data buffer中有哪些数据对象的data block以及他们占用多少buffer。这里限定了表空间号大于2是为了除去一些系统表占用的buffer。
SQL> select 2 o.owner owner, o.object_name, blsiz , count(*) blocks 3 from x$bh b , dba_objects o 4 where b.obj = o.data_object_id 5 and b.ts# > 2 6 group by o.owner,o.object_name, blsiz 7 order by blocks desc;OWNER OBJECT_NAME BLSIZ BLOCKS------------------ ------------------------------------------------ ---------- ----------SATTEST AUDIT_LOG 8192 404CITOSADMIN RMS_AUDIT_LOG 8192 68CICOSADMIN BERTH_ALLOCATION 8192 14CITOSADMIN EAS_DOCUMENT_DETAIL 8192 10CITOSADMIN TEST 8192 8CITOSADMIN TERMINAL2 8192 5CITOSADMIN EAS_CONFIG 8192 5CICOSADMIN BERTH_ALLOCATION_NNDX4 8192 4CITOSADMIN SITE 8192 3CICOSADMIN BERTH_ALLOCATION_NNDX3 8192 3CITOSADMIN TERMINAL 8192 3CICOSADMIN BERTH_ALLOCATION_NNDX2 8192 3CICOSADMIN BERTH_ALLOCATION_NNDX1 8192 3CITOSADMIN REEFER_PAYMENT_SCHEME 8192 1CITOSADMIN CNTR 8192 1SUMSADMIN SM_DISCHARGING_LIST 8192 1CITOSADMIN CNTR_2 8192 1CITOSADMIN GATE_PM_TRIP 8192 1
下面的查询可以查到data buffer中的某个数据对象(这里叫TEST)其占用的buffer的状态。这里看到TEST有两个cr块和6个xcur块。
SQL> selecto.object_name 2 3 ,decode(state,0,‘free‘,1,‘xcur‘,2,‘scur‘,3,‘cr‘, 4,‘read‘,5,‘mrec‘ 4 ,6,‘irec‘,7,‘write‘,8,‘pi‘) state 5 , blsiz , count(*) blocks 6 from x$bh b , dba_objects o 7 where b.obj = o.data_object_id 8 and b.ts# > 0 9 and o.object_name = ‘TEST‘ 10 group by o.object_name, state, blsiz;OBJECT_NAME STATE BLSIZ BLOCKS------------------------------------------------ --------------- ---------- ----------TEST cr 8192 2TEST xcur 8192 6
下面的查询查到data buffer中特定数据对象不同状态buffer的地址信息，DBARFIL是相对文件号，是每个表空间中确定datafile的文件号，DBABLK是确定文件号后确定datablock的block number。BA是该buffer的实际addr
SQL> select 2 o.object_name 3 ,decode(state,0,‘free‘,1,‘xcur‘,2,‘scur‘,3,‘cr‘, 4,‘read‘,5,‘mrec‘,6,‘irec‘,7,‘write‘,8,‘pi‘) state 4 , dbarfil 5 , dbablk 6 , ba 7 from x$bh b , dba_objects o 8 where b.obj = o.data_object_id 9 and b.ts# > 0 10 and o.object_name = ‘TEST‘ 11 ;OBJECT_NAME STATE DBARFIL DBABLK BA------------------------------------------------ --------------- ---------- ---------- ----------------TEST xcur 4 61470 000000038A380000TEST xcur 4 61467 00000003866EA000TEST xcur 4 61472 0000000385E5E000TEST cr 4 61469 000000038BF08000TEST cr 4 61469 0000000386F20000TEST xcur 4 61469 000000038186C000TEST xcur 4 61471 0000000399D5E000TEST xcur 4 61468 0000000381716000
下面做一些操作以便熟悉该视图。
首先创建一个表如下：
create table test (id1 number,id2 number,text varchar2(2000));insert into citosadmin.test values(1,1,‘a‘);insert into citosadmin.test values(2,2,‘b‘);insert into citosadmin.test values(3,3,‘c‘);alter system flush buffer_cache;
然后看一下data buffer中这个表占用的情况。占用了6个buffer
SQL> select o.owner owner, o.object_name, blsiz , count(*) blocks from x$bh b , dba_objects o 2 where b.obj = o.data_object_id and o.object_name=‘TEST‘ group by o.owner,o.object_name, blsiz;OWNER OBJECT_NAME BLSIZ BLOCKS------------------ ------------------------------------------------ ---------- ----------CITOSADMIN TEST 8192 6
全都是xcur状态的。xcur是表示是current的最新的block。如果是rac block和其它的instance共享则是scur。
SQL> select 2 o.object_name 3 ,decode(state,0,‘free‘,1,‘xcur‘,2,‘scur‘,3,‘cr‘, 4,‘read‘,5,‘mrec‘ 4 ,6,‘irec‘,7,‘write‘,8,‘pi‘) state 5 , blsiz , count(*) blocks 6 from x$bh b , dba_objects o 7 where b.obj = o.data_object_id 8 and b.ts# > 0 9 and o.object_name = ‘TEST‘ 10 group by o.object_name, state, blsiz;OBJECT_NAME STATE BLSIZ BLOCKS------------------------------------------------ --------------- ---------- ----------TEST xcur 8192 6
每个buffer的地址如下
SQL> select o.object_name 2 ,decode(state,0,‘free‘,1,‘xcur‘,2,‘scur‘,3,‘cr‘, 4,‘read‘,5,‘mrec‘,6,‘irec‘,7,‘write‘,8,‘pi‘) state 3 , dbarfil 4 , dbablk 5 , ba 6 from x$bh b , dba_objects o 7 where b.obj = o.data_object_id 8 and o.object_name = ‘TEST‘;OBJECT_NAME STATE DBARFIL DBABLK BA------------------------------------------------ --------------- ---------- ---------- ----------------TEST xcur 4 61478 00000003816BC000TEST xcur 4 61475 0000000389494000TEST xcur 4 61480 000000039CEB8000TEST xcur 4 61477 000000039CD8A000TEST xcur 4 61479 000000038DDD8000TEST xcur 4 61476 000000038AB52000
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。