User sessions hit ratios
                                                 Last update (2008-05-16 10:13:55)
                                                                                                                   Date added (2008-05-15 13:26:12)

Summary
Show hit ratios, consistent-gets, db-block-gets, physical-reads for the sessions.
SELECT  se.username || '(' || se.SID || ')' "User(sid)",
       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$sesstat ss, v$statname sn, v$session se
WHERE   ss.SID    = se.SID
  AND   sn.statistic# = ss.statistic#
  AND   VALUE != 0
  AND   sn.NAME IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY se.username, se.SID
HAVING  (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< 100;
Reviews
Categories
Filters
Search