查看scnheadroom变化趋势的几种方法(二)
ose FROM
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt entering the NUMBER OF days (n) will result IN the most recent
prompt (n) days OF snapshots being listed. pressing without
prompt specifying a NUMBER LISTS ALL completed snapshots.
prompt
prompt
set heading OFF;
column num_days new_value num_days noprint;
SELECT 'Listing '
|| Decode(Nvl('&&num_days', 3.14), 0, 'no snapshots',
3.14, 'all Completed Snapshots',
1,
'the last day''s Completed Snapshots',
'the last &num_days days of Completed Snapshots'
),
Nvl('&&num_days', 3.14) num_days
FROM sys.dual;
set heading ON;
--
-- List available snapshots
break ON inst_name ON db_name ON host ON instart_fmt skip 1;
ttitle OFF;
SELECT To_char(s.startup_time, 'dd Mon "at" HH24:mi:ss') instart_fmt,
di.instance_name inst_name,
di.db_name db_name,
s.snap_id snap_id,
To_char(s.end_interval_time, 'dd Mon YYYY HH24:mi') snapdat,
s.snap_level lvl
FROM dba_hist_snapshot s,
dba_hist_database_instance di
WHERE s.dbid = :dbid
AND di.dbid = :dbid
AND di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
AND s.end_interval_time >= Decode(&num_days, 0, To_date('31-JAN-9999',
'DD-MON-YYYY'
),
3.14, s.end_interval_time,
To_date(:max_snap_time,
'dd/mm/yyyy')
- ( &num_days - 1 ))
ORDER BY db_name,
instance_name,
snap_id;
clear break;
ttit
le OFF;
--
-- Ask for the snapshots Id's which are to be compared
prompt
prompt
prompt specify the BEGIN AND END SNAPSHOT ids
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt BEGIN SNAPSHOT id specified: &&begin_snap
prompt
prompt END SNAPSHOT id specified: &&end_snap
prompt
--
-- Set up the snapshot-related binds
--
variable bid NUMBER;
variable eid NUMBER;
BEGIN
:bid := &begin_snap;
:eid := &end_snap;
END;
/
prompt
--
-- Ask for Statistics Name Filter
--
prompt
prompt
prompt search statistic
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt search BY STATISTICS name. pressing without
prompt specifying anything show ALL STATISTICS.
set heading OFF;
column stat_search new_value stat_search noprint;
SELECT 'Statistic Name Filter: '
|| Nvl('&&stat_search', '%'),
Nvl('&&stat_search', '%') stat_search
FROM sys.dual;
set heading ON;
column stat_id heading "Statistic ID" format 9999999999999;
column name heading "Statistic Name" format a64;
column class_name heading "Statistic Class" format a10;
SELECT stat_id,
( CASE
WHEN class = 1 THEN 'USER'
WHEN class = 2 THEN 'REDO'
WHEN class = 4 THEN 'ENQUEUE'
WHEN class = 8 THEN 'CACHE'
WHEN class = 16 THEN 'OS'
WHEN class = 32 THEN 'RAC'
WHEN class = 40 THEN 'RAC-CACHE'
WHEN class = 64 THEN 'SQL'
WHEN class = 72 THEN 'SQL-CACHE'
WHEN class = 128 THEN 'DEBUG'
ELSE To_char(class)
END ) CLASS_NAME,
name
FROM v$sysstat
WHERE Upper(name) LIKE Trim(Upper('%&stat_search%'))
ORDER BY class,
name
/
--
-- Ask for the statistics
variable stat_filter_id NUMBER
variable stat_filter_name VARCHAR2(64)
prompt
prompt
prompt specify the STATISTICS
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt enter STATISTICS id OR STATISTICS name.
prompt
BEGIN
SELECT To_number('&&stat_input')
INTO :stat_filter_id
FROM dual;
EXCEPTION
WHEN invalid_number THEN