数据库高速缓存之SQL

wlzxwfk

　　--调整缓冲区命中率计算
　　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;