Page Comparison

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

...

Source: http://www.oreilly.com/catalog/oracle2/chapter/ch10.html

This listing provides you with a list of the number of objects on a per-user basis. It can be run regularly to make sure that your database is operating as you expect it to.

Code Block

SELECT  username,
        COUNT(DECODE(o.type#, 2, o.obj#, '')) Tab,
        COUNT(DECODE(o.type#, 1, o.obj#, '')) Ind,
        COUNT(DECODE(o.type#, 5, o.obj#, '')) Syn,
        COUNT(DECODE(o.type#, 4, o.obj#, '')) Vew,
        COUNT(DECODE(o.type#, 6, o.obj#, '')) Seq,
        COUNT(DECODE(o.type#, 7, o.obj#, '')) Prc,
        COUNT(DECODE(o.type#, 8, o.obj#, '')) Fun,
        COUNT(DECODE(o.type#, 9, o.obj#, '')) Pck,
        COUNT(DECODE(o.type#,12, o.obj#, '')) Trg,
        COUNT(DECODE(o.type#,10, o.obj#, '')) Dep
  FROM  sys.obj$ o,  sys.dba_users U
 WHERE  u.user_id = o.owner# (+)
 GROUP  BY username;