Oracle 11g 验证数据库块缓存和共享池作用及缓存命中率
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1验证数据库块缓存和共享池作用
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
2缓存命中率
SQL> col name for a30
SQL> select statistic#,name,value from v$sysstat
2 where name in ('physical reads','db block gets','consistent gets');
STA TISTIC# NAME V ALUE
---------- ------------------------------ ----------
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.8379397
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.8409046
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.8438748
SQL> select sum(pins-reloads)/sum(pins) from v$librarycache;
SUM(PINS-RELOADS)/SUM(PINS)
---------------------------
.996710092
SQL> select sum(gets-getmisses-usage-fixed)/sum(gets) from v$rowcache;
SUM(GETS-GETMISSES-USAGE-FIXED)/SUM(GETS)
-----------------------------------------