Oracle11g验证数据库块缓存和共享池作用及缓存命中率.docVIP

验证数据库块缓存和共享池作用 SQL conn /as sysdba 已连接。 SQL set timing on SQL select count * from user_tablespaces; COUNT * 7 已用时间: 00: 00: 00.11 SQL select count * from user_tablespaces; COUNT * 7 已用时间: 00: 00: 00.02 SQL select count * from user_tablespaces; COUNT * 7 已用时间: 00: 00: 00.00 SQL alter system flush shared_pool; 系统已更改。 已用时间: 00: 00: 00.03 SQL select count * from user_tablespaces; COUNT * 7 已用时间: 00: 00: 00.02 缓存命中率 SQL col name for a30 SQL select statistic#,name,value from v$sysstat 2 where name in physical reads,db block gets,consistent gets ; STATISTIC# NAME VALUE 63 db block gets 167326 67 consistent gets 1192052 72 physical reads 15800 SQL select a.value+b.value-c.value logical_reads,c.value phys_reads, 2 100* a.value+b.value -c.value / a.value+b.value buffer hit ratio 3 from v$sysstat a,v$sysstat b,v$sysstat c 4 where a.statistic# 63 and b.statistic# 67 and c.statistic# 72 5 / logical_reads phys_reads buffer hit ratio 1343854 15800 98.座机电话号码 SQL select * from user_tables; SQL select a.value+b.value-c.value logical_reads,c.value phys_reads, 2 100* a.value+b.value -c.value / a.value+b.value buffer hit ratio 3 from v$sysstat a,v$sysstat b,v$sysstat c 4 where a.statistic# 63 and b.statistic# 67 and c.statistic# 72 5 / logical_reads phys_reads buffer hit ratio 1347332 15800 98.座机电话号码 SQL select * from user_tables; SQL select a.value+b.value-c.value logical_reads,c.value phys_reads, 2 100* a.value+b.value -c.value / a.value+b.value buffer hit ratio 3 from v$sysstat a,v$sysstat b,v$sysstat c 4 where a.statistic# 63 and b.statistic# 67 and c.statistic# 72 5 / logical_reads phys_reads buffer hit ratio 1350834 15800 98.座机电话号码 SQL select sum pins-reloads /sum pins from v$librarycache; SUM PINS-RELOADS /SUM PINS .座机电话号码2 SQL select sum gets-getmisses-usage-fixed /sum gets from v$rowcache; SUM GETS-GETMISSES-USAGE-FIXED /SUM GETS .座机电话号码