Auto sample gather stats procedure

Posted on 2008-11-15
Last Modified: 2013-12-19
can some one explain the  Oracle's auto sample gather stats procedure and how to run it.

Question by:taaz
    LVL 73

    Expert Comment

    it doesn't exist in 9i, you have to invoke dbms_stats yourself.  You can schedule it to run automatically yourself via dbms_job if you want.

    it does exist in 10g and you don't have to do anything because it runs automatically via dbms_scheduler.
    What the procedure does is count the rows in your tables and indexes, or at least sampling them if they are very large.  It also gathers some statistical information to describe the data in each table such as min and max value, number of nulls, distribution of values into histograms and count of associated index values.  It will also gather some system statistics so the optimizer will know how fast your cpu is, how fast the io, etc.
    LVL 28

    Expert Comment

    in 10g database, you can use the below queries to see what the job calls and when the job runs etc....

    select * from dba_scheduler_jobs
    where owner ='SYS'
    and job_name = 'GATHER_STATS_JOB'

    select * from dba_scheduler_programs
    where owner ='SYS'
    and program_name ='GATHER_STATS_PROG'

    select * from dba_scheduler_schedules
    where owner ='SYS'

    select * from dba_scheduler_window_groups;

    also to check the log,  use the below :

    select * from dba_scheduler_job_run_details
    where owner ='SYS'
    and job_name ='GATHER_STATS_JOB'
    order by 1 desc

    select * from dba_scheduler_job_log
    where owner ='SYS'
    and job_name ='GATHER_STATS_JOB'
    order by 1 desc

    Author Comment

    Thanks Sdstuber and  Mav Kum V I really appreciate you.
    I will let you know once I run these queries.
    LVL 47

    Accepted Solution

    I use the following code to compute statistics:

    ** To run automatically statistics computing on
    ** middnight today and every week after that also on middnight
    ** run in SQL*Plus as user SYS the following script
    ** Write down the number returned by the script.
    ** It should be used to point the queued job
    ** for operations like delete, reschedule, etc.
    ** when using the DBMS_JOB package

    CREATE OR REPLACE PROCEDURE compute_statistics IS
    END compute_statistics;

    set serveroutput on
    set linesize 10000
    variable x number;

    Author Closing Comment

    Thanks It worked.

    Sorry for the late response.
    LVL 73

    Expert Comment

    hmmm, odd answer to accept.

    the first two posts actually answered your question about the 10g automatic gathering of statistics.

    the answer you accepted is the exact opposite, it shows how to do it manually.

    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
    This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now