Installing and configuring Statspack to Oracle (dbms_job configuration)
I was asked to provide instructions on how to install and configure Statspack into Oracle so here goes.
The statspack scripts can be found from under this directory structure:
$Oracle_home/rdbms/admin
The installation needs to be run as SYS user.
Basic installation is done simply by running this script:
@?/rdbms/admin/spcreate.sql
This installs the Statspack engine & tables to database.
You can (if you want) automate the snap creation with this script:
@?/rdbms/admin/spauto.sql
This script takes snaps every hour starting from the next hour.
You can modify this script to make another kind of scheduling.
For example if you want to take snaps every half an hour, you need to modify:
"sysdate+1/24" => "sysdate+1/48"
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
=>
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);
If you need to ie. change the currently running job interval, you need to find out the job number first.
Connect as dba user to Oracle and run this query:
select job, what, interval from dba_jobs;
JOB
----------
WHAT
-----------------------------
INTERVAL
-----------------------------
21
statspack.snap;
trunc(SYSDATE+1/24,'HH')
So the statspack.snap is run every hour and job number is 21. Fine.
Let’s say that we need to run it every half an hour.
We need to change the interval to: 1/48.
You can update the job interval like this. You can set is as the job owner with this syntax:
exec dbms_job.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');
PL/SQL procedure successfully completed.
Or as another user with DBA privileges:
SQL> show user
USER is "SYS"
SQL> exec dbms_ijob.interval(21,'TRUNC(SYSDATE+1/48,''HH'')');
PL/SQL procedure successfully completed.
Please note: there are two single quotes ‘ around HH, not one double-quote “.
Also you could remove the job with:
exec dbms_job.remove(21);
and re-create it with:
variable jobno number;
variable instno number;
begin
select instance_number into :instno from v$instance;
dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'HH'), 'trunc(SYSDATE+1/48,''HH'')', TRUE, :instno);
commit;
end;
/
You should try that the job runs now.
Run as the job owner:
SQL> exec dbms_job.run(21);
PL/SQL procedure successfully completed.
Run as a DBA user:
SQL> exec dbms_ijob.run(21);
PL/SQL procedure successfully completed.
You can check when the next scheduled job run is:
SQL> select job, what, interval, to_char(next_date, 'DD.MM.YYYY HH24:MI:SS') from dba_jobs where job=21;
JOB
----------
WHAT
-------------------------------------
INTERVAL
-------------------------------------
TO_CHAR(NEXT_DATE,'
-------------------
21
statspack.snap;
TRUNC(SYSDATE+1/48,'HH')
29.03.2010 11:00:00
Statspack was introduced in Oracle 9i and it is still an usable tool in current Oracle version of 11.2.
AWR is superior technology compared to Statspack, but for many purposes Statspack still holds its place.