ratio.sql

--
-- Title :       ratio.sql 
-- Description : To show database memory ratios.
-- 
-- Usage/Notes : Coded to handle up to three RAC nodes.
-- 
-- Copyright :   ABCdba.com 2014 
-- 
 
SET PAGESIZE 40
 
COL instance HEA 'Instance'      FOR 999
COL ratio    HEA 'Ratio Metric' FOR a30
COL value    HEA 'Value'         FOR 999,990.99
 
SELECT
   cur.inst_id instance,
   'Buffer Cache Hit Ratio ' ratio,
   ROUND( (1- (phy.value / (cur.value + con.value) ) ) *100, 2)  value
FROM
   gv$sysstat cur,
   gv$sysstat con,
   gv$sysstat phy
WHERE
   cur.name = 'db block gets'
AND
   con.name = 'consistent gets'
AND
   phy.name = 'physical reads'
AND
   phy.inst_id = 1
AND
   cur.inst_id = 1
AND
   con.inst_id = 1
UNION ALL
SELECT
   cur.inst_id,
   'Buffer Cache Hit Ratio ',
   ROUND( (1- (phy.value / (cur.value + con.value) ) ) *100, 2)
FROM
   gv$sysstat cur,
   gv$sysstat con,
   gv$sysstat phy
WHERE
   cur.name = 'db block gets'
AND
   con.name = 'consistent gets'
AND
   phy.name = 'physical reads'
AND
   phy.inst_id = 2
AND
   cur.inst_id = 2
AND
   con.inst_id = 2
UNION ALL
SELECT
   cur.inst_id,
   'Buffer Cache Hit Ratio ',
   ROUND( (1- (phy.value / (cur.value + con.value) ) ) *100, 2)
FROM
   gv$sysstat cur,
   gv$sysstat con,
   gv$sysstat phy
WHERE
   cur.name = 'db block gets'
AND
   con.name = 'consistent gets'
AND
   phy.name = 'physical reads'
AND
   phy.inst_id = 3
AND
   cur.inst_id = 3
AND
   con.inst_id = 3
UNION
SELECT
   inst_id,
   'Library Cache Hit Ratio ',
   ROUND( SUM(pins) / (SUM(pins)+SUM(reloads)) * 100, 2)
FROM
   gv$librarycache
GROUP BY
   inst_id
UNION
SELECT
   inst_id,
   'Dictionary Cache Hit Ratio ',
   ROUND( (1 - (SUM (getmisses) / SUM (gets)) ) * 100, 2)
FROM
   gv$rowcache
GROUP BY
   inst_id
UNION
SELECT
   inst_id,
   'Get Hit Ratio ',
   ROUND( ( SUM(gethitratio))*100, 2)
FROM
   gv$librarycache
WHERE
   namespace IN ('SQL AREA')
GROUP BY
   inst_id
UNION
SELECT
   inst_id,
   'Pin Hit Ratio ',
   ROUND( ( SUM(pinhitratio))*100, 2)
FROM
   gv$librarycache
WHERE
   namespace IN ('SQL AREA')
GROUP BY
   inst_id
UNION
SELECT
   a.inst_id,
   'Soft-Parse Ratio ',
   ROUND( 100 * ((a.value - b.value) / a.value ), 2)
FROM
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'parse count (total)'
   ) a,
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'parse count (hard)'
   ) b
WHERE
   a.inst_id = b.inst_id
UNION
SELECT
   a.inst_id,
   'Execute Parse Ratio ',
   ROUND( 100 - ((a.value / b.value)* 100), 2)
FROM
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'parse count (total)'
   ) a,
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'execute count'
   ) b
WHERE
   a.inst_id = b.inst_id
UNION
SELECT
   a.inst_id,
   'Parse CPU to Elapsed Ratio ',
   ROUND( (a.value / b.value)* 100, 2)
FROM
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'parse time cpu'
   ) a,
   (
   SELECT
      inst_id,
      value
   FROM
      gv$sysstat
   WHERE
      name = 'parse time elapsed'
   ) b
WHERE
   a.inst_id = b.inst_id
UNION
SELECT
   a.inst_id,
   'Chained Row Ratio ',
   ROUND( (a.val/b.val)*100, 2)
FROM
   (
   SELECT
      inst_id,
      SUM(value) val
   FROM
      gv$sysstat
   WHERE
      name = 'table fetch continued row'
   GROUP BY
      inst_id
   ) a,
   (
   SELECT
      inst_id,
      SUM(value) val
   FROM
      gV$SYSSTAT
   WHERE
      name IN ('table scan rows gotten', 'table fetch by rowid')
   GROUP BY inst_id
   ) b
WHERE
   a.inst_id = b.inst_id
UNION
SELECT
   inst_id,
   'Latch Hit Ratio ',
   ROUND( ( (SUM(gets) - SUM(misses))/SUM(gets) )*100, 2)
FROM
   gv$latch
GROUP BY
   inst_id
UNION
SELECT
   a.inst_id,
   metric_name,
   value
FROM
   gv$sysmetric a,
   (
   SELECT
      inst_id,
      MAX(intsize_csec) max_csec
   FROM
      gv$sysmetric
   GROUP BY
      inst_id
   ) b
WHERE
   metric_name IN ( 'Database Wait Time Ratio', 'Database CPU Time Ratio')
AND
   a.intsize_csec = b.max_csec
AND
   a.inst_id = b.inst_id
/

© copyright 2001-2014 ABCdba.com | all rights reserved