42 lines
1.9 KiB
MySQL
42 lines
1.9 KiB
MySQL
|
-- +----------------------------------------------------------------------------+
|
||
|
|
-- | Jeffrey M. Hunter |
|
||
|
|
-- | jhunter@idevelopment.info |
|
||
|
|
-- | www.idevelopment.info |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved. |
|
||
|
|
-- |----------------------------------------------------------------------------|
|
||
|
|
-- | DATABASE : Oracle |
|
||
|
|
-- | FILE : perf_hit_ratio_system.sql |
|
||
|
|
-- | CLASS : Tuning |
|
||
|
|
-- | PURPOSE : Reports buffer cache hit ratio. |
|
||
|
|
-- | NOTE : As with any code, ensure to test this script in a development |
|
||
|
|
-- | environment before attempting to run it in production. |
|
||
|
|
-- +----------------------------------------------------------------------------+
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
TO_CHAR(SUM(DECODE(name, 'consistent gets', value, 0)),
|
||
|
|
'999,999,999,999,999,999') con
|
||
|
|
, TO_CHAR(SUM(DECODE(name, 'db block gets' , value, 0)),
|
||
|
|
'999,999,999,999,999,999') dbblockgets
|
||
|
|
, TO_CHAR(SUM(DECODE(name, 'physical reads' , value, 0)),
|
||
|
|
'999,999,999,999,999,999') physrds
|
||
|
|
, ROUND( ( (
|
||
|
|
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,2
|
||
|
|
) Hitratio
|
||
|
|
FROM v$sysstat
|
||
|
|
/
|
||
|
|
|
||
|
|
|