7 Replies Latest reply: Sep 15, 2008 4:02 PM by 529937 RSS

    How to disable automatic statistics collections on tables

    jdi
      Hi

      I am using Oracle 10g and we have few tables which are frequently truncated and news rows added to it. Oracle automatically analyzes the table by some means which collects statistics of the table but at the wrong time(when the table is empty). This makes my query to do a full table scan rather using indexes since the statistics was collected when the table was empty.Could any one please let me know how to disable the automatic statistics collection feature of Oracle?

      Cheers
      Anantha PV
        • 1. Re: How to disable automatic statistics collections on tables
          Paul M.
          See http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#i41788
          • 2. Re: How to disable automatic statistics collections on tables
            kgamerdi
            Hi Anantha,

            you can lock the statistics with LOCK_SCHEMA_STATS or LOCK_TABLE_STATS:

            http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1043184

            Then calculate the statistics with your own procedure using GATHER_SCHEMA_STATS or GATHER_TABLE_STATS togehter with the parameter FORCE=TRUE.
            FORCE will overwrite the lock.

            --Klaus                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
            • 3. Re: How to disable automatic statistics collections on tables
              jdi
              I will implement the steps as you suggested. Thank you all for your quick response. Much Appreciated!!!
              • 4. Re: How to disable automatic statistics collections on tables
                Randolf Geist
                Hi

                I am using Oracle 10g and we have few tables which
                are frequently truncated and news rows added to it.
                Oracle automatically analyzes the table by some means
                which collects statistics of the table but at the
                wrong time(when the table is empty). This makes my
                query to do a full table scan rather using indexes
                since the statistics was collected when the table was
                empty.Could any one please let me know how to disable
                the automatic statistics collection feature of
                Oracle?
                First of all I think it's important that you understand why Oracle collects statistics on these tables: Because it considers the statistics of the object to be missing or stale. So if you just disable the statistics gathering on these tables then you won't have statistics at all or outdated statistics.

                So as said by the previous posts you should gather the statistics manually yourself anyway. If you do so right after loading the data into the truncated table, you don't need to disable the automatic statistics gathering as it only processes objects that are stale or don't have statistics at all.

                If you still think that you need to disable it there are several ways to accomplish it:

                As already mentioned, for particular objects you can lock the statistics using DBMS_STATS.LOCK_TABLE_STATS, or for a complete schema using DBMS_STATS.LOCK_SCHEMA_STATS. Then these statistics won't be touched by the automatic gathering job. You still can gather statistics using the FORCE=>true option of the GATHER__STATS procedures.

                If you want to change the automatic gathering job that it only gathers statistics on objects owned by Oracle (data dictionary, AWR etc.), then you can do so by calling DBMS_STATS.SET_PARAM('AUTOSTATS_TARGET', 'ORACLE'). This is the recommended method.

                If you disable the schedule job as mentioned in the documentation by calling DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB') then no statistics at all will be gathered automatically, causing your data dictionary statistics to be become stale over time, which could lead to suboptimal performance of queries on the data dictionary.

                All this applies to Oracle 10.2, some of the features mentioned might not be available in Oracle 10.1 (as you haven't mentioned your version of 10g).

                Regards,
                Randolf

                Oracle related stuff blog:
                http://oracle-randolf.blogspot.com/

                SQLTools++ for Oracle:
                http://www.sqltools-plusplus.org:7676/
                http://sourceforge.net/projects/sqlt-pp/
                • 5. Re: How to disable automatic statistics collections on tables
                  513339
                  hi

                  Randolf is right .......

                  thank you for clearing ....the concept of Stats collection...

                  now i know how much they are important from oracle DB prospective...

                  CHeers
                  • 6. Re: How to disable automatic statistics collections on tables
                    659905
                    Hello all,

                    I would truly appreciate some help...This is my first post on OTN. This is an issue on an Oralce 10.2.0.4 data warehouse on Linux 64 bit.
                    I have locked a few volatile tables with a representative number of rows (10-50 million) using dbms_stats.lock_table_stats. I verified that they were locked by querying the user_tab_statistics table. I run a process that inserts thousands of records into these tables and found that the user_tab_statistics.stale_stats column is set to 'YES' for the tables whose stats were supposedly locked. Oracle SQL Advisor also recognized these tables as having stale statistics and the execution plan uses a full table scan instead of the proper index it has used before.

                    Thanks a bunch,
                    Gladys
                    • 7. Re: How to disable automatic statistics collections on tables
                      529937
                      BEGIN
                      DBMS_SCHEDULER.DISABLE('SYS.GATHER_STATS_JOB');
                      END;
                      /



                      select JOB_NAME, ENABLED, STATE, LAST_START_DATE from DBA_SCHEDULER_JOBS;