学习oracle动态性能表--V$BH

v$bh

oralce使用v$bh视图来记录与数据缓冲（data buffer）相关的信息，它详细记录了数据缓冲中每一个数据块（data block）的状态信息。
虽然Oracle最初开发V$BH查看表的时候把它用于Oracle平行服务器（Oracle Parallel Server，OPS），但是你也可以用V$BH查看表来显示数据库里每个对象类型的数据缓冲区里数据块的数量。
这个查询让人尤其兴奋，因为你现在可以知道哪些对象正在消耗数据缓冲区。在Oracle9i里，你可以使用这一信息来隔离表格，以便用不同的块大小来分隔RAM缓冲区。

一、各字段的说明如下：
----------------------------------------------
FILE# NUMBER Datafile identifier number (to find the filename, query DBA_DATA_FILES or V$DBFILE)
BLOCK# NUMBER Block number
CLASS# NUMBER Class number
STATUS VARCHAR2(6) Status of the buffer:
free - Not currently in use,表示这是一个限制的数据块，oracle现在没有使用它；

xcur - Exclusive（exclusive current），表示该数据块处于排外模式；

scur - Shared current,表示该数据库正在和其他实例共享数据。

cr - Consistent read,表示该数据块是一个克隆（clone）的数据库，可以执行共享的只读操作；

read - Being read from disk,表示该数据块正在从磁盘读取数据；

mrec - In media recovery mode,表示数据块处于介质恢复模式；

irec - In instance recovery mode,表示数据块处于实例恢复模式；

XNC NUMBER Number of PCM x to null lock conversions due to contention with another instance. This column is obsolete and maintained for backward compatibility.
LOCK_ELEMENT_ADDR RAW(4 | 8) Address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT_NAME NUMBER The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
LOCK_ELEMENT_CLASS NUMBER The address of the lock element that contains the PCM lock that is covering the buffer. If more than one buffer has the same address, then these buffers are covered by the same PCM lock.
FORCED_READS NUMBER Number of times the block had to be reread from the cache because another instance has forced it out of this instance's cache by requesting the lock on the block in exclusive mode
FORCED_WRITES NUMBER Number of times GCS had to write this block to cache because this instance had used the block and another instance had requested the lock on the block in a conflicting mode
DIRTY VARCHAR2(1) Y - block modified
TEMP VARCHAR2(1) Y - temporary block
PING VARCHAR2(1) Y - block pinged
STALE VARCHAR2(1) Y - block is stale
DIRECT VARCHAR2(1) Y - direct block
NEW VARCHAR2(1) Always set to N. This column is obsolete and maintained for backward compatibility.
OBJD NUMBER Database object number of the block that the buffer represents
TS# NUMBER Tablespace number of block

二、CLASS#的数据来源于如下：
---------------------------
SQL> select distinct class# from v$bh;

    CLASS#
----------
         1
         4
         8
         9
        12
        13
        15
        17
        18
        19
        20

    CLASS#
----------
        21
        22
        23
        24
        25
        26
        27
        28
        29
        30
        31

    CLASS#
----------
        32
        33
        34
        35
        36
        37
        38
        39
        40
        41
        42

33 rows selected.

SQL> select * from v$waitstat;

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block           23369217    5855617
sort block                  0          0
save undo block             0          0
segment header            238        162
save undo header            0          0
free list                   0          0
extent map                  0          0
1st level bmb             323         95
2nd level bmb              79         35
3rd level bmb               0          0
bitmap block                0          0

CLASS                   COUNT       TIME
------------------ ---------- ----------
bitmap index block          0          0
file header block         220       6458
unused                      0          0
system undo header          0          0
system undo block           0          0
undo header             13694       1794
undo block              16828       2699

18 rows selected.

SQL> select  count(*) from v$rollstat;

  COUNT(*)
----------
        14

SQL>




其中 v$bh 里面的 class# 的 1 ---14 分别对应如下类型
data block           23369217    5855617         -------  1
sort block                  0          0         -------  2
save undo block             0          0        -------  3
segment header            238        162
save undo header            0          0
free list                   0          0
extent map                  0          0
1st level bmb             323         95
2nd level bmb              79         35
3rd level bmb               0          0
bitmap block                0          0

CLASS                   COUNT       TIME
------------------ ---------- ----------
bitmap index block          0          0
file header block         220       6458        -------  13
unused                                           ---------  14


system undo header          0          0         --------   15
system undo block           0          0         ----------   16
undo header 1                                  --------------- 17
undo  block  1                                 -------------- 18
undo  header 2                                   -------------- 19
undo  block   2                                 -------------- 20
依次类推

（9i里v$bh里从class 15开始是undo segment header,undo block。class对应着15+2*usn的规则，奇数对应header,偶数对应block，usn号可以从v$rollstat中得到，
比如system undo header对应class15.在rac环境中，则对应着gv$rollstat中的usn.）

我这里的 回滚段（算上系统回滚段）一共是 14  = v$rollstat.count(*) ,v$waitstat.count(*) = 18(但是这里计算了 系统回滚段和非系统回滚段共4条记录)
所以就是 14*2 + (18 - 4) = 42
42也就是 v$bh 中类型编号最大的

而中间有些不存在的编号，也就是如
save undo block             0          0        -------  3
这样暂时不存在的block

三、示例1
----------------------------------------------
SQL> create table a_test(f1 varchar2(30));

Table created.

SQL> insert into a_test values('99999');

1 row created.

SQL> select obj# from sys.obj$ where name = 'A_TEST';

      OBJ#
----------
     11046

SQL> select file#,block#,class#,status  from v$bh where bjd=&1   
  2  ;   
Enter value for 1: 11046
old   1: select file#,block#,class#,status  from v$bh where bjd=&1
new   1: select file#,block#,class#,status  from v$bh where bjd=11046

     FILE#     BLOCK#     CLASS# STATUS
---------- ---------- ---------- -------
         3      16497          8 xcur
         3      16498          9 xcur
         3      16499          4 xcur
         3      16500          1 xcur
         3      16501          1 xcur
         3      16502          1 xcur
         3      16503          1 xcur
         3      16504          1 xcur

8 rows selected.

SQL> commit;

Commit complete.

SQL> select file#,block#,class#,status  from v$bh where bjd=&1;
Enter value for 1: 11046
old   1: select file#,block#,class#,status  from v$bh where bjd=&1
new   1: select file#,block#,class#,status  from v$bh where bjd=11046

     FILE#     BLOCK#     CLASS# STATUS
---------- ---------- ---------- -------
         3      16497          8 xcur
         3      16498          9 xcur
         3      16499          4 xcur
         3      16500          1 xcur
         3      16501          1 xcur
         3      16502          1 xcur
         3      16503          1 xcur
         3      16504          1 xcur

8 rows selected.

SQL> update a_test set f1='11111';

1 row updated.

SQL> select file#,block#,class#,status  from v$bh where bjd=&1;
Enter value for 1: 11046
old   1: select file#,block#,class#,status  from v$bh where bjd=&1
new   1: select file#,block#,class#,status  from v$bh where bjd=11046

     FILE#     BLOCK#     CLASS# STATUS
---------- ---------- ---------- -------
         3      16497          8 xcur
         3      16498          9 xcur
         3      16499          4 xcur
         3      16500          1 xcur
         3      16501          1 xcur
         3      16502          1 xcur
         3      16503          1 xcur
         3      16504          1 cr
         3      16504          1 xcur

9 rows selected.

SQL> commit;

Commit complete.

SQL> select file#,block#,class#,status  from v$bh where bjd=&1;
Enter value for 1: 11046
old   1: select file#,block#,class#,status  from v$bh where bjd=&1
new   1: select file#,block#,class#,status  from v$bh where bjd=11046

     FILE#     BLOCK#     CLASS# STATUS
---------- ---------- ---------- -------
         3      16497          8 xcur
         3      16498          9 xcur
         3      16499          4 xcur
         3      16500          1 xcur
         3      16501          1 xcur
         3      16502          1 xcur
         3      16503          1 xcur
         3      16504          1 cr
         3      16504          1 xcur
9 rows selected.

四、示例2
----------------------------------------------
1，创建一个测试表，test，并且插入10000行数据；
    SQL>  create table test (id int);

    SQL> begin
      2  for i in 1..10000 loop
      3  insert into test values(i)
      4  end loop;
      5  end;
      6  /

    SQL> commit；

2，创建一个存储过程SHOW_SPACE：
create or replace procedure show_space

    ( p_segname in varchar2,

  p_owner   in varchar2 default user,

  p_type    in varchar2 default 'TABLE',

  p_partition in varchar2 default NULL )

as

    l_total_blocks              number;

    l_total_bytes               number;

    l_unused_blocks             number;

    l_unused_bytes              number;

    l_LastUsedExtFileId         number;

    l_LastUsedExtBlockId        number;

    l_last_used_block           number;

    procedure p( p_label in varchar2, p_num in number )

    is

    begin

        dbms_output.put_line( rpad(p_label,40,'.') ||

                              p_num );

    end;

begin

   

    dbms_space.unused_space

    ( segment_owner     => p_owner,

      segment_name      => p_segname,

      segment_type      => p_type,

      partition_name    => p_partition,

      total_blocks      => l_total_blocks,

      total_bytes       => l_total_bytes,

      unused_blocks     => l_unused_blocks,

      unused_bytes      => l_unused_bytes,

      last_used_extent_file_id => l_LastUsedExtFileId,

      last_used_extent_block_id => l_LastUsedExtBlockId,

      last_used_block => l_last_used_block );



    p( 'Total Blocks', l_total_blocks );

    p( 'Total Bytes', l_total_bytes );

    p( 'Unused Blocks', l_unused_blocks );

    p( 'Unused Bytes', l_unused_bytes );

    p( 'Last Used Ext FileId', l_LastUsedExtFileId );

    p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );

    p( 'Last Used Block', l_last_used_block );

end;
/

3，检查表test的空间使用情况：
    SQL> exec show_space('TEST');
    Total Blocks............................24
    Total Bytes.............................196608
    Unused Blocks...........................3
    Unused Bytes............................24576
    Last Used Ext FileId....................1
    Last Used Ext BlockId...................62177
    Last Used Block.........................5
  
    由上可知，该表test共占用了24个数据块，196608字节，文件ID为1

4， 获得表test在数据块中的分布情况：
  SQL> select f,b from (
  2  select dbms_rowid.rowid_relative_fno(rowid) f,
  3         dbms_rowid.rowid_block_number(rowid) b
  4  from test) group by f,b order by b;

         F          B
---------- ----------
         1      62162
         1      62163
         1      62164
         1      62165
         1      62166
         1      62167
         1      62168
         1      62169
         1      62170
         1      62171
         1      62172
         1      62173
         1      62174
         1      62175
         1      62176
         1      62177

16 rows selected.
    由此可见，表test中的数据共占用了16个数据块，但是前面第三步中，发现该表占用了24个数据块。这是正常的，因为oracle本身会使用8个数据库来记录段头、位图块等额外的信息。我们现在只需要了解到，表test共占用了24个数据块，其中16个是数据，8个是表信息。

5，检查x$bh和v$bh的更新：
  SQL> select file#,dbablk,tch from x$bh where bj=
  2  (select data_object_id from dba_objects
  3  where wner='SYS'  and object_name='TEST')
  4  order by dbablk;

     FILE#     DBABLK        TCH
---------- ---------- ----------
         1      62161          6
         1      62162          3
         1      62163          3
         1      62164          3
         1      62165          3
         1      62166          3
         1      62167          3
         1      62168          3
         1      62169          3
         1      62170          3
         1      62171          3
         1      62172          3
         1      62173          3
         1      62174          3
         1      62175          3
         1      62176          3
         1      62177          3
         1      62178          3
         1      62179          3
         1      62180          3
         1      62181          3

21 rows selected.

  SQL> select file#,block#,status from v$bh where bjd=
  2  (select data_object_id from dba_objects
  3  where wner='SYS'  and object_name='TEST')
  4  order by block#;

     FILE#     BLOCK# STATUS
---------- ---------- -------
         1      62161 xcur
         1      62162 xcur
         1      62163 xcur
         1      62164 xcur
         1      62165 xcur
         1      62166 xcur
         1      62167 xcur
         1      62168 xcur
         1      62169 xcur
         1      62170 xcur
         1      62171 xcur
         1      62172 xcur
         1      62173 xcur
         1      62174 xcur
         1      62175 xcur
         1      62176 xcur
         1      62177 xcur
         1      62178 xcur
         1      62179 xcur
         1      62180 xcur
         1      62181 xcur

21 rows selected.

    这里可以看到，在v$bh和x$bh中得到的数据块，是从62161～62181的21条记录，但是在第四步中，我们知道数据是占用了62162～62177的16个数据库，这里，62161数据块里面存放的是段头信息，可以通过如下命令进行验证：
  SQL> select header_file,header_block from dba_segments
  2  where wner='SYS' and segment_name='TEST';

    HEADER_FILE HEADER_BLOCK
    ----------- ------------
          1        62161

    在v$bh视图中，我们可以看到这21个数据块都是xcur状态，表示这些数据块都是排斥状态，正在被使用，该字段还有其他的类型，请参见数据块的状态类型。

6，清空数据缓存：
    SQL> alter system flush buffer_cache;

7，重新检查v$bh和x$bh的内容：
  SQL> select file#,dbablk,tch from x$bh where bj=
  2  (select data_object_id from dba_objects
  3  where wner='SYS'  and object_name='TEST')
  4  order by dbablk;

     FILE#     DBABLK        TCH
---------- ---------- ----------
         1      62161          0
         1      62162          0
         1      62163          0
         1      62164          0
         1      62165          0
         1      62166          0
         1      62167          0
         1      62168          0
         1      62169          0
         1      62170          0
         1      62171          0
         1      62172          0
         1      62173          0
         1      62174          0
         1      62175          0
         1      62176          0
         1      62177          0
         1      62178          0
         1      62179          0
         1      62180          0
         1      62181          0

21 rows selected.

    SQL> select file#,block#,status from v$bh where bjd=
  2  (select data_object_id from dba_objects
  3  where wner='SYS'  and object_name='TEST')
  4  order by block#;

     FILE#     BLOCK# STATUS
---------- ---------- -------
         1      62161 free
         1      62162 free
         1      62163 free
         1      62164 free
         1      62165 free
         1      62166 free
         1      62167 free
         1      62168 free
         1      62169 free
         1      62170 free
         1      62171 free
         1      62172 free
         1      62173 free
         1      62174 free
         1      62175 free
         1      62176 free
         1      62177 free
         1      62178 free
         1      62179 free
         1      62180 free
         1      62181 free

21 rows selected.

    这时候我们可以看到，x$bh中的tch字段，已经由原来的3变成了0，同时v$bh视图的数据块状态也变成了free，但是记录的数据块并没有发生变化，还是在62161~62181这些数据块中，这就是说，虽然数据已经被写到了磁盘中，但是数据库记录的指针并没有清空，仅仅是其状态发生了改变。

8，进阶
     明白是oracle数据库管理数据块的部分工作模式后，我们可以利用v$bh文件统计对象在数据缓冲中被cache的块数了，如：
    SQL> select count(*) from v$bh where bjd=
  2  (select data_object_id from dba_objects
  3  where wner='SYS'  and object_name='TEST')
  4  and status != 'free';

  COUNT(*)
----------
        17
    表示表test中有17个数据块还存在于缓存当中。

五、示例3
------------------------------------------------
下面是一个示例查询，它显示了数据库里每个对象对数据缓冲区的使用情况。要注意，这个脚本使用了一个Oracle9i标量子查询，而它在Oracle9i之前的系统里是无法使用的，除非你注释掉c3这个列。

column c0 heading 'Owner'                     format a15
column c1 heading 'Object|Name'               format a30
column c2 heading 'Number|of|Buffers'         format 999,999 
column c3 heading 'Percentage|ofData|Buffer' format 999,999,999

select
   owner                        c0,
   object_name                  c1,
   count(1)                     c2,
   (count(1)/(select count(*) from v$bh)) *100  c3
from
   dba_objects o,
   v$bh        bh
where
   o.object_id  = bh.objd
and
   o.owner not in ('SYS','SYSTEM','AURORA$JIS$UTILITY$')
group by
   owner,
   object_name
order by
   count(1) desc
;

下面是一个来自Oracle Financials数据库的一个真实列表。我们很容易就可以看到数据缓冲区里最常用的表格，以及它们所消耗的内存量。在规划Oracle9i里的多数据缓冲池的时候，这就是无价的信息。

                                                                                                                      Number
                                        Object                                                                  of
Owner                             Name                                                                   Buffers
---------------                    ------------------------------                                       --------
INV                                 MTL_SYSTEM_ITEMS                                      7,098
WIP                                WIP_TRANSACTION_ACCOUNTS_N1        6,583
PERFSTAT                    STATS$ERROR_LOG                                         5,635
OE                                   SO_LINES_ALL                                                   5,091
OE                                   SO_LINES_ALL                                                   5,091
INV                                 MTL_DEMAND                                                  4,924
INV                                 MTL_ITEM_CATEGORIES                               3,098
MRP                               MRP_FORECAST_DATES                                2,248

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/95233/viewspace-584623/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/95233/viewspace-584623/