Automating Statspack Report

  Can any one tell me how to automate statspack report so that it generates the report every 1/2 hr .

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

To be able to make comparisons of performance from one day, week or year to the next, there must be multiple snapshots taken over a period of time.  A minimum of two snapshots are required before any performance characteristics of the application and database can be made.
  The best method to gather snapshots is to automate the collection on a regular time interval.  It is possible to do this:
    - Within the database, using the Oracle dbms_job procedure to schedule the snapshots
    - Using Operating System utilities (such as 'cron' on Unix or 'at' on NT) to schedule the snapshot.  
- Scheduling StatsPack snapshots using DBMS_JOB package
  To use an Oracle-automated method for collecting statistics, you can use dbms_job.  A sample script on how to do this is supplied in statsauto.sql, which schedules a snapshot every hour, on the hour.
  In order to use dbms_job to schedule snapshots, the job_queue_processes  initialization parameter must be set to greater than 0 in the configuration  file used to start the instance for the job to be run automatically.
  Example of an init<SID>.ora entry:
    #  Set to enable the job queue process to start.  This allows dbms_job
    #  to schedule automatic statistics collection using STATSPACK
  If using statsauto.sql in OPS environment, the statsauto.sql script must be run once on each instance in the cluster.  Similarly, the job_queue_processes parameter must also be set for each instance.
Changing the interval of statistics collection
  To change the interval of statistics collection use the dbms_job.intervalprocedure e.g.
      execute dbms_job.interval(1,'SYSDATE+(1/48)');
  Where 'SYSDATE+(1/48)' will result in the statistics being gathered each 1/48 hours (i.e. every half hour).
  To force the job to run immediately,  
      execute<job number>);
  To remove the autocollect job,
      execute dbms_job.remove(<job number>);
  For more information on dbms_job, see the Supplied Packages Reference Manual.
Gathering a StatsPack snapshot
  The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
       SQL>  connect perfstat/perfstat
       SQL>  execute statspack.snap;

i think u can use statsauto.sql script which will help u in automating the process

wht u can do is write a script to connect to the particulatuser it should also call this script..make it as a shell script and put it in tne crontab and set ur time

if in windows make it as abatch file and put it in the scheduler..


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.