text: -- +----------------------------------------------------------------------------+
  -- |                          Jeffrey M. Hunter                                 |
  -- |                      jhunter@idevelopment.info                             |
  -- |                         www.idevelopment.info                              |
  -- |----------------------------------------------------------------------------|
  -- |      Copyright (c) 1998-2011 Jeffrey M. Hunter. All rights reserved.       |
  -- |----------------------------------------------------------------------------|
  -- | DATABASE : Oracle                                                          |
  -- | FILE     : perf_performance_snapshot.sql                                   |
  -- | CLASS    : Tuning                                                          |
  -- | PURPOSE  : This script will generate a small performance overview report   |
  -- |            checking all key database performance indicators.               |
  -- | NOTE     : As with any code, ensure to test this script in a development   |
  -- |            environment before attempting to run it in production.          |
  -- +----------------------------------------------------------------------------+
  
  SET LINESIZE 145
  SET PAGESIZE 9999
  SET VERIFY   off
  
  PROMPT 
  PROMPT +-----------------------------------+
  PROMPT | Database Startup Date/Time        |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT
      instance   "Instance Name"
    , open_time  "Open Date/Time"
  FROM
    v$thread
  /
  
  PROMPT 
  PROMPT +-----------------------------------+
  PROMPT | Buffer Cache Hit Ratio            |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT
    TRUNC( ( 1 - ( SUM(decode(name,'physical reads',value,0)) /
                   ( SUM(DECODE(name,'db block gets',value,0))
                     +
                     (SUM(DECODE(name,'consistent gets',value,0)))
                   )
                 )
           ) * 100
         ) "Buffer Hit Ratio"
  FROM v$sysstat
  /
  
  SELECT
      a.value + b.value                          "Logical reads"
    , c.value                                    "Physical Reads"
    , d.value                                    "Physical Writes"
    , ROUND (100 * ( (a.value+b.value)-c.value) / (a.value+b.value)
             )                                   "Buffer Hit Ratio"
    , ROUND(c.value * 100 / (a.value + b.value)) "% Missed"
  FROM
      v$sysstat a
    , v$sysstat b
    , v$sysstat c
    , v$sysstat d
  WHERE
        a.statistic#=37
    AND b.statistic#=38
    AND c.statistic#=39
    AND d.statistic#=40
  /
  
  PROMPT 
  PROMPT +-----------------------------------+
  PROMPT | Data Dictionary Hit Ratio         |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT
      SUM(gets)                                 "Data Dict. Gets"
    , SUM(getmisses)                            "Data Dict. Cache Misses"
    , ROUND((1-(sum(getmisses)/SUM(gets)))*100) "Data Dict Cache Hit Ratio"
    , ROUND(SUM(getmisses)*100/SUM(gets))       "% Missed"
  FROM
    v$rowcache
  /
  
  PROMPT 
  PROMPT +-----------------------------------+
  PROMPT | Library Cache Miss Ratio          |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT
      SUM(pins)                               "Executions"
    , SUM(reloads)                            "Cache Misses"
    , ROUND((1-(SUM(reloads)/SUM(pins)))*100) "Library Cache Hit Ratio"
    , ROUND(SUM(reloads)*100/SUM(pins))       "% Missed"        
  FROM
    v$librarycache
  /
  
  SELECT
      namespace                 "Namespace"
    , TRUNC(gethitratio*100)    "Hit Ratio"
    , TRUNC(pinhitratio*100)    "Pin Hit Ratio"
    , reloads                   "Reloads"
    , invalidations             "Invalidations"
  FROM
    v$librarycache
  /
  
  PROMPT
  PROMPT +-----------------------------------+
  PROMPT | Redo Log Buffer                   |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT
      SUBSTR(name,1,30)            "Name"
    , TO_CHAR(value, '999,999')    "Bytes"
  FROM
      v$sysstat
  WHERE
      name ='redo log space requests'
  /
  
  SELECT
      name                              "Name"
    , TO_CHAR(bytes, '999,999,999,999') "Bytes"
  FROM
      v$sgastat
  WHERE
      name ='free memory'
  /
  
  SELECT
      TO_CHAR(SUM(executions), '999,999,999,999,999,999')  "Tot SQL since startup"
    , TO_CHAR(SUM(users_executing), '999,999,999,999,999') "SQL executing now"
  FROM
      v$sqlarea
  /
  
  PROMPT
  PROMPT +--------------------------------------------------------+
  PROMPT | If miss_ratio or immediate_miss_ratio > 1 then latch   |
  PROMPT | contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE   |
  PROMPT +--------------------------------------------------------+
  PROMPT
  
  SELECT
      SUBSTR(ln.name,1,30)                            "Name"
    , (misses/(gets+.001)) * 100                      "Miss Ratio"
    , (immediate_misses/(immediate_gets+.001)) * 100  "Immd. Miss Ratio"
  FROM
      v$latch     l
    , v$latchname ln
  WHERE
        l.latch# = ln.latch#
    AND ( (( misses / (gets+.001)) * 100 > .1 )
          OR
          (( immediate_misses / (immediate_gets+.001)) * 100 > .1)
        )
  ORDER BY
    ln.name
  /       
  
  PROMPT
  PROMPT +--------------------------------------------------------+
  PROMPT | If these are < 1% of Total Number of requests for data |
  PROMPT | then extra rollback segments are needed.               |
  PROMPT +--------------------------------------------------------+
  PROMPT
  
  SELECT
      class    "Class"
    , count    "Count"
  FROM
      v$waitstat 
  WHERE
    class IN (   'free list'
               , 'system undo header'
               , 'system undo block'
               , 'undo header'
               , 'undo block') 
  GROUP BY
      class
    , count
  /
  
  PROMPT 
  PROMPT +-----------------------------------+
  PROMPT | Total Number of Requests for Data |
  PROMPT +-----------------------------------+
  PROMPT
  
  SELECT  TO_CHAR(SUM(value), '999,999,999,999,999') "Total Requests"
  FROM    v$sysstat 
  WHERE   name IN ('db block gets','consistent gets')
  /
  
  