Posted by: Colin Smith
Database, DBA, Oracle
Today I was tasked to change the statspack job to get a snapshot every 15 minutes instead of once every hour. These statspack snapshots are something that Oracle does in order for a DBA to look at historical performance data. The idea in making this change is that a lot can change in one hour so we might want smaller snaps more often to get a better look at what is actually happening in the Database when doing performance and tuning. So here is what I am going to do.
In our environment we use the Oracle Scheduler to run this job and as is default it runs as the perfstat user. I connected to the database as perfstat and then I can query the user_jobs table to get the information that I need.
I did the following:
SQL> select job, next_date, interval from user_jobs;
As you can see the field that I want to change is the INTERVAL. To do this I will need to call the dbms_job.interval() function and figure out the math for the correct interval. I found this Oracle Tips site to be helpful with the math conversion. While I was working on it I was having a bit of difficulty getting it to work. I tried breaking the job in order to change the interval value but you do not have to do this. Below is the PL/SQL that I ran to change the interval.
2 dbms_job.interval(281, ‘SYSDATE + 3/288′);
PL/SQL procedure successfully completed.
As you can see the dbms_job.interval() function was called upon and I passed it some data. First is the job name. My job is named 281. Then I passed it the Interval that I want the job to run at. So I said take the current system date and then add 3/288. This essentially means run every 15 minutes from this time forward. Below you can see that I am now getting the results that I was looking for.
13942 5 21 Oct 2008 10:00:02 fire440-8
13943 5 21 Oct 2008 11:00:04 fire440-8
13944 5 21 Oct 2008 12:42:09 fire440-8
13945 5 21 Oct 2008 13:00:04 fire440-8
13946 5 21 Oct 2008 13:15:06 fire440-8
13947 5 21 Oct 2008 13:30:08 fire440-8
You will notice that the top snap was at 10:00 and then 11:00 then I broke the job so it did not run until I un-broke the job. From 13:00 you can see that it is indeed running every 15 minutes. If you have any questions or comments please let me know. Again you can check out my site at sysadminsmith.com if you would like some help with this or any other issue and check out some of my other blog postings. Thanks and I hope you learned something from this. I know I did.