Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Schedule Automatic STATSPACK Data Collection

Oracle Tips by Burleson Consulting
Don Burleson

After we have verified that STATSPACK is installed and working, we can schedule automatic data collection. By using the statsauto.sql script we can automatically schedule an hourly data collection for STATSPACK. The statsauto.sql script contains the following directive:

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);

The important thing to note in this call to dbms_job.submit is the execution interval. The SYSDATE+1/24 is the interval that is stored in the dba_jobs view to produce hourly snapshots. You can change this as follows for different sample times. There are 1,440 minutes in a day, and you can use this figure to adjust the execution times.

SQL> execute dbms_job.submit(:jobno, 'statspack.snap;',
trunc(sysdate+1/144,'MI'), 'trunc(SYSDATE+1/144,''MI'')', TRUE, :instno);

In the real world, you may have times where you want to sample the database over short time intervals. For example, if you have noticed that a performance problem happens every day between 4:00 P.M. and 5:00 P.M., you can request more frequent snapshots during this period.

For normal use, you probably want to accept the hourly default and execute a snapshot every hour. Below is the standard output from running the statsauto.sql script:

SQL> connect perfstat/perfstat;
SQL> @statsauto
PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance

Note that this job number is needed when modifying or removing the job:


Job queue process

Below is the current setting of the job_queue_processes init.ora parameter - the value for this parameter must be greater than 0 to use automatic statistics gathering:

------------------------------------ ------- -----------------------------
job_queue_processes integer 1

Next scheduled run
The next scheduled run for this job is:

---------- --------- --------
1 12-MAY-02 16:00:00

We can now see that a STATSPACK snapshot will automatically be executed every hour. We see that this is scheduled as job number 1, and we can use this job number to cancel this collection at any time using the dbms_job.remove procedure:



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.