|
ttitle off
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 2.0 DB Block Buffer - Hit Ratio (Database Wise) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
set heading on
set termout on
column "Physical Reads" format 9,999,999,999,999
column "Consistent Gets" format 9,999,999,999,999
column "DB Block Gets" format 9,999,999,999,999
column "Hit Ratio" format 999.99
TTitle left "*** Database: "xdbname", DB Block Buffers Hit Ratio (As of : "xdate" ) ***" skip 1-
left "Percent = ((100*(1-(Physical Reads/(Consistent Gets + DB Block Gets))))" skip 2
select pr.value "Physical Reads",
cg.value "Consistent Gets",
bg.value "DB Block Gets",
round((1-(pr.value/(bg.value+cg.value)))*100,2) "Hit Ratio"
from v$sysstat pr, v$sysstat bg, v$sysstat cg
where pr.name = 'physical reads'
and bg.name = 'db block gets'
and cg.name = 'consistent gets'
/
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 2.0 Investigation IF Percent is less than 70% , increase DB_BLOCK_BUFFERS
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
Ttitle Off
prompt
prompt
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
prompt + 2.1 DB Block Buffer - Hit Ratio (Session Wise) +
prompt ++++++++++++++++++++++++++++++++++ Item ++++++++++++++++++++++++++++++++++
clear breaks
clear computes
break on report
compute sum of Consistent_Gets on report
compute sum of Block_Gets on report
compute sum of Physical_Reads on report
column "Hit Ratio %" format 999.99
column Username format A10
TTitle left "*** Database: "xdbname", Hit Ratio For User Sessions(As of : "xdate" ) ***" skip 1
select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";
prompt
prompt <<<<<<<<<<<<<<<<<<< NOTE: >>>>>>>>>>>>>>>>>>>>>>
prompt 2.0/2.1 Investigation
prompt If you have 20 or more users and batch users cause less than 50%
prompt logical reads within your database , you should aim for a hit ratio
prompt of between 94% ~ 97%.
prompt If you have fewer than 20 users , the sharing of data among users depends 上一页 [1] [2] [3] [4] 下一页
没有相关教程
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
