2 minute guide for Statspack sample scheduling

Now that you have installed the Statspack tool, you need to start taking samples or snapshots. This is a task you may easily automate, and is recommended to do so, because snapshots are evenly and uniformly spaced.

You may schedule this task using cron, at or any OS or 3rd party scheduler, but I would suggest a better way: database jobs.

Advantages:
-Database Contained
-Don't breach security exposing users/passwords

The Statspack set of tools provides a script that automatically creates the job for you: spauto.sql. It's located at $ORACLE_HOME/rdbms/admin and you may easily customize the NEXT_DATE parameter for dbms_job.submit (below, red color) and adjust the time interval . You will need to change TRUNC rounding precision if you go from hours to minutes.

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

Then you need to run the spauto script as the user perfstat or the Statspack owner you have:

SQL> conn perfstat
Password:

Connect
SQL> @?/rdbms/admin/spauto

You will get an output like this, showing the job number created by spauto and information regarding the next execution time. At this point, snapshot taking has been scheduled succesfuly.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:


     JOBNO
----------
        23


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:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10


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


       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
        23 23-MAY-08 17:52:00

How to stop taking snapshots

After you finish taking samples or if you ran out of space for the Statspack tablespace, you'll need to stop the snapshot job. This is very easy, as you may see next:

SQL> conn perfstat    -- the Statspack owner
Password:

Connected
SQL> select job, what from user_jobs;

       JOB                      WHAT
----------  ------------------------
         2
        23  statspack.snap(10);

SQL> exec rdbms_job.remove(23);

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB                      WHAT
----------  ------------------------
         2

I hope this tip is useful for you, as it has been very helpful for me.

Please leave your comments, your feedback is vital to improve this content



Subscribe to Oracle Database Disected by Email