Scripts--Show Sessions with bad Buffer Cache Hit Ratio in %
在Oracle DB中我們可以通過下面的sql來查看各session的buffer cache hit ratio,當然hit ratio越低我們就要根據實際session在執行的動作做分析。
select substr(a.username, 1, 12) "User",
a.sid "sid",
a.SERIAL#,
a.PROGRAM,
b.consistent_gets "ConsGets",
b.block_gets "BlockGets",
b.physical_reads "PhysReads",
100 * round((b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),
3) HitRatio
from v$session a, v$sess_io b
where a.sid = b.sid
and (b.consistent_gets + b.block_gets) > 0
and a.username is not null
order by HitRatio asc;
select substr(a.username, 1, 12) "User",
a.sid "sid",
a.SERIAL#,
a.PROGRAM,
b.consistent_gets "ConsGets",
b.block_gets "BlockGets",
b.physical_reads "PhysReads",
100 * round((b.consistent_gets + b.block_gets - b.physical_reads) /
(b.consistent_gets + b.block_gets),
3) HitRatio
from v$session a, v$sess_io b
where a.sid = b.sid
and (b.consistent_gets + b.block_gets) > 0
and a.username is not null
order by HitRatio asc;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29987/viewspace-51869/，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
