oracle buffercache,Oracle DBA开发之:使用sql来查看buffer cache的使用情况

1、查看一个对象在内存中的使用情况

select object_name,DBARFIL /*文件编号*/,DBABLK/*块的位置*/ from x$bh a,dba_objects b

where a.obj=b.object_id and object_name='T2'

2、查看你个对象在内存中的状态(通过上面查出来的文件编号和快的位置)

select class, flag, state, lru_flag from x$bh

where dbarfil = 1 and dbablk = 88192;

当然也可以在第一个sql语句中直接查询

1、select distinct object_name , DBARFIL ,DBABLK   FROM X$bh a , DBA_OBJECTS b where a.obj = b.object_id and object_name = 'TEST2' ;

2、查看各个对象在buffer_cache所占的大小和状态

Select o.object_name ,decode(state , 0, 'free' , 1 ,'xcur' , 2 , 'scur' , 3 , 'cr' , 4 , 'read' , 5 , 'mree' , 6 , 'iree' , 7 , 'write' , 8 , 'pi') state , count(*) blocks  from x$bh b, dba_objects o where b.obj = o.data_object_id and state <> 0 group by o.object_name , state order by blocks asc ;

3、寻找热快

Select obj , dbarfil file#, dbablk block# , tch touches from x$bh where tch > 10 order by tch asc ;

根据查询出来的文件编号和快的位置可以查询出一序列的信息，比如该对象名

select b.object_name , b.object_id from x$bh a,dba_objects b where a.obj=b.object_id and a.DBARFIL = 1 and a.DBABLK = 11666;

4、查看数据库的block的总和

Select sum(blocks) from dba_data_files ;

5、查看buffer_cache中各个状态的大小，可以查看空闲空间的大小，最好控制在10%

Select decode(state , 0 , 'free' ,1, decode(lrba_seq , 0 , 'available' , 'being use') , 3 , 'being use' , state)  "block states" , count(*) from x$bh group by decode(state , 0 , 'free' , 1,decode(lrba_seq , 0 , 'available' , 'being use') , 3 , 'being use' , state);

6、找出消耗物理IO资源最多的sql语句

Select disk_reads , substr(sql_text , 1 , 4000) from v$sqlarea order by disk_reads desc ;

通过这样我们就可以找出之该sql的用户

Select disk_reads , substr(sql_text , 1 , 4000) , PARSING_SCHEMA_NAME from v$sqlarea order by disk_reads desc ;

当然除此之外还有很多相关的信息，可以去查看官方文档v$sqlarea的所以字段信息

7、清空buffer_cache

Alter system flush buffer_cache