44 lines
1.3 KiB
SQL
44 lines
1.3 KiB
SQL
@@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
|