Немного о таблица x$bh, v$bh, x$kcbwbpd, x$kcbwds

dbstalker, 26 мая

Есть такие «магические» (по выражению Тома Кайта) таблицы. Все они содержат определенную информацию о буферном кеше.

X$BH - содержит информацию о заголовках буферов (всех размеров) в кэш-буфере.

X$KCBWBPD - информация о пулах ( V$BUFFER_POOL)

X$KCBWDS - информация о WS (Working Sets)

Можно построить цепочку: блок объекта (obj$) находится в буфере (X$BH) – буфер находится в Working Sets (X$KCBWDS) - Working Sets находится в пуле (X$KCBWBPD).

Это можно продемонстрировать с помощью запроса:

select/*+ ordered */bp.name pool_name, ob.name object,ws.addr,sum(ct) blocks from 
(select set_ds,obj, count(*) ct from x$bh  group by set_ds, obj)  bh,
 obj$                   ob,
 x$kcbwds               ws,
 v$buffer_pool          bp  --X$KCBWBPD   
where ob.dataobj# = bh.obj and ob.owner# > 0 and bh.set_ds = ws.addr and ws.set_id between bp.lo_setid and bp.hi_setid and bp.buffers != 0  
group by bp.name,ob.name, ws.addr

x$kcbwbpd (описывает пулы)

Buffer pool descriptor, the base table for v$buffer_pool.

How is the buffer cache split between the default, the recycle and the keep buffer pool.

x$kcbwds (описывает working sets)

Set descriptor, see also x$kcbwbpd

The column id can be joined with v$buffer_pool.id.

The column bbwait corresponds to the buffer busy waits wait event.

Information on working set buffers

addr can be joined with x$bh.set_ds.

set_id will be between lo_setid and hi_setid in v$buffer_pool for the relevant buffer pool.

Приведу несколько полезных запросов, которые вам пригодятся в работе.

Состояние буферов:

select decode(state, 0, 'Free', 1, 'Modified', 2, 'Not Modified',
             3, 'Being    Read', 'Other') State, count(*) cnt
from sys.x$bh group by state;

или

select decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
              'BEING USED'),3,'BEING USED',state) "BLOCK STATUS",count(*)
  from x$bh
  group by decode(state,0,'FREE',1,decode(lrba_seq,0,'AVAILABLE',
              'BEING USED'),3,'BEING USED',state);

Если число блоков в состоянии Free велико, можно сократить буфер. Если после старта базы оно напротив, невелико, то возможно буферный кэш стоит и увеличить.

Количество блоков в разрезе объектов:

select a.owner,a.object_type,a.object_name,count(*) buffers,round((count(*)/(select count(*) from v$bh))*100) buffer_percent
from dba_objects a,v$bh b
where a.object_id=b.objd and a.owner not in ('SYS','SYSTEM')
group by a.owner,a.object_type,a.object_name
order by 5 desc

Информация востребованности буфера можно отследить по значению полей tch (счетчик touch count) и tim (текущее время последнего обращения touch time). Для буферов, перенесенных в горячий конец очереди LRU, флаг LRU_FLAG принимает значение 8.

Пример почти от Тома Кайта. Находим блок за которым будем следить:

 select file_id, block_id   from dba_extents   where segment_name = 'DUAL' and owner = 'SYS';

   FILE_ID   BLOCK_ID
---------- ----------
         1        465

Определим значение для нашего блока:

select tch from x$bh where file# = 1 and dbablk = 465;

       TCH
----------
        10

Теперь будем обращаться к этому блоку, и измерять touch count:

select * from dual;
D
-
X
select tch from x$bh where file# = 1 and dbablk = 465;
       TCH
----------
        11

select * from dual;
D
-
X

select tch from x$bh where file# = 1 and dbablk = 465;
       TCH
----------
        12

Перечень самых популярных блоков:

Select b.Object_name object, a.tch touch_count from x$bh a, dba_objects b
 Where a.obj=b.object_id and a.tch > 100
 Order by a.tch desc;

Мониторинг использования кєшей разного размера и разного предназначения.

Если используется только один размер блоков, можно определить имена объектов в кеше буферов и количество буферов, используя соединение столбца Obj таблицы X$BH со столбцом Object_ID представления DBA_OBJECTS.

select Object_Name, Object_Type, COUNT(*) Num_Buff 
from X$BH A, SYS.DBA_OBJECTS B 
where A.Obj = B.Object_ID group by Object_Name, Object_Type; 

Для блоков с размером отличным от размера по умолчанию предлагается выполнять такой запрос (Blsiz – размер блока):

select Segment_Name, Segment_Type, 
  COUNT(*) Num_Buff 
from X$BH a, SYS.DBA_EXTENTS B 
where a.Dbarfil = b.File_id 
and a.Dbablk >= b.Block_id 
and a.Dbablk <= (b.Block_id + b.Blocks) 
and Owner not in ('SYS','SYSTEM') 
and a.Blsiz=8,192 
group by Segment_Name, Segment_Type; 
 

или

select Blsiz, Owner, Segment_Name, 
  Segment_Type, COUNT(*) Num_Buff 
from X$BH a, SYS.DBA_EXTENTS B 
where a.Dbarfil = b.File_id 
and a.Dbablk >= b.Block_id 
and a.Dbablk <= (b.Block_id + b.Blocks) 
and Owner not in (‘SYS’, ‘SYSTEM’) 
group by Blsiz, Owner, Segment_Name, Segment_Type; 

Так как, в базе данных можно создавать кеши для различных размеров блоков, перемещать в оперативном режиме объекты в кеши для больших размеров блоков, нужно следить за размерами кешей, чтобы они могли обеспечить поддержку необходимых объемов данных.

Интересен также результат такого запроса:

Select decode(pd.bp_id,1,'KEEP',2,'RECYCLE',3,'DEFAULT',4,'2K SUBCACHE', 
5,'4K SUBCACHE',6,'8K SUBCACHE',7,'16K SUBCACHE',8,'32K SUBCACHE', 
'UNKNOWN') subcache, 
bh.object_name,bh.blocks 
from 
x$kcbwds ds, 
x$kcbwbpd pd, 
(select /*+ use_hash(x) */ set_ds,o.name object_name, count(*) BLOCKS from obj$ o, x$bh x where o.dataobj# = x.obj and x.state !=0 and o.owner# !=0 
group by set_ds,o.name) bh 
where ds.set_id >= pd.bp_lo_sid 
and ds.set_id <= pd.bp_hi_sid 
and pd.bp_size != 0 
and ds.addr=bh.set_ds; 

Перечень таблиц, чьи блоки читались в ходе FTS (полного сканирования таблицы, то есть индексы не использовались).Для этого запроса использовалось поле flag:

flag is a bit array.

Bit	if set	 	
0	Block is dirty	 	
4	temporary block	 	
9 or 10	ping	 	
14	stale	 	
16	direct	 	
524288 (=0x80000)	Block was read in a full table scan
select DISTINCT   substr(obj.object_name, 1,30)  object,
  obj.owner                      owner
from 
  dba_objects obj,
  x$bh        xbh
where 
  xbh.obj         = obj.data_object_id and 
  obj.object_type ='TABLE'             and 
  bitand(xbh.flag,524288)>0            and 
  obj.owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP');

Один и тот же блок считывается с диска несколько раз?

Если выполнить следующий запрос:

SELECT  bh.OBJD,file#,block#,count(*)
FROM DBA_OBJECTS o, V$BH bh 
WHERE o.object_id = bh.objd --and bh.status<>'cr'
group by bh.OBJD, bh.file#, bh.BLOCK#
having count(*) >1

то, действительно, кажется, что один и тот же блок находится в нескольких буферах. Но это не так. Один и тот же блок может быть в нескольких состояниях для выполнения согласованного чтения. Добавьте условие and bh.status<>'cr'и будет ясно.

Из документации

V$BH

Block classes
KCBCSVSH (Save Undo Segment Headers (block class 5))
KCBCSVUN (Save Undo Blocks (block class 3))
KCBCSORT (Sort Blocks (block class 2))
KCBCDATA (Data Blocks (block class 1))
KCBCSEGH (Segment Headers (block class 4))
KCBCFRLS (Free List Blocks (block class 6))
KCBCUSH (Undo Segment Header (block class 7 + (n*2)))
KCBCUSB (Undo Segment Block (block class 7 + (n*2) + 1))
Note: For undo (rollback) segments, 'n' is the undo segment number.
Block Class ‘0’ is reserved for error detection in KCB.	

x$bh.state	
decode(state, 0, 'FREE', /* not currently is use */ 
1, 'XCUR', /* held exclusive by this instance */ 
2, 'SCUR', /* held shared by this instance */ 
3, 'CR', /* only valid for consistent read */ 
4, 'READ', /* is being read from disk */ 
5, 'MREC', /* in media recovery mode */ 
6, 'IREC') /* in instance(crash) recovery mode */

Литература

http://www.ixora.com.au/q+a/0011/08135901.htm

http://www.sibinfo.ru/archive/news/02_11_12/admin_blocks.html

1 комментарий

Прокоментировать

kireal
20 июля 2009 г. в 01:27

Отличная заметка об очень полезных представлениях.

 

Новый комментарий

Я не спамер: введите суму 4+4



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Микрофон
19 августа, 2 ответа
Сумочка
19 августа, 2 ответа
средства для рук
17 августа, 3 ответа