数据库高速缓存之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> --查看高速缓冲区和分区的命中率
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;
页:
[1]