66 lines
2.7 KiB
SQL
66 lines
2.7 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display ASH Info
|
|
* Compatibility : 11.2 and above
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 30-Aug-12 Vishal Gupta First cut
|
|
* 27-Sep-12 Vishal Gupta Updated output formatting
|
|
*
|
|
*/
|
|
|
|
PROMPT
|
|
PROMPT *******************
|
|
PROMPT * ASH Info
|
|
PROMPT *******************
|
|
|
|
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN total_size HEADING "Total|Size" FORMAT 999,999,999
|
|
COLUMN fixed_size HEADING "Fixed|Size" FORMAT 999,999,999
|
|
COLUMN sampling_interval HEADING "Sampling|Interval|(ms)" FORMAT 99999
|
|
COLUMN avg_sample_time HEADING "Avg|Sample|Time|(ms)" FORMAT 99999
|
|
COLUMN disk_filter_ratio HEADING "Disk|Filter|Ratio" FORMAT 99
|
|
COLUMN oldest_sample_time HEADING "Oldest|SampleTime" FORMAT a18
|
|
COLUMN latest_sample_time HEADING "Latest|SampleTime" FORMAT a18
|
|
COLUMN duration HEADING "Duration" FORMAT a12
|
|
COLUMN sampled_bytes HEADING "Sampled|MB" FORMAT 999,999,999
|
|
COLUMN sample_count HEADING "Sample|Count" FORMAT 999,999,999
|
|
COLUMN sampler_elapsed_time HEADING "Sampler|Elapsed|Time|(ms)" FORMAT 999,999,999,999
|
|
COLUMN awr_flush_count HEADING "AWR|Flush|Count" FORMAT 999,999
|
|
COLUMN awr_flush_emergency_count HEADING "AWR|Emer|Flush|Count" FORMAT 999,999
|
|
COLUMN awr_flush_bytes HEADING "AWR|Flush|MB" FORMAT 999,999
|
|
|
|
SELECT i.inst_id
|
|
, i.total_size
|
|
, i.fixed_size
|
|
, i.sampling_interval
|
|
, ROUND(i.sampler_elapsed_time/i.sample_count) avg_sample_time
|
|
, i.disk_filter_ratio
|
|
, to_char(i.oldest_sample_time,'DD-MON-YY HH24:MI:SS') oldest_sample_time
|
|
, to_char(i.latest_sample_time,'DD-MON-YY HH24:MI:SS') latest_sample_time
|
|
, REPLACE(SUBSTR((i.latest_sample_time - i.oldest_sample_time)
|
|
,1,INSTR((i.latest_sample_time - i.oldest_sample_time),'.')-1
|
|
)
|
|
,'+0000000','+'
|
|
) duration
|
|
, i.sample_count
|
|
, ROUND(i.sampled_bytes/1024/1024) sampled_bytes
|
|
, i.sampler_elapsed_time
|
|
, i.awr_flush_count
|
|
, i.awr_flush_emergency_count
|
|
, ROUND(i.awr_flush_bytes/1024/1024) awr_flush_bytes
|
|
FROM gv$ash_info i
|
|
ORDER BY i.inst_id
|
|
;
|
|
|
|
@@footer
|