Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle > Performance Tuning     

Get buffer cache hit ratio / miss ratio


SQL> SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets )) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
/

NAME                  HIT_RATIO
-------------------- ----------
DEFAULT              .817011081


-- OR

SQL> select a.value + b.value "logical_reads", c.value "phys_reads",
round (100 * ((a.value + b.value)-c.value) / (a.value + b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;


-- OR
-- Check hit ratio for a specific session

SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM   v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
  v$sesstat P3, v$statname N3
WHERE  N1.name = 'db block gets'
AND    P1.statistic# = N1.statistic#
AND    P1.sid = <SID>
AND    N2.name = 'consistent gets'
AND    P2.statistic# = N2.statistic#
AND    P2.sid = P1.sid
AND    N3.name = 'physical reads'
AND    P3.statistic# = N3.statistic#
AND    P3.sid = P1.sid;


  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

       Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #195
views : 1982
Added on : 06/24/08
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (202)
 
 
<< Previous Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Operating Systems > Unix : How to kill Unix user session  
  Databases > Oracle : Kill user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com