Install STATSPACK in a 11G Database:
Install statspack: @spcreate.sql
Auto-execute statspack jobs: @spauto.sql
Run statspack report: @spreport.sql
to remove a statspack job: exec dbms_job.remove('<job number>');
to stop a job from running again: exec dbms_job.broken('<job number>',TRUE);
to run a job immediately: exec dbms_job.run('<job number>')
To show all info for jobs in the active job queue:
set linesize 130;
col what for a30;
col log_user for a12;
col interval for a30;
select job,what,log_user,
to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "Sysdate",
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') "Next Date",
interval
from user_jobs;
To change the default 60 minute intervals, change the below string in the spauto.sql script to meet any of the requirements below:
###########################
spool spauto.lis
--
-- Schedule a snapshot to be run on this instance every hour, on the hour
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
commit;
end;
/
############################
Run Statspack Snapshot Every 5 Minutes Starting at the Next 5 Minute Interval
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/5)+1)*5)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/5)+1)*5)/(24*60)', TRUE, :instno);
COMMIT;
END;
/
Run Statspack Snapshot Every 15 Minutes Starting at the Next 15 Minute Interval
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/15)+1)*15)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/15)+1)*15)/(24*60)', TRUE, :instno);
COMMIT;
END;
/
Run Statspack Snapshot Every 30 Minutes Starting at the Next 30 Minute Interval
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', trunc(sysdate,'HH24')+((floor(to_number(to_char(sysdate,'MI'))/30)+1)*30)/(24*60), 'trunc(sysdate,''HH24'')+((floor(to_number(to_char(sysdate,''MI''))/30)+1)*30)/(24*60)', TRUE, :instno);
COMMIT;
END;
/
Run Statspack Snapshot Every 1 Hour
variable jobno number;
variable instno number;
BEGIN
SELECT instance_number INTO :instno FROM v$instance;
DBMS_JOB.SUBMIT(:jobno, 'statspack.snap;', TRUNC(sysdate+1/24,'HH'), 'TRUNC(SYSDATE+1/24,''HH'')', TRUE, :instno);
COMMIT;
END;
/
|
Action |
Interval Time |
|
Execute daily |
'SYSDATE + 1' |
|
Execute every 4 hours |
'SYSDATE + 4/24' |
|
Execute every 10 minutes |
'SYSDATE + 10/1440' |
|
Execute every 30 seconds |
'SYSDATE + 30/86400' |
|
Execute every 7 days |
'SYSDATE + 7' |
|
Do no re-execute and remove job |
NULL |
|
Action |
Interval Time |
|
Every day at 12:00 midnight |
TRUNC(SYSDATE + 1) |
|
Every day at 8:00 p.m. |
TRUNC(SYSDATE + 1) + 20/24 |
|
Every Tuesday at 12:00 noon |
NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24 |
|
First day of the month at midnight |
TRUNC(LAST_DAY(SYSDATE) + 1) |
|
Last day of the quarter at 11:00 p.m. |
TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24 |
|
Every Monday, Wednesday and Friday at 9:00 p.m. |
TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24 |
Oracle Date Math Examples
|
Date / Time Fraction |
Description |
|
WHERE (date) > sysdate - 6/24; |
Past 6 hours. (or 1/4 of a day ago) |
|
WHERE (date) > sysdate - 6; |
Past six days |
|
WHERE (date) > sysdate - 6/1440; |
Past six minutes |
|
6/24 |
6 hours |
|
1/24/60/60 |
One second |
|
1/24/60 |
One minute |
|
1/24 |
One hour |
|
TRUNC(SYSDATE+1/24,'HH') |
Every one hour starting with the next hour |
Job interval Examples
Remember that the dbms_job.submit() procedure accepts three parameters:
- the name of the job to submit
- the start time for the job
- and the interval to execute the job
DBMS_JOB.SUBMIT (
job => :jobno
, what => 'statspack.snap;' -- What to run
, next_date => TRUNC(sysdate+1/24,'HH') -- Start next hour
, interval => 'TRUNC(SYSDATE+1/24,''HH'')' -- Run every hour
);
-- =========================================================
-- Schedule a SNAPSHOT to be run on this instance every hour
-- =========================================================
VARIABLE jobno NUMBER;
VARIABLE instno NUMBER;
BEGIN
select instance_number into :instno from v$instance;
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour.
-- ------------------------------------------------------------
DBMS_JOB.SUBMIT (
:jobno
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, 'TRUNC(SYSDATE+1/24,''HH'')'
, TRUE
, :instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0900 and run 12 hours later
-- ------------------------------------------------------------
DBMS_JOB.SUBMIT (
:jobno
, 'statspack.snap;'
, TRUNC(sysdate+1)+9/24
, 'TRUNC(SYSDATE+12/24,''HH'')'
, TRUE
, :instno);
-- ------------------------------------------------------------
-- Submit job to begin at 0600 and run every 10 minutes
-- ------------------------------------------------------------
DBMS_JOB.SUBMIT (
:jobno
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, 'TRUNC(sysdate+10/1440,''MI'')'
, TRUE
, :instno);
-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
DBMS_JOB.SUBMIT (
:jobno
, 'statspack.snap;'
, TRUNC(sysdate+1)+6/24
, 'TRUNC(
LEAST(
NEXT_DAY(sysdate,''MONDAY'')
, NEXT_DAY(sysdate,''TUESDAY'')
, NEXT_DAY(sysdate,''WEDNESDAY'')
, NEXT_DAY(sysdate,''THURSDAY'')
, NEXT_DAY(sysdate,''FRIDAY'')
) + 1/24
, ''HH'')'
, TRUE
, :instno);
COMMIT;
END;
/
Add a job to auto purge snapshots aged more than 31 days.
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.PURGE( i_num_days=>31);', trunc(sysdate+1,'DD'), 'trunc(SYSDATE+1,''DD'')', TRUE, :instno);
commit;
end;
/
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE( schedule_name => 'perfstat.statspack_every20', repeat_interval => 'FREQ=MINUTELY;BYMINUTE=10,30,50'); DBMS_SCHEDULER.CREATE_JOB( job_name => 'perfstat.sp_snapshot', job_type => 'STORED_PROCEDURE', job_action => 'perfstat.statspack.snap', schedule_name => 'perfstat.statspack_every20', comments => 'Statspack collection'); DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot'); END; /
contain below script file.
spdrop.sql
@@spdtab.sql
@@spdusr
spcreate.sql
@@spcusr
@@spctab
@@spcpkg
sppurge.sql
spauto.sql
spdoc.txt
