Home  Easy-Code   Forum    Updater 


Not signed in (Sign In)
    • CommentAuthorPaddy
    • CommentTimeDec 2nd 2010
     
    Statistics are a collection of data that describe more details about the database
    and the objects in the database. These are used by the query optimizer to choose
    the best execution plan for each SQL statement, which will inturn depend on the performance of the application .
    So keeping the statistics up to date means a better performance.

    Gathering statistics for a database can be done in two ways.

    1) Manual statistics gathering
    2) Automatic statistics gathering

    In manual statistics gathering we can just execute the below command by login as admin into the database .This will collect stats for entire database.

    a)EXEC DBMS_STATS.gather_database_stats;

    If you want to collect stats for a particular schema or table then we can use

    a)exec dbms_stats.gather_schema_stats (ownname => 'STU_OWNER', estimate_percent => 10, method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);

    b)exec dbms_stats.gather_table_stats(ownname => user, tabname =>v_table , estimate_percent => 100, method_opt => 'for all indexed columns size auto',cascade => TRUE);

    Automatic stats gathering feature is provided by oracle internally, a scheduled job runs every day night it collects the stats for teh whole database.
    to disable this job we can use below command.


    a) exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

    statistics of below objects are ketp up to date by running these jobs


    Table statistics
    Number of rows
    Number of blocks
    Average row length
    Column statistics
    Number of distinct values (NDV) in column
    Number of nulls in column
    Data distribution (histogram)
    Index statistics