@@header

/*
*
*  Author    : Vishal Gupta
*  Purpose   : Display AWR Configuration
*  Parameter : None
*  
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  10-Apr-12  Vishal Gupta  First Draft
*  
*/

PROMPT 
PROMPT ##################################################################
PROMPT  A W R     R E P O S I T O R Y    C O N F I G U R A T I O N
PROMPT ##################################################################

COLUMN snap_interval      HEADING "Snapshot Interval"            FORMAT a20
COLUMN retention          HEADING "Snapshot Retention"           FORMAT a20
COLUMN topnsql            HEADING "Top N SQL"                    FORMAT a60

SELECT c.snap_interval
     , c.retention  
     , DECODE(c.topnsql
           ,'DEFAULT',DECODE(p.value
                       ,'TYPICAL','DEFAULT - 30 for current statistic_level=TYPICAL'
                       ,'ALL','DEFAULT - 100 for current statistic_level=ALL'
                       ,c.topnsql
                       )
     ,c.topnsql) topnsql
 FROM DBA_HIST_WR_CONTROL c
      JOIN v$database d ON c.dbid = d.dbid
      LEFT OUTER JOIN v$system_parameter p ON p.name = 'statistics_level'
WHERE 1=1
;


@@footer