Script :
set serveroutput on
set lines 130
set verify off
accept v_begin_time prompt ‘Enter BEGIN_TIME DD-MON-YY HH24:MI:SS->’
accept v_end_time prompt ‘Enter END_TIME DD-MON-YY HH24:MI:SS->’
DECLARE
lv_v_stat_name dba_hist_sysstat.stat_name%TYPE;
lv_v_per_sec NUMBER;
lv_n_cnt NUMBER := 0;
BEGIN
FOR c1 IN
(SELECT TO_CHAR (begin_interval_time, ‘DD/MON/YY:HH24’) sun,TO_CHAR (begin_interval_time + (1/24), ‘DD-MON-YY:HH24’) sun1
FROM dba_hist_snapshot
WHERE begin_interval_time BETWEEN TO_DATE (‘&v_begin_time’,
‘DD-MON-YYYY HH24:MI’
)
– 1 / 24
AND TO_DATE (‘&v_end_time’,
‘DD-MON-YYYY HH24:MI’
)order by 1)
LOOP
BEGIN
–dbms_output.put_line (c1.sun);
SELECT distinct e.stat_name “Statistic Name”,
ROUND
( (e.VALUE – b.VALUE)
/ (SELECT AVG
( EXTRACT
(DAY FROM ( e1.end_interval_time
– b1.end_interval_time
)
)
* 24
* 60
* 60
+ EXTRACT
(HOUR FROM ( e1.end_interval_time
– b1.end_interval_time
)
)
* 60
* 60
+ EXTRACT
(MINUTE FROM ( e1.end_interval_time
– b1.end_interval_time
)
)
* 60
+ EXTRACT
(SECOND FROM ( e1.end_interval_time
– b1.end_interval_time
)
)
)
FROM dba_hist_snapshot b1, dba_hist_snapshot e1
WHERE b1.snap_id = b.snap_id
AND e1.snap_id = e.snap_id
AND b1.dbid = b.dbid
AND e1.dbid = e.dbid
AND b1.instance_number = b.instance_number
AND e1.instance_number = e.instance_number
AND b1.startup_time = e1.startup_time
AND b1.end_interval_time < e1.end_interval_time),
2
) “Per Second”
INTO lv_v_stat_name,
lv_v_per_sec
FROM dba_hist_sysstat b, dba_hist_sysstat e
WHERE b.snap_id =
(SELECT max(snap_id)
FROM dba_hist_snapshot
WHERE begin_interval_time
BETWEEN TO_DATE (c1.sun,
‘DD-MON-YY HH24:MI’
)
– 1 / 24
AND TO_DATE (c1.sun,
‘DD-MON-YY HH24:MI’))
AND e.snap_id =
(SELECT max(snap_id)
FROM dba_hist_snapshot
WHERE begin_interval_time
BETWEEN TO_DATE (c1.sun1,
‘DD-MON-YY HH24:MI’
) – 1/24
AND TO_DATE (c1.sun1,
‘DD-MON-YY HH24:MI’))
AND b.stat_id = e.stat_id
AND e.stat_name LIKE ‘%hard%’
AND e.VALUE >= b.VALUE
AND e.VALUE > 0
ORDER BY 1 ASC;
lv_n_cnt := lv_n_cnt + 1;
DBMS_OUTPUT.put_line (c1.sun||’ ‘||lv_v_stat_name||’ ‘||lv_v_per_sec);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (‘No Data Found’);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘When Others Error’);
END;
END LOOP;
END;
/
====
Sample Output :
| Date | Stat_Name | Value |
| 18/APR/13:07 | parse count (hard) | 3.23 |
| 18/APR/13:08 | parse count (hard) | 3.95 |
| 18/APR/13:09 | parse count (hard) | 4.14 |
| 18/APR/13:10 | parse count (hard) | 4.26 |
| 18/APR/13:11 | parse count (hard) | 4.12 |
Recent Posts