Logo Background

Gather Schema Statistics In Oracle

  • By on August 13, 2010 | No Comments

    Looking way to optmize database by running gather schema statistics in Oracle? You as DBA can generate statistics that quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions.

    The cost-based optimization (CBO) approach uses these statistics to calculate the selectivity of prediction and to estimate the cost of each execution plan.

    The optimizer uses the selectivity of a predicate to estimate the cost of a particular access method and to determine the optimal join order and join method.

    You should gather statistics periodically for objects where the statistics become stale over time because of changing data volumes or changes in column values.

    Gather Schema Stats Syntax

    In Oracle E-Business Suite, you can run the concurrent request name Gather Schema Statistics in System Administrator. This is similar to running the DBMS_STATS.gather_schema_stats from Oracle database.

    SQL> EXEC DBMS_STATS.gather_schema_stats(ownname => 'APPLSYS', estimate_percent => 99);

    There’s one time we faced the error when we run the Gather Schema Statistics for APPLSYS.

    IN GATHER_SCHEMA_STATS , schema_name= APPLSYS percent= 99 
    degree = 9 internal_flag= NOBACKUP
    Error #1: ERROR: While GATHER_TABLE_STATS:
    object_name=APPLSYS.AQ$_WF_CONTROL_P***
    ORA-20005: object statistics are locked (stattype = ALL)***
    Error #2: ERROR: While GATHER_TABLE_STATS:
    object_name=APPLSYS.WF_NOTIFICATION_OUT***
    ORA-20005: object statistics are locked (stattype = ALL)***

    According to some of the notes, this can happen with Advance Queue table in Oracle Database 10g.

    If a queue is created or recreated during the upgrade, automatic statistics gather is locked (disabled) on these queue.

    You can use the following statement can be used to check the tables which have statistics locked.

    SQL> SELECT owner, TABLE_NAME, stattype_locked FROM dba_tab_statistics 
    WHERE stattype_locked IS NOT NULL;

    You can run this command as below to unlock the statistics but please confirm with Oracle Support before proceed.

    SQL> EXEC dbms_stats.unlock_schema_stats ('APPLSYS'); 
    SQL> EXEC dbms_stats.unlock_table_stats('APPLSYS', 'WF_NOTIFICATION_OUT');

    Most Oracle experts only recommend scheduled re-analysis for highly dynamic databases.

    Previous
    Next
    » Managing v$log_history In Oracle Database
Leave a Comment