溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--動態視圖（v$bh和x$bh)
v$bh與x$bh
v$bh:非常詳細地記錄了數據塊在數據緩沖區內的使用情況，一條記錄對應一個block的詳細記錄。
v$bh來自于基表x$bh與x$le
查看v$bh結構:
13:07:20 SYS@ test1 >desc v$bh Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- FILE# NUMBER BLOCK# NUMBER CLASS# NUMBER STATUS VARCHAR2(10) XNC NUMBER FORCED_READS NUMBER FORCED_WRITES NUMBER LOCK_ELEMENT_ADDR RAW(4) LOCK_ELEMENT_NAME NUMBER LOCK_ELEMENT_CLASS NUMBER DIRTY VARCHAR2(1) TEMP VARCHAR2(1) PING VARCHAR2(1) STALE VARCHAR2(1) DIRECT VARCHAR2(1) NEW CHAR(1) OBJD NUMBER TS# NUMBER LOBID NUMBER CACHEHINT NUMBER
STATUS
free - not currently in use
xcur - exclusive current,表示該數據塊處于排外模式,正在被當前Instance占用；
scur - shared current,在RAC環境中表示該數據庫正在和其他實例共享數據。
cr - consistent read,一致性讀。
read - being read from disk
mrec - in media recovery mode,表示數據塊處于介質恢復模式；
irec - in instance recovery mode ,表示數據塊處于實例恢復模式；
write - 表示數據庫正在往磁盤寫入數據；
13:07:19 SYS@ test1 >select * from v$fixed_view_definition t where t.view_name='GV$BH'
VIEW_NAME VIEW_DEFINITION ------------------------------ -------------------------------------------------- GV$BH select bh.inst_id, file#, dbablk, class, decode(st ate,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5, 'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'m write',11,'donated', 12,'protected', 13,'securefi le', 14,'siop',15,'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna'), 0, 0, 0, bh.le_addr, le_id1, le_id2, decode(bitand(flag,1), 0, 'N', 'Y' ), decode(bitand(flag,16), 0, 'N', 'Y'), decode(bi tand(flag,1536), 0, 'N', 'Y'), decode(bitand(flag, 16384), 0, 'N', 'Y'), decode(bitand(flag,65536), 0 , 'N', 'Y'), 'N', obj, ts#, lobid, bitand(OBJ_FLA G, 240)/16 from x$bh bh, x$le le where bh.le_addr = le.le_addr (+)
13:08:20 SYS@ test1 >desc x$bh
Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- ADDR RAW(4) INDX NUMBER INST_ID NUMBER HLADDR RAW(4) BLSIZ NUMBER NXT_HASH RAW(4) PRV_HASH RAW(4) NXT_REPL RAW(4) PRV_REPL RAW(4) FLAG NUMBER FLAG2 NUMBER LOBID NUMBER RFLAG NUMBER SFLAG NUMBER LRU_FLAG NUMBER TS# NUMBER FILE# NUMBER DBARFIL NUMBER DBABLK NUMBER CLASS NUMBER STATE NUMBER MODE_HELD NUMBER CHANGES NUMBER CSTATE NUMBER LE_ADDR RAW(4) DIRTY_QUEUE NUMBER SET_DS RAW(4) OBJ NUMBER BA RAW(4) CR_SCN_BAS NUMBER CR_SCN_WRP NUMBER CR_XID_USN NUMBER CR_XID_SLT NUMBER CR_XID_SQN NUMBER CR_UBA_FIL NUMBER CR_UBA_BLK NUMBER CR_UBA_SEQ NUMBER CR_UBA_REC NUMBER CR_SFL NUMBER CR_CLS_BAS NUMBER CR_CLS_WRP NUMBER LRBA_SEQ NUMBER LRBA_BNO NUMBER HSCN_BAS NUMBER HSCN_WRP NUMBER HSUB_SCN NUMBER US_NXT RAW(4) US_PRV RAW(4) WA_NXT RAW(4) WA_PRV RAW(4) OQ_NXT RAW(4) OQ_PRV RAW(4) AQ_NXT RAW(4) AQ_PRV RAW(4) OBJ_FLAG NUMBER TCH NUMBER TIM NUMBER CR_RFCNT NUMBER SHR_RFCNT NUMBER
x$bh中“touch count”信息對應到x$bh的tch字段，而段的data_object_id信息對應到x$bh的obj，或者是v$bh的objd。
案例分析：
（1）對象有多少個數據塊緩存到Data buffer中
13:29:01 SCOTT@ test1 >truncate table t1; 13:29:09 SCOTT@ test1 >begin 13:29:12 2 for i in 1..5000 loop 13:29:25 3 insert into t1 values (i); 13:29:35 4 end loop; 13:29:38 5 end; 13:29:39 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.67 13:29:41 SCOTT@ test1 >commit;
創建一存儲過程：show space讀取數據塊信息 13:29:43 SCOTT@ test1 >create or replace procedure show_space 13:34:44 2 13:34:44 3 ( p_segname in varchar2, 13:34:44 4 13:34:44 5 p_owner in varchar2 default user, 13:34:44 6 13:34:44 7 p_type in varchar2 default 'TABLE', 13:34:44 8 13:34:44 9 p_partition in varchar2 default NULL ) 13:34:44 10 13:34:44 11 as 13:34:44 12 13:34:44 13 l_total_blocks number; 13:34:44 14 13:34:44 15 l_total_bytes number; 13:34:44 16 13:34:44 17 l_unused_blocks number; 13:34:44 18 13:34:44 19 l_unused_bytes number; 13:34:44 20 13:34:44 21 l_LastUsedExtFileId number; 13:34:44 22 13:34:44 23 l_LastUsedExtBlockId number; 13:34:44 24 13:34:44 25 l_last_used_block number; 13:34:44 26 13:34:44 27 procedure p( p_label in varchar2, p_num in number ) 13:34:44 28 13:34:44 29 is 13:34:44 30 13:34:44 31 begin 13:34:44 32 13:34:44 33 dbms_output.put_line( rpad(p_label,40,'.') || 13:34:44 34 13:34:45 35 p_num ); 13:34:45 36 13:34:45 37 end; 13:34:45 38 13:34:45 39 begin 13:34:45 40 13:34:45 41 13:34:45 42 13:34:45 43 dbms_space.unused_space 13:34:45 44 13:34:45 45 ( segment_owner => p_owner, 13:34:45 46 13:34:45 47 segment_name => p_segname, 13:34:45 48 13:34:45 49 segment_type => p_type, 13:34:45 50 13:34:45 51 partition_name => p_partition, 13:34:45 52 13:34:45 53 total_blocks => l_total_blocks, 13:34:45 54 13:34:45 55 total_bytes => l_total_bytes, 13:34:45 56 13:34:45 57 unused_blocks => l_unused_blocks, 13:34:45 58 13:34:45 59 unused_bytes => l_unused_bytes, 13:34:45 60 13:34:45 61 last_used_extent_file_id => l_LastUsedExtFileId, 13:34:45 62 13:34:45 63 last_used_extent_block_id => l_LastUsedExtBlockId, 13:34:45 64 13:34:45 65 last_used_block => l_last_used_block ); 13:34:45 66 13:34:45 67 13:34:45 68 13:34:45 69 p( 'Total Blocks', l_total_blocks ); 13:34:45 70 13:34:45 71 p( 'Total Bytes', l_total_bytes ); 13:34:45 72 13:34:45 73 p( 'Unused Blocks', l_unused_blocks ); 13:34:45 74 13:34:45 75 p( 'Unused Bytes', l_unused_bytes ); 13:34:45 76 13:34:45 77 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 13:34:45 78 13:34:45 79 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 13:34:45 80 13:34:45 81 p( 'Last Used Block', l_last_used_block ); 13:34:45 82 13:34:45 83 end; 13:34:47 84 /
13:34:49 SCOTT@ test1 >set serverout on
13:35:01 SCOTT@ test1 >exec show_space(p_segname=>'T1'); Total Blocks............................16 Total Bytes.............................131072 Unused Blocks...........................0 Unused Bytes............................0 Last Used Ext FileId....................4 Last Used Ext BlockId...................136 Last Used Block.........................8 PL/SQL procedure successfully completed.
數據文件id為4，t1表總共使用了16個塊
分析數據塊的rowid：
13:35:40 SCOTT@ test1 >select f,b from ( 13:41:15 2 select dbms_rowid.rowid_relative_fno(rowid) f, 13:41:44 3 dbms_rowid.rowid_block_number(rowid) b 13:42:04 4 from t1) group by f,b; F B ---------- ---------- 4 143 11 691 4 142 11 693 11 694 4 141 11 692 11 695 8 rows selected.
Users 表空間有兩個datafile，id為4，11
13:42:52 SYS@ test1 >select file#,name from v$datafile; FILE# NAME ---------- -------------------------------------------------- 1 /u01/app/oracle/oradata/test1/system01.dbf 2 /u01/app/oracle/oradata/test1/sysaux01.dbf 3 /u01/app/oracle/oradata/test1/undotbs01.dbf 4 /u01/app/oracle/oradata/test1/users01.dbf 10 /u01/app/oracle/oradata/test1/index01.dbf 11 /dsk1/oradata/test1/users02.dbf 6 rows selected.
T1表總共為16個塊，數據占用了8個塊
13:47:43 SYS@ test1 >select file#,dbablk,tch from x$bh where obj=
2 (select data_object_id from dba_objects
3 where owner='SCOTT' AND object_name='T1')
4* order by dbablk
FILE# DBABLK TCH ---------- ---------- ---------- 4 136 2 4 137 2 4 138 2 4 139 2 4 140 2 4 141 2 4 142 2 4 143 2 11 688 1 11 689 1 11 690 4 11 690 1 11 691 2 11 692 2 11 693 2 11 694 2 11 695 2 17 rows selected.
13:48:59 SYS@ test1 >select file#,block#,status from v$bh where objd=
13:49:18 2 (select data_object_id from dba_objects
13:49:33 3 where owner='SCOTT' and object_name='T1')
13:49:51 4 order by block#;
FILE# BLOCK# STATUS ---------- ---------- ---------- 4 136 xcur 4 137 xcur 4 138 xcur 4 139 xcur 4 140 xcur 4 141 xcur 4 142 xcur 4 143 xcur 11 688 xcur 11 689 xcur 11 690 xcur 11 690 cr 11 691 xcur 11 692 xcur 11 693 xcur 11 694 xcur 11 695 xcur 17 rows selected.
查看x$bh,返回了block的touch count信息，表示了塊的熱點程度，現在最熱的塊是690，此塊應該是段頭塊。
13:56:05 SYS@ test1 >select header_file,header_block from dba_segments 13:56:25 2 where owner='SCOTT' and segment_name='T1'; HEADER_FILE HEADER_BLOCK ----------- ------------ 11 690
手工清空buffer_cache：
14:03:30 SYS@ test1 >alter system flush buffer_cache; System altered. 14:06:02 SYS@ test1 >select file#,dbablk,tch from x$bh where obj=(select data_object_id from dba_objects where owner='SCOTT' AND object_name='T1') order by dbablk; no rows selected 14:06:16 SYS@ test1 >select file#,block#,status from v$bh where objd=(select data_object_id from dba_objects where owner='SCOTT' and object_name='T1') order by block#; no rows selected
統計一個object的非free狀態的v$bh的記錄數，基本就反映了一個對象再data buffer中被cache的塊數。
14:10:29 SYS@ test1 >select count(*) from v$bh where objd=
14:10:45 2 (select data_object_id from dba_objects
14:10:59 3 where owner='SCOTT' and object_name='T1')
14:11:23 4 and status !='free';
COUNT(*)
----------
0
對t1表進行事務操作：
14:12:05 SCOTT@ test1 >insert into t1 values (5001); 1 row created. Elapsed: 00:00:00.00 14:13:38 SCOTT@ test1 >commit; Commit complete. 14:13:12 SYS@ test1 >select count(*) from v$bh where objd= 2 (select data_object_id from dba_objects 3 where owner='SCOTT' and object_name='T1') 4* and status !='free' COUNT(*) ---------- 4
14:13:43 SYS@ test1 >select file#,block#,status from v$bh where objd=(select data_object_id from dba_objects where owner='SCOTT' and object_name='T1') order by block#;
FILE# BLOCK# STATUS ---------- ---------- ---------- 4 143 xcur 11 688 xcur 11 689 xcur 11 690 xcur
（2）熱點塊問題
x$bh的touch count，這個數字將作為LRU算法的一個重要參考信息，如果一個塊被多次訪問，每次訪問都會導致該塊的記錄加一。
1）查看t1的object_id
14:26:37 SYS@ test1 >select data_object_id from dba_objects 14:26:56 2 where owner='SCOTT' and object_name='T1'; DATA_OBJECT_ID -------------- 16399
2）查看block 143的tch
14:29:05 SYS@ test1 >select tch from x$bh 14:29:20 2 where obj=16399 and dbablk=143 and file#=4 and tch>0; TCH ---------- 1
3）訪問block 143
采用了dbms_rowid.rowid_create函數來創建rowid，表示新創建的rowid類型為擴展rowid，類型為1；data_object_id為16399；數據文件id為4；塊的id為143；行數為一行（0）。
14:30:47 SYS@ test1 >select count(*) from scott.t1 14:31:02 2 where rowid=dbms_rowid.rowid_create(1,16399,4,143,0); COUNT(*) ---------- 1
多次訪問后，block 143的tch在增加
14:32:19 SYS@ test1 >select tch from x$bh where obj=16399 and dbablk=143 and file#=4 and tch>0; TCH ---------- 2 14:32:35 SYS@ test1 >select count(*) from scott.t1 where rowid=dbms_rowid.rowid_create(1,16399,4,143,0); COUNT(*) ---------- 1 14:33:23 SYS@ test1 >select tch from x$bh where obj=16399 and dbablk=143 and file#=4 and tch>0; TCH ---------- 3
block 143的初始tch=1，經過連續兩次查詢后，tch=3.根據這一變化，可以基本判斷x$bh表中的tch大的塊，一般都是熱點塊。
查詢top 10熱點塊所在的對象：
14:43:43 SYS@ test1 >select /*+ rule */ owner,object_name from dba_objects 2 where data_object_id in 3 (select obj from 4 (select obj from x$bh order by tch desc ) 5* where rownum <11) OWNER OBJECT_NAME ------------------------------ ------------------------------ SYS I_OBJ# SYS I_OBJ2 SYS I_ICOL1 SYS I_CDEF2 SYS I_OBJ#_INTCOL# SYS I_HH_OBJ#_INTCOL# SYS I_TAB_STATS$_OBJ# SYS I_IND_STATS$_OBJ# 8 rows selected.
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
