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]  下一页