Shulou(Shulou.com)06/01 Report--

Optimize it through case study-- dynamic view (v$bh and x$bh)

V$bh and x$bh

V$bh: the usage of data blocks in the data buffer is recorded in great detail, with a record corresponding to a detailed record of a block.

V$bh comes from base tables x$bh and x$le

View the v$bh structure:

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 NUMBERSTATUS

Free-not currently in use

Xcur-exclusive current, indicating that the data block is in exclusive mode and is being occupied by the current Instance

Scur-shared current, which indicates that the database is sharing data with other instances in a RAC environment.

Cr-consistent read, consistent read.

Read-being read from disk

Mrec-in media recovery mode, indicating that the data block is in media recovery mode

Irec-in instance recovery mode, indicating that the data block is in instance recovery mode

Write-indicates that the database is writing data to disk

13:07:19 SYS@ test1 > select * from v$fixed_view_definition t where t.viewroomnameplates GVDs 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, write',11,'donated', 12, protection 5, mrec',6,'irec',7,'write',8,'pi', 9, write',11,'donated', 12, etc.) 13 bh.le_addr securefi le', 14 bh.le_addr le_id2, decode (bitand (flag,1), 0,'N' N') 'Y'), decode (bitand (flag,16), 0,' n','Y'), decode (bitand (flag,1536), 0,'N','Y'), decode (bitand (flag, 16384), 0,'N,'Y'), decode (bitand (flag,65536)) 0, 'nasty,' Y'), 'nicked, obj, ts#, lobid, bitand (OBJ_FLA G240) / 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

The "touch count" information in the x$bh corresponds to the tch field of the x$bh, while the data_object_id information of the segment corresponds to the obj of the x$bh or the objd of the v$bh.

Case study:

(1) how many data blocks of an object are cached in Data buffer

13:29:01 SCOTT@ test1 > truncate table T1 politics 13 begin13:29:12 29 SCOTT@ test1 > begin13:29:12 2 for i in 1.. 5000 loop13: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: 0000 SCOTT@ test1 00.6713 29 SCOTT@ test1 > commit Create a stored procedure: show space reads block information 13:29:43 SCOTT@ test1 > create or replace procedure show_space13:34:44 213 create or replace procedure show_space13:34:44 34 p_segname in varchar2,13:34:44 44 3 (p_segname in varchar2,13:34:44 413 34 p_segname in varchar2,13:34:44 44 5 p_owner in varchar2 default user,13:34:44 613 14 44 7 p_type in varchar2 default 'TABLE' 13:34:44 813 as13:34:44 34 p_partition in varchar2 default NULL 44 9 l_total_blocks number) 13:34:44 1013 14 14 14 44 11 as13:34:44 1213 34 14 13 13 14 13:34:44 1413:34:44 15 l_total_bytes number;13:34:44 1613:34:44 17 l_unused_blocks number;13:34:44 1813:34:44 19 l_unused_bytes number;13:34:44 2013:34:44 21 l_LastUsedExtFileId number;13:34:44 2213:34:44 23 l_LastUsedExtBlockId number 13:34:44 2413:34:44 25 l_last_used_block number 13:34:44 2613 is13:34:44 34 procedure 44 27 procedure p (p_label in varchar2, p_num in number) 13:34:44 2813 procedure 34 is13:34:44 3013 34 dbms_output.put_line 44 31 begin13:34:44 3213 14 44 33 dbms_output.put_line (rpad (paired labelleg.') | 13:34:44 3413 34 begin13:34:44 45 35 p_num) 13:34:45 3613:34:45 37 end 13:34:45 3813 begin13:34:45 4539 begin13:34:45 4013 segment_type 45 4113 (segment_owner = > paired owner.1313) 45461314449 segment_type = > p_type 13:34:45 5013 unused_blocks 34 partition_name 45 51 partition_name = > pendant partitionalism 1314 unused_blocks 45 5213 14 unused_bytes 45 53 unused_bytes = > lounge totalizer blocks 34 unused_blocks 45 5413 34 unused_bytes 45 55 unused_bytes = > lounge unusedblock blocks13445 5813 unused_bytes 34 unused_bytes = > 13:34:45 6013 LastUsedExtFileId last_used_extent_file_id = > lounge LastUsedExtFileId13 last_used_extent_block_id = > lounge LastUsedExtBlockId last_used_extent_block_id = > l_last_used_block) 13:34:45 6613, 456713, 4569 p ('Total Blocks', l_total_blocks), 13:34:45, 7013, 34, 45 (' Total Bytes', l_total_bytes), 13:34:45, 7213, 34, 45, 73 ('Unused Blocks', l_unused_blocks), 13:34:45, 7413, 34 (Unused Bytes', l_unused_bytes). 13:34:45 7613 LastUsedExtBlockId 34 LastUsedExtFileId 45 77 p ('LastUsedExtBlockId', l_LastUsedExtFileId); 13:34:45 7813 LastUsedExtBlockId 34 LastUsedExtFileId 45 79 p ('LastUsedExtBlockId', l_LastUsedExtBlockId); 13:34:45 8013 14 l_LastUsedExtBlockId 45 81 p ('LastUsed Block', l_last_used_block); 13:34:45 8213 14 14 14 45 83 MAV 84 /

13:34:49 SCOTT@ test1 > set serverout on

13:35:01 SCOTT@ test1 > exec show_space (paired segname = > 'T1') Total Blocks..16Total Bytes..131072Unused Blocks..0Unused Bytes.. 0Last Used Ext FileId.4Last Used Ext BlockId.136Last Used Block.8PL/SQL procedure successfully completed.

The data file id is 4 and the T1 table uses a total of 16 blocks

Analyze the rowid of the block:

13:35:40 SCOTT@ test1 > select faggot b from (13:41:15 2 select dbms_rowid.rowid_relative_fno (rowid) f 13 from 41 from 44 3 dbms_rowid.rowid_block_number (rowid) b13 from 42from 04 4 from T1) group by flegue b F B-4143 11 691 4 142 11 693 11 694 4 141 11 692 11 6958 rows selected.

The Users tablespace has two datafile,id for 4pm 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.dbf6 rows selected.

The T1 table has a total of 16 blocks, and the data occupies 8 blocks.

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 217 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 xcur17 rows selected.

Check the x$bh and return the touch count information of block, indicating the hot degree of the block. Now the hottest block is 690. this block should be a segment header block.

13:56:05 SYS@ test1 > select header_file,header_block from dba_segments13:56:25 2 where owner='SCOTT' and segment_name='T1';HEADER_FILE HEADER_BLOCK--11 690

Manually empty the 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 selected14: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

Counting the number of records of an object's non-free state v$bh basically reflects the number of blocks of an object that are cache in the data buffer.

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

Transactional operations on the T1 table:

14:12:05 SCOTT@ test1 > insert into T1 values (5001); 1 row created.Elapsed: 00values 00.0014 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) Hot spot block problem

The touch count of x$bh, this number will be used as an important reference for the LRU algorithm. If a block is accessed multiple times, each visit will cause the record of the block to be increased by one.

1) check the object_id of T1

14:26:37 SYS@ test1 > select data_object_id from dba_objects14:26:56 2 where owner='SCOTT' and object_name='T1';DATA_OBJECT_ID- 16399

2) View the tch of block 143s

14:29:05 SYS@ test1 > select tch from x$bh14:29:20 2 where obj=16399 and dbablk=143 and file#=4 and tch > 0; TCH- 1

3) visit block 143,

The dbms_rowid.rowid_create function is used to create the rowid, which means that the newly created rowid type is extended rowid, the type is 1 position data object object id is 16399, the data file id is 4, the block id is 143, and the number of rows is one line (0).

14:30:47 SYS@ test1 > select count (*) from scott.t114:31:02 2 where rowid=dbms_rowid.rowid_create; COUNT (*)-1

After multiple visits, the tch of block 143is increasing

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 TCH- 3

The initial tch=1 of block 143.After two consecutive queries, tch=3. According to this change, we can basically judge that the large tch blocks in the x$bh table are generally hot blocks.

Query the object where the top 10 hotspot block resides:

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.