1.对象有多少个数据块缓冲在Data buffer中
首先创建测试表,并插入记录
SQL> create table test(a int);
Table created.
SQL> begin
2 for i in 1..5000
3 loop
4 insert into test values(i);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
分析数据所在的ROWID
SQL> select f,b from (
select dbms_rowid.rowid_relative_fno(rowid) f,
dbms_rowid.rowid_block_number(rowid) b
from test) group by f,b;
F B
---------- ----------
4 532
4 534
4 536
4 539
4 541
4 533
4 540
4 535
8 rows selected.
说明:数据块占用了8个块。
查询X$BH与V$BH当前保存的记录
SQL> select file#,dbablk,tch
from x$bh
where bj=(select data_object_id
from dba_objects
where wner='ORACLE'
and object_name='TEST')
order by dbablk;
FILE# DBABLK TCH
---------- ---------- ----------
4 529 2
4 530 2
4 531 3
4 532 2
4 533 2
4 534 2
4 535 2
4 536 2
4 537 2
4 538 2
4 539 2
4 540 2
4 541 2
4 542 2
4 543 2
4 544 2
16 rows selected.
SQL> select file#,block#,status
from v$bh
where bjd=(select data_object_id
from dba_objects
where wner='ORACLE'
and object_name='TEST')
order by block#;
FILE# BLOCK# STATUS
---------- ---------- ----------
4 529 xcur
4 530 xcur
4 531 xcur
4 532 xcur
4 533 xcur
4 534 xcur
4 535 xcur
4 536 xcur
4 537 xcur
4 538 xcur
4 539 xcur
4 540 xcur
4 541 xcur
4 542 xcur
4 543 xcur
4 544 xcur
16 rows selected.
说明:这两个查询都返回了16条记录,其中8个块是数据块,而剩下的8个块是段头,位图块等,是表中的额外开销。X$BH中的tch(touch count)列表示了一个块的热点程度,现在最热的是数据块531,它并不是数据块,而是段头,通过如下查询可以证明:
SQL> select header_file,header_block
from dba_segments
where wner='ORACLE'
and segment_name='TEST';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 531
说明:查询V$BH,返回了块的状态信息,这里是xcur,表示排斥状态,被当前instance独占,也就是该块正在被使用。常见的状态还有scur,表示被instance共享;cr,表示一致性读;free,表示空闲状态;read,表示正在从磁盘上读取;wirte,表示正在被写入。
手工清除一次DATA BUFFER
SQL> alter system flush buffer_cache;
System altered.
用上面同样的脚本查询X$BH和V$BH,得到的结果如下:
X$BH:
FILE# DBABLK TCH
---------- ---------- ----------
4 529 0
4 530 0
4 531 0
4 532 0
4 533 0
4 534 0
4 535 0
4 536 0
4 537 0
4 538 0
4 539 0
4 540 0
4 541 0
4 542 0
4 543 0
4 544 0
16 rows selected.
V$BH:
FILE# BLOCK# STATUS
---------- ---------- ----------
4 529 free
4 530 free
4 531 free
4 532 free
4 533 free
4 534 free
4 535 free
4 536 free
4 537 free
4 538 free
4 539 free
4 540 free
4 541 free
4 542 free
4 543 free
4 544 free
16 rows selected.
说明:可以看到,X$BH中的块的tch都恢复到0了,而V$BH块的状态也都变成free了,但是记录数并没有发生变化,也就是说,数据块虽然被刷到磁盘上去了,但数据块的记录指针只不过是简单地被清空而已。
如果统计一个对象的非free状态的V$BH的记录数,基本就反映了一个对象在DATA BUFFER中被CACHE的块数:
select count(*)
from v$bh
where bjd=(select data_object_id from dba_objects
where wner='ORACLE'
and object_name='TEST')
and status <> 'free';
热点块问题
如果一个块被多次访问,每次访问都会导致该块在X$BH中tch(touch count)加一。
例:SQL> select data_object_id
from dba_objects
where wner='ORACLE'
and object_name='TEST';
DATA_OBJECT_ID
--------------
71301
SQL> select tch
from x$bh
where bj=71301
and dbablk=532
and file#=4
and tch>0;
TCH
----------
3
SQL> select count(1)
2 from test
3 where rowid=dbms_rowid.rowid_create(1,71301,4,532,0);
COUNT(1)
----------
1
再次查询TCH
TCH
----------
4
一致性块问题
例:在第一个会话中:
SQL> create table t(x int);
Table created.
SQL> declare
i number;
begin
for i in 1..10 loop
execute immediate 'insert into t values(:x)' using i;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select file#,block#,status
from v$bh
where bjd=(select data_object_id
from dba_objects
where wner='ORACLE'
and object_name='TEST')
order by block#;
FILE# BLOCK# STATUS
---------- ---------- ----------
561 xcur
562 xcur
563 xcur
564 xcur
565 xcur
566 xcur
567 xcur
568 xcur
8 rows selected.
在第二个会话中:
SQL> select * from t;
no rows selected
SQL> select file#,block#,status
from v$bh
where bjd=(select data_object_id
from dba_objects
where wner='ORACLE'
and object_name='TEST')
order by block#;
FILE# BLOCK# STATUS
---------- ---------- ----------
561 xcur
562 xcur
563 xcur
564 xcur
565 xcur
566 xcur
567 cr
567 cr
567 xcur
568 xcur
10 rows selected.
说明:果然和上一节查询出来的结果不同,多了红色字体标识出来的两行,大家可以看到这两行的STATUS字段值为cr,它是Consistency Read(一致性读取)的缩写。从这里可以看出567这个块被两个会话进行了操作。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17014649/viewspace-610486/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17014649/viewspace-610486/