Automating Statspack Report

Posted on 2005-03-29
Medium Priority
Last Modified: 2008-01-09
  Can any one tell me how to automate statspack report so that it generates the report every 1/2 hr .

Question by:ratnaprasad123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 48

Assisted Solution

schwertner earned 1000 total points
ID: 13659587
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 dbms_job.run(<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.
LVL 48

Expert Comment

ID: 13659834
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;

Accepted Solution

helpneed earned 1000 total points
ID: 13659994

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..


Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question