数据库高速缓存之SQL

--调整缓冲区命中率计算

select 1-(phy.value/(cur.value+con.value)) "CACHE HIT RATIO"

from v$sysstat cur,v$sysstat con,v$sysstat phy

where cur.name='db block gets'

and con.name='consistent gets'

and phy.name='physical reads'

--清除数据库高速缓冲区

alter system flush buffercache;

--buffer中取latch的比率

select name,round((100-(sleeps/gets)*100),3)||'%' "LRU HIT%"

from v$latch where name='cache buffers lru chain';

--根据此视图调整db_cache_size大小

select ADVICE_STATUS,SIZE_FOR_ESTIMATE,ESTD_PHYSICAL_READ_FACTOR,ESTD_PHYSICAL_READS

from v$db_cache_advice where name='DEFAULT'

--查询高速缓冲区每个分区的详细情况

select * from v$buffer_pool where id<>0

--查看高速缓冲区和分区的命中率

select name,1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO" 

from v$buffer_pool_statistics

where db_block_gets+consistent_gets>0

--查询大量消耗数据库高速缓冲区的对象

select owner,object_name,count(*) Buffers,(count(1)/(select count(*) from v$bh))*100 "%of Data Buffer"

from dba_objects o,v$bh bh

where o.object_id=bh.objd and o.owner not in ('SYS','SYSTEM')

group by owner,object_name

order by count(1) desc;