2 Replies Latest reply: Jul 31, 2013 3:36 PM by SGUN RSS

    Incremental Stats not being collected.

    SGUN

      Hi all,

       

      I'm trying to tune my pl/sql code and found that below statement is taking more and more time for every run as more records are getting added to the table.

       

      DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'DW_OWNER', TABNAME => 'TABLE_XYZ', CASCADE => true, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => DBMS_STATS.AUTO_DEGREE, GRANULARITY => 'AUTO', METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

       

      'Table_XYZ' is basically partitioned by month and has 150835365 rows. I'm on 11g.

      As I researched I found that every-time I run the code STATS are being collected for all partitions.

      So I tried turning on Incremental STATS using below statement:

       

      EXEC DBMS_STATS.SET_TABLE_PREFS('DW_OWNER','Table_XYZ','INCREMENTAL','TRUE');

       

      But couldn't turn it on.

      select DBMS_STATS.get_prefs(ownname=> 'DW_OWNER',tabname=> 'TABLE_XYZ',pname=>'INCREMENTAL') incremental from dual;

      INCREMENTAL still shows FALSE and the STATS are still being collected for all the partitions.

       

      Please help.

        • 1. Re: Incremental Stats not being collected.
          Etbin

          Maybe

          http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_stats.htm#BEIBJJHC says;

          INCREMENTAL - Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

          • INCREMENTAL value for the partitioned table is set to TRUE;
          • PUBLISH value for the partitioned table is set to TRUE;
          • User specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.

           

          Regards

           

          Etbin

          • 2. Re: Incremental Stats not being collected.
            SGUN

            Thanks for responding Etbin. I verified and found that PUBLISH is set to TRUE by default.

             

            SELECT owner, table_name, DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'PUBLISH') PUBLISH

            FROM dba_tables

            WHERE TABLE_NAME = 'TABLE_XYZ'

            AND OWNER        = 'DW_OWNER';

            Answer TRUE.

             

            BUT I still couldn't turn INCREMENTAL as TRUE. I set it true but still showing FALSE.

            SELECT owner, table_name, DBMS_STATS.get_prefs(ownname=>USER,tabname=>table_name,pname=>'INCREMENTAL') incremental

            FROM dba_tables

            WHERE TABLE_NAME = 'TABLE_XYZ'

            AND OWNER        = 'DW_OWNER';

            Answer FALSE.

             

            Anyways I created new table with same definition and tested to find that it is turning ON.

            I'm leaving with no answer to this issue. I think something is happening behind the scenes for the old table.