X$BH.STATE
0: FREE--not currently in use
1: XCUR--exclusive
2: SCUR--shared current
3: CR--CR block
4: READ-- being read from disk
5: MREC--in media recovery mode
6: IREC-- in instance recovery mode
7: WRITE-- writing to disk
8: PI-- Past image block involved in cache fusion block transfer
1、检查数据库buffer中哪个对象占用最多空间
select
o.object_name,
blsiz,
count(*) blocks
from x$bh b, dba_objects o
where b.obj = o.data_object_id
and b.ts#>0
group by o.object_name, blsiz
order by blocks desc;
占用数据库buffer数量比较大的对象，有可能是需要调优的对象。
2、检查某个对象的在数据库buffer的状态情况
select
o.object_name,
state,
blsiz,
count(*) blocks
from x$bh b, dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
and o.object_name ='obj_name'
group by o.object_name, state, blsiz
order by blocks desc
0: FREE--not currently in use
1: XCUR--exclusive
2: SCUR--shared current
3: CR--CR block
4: READ-- being read from disk
5: MREC--in media recovery mode
6: IREC-- in instance recovery mode
7: WRITE-- writing to disk
8: PI-- Past image block involved in cache fusion block transfer
1、检查数据库buffer中哪个对象占用最多空间
select
o.object_name,
blsiz,
count(*) blocks
from x$bh b, dba_objects o
where b.obj = o.data_object_id
and b.ts#>0
group by o.object_name, blsiz
order by blocks desc;
占用数据库buffer数量比较大的对象，有可能是需要调优的对象。
2、检查某个对象的在数据库buffer的状态情况
select
o.object_name,
state,
blsiz,
count(*) blocks
from x$bh b, dba_objects o
where b.obj = o.data_object_id
and b.ts# > 0
and o.object_name ='obj_name'
group by o.object_name, state, blsiz
order by blocks desc
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/354732/viewspace-625952/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查看过去的session锁情况脚本
下一篇：
学习ASM技术（一）--环境搭建
请登录后发表评论
登录
全部评论
