26май
Немного о таблица x$bh, v$bh, x$kcbwbpd, x$kcbwds
Есть такие «магические» (по выражению Тома Кайта) таблицы. Все они содержат определенную информацию о буферном кеше.
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