--
-- THIS VIEW MAKES CALCULATING THE ACCUMULATIVE BUFFER CACHE HIT RATIO A SNAP. THERE 
-- ARE THREE COLUMNS: "RATIO,PURPOSE,QUERY" YOU  CAN TYPE IN ANY PURPOSE YOU DESIRE.
-- YOU NEED TO WRAP THE MAIN PORTION OF THE QUERY IN ANOTHER QUERY AND INCLUDE WHERE
-- ROWNUM = 1, OTHERWISE IT WILL RETURN MORE THAN ONE ROW WHEN NOT INCLUDING THE 
-- RATIO COLUMN.
-- 

CREATE OR REPLACE FORCE VIEW SYSTEM.BUFFER_CACHE_HIT_RATIO
/* ©2004 by Edward Stoever,  */
(RATIO, PURPOSE, QUERY)
AS 
SELECT ratio, purpose, query
  FROM (SELECT    ROUND ((  (  1
                             - (  SUM (DECODE (NAME,
                                               'physical reads', VALUE,
                                               0
                                              )
                                      )
                                / (  SUM (DECODE (NAME,
                                                  'db block gets', VALUE,
                                                  0
                                                 )
                                         )
                                   + (SUM (DECODE (NAME,
                                                   'consistent gets', VALUE,
                                                   0
                                                  )
                                          )
                                     )
                                  )
                               )
                            )
                          * 100
                         ),
                         2
                        )
               || '%' AS ratio,
               'The Buffer Cache Hit Ratio, which you generally want to be above 90%.'
                                                                   AS purpose,
                  'select round(((1-(sum(decode(name,'
               || CHR (10)
               || '''physical reads'', value,0))/'
               || CHR (10)
               || '(sum(decode(name, ''db block gets'', value,0))+'
               || CHR (10)
               || '(sum(decode(name, ''consistent gets'', value, 0))))))*100),2)'
               || CHR (10)
               || '|| ''%'' "Buffer Cache Hit Ratio"'
               || CHR (10)
               || 'from v$sysstat;' AS QUERY
          FROM v$sysstat)
 WHERE ROWNUM = 1
          WITH READ ONLY;


CREATE PUBLIC SYNONYM BUFFER_CACHE_HIT_RATIO FOR SYSTEM.BUFFER_CACHE_HIT_RATIO;