Wednesday, March 22, 2006

Statspack

Quick SQL to submit a statspack snapshot ever 10 minutes for 12 hours... or so.
set lines 132
show parameter job
DECLARE
I NUMBER;
A NUMBER;
START_TIME DATE;
BEGIN
EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING SYSDATE;
FOR I IN 1 ..72 LOOP
A := I*10;
START_TIME := SYSDATE+A/(24*60);
EXECUTE IMMEDIATE 'DECLARE JOBOUT NUMBER; BEGIN DBMS_JOB.SUBMIT(JOBOUT,''statspack.snap;'',:A,null); COMMIT; END;' USING START_TIME;
NULL;
END LOOP;
END;
/
SELECT
JOB
,to_char(next_date,'DD-MON-YYYY HH24:MI:SS') next_date
from
user_jobs
order by
next_date
/
exit

No comments: