Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1825
  • Last Modified:

Automating Statspack Report

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

  • 2
2 Solutions
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.
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..


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now