Home : Automating Estimate/Compute Statistics for Oracle

Revision Notes

July 6, 2001 - Document Created

Downloads/Links

• analyzer.zip (3K)

Overview

Most Oracle databases these days are running with cost based optimization.  If you are using rule based optimization you do not need this script.  The cost based optimizer requires that you keep your objects analyzed.  This task is usually scheduled to run automatically during off-peak hours.   With many databases this is pretty easy to do.  Most of the objects are small and there are plenty of off-peak hours to play with.  However, some shops have very large objects to deal with and very few off-peak hours.  If this is the case then perhaps this PL/SQL procedure can help you.  This procedure is easy to setup and  get going.  Here are some of the current and proposed features (proposed features are in italics).

• Easily scheduled using the DBMS_JOBS package.

• Can be scheduled to run for "x" number of minutes.  This allows you to pick a short window, say between 5-6 am and get a little work done.

• Job is logged (job_log table).  Tells you how many objects, how much time, and how many total megabytes the objects analyzed totaled to.  You can also see how many objects that still need to be analyzed.

• Job can be cancelled gracefully.

• You define the threshold for COMPUTE vs. ESTIMATE statistics.  The percentage that is estimated is based on your threshold (max_mb parameter into the procedure).  For example, if you specify 10 MB and the object is 400 MB then 2.5% of the table will be analyzed.  If the object is below 10MB then compute statistics will be performed.

• The command issued to run the job is also logged.

• Checks with performance monitoring software to see if it is OK to run the job.  If not attempts to run every "x" minutes until window has expired.

• Summary of job is optionally emailed to administrator when job has completed.

• If version is 8i will optionally drive off of table monitoring.

A Note on 8i

I am aware that you can use table monitoring and the GATHER_SCHEMA_STATS procedure to automate this task.  However, this system allows you to define very short windows that you will allow the job to run.  I can have it run 3 days a week at 2 different intervals during the day for 30 minutes each time if I want.  This is something you can not do with the afore mentioned procedure.

Configuration

If you are not on version 8i or above run the execute_immediate.sql file.  This will install a procedure called EXECUTE_IMMEDIATE which will simulate the execute_immediate function in 8i. I got the procedure from an Ask Tom archive question.

Run the analyzer.sql script.  This will create the following objects.

• SEQ_JOB_ID (sequence) - Sequence for each job in the job_log table.

• job_log (table) - Jobs are logged in this table.

• ANALYZER (procedure) - Procedure that runs this whole thing.

• KILL_JOB (procedure) - Procedure used to kill a running job gracefully.

You may optionally want to edit the ANALYZER procedure and re-compile it to suit your specific needs.  Out of the box it will analyze all non-SYSTEM, non-SYS owned tables and indexes.

The procedure accepts three parameters, all are optional.

1. RUN_TIME (integer, default 0 which means don't quit until completed) - Number of minutes to run from start time.

2. SINCE_DAYS (integer, default 7) - Analyze all objects that have not been analyzed within this many days.

3. MAX_MB (integer, default 5) - Threshold that is used to determine how much of an object is analyzed and whether or not ESTIMATE vs. COMPUTE statistics is used.

You can run the job manually whenever you want or schedule it to run using the DBMS_JOB package.  If you want to kill the job get the JOB_ID from the job_log table and use the KILL_JOB procedure.  If you do not pass in a JOB_ID all running jobs will be stopped.  Note that the KILL_JOB procedure will return right away but is may take a few minutes for any objects already being analyzed to complete.  Check the status column of the job_log table to determine if the job has completed.