Long Running Queries
- This view displays the status of various operations that run for longer than 6 seconds
Code Block select * from V$SESSION_LONGOPS;
Computing the Hit Ratio
- Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html
Code Block SELECT SUM(DECODE(name, 'consistent gets',value, 0)) "Consis Gets", SUM(DECODE(name, 'db block gets',value, 0)) "DB Blk Gets", SUM(DECODE(name, 'physical reads',value, 0)) "Phys Reads", (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) - SUM(DECODE(name, 'physical reads',value, 0))) / (SUM(DECODE(name, 'consistent gets',value, 0)) + SUM(DECODE(name, 'db block gets',value, 0)) ) * 100 "Hit Ratio" FROM v$sysstat;
- RAC External Results:
No Format Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 547094930 10815948 316183 99.94332732834795165976312080439485533745
- CAJE-Prod External Results:
No Format Consis Gets DB Blk Gets Phys Reads Hit Ratio -------------- -------------- ------------- ----------------------------------------- 1222124214 26405978 540427901 56.71487125719423531569671484564307596656
...