Oracle 11g: Incremental Global Statistics On Partitioned Tables

Previously I blogged about the new and improved DBMS_STATS.AUTO_SAMPLE_SIZE used to calculate NDV in Oracle 11g and now I wanted to touch on another new feature of DBMS_STATS in 11g: Incremental Global Statistics On Partitioned Tables.

Before Incremental Global Stats (Two-Pass Method)

When DBMS_STATS.GATHER_TABLE_STATS collects statistics on a partitioned table, generally it does so at the partition and table (global) level (the default behavior can be modified by changing the GRANULARITY parameter). This is done in two steps. First, partition level stats are gathered by scanning the partition(s) that have stale or empty stats, then a full table scan is executed to gather the global statistics. As more partitions are added to a given table, the longer the execution time for GATHER_TABLE_STATS, due to the full table scan requited for global stats.

Using Incremental Global Stats (Synopsis-Based Method)

Incremental Global Stats works by collecting stats on partitions and storing a synopsis which is the statistics metadata for that partition and the columns for that partition. This synopsis is stored in the SYSAUX tablespace, but is quite small (only a few kilobytes). Global stats are then created not by reading the entire table, but by aggregating the synopses from each partition. Incremental Global Stats, in conjunction with the new 11g DBMS_STATS.AUTO_SAMPLE_SIZE, yield a significant reduction in the time to collect statistics and produce near perfect accuracy.

Turning On Incremental Global Stats

Incremental Global Stats can only be used for partitioned tables and is activated by this command:

SQL> exec DBMS_STATS.SET_TABLE_PREFS(user,'FOO','INCREMENTAL','TRUE')

-- To see the value for INCREMENTAL for a given table:

SQL> select dbms_stats.get_prefs('INCREMENTAL', tabname=>'FOO') from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL',TABNAME=>'FOO')
--------------------------------------------------
TRUE

You may also use any of the other DBMS_STATS.SET_*_PREFS as well.

A Real-World Example

To demonstrate the benefit of Incremental Global Statistics, I created a range partitioned table consisting of 60 range partitions. The target table starts empty and one million (1,000,000) rows are inserted into a single partition of the table and then statistics are gathered. This is done 60 times, simulating loading 60 one day partitions (one at a time) emulating a daily ETL/ELT process over 60 days.

Incremental_Stats.png

Elapsed Times
Partitions
Incremental=FALSE
Incremental=TRUE
1
00:00:20.36
00:00:21.14
10
00:02:27.25
00:00:37.76
20
00:04:46.23
00:00:49.83
30
00:07:05.47
00:01:01.80
40
00:09:11.09
00:01:23.33
50
00:11:33.18
00:01:30.40
60
00:13:18.15
00:01:40.28
Cumulative Elapsed Time
06:42:21.20
01:00:53.80

As you can see from the chart and the table, without Incremental Global Stats the time to gather stats increases pretty much linearly with the number of partitions, but with Incremental Global Stats the elapse time only slightly increases. The big difference is in the cumulative elapsed time: It takes 6 hours 42 minutes without Incremental Global Stats, but only 1 hour with. Quite a significant savings over time!

Revisiting The Math

For this experiment the time to gather stats without Incremental Global Stats is:
(time to scan & gather for 1 partition) + (time to scan and gather for entire table)
When Incremental Global Stats is used the time to gather stats is:
(time to scan & gather for 1 partition) + (time to aggregate all synopses)

The Diff Test

I exported the stats into a stats table and then ran the diff to compare the two runs. This will show us how comparable the two methods of stats gathering are.

SQL> set long 500000 longchunksize 500000
SQL> select report, maxdiffpct from
     table(dbms_stats.diff_table_stats_in_stattab(user,'CATALOG_SALES','STATS_DEFAULT','STATS_INC'));

REPORT
------------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE	      : CATALOG_SALES
OWNER	      : TPCDS
SOURCE A      : User statistics table STATS_DEFAULT
	      : Statid	   :
	      : Owner	   : TPCDS
SOURCE B      : User statistics table STATS_INC
	      : Statid	   :
	      : Owner	   : TPCDS
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME	SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

CS_BILL_ADDR_SK A   1001152 .000000998 NO   148640  5	 C102  C402  5.9E+07
		B   1001176 .000000998 NO   148613  5	 C102  C402  5.9E+07
CS_BILL_CDEMO_S A   1868160 .000000535 NO   148646  6	 C102  C4025 5.9E+07
		B   1878320 .000000532 NO   148753  6	 C102  C4025 5.9E+07
CS_BILL_CUSTOME A   1942528 .000000514 NO   148104  6	 C102  C403  5.9E+07
		B   1949464 .000000512 NO   148192  6	 C102  C403  5.9E+07
CS_BILL_HDEMO_S A   7200    .000138888 NO   148227  4	 C102  C249  5.9E+07
		B   7200    .000138888 NO   148250  4	 C102  C249  5.9E+07
CS_CALL_CENTER_ A   30	    .033333333 NO   148310  3	 C102  C11F  5.9E+07
		B   30	    .033333333 NO   148272  3	 C102  C11F  5.9E+07
CS_CATALOG_PAGE A   11092   .000090155 NO   148111  5	 C102  C3023 5.9E+07
		B   11092   .000090155 NO   148154  5	 C102  C3023 5.9E+07
CS_EXT_LIST_PRI A   1133824 .000000881 NO   148461  6	 C102  C3036 5.9E+07
		B   1131680 .000000883 NO   148368  6	 C102  C3036 5.9E+07
CS_EXT_WHOLESAL A   394880  .000002532 NO   148842  5	 C102  C302  5.9E+07
		B   394880  .000002532 NO   148772  5	 C102  C302  5.9E+07
CS_ITEM_SK	A   205888  .000004857 NO   0	    5	 C102  C3152 5.9E+07
		B   205408  .000004868 NO   0	    5	 C102  C3152 5.9E+07
CS_LIST_PRICE	A   29896   .000033449 NO   148438  5	 C102  C204  5.9E+07
		B   29896   .000033449 NO   148458  5	 C102  C204  5.9E+07
CS_ORDER_NUMBER A   7151104 .000000139 NO   0	    6	 C102  C4102 5.9E+07
		B   7122072 .000000140 NO   0	    6	 C102  C4102 5.9E+07
CS_PROMO_SK	A   1000    .001       NO   148617  4	 C102  C20B  5.9E+07
		B   1000    .001       NO   148693  4	 C102  C20B  5.9E+07
CS_QUANTITY	A   100     .01        NO   148737  3	 C102  C202  5.9E+07
		B   100     .01        NO   148751  3	 C102  C202  5.9E+07
CS_SHIP_ADDR_SK A   1001088 .000000998 NO   148150  5	 C102  C402  5.9E+07
		B   1001152 .000000998 NO   148235  5	 C102  C402  5.9E+07
CS_SHIP_CDEMO_S A   1870592 .000000534 NO   148918  6	 C102  C4025 5.9E+07
		B   1878272 .000000532 NO   148862  6	 C102  C4025 5.9E+07
CS_SHIP_CUSTOME A   1938816 .000000515 NO   148300  6	 C102  C403  5.9E+07
		B   1948928 .000000513 NO   148309  6	 C102  C403  5.9E+07
CS_SHIP_DATE_SK A   1884    .000530785 NO   148674  6	 C4032 C4032 5.9E+07
		B   1884    .000530785 NO   148608  6	 C4032 C4032 5.9E+07
CS_SHIP_HDEMO_S A   7200    .000138888 NO   148172  4	 C102  C249  5.9E+07
		B   7200    .000138888 NO   148161  4	 C102  C249  5.9E+07
CS_SHIP_MODE_SK A   20	    .05        NO   148437  3	 C102  C115  5.9E+07
		B   20	    .05        NO   148486  3	 C102  C115  5.9E+07
CS_SOLD_DATE_SK A   1595    .000626959 NO   0	    6	 C4032 C4032 5.9E+07
		B   1587    .000630119 NO   0	    6	 C4032 C4032 5.9E+07
CS_WAREHOUSE_SK A   15	    .066666666 NO   148651  3	 C102  C110  5.9E+07
		B   15	    .066666666 NO   148620  3	 C102  C110  5.9E+07
CS_WHOLESALE_CO A   9901    .000100999 NO   149054  4	 C102  C202  5.9E+07
		B   9901    .000100999 NO   149099  4	 C102  C202  5.9E+07

The stats diff shows that for many columns the NDV is identical and the others are statistically equivalent (close enough to be the same). I will certainly be adding this feature to my “conviction must use list” for Oracle 11g.

Further Reading

If you are interested in the bits and bytes of how the synopsis-based method works, I would suggest you read the whitepaper, Efficient and Scalable Statistics Gathering for Large Databases in Oracle 11g that was presented on this topic at SIGMOD 2008.

20 comments

  1. Greg Rahn

    Histograms are (can be) gathered at the partition level and then aggregated to form a global/table level histogram. As long as the same method_opt command is used from the start, it is possible. Only in the scenario where the method_opt changes triggering a new histogram collection on a partition, is it a requirement to look at data from the entire table. This is because not all partitions will have histograms (only the ones where stats have been gathered since the method_opt change). In this case there is a scan of the whole table done using a small sample to create the global histogram.

    Is histogram data stored in the synopsis: No.
    Can histograms on partitions be aggregated to form a global histogram when they exist on each partition: Yes.

    Hopefully that makes it a bit more clear.

  2. Pingback: Log Buffer #106: A Carnival of the Vanities for DBAs
  3. amit poddar

    Hi,

    We have been using new granularity APPROX_GLOBAL AND PARTITION which comes with patch 6526370.

    It aggregates all the global statistics (including histograms) from partition level statistics except NDV. Following is the description from dbmsstat.sql

    --         'APPROX_GLOBAL AND PARTITION' - This option is similar to
    --         'GLOBAL AND PARTITION'. But the global statistics are aggregated
    --         from partition level statistics. It will aggregate all statistics
    --         except number of distinct values for columns and number of distinct
    --         keys of indexes.
    --         The existing histograms of the columns at the table level
    --         are also aggregated.The global statistics are  gathered
    --         (i.e., going back to GLOBAL AND PARTITION behaviour)
    --         if partname argument is null or if the aggregation cannot be done
    --         e.g., statistics of one of the partitions is missing.
    --         This option is useful when you collect statistics for a new
    --         partition added into a range partitioned table (for example,
    --         a table  partitioned by month).
    --         The new data in the partition makes the global statistics stale
    --         (especially the min/max values of the partitioning column).
    --         This stale global statistics may cause suboptimal plans.
    --         In this scenario, users can collect statistics for the newly added
    --         partition with 'APPROX_GLOBAL AND PARTITION' option so that the
    --         global statistics will reflect the newly added range.
    --         This option will take less time than 'GLOBAL AND PARTITION' option
    --         since the global statistics are aggregated from underlying
    --         partition level statistics.
    --         Note that, if you are using APPROX_GLOBAL AND PARTITION,
    --         you still  need to collect global statistics (with granularity =
    --         'GLOBAL' option)
    --         when there is substantial amount of change at the table level.
    --         For example you added 10% more data to the table.
    --         This is needed to get the correct number of distinct values/keys
    --         statistic at table level.
    
  4. Pingback: Kerry Osborne’s Oracle Blog » Blog Archive I’ll Gladly Pay You Tuesday for a Hamburger Today - Kerry Osborne’s Oracle Blog
  5. Pingback: //TheOracles בלוג המומחים של אורקל » ארכיון » איסוף סטטיסטיקות על טבלאות ב-11g
  6. Pingback: Data Warehousing and Statistics in Oracle 11g – incremental global statistics « RNM
  7. DBA

    I tested the incremental stats feature with 11.1.0.7 version on Linux 64 bit OS. Sometimes it works, sometimes it doesn’t. And the only thing I have been able to find so far is that if you have a Primary key (with a global index) on a partitioned table that is not part of the partition key (e.g. in OLTP), the incremental stats don’t work. I have gathered stats on a table back to back that was set to use incremental stats. It gathered stats on all the partitions all over again the second time even when there was not a single DML that happened in between the two stat gathering. I have seen a few bugs about this on Mysupport website. Have you anything to share about this phenomenon?

  8. DBA

    Here is the test case. Watch for the last_analyzed column.

    
    SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH12:MI:SS AM';
    
    Session altered.
    
    SQL> 
    SQL> DROP TABLE t PURGE;
    
    Table dropped.
    
    SQL> prompt ==>Create a partitioned table T using interval partitioning
    ==>Create a partitioned table T using interval partitioning
    SQL> 
    SQL> CREATE TABLE t (
      2  id NUMBER CONSTRAINT pk_t PRIMARY KEY,
      3  sample_time DATE,
      4  description VARCHAR2 (100)
      5  )
      6  PARTITION BY RANGE (sample_time)
      7    INTERVAL ( NUMTODSINTERVAL ('1',
      8  				   'DAY') )
      9    (PARTITION p1
     10  	  VALUES LESS THAN
     11  	    (TO_DATE ('2011-04-01',
     12  		      'YYYY-MM-DD')));
    
    Table created.
    
    SQL> 
    SQL> prompt ==>Check the preferences for the table
    ==>Check the preferences for the table
    SQL> 
    SQL>   SELECT table_name,
      2  	      DBMS_STATS.get_prefs (pname   => 'INCREMENTAL',
      3  				    ownname => USER,
      4  				    tabname => table_name)
      5  		incremental,
      6  	      DBMS_STATS.get_prefs (pname   => 'GRANULARITY',
      7  				    ownname => USER,
      8  				    tabname => table_name)
      9  		granularity,
     10  	      DBMS_STATS.get_prefs (pname   => 'PUBLISH',
     11  				    ownname => USER,
     12  				    tabname => table_name)
     13  		publish,
     14  	      DBMS_STATS.get_prefs (pname   => 'ESTIMATE_PERCENT',
     15  				    ownname => USER,
     16  				    tabname => table_name)
     17  		estimate_percent
     18  	 FROM user_tables
     19  	WHERE table_name = 'T'
     20  ORDER BY table_name;
    
    TABLE_NAME|INCREMENTAL |GRANULARITY |PUBLISH     |ESTIMATE_PERCENT
    ----------|------------|------------|------------|------------------------------
    T         |FALSE       |AUTO        |TRUE        |DBMS_STATS.AUTO_SAMPLE_SIZE
    
    SQL> 
    SQL> prompt ==>Set table to use incremental stats
    ==>Set table to use incremental stats
    SQL> 
    SQL> BEGIN
      2    DBMS_STATS.set_table_prefs (ownname => USER,
      3  				   tabname => 'T',
      4  				   pname   => 'INCREMENTAL',
      5  				   pvalue  => 'TRUE');
      6  END;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> prompt ==>Check the preferences for the table
    ==>Check the preferences for the table
    SQL> 
    SQL>   SELECT table_name,
      2  	      DBMS_STATS.get_prefs (pname   => 'INCREMENTAL',
      3  				    ownname => USER,
      4  				    tabname => table_name)
      5  		incremental,
      6  	      DBMS_STATS.get_prefs (pname   => 'GRANULARITY',
      7  				    ownname => USER,
      8  				    tabname => table_name)
      9  		granularity,
     10  	      DBMS_STATS.get_prefs (pname   => 'PUBLISH',
     11  				    ownname => USER,
     12  				    tabname => table_name)
     13  		publish,
     14  	      DBMS_STATS.get_prefs (pname   => 'ESTIMATE_PERCENT',
     15  				    ownname => USER,
     16  				    tabname => table_name)
     17  		estimate_percent
     18  	 FROM user_tables
     19  	WHERE table_name = 'T'
     20  ORDER BY table_name;
    
    TABLE_NAME|INCREMENTAL |GRANULARITY |PUBLISH     |ESTIMATE_PERCENT
    ----------|------------|------------|------------|------------------------------
    T         |TRUE        |AUTO        |TRUE        |DBMS_STATS.AUTO_SAMPLE_SIZE
    
    SQL> 
    SQL> prompt ==>Insert 2 rows
    ==>Insert 2 rows
    SQL> 
    SQL> INSERT INTO t
      2  	  VALUES (1, SYSDATE - 5, 'A');
    
    1 row created.
    
    SQL> 
    SQL> INSERT INTO t
      2  	  VALUES (2, SYSDATE - 4, 'A');
    
    1 row created.
    
    SQL> 
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> 
    SQL> SELECT * FROM t;
    
            ID|SAMPLE_TIME            |DESCRIPTION
    ----------|-----------------------|------------
             1|06-APR-2011 12:21:31 PM|A
             2|07-APR-2011 12:21:31 PM|A
    
    SQL> 
    SQL> BEGIN
      2    DBMS_STATS.gather_table_stats (ownname	       => USER,
      3  				      tabname	       => 'T',
      4  				      estimate_percent => DBMS_STATS.auto_sample_size,
      5  				      method_opt       => 'FOR ALL COLUMNS SIZE 1',
      6  				      granularity      => 'AUTO',
      7  				      degree	       => 2,
      8  				      cascade	       => TRUE,
      9  				      no_invalidate    => TRUE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> SELECT table_name, num_rows, last_analyzed
      2    FROM user_tables
      3   WHERE table_name = 'T';
    
    TABLE_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    T         |         2|11-APR-2011 12:21:32 PM
    
    SQL> 
    SQL>   SELECT table_name,
      2  	      partition_name,
      3  	      partition_position,
      4  	      num_rows,
      5  	      last_analyzed
      6  	 FROM user_tab_partitions
      7  	WHERE table_name = 'T'
      8  ORDER BY partition_position;
    
    TABLE_NAME|PARTITION_NAME|PARTITION_POSITION|  NUM_ROWS|LAST_ANALYZED
    ----------|--------------|------------------|----------|-----------------------
    T         |P1            |                 1|         0|11-APR-2011 12:21:31 PM
    T         |SYS_P123420   |                 2|         1|11-APR-2011 12:21:31 PM
    T         |SYS_P123421   |                 3|         1|11-APR-2011 12:21:31 PM
    
    SQL> 
    SQL> SELECT index_name, num_rows, last_analyzed
      2    FROM user_indexes
      3   WHERE table_name = 'T';
    
    INDEX_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    PK_T      |         2|11-APR-2011 12:21:32 PM
    
    SQL> 
    SQL> EXEC dbms_lock.sleep(2);
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> prompt ==>Insert 2 rows
    ==>Insert 2 rows
    SQL> 
    SQL> INSERT INTO t
      2  	  VALUES (3, SYSDATE - 3, 'A');
    
    1 row created.
    
    SQL> 
    SQL> INSERT INTO t
      2  	  VALUES (4, SYSDATE - 2, 'A');
    
    1 row created.
    
    SQL> 
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> 
    SQL> SELECT * FROM t;
    
            ID|SAMPLE_TIME            |DESCRIPTION
    ----------|-----------------------|------------
             1|06-APR-2011 12:21:31 PM|A
             2|07-APR-2011 12:21:31 PM|A
             3|08-APR-2011 12:21:34 PM|A
             4|09-APR-2011 12:21:34 PM|A
    
    SQL> 
    SQL> BEGIN
      2    DBMS_STATS.gather_table_stats (ownname	       => USER,
      3  				      tabname	       => 'T',
      4  				      estimate_percent => DBMS_STATS.auto_sample_size,
      5  				      method_opt       => 'FOR ALL COLUMNS SIZE 1',
      6  				      granularity      => 'AUTO',
      7  				      degree	       => 2,
      8  				      cascade	       => TRUE,
      9  				      no_invalidate    => TRUE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> SELECT table_name, num_rows, last_analyzed
      2    FROM user_tables
      3   WHERE table_name = 'T';
    
    TABLE_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    T         |         4|11-APR-2011 12:21:35 PM
    
    SQL> 
    SQL>   SELECT table_name,
      2  	      partition_name,
      3  	      partition_position,
      4  	      num_rows,
      5  	      last_analyzed
      6  	 FROM user_tab_partitions
      7  	WHERE table_name = 'T'
      8  ORDER BY partition_position;
    
    TABLE_NAME|PARTITION_NAME|PARTITION_POSITION|  NUM_ROWS|LAST_ANALYZED
    ----------|--------------|------------------|----------|-----------------------
    T         |P1            |                 1|         0|11-APR-2011 12:21:31 PM
    T         |SYS_P123420   |                 2|         1|11-APR-2011 12:21:34 PM
    T         |SYS_P123421   |                 3|         1|11-APR-2011 12:21:34 PM
    T         |SYS_P123422   |                 4|         1|11-APR-2011 12:21:34 PM
    T         |SYS_P123423   |                 5|         1|11-APR-2011 12:21:34 PM
    
    SQL> 
    SQL> SELECT index_name, num_rows, last_analyzed
      2    FROM user_indexes
      3   WHERE table_name = 'T';
    
    INDEX_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    PK_T      |         4|11-APR-2011 12:21:35 PM
    
    SQL> 
    SQL> EXEC dbms_lock.sleep(2);
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> prompt ==>No insert here, just gather stats
    ==>No insert here, just gather stats
    SQL> 
    SQL> BEGIN
      2    DBMS_STATS.gather_table_stats (ownname	       => USER,
      3  				      tabname	       => 'T',
      4  				      estimate_percent => DBMS_STATS.auto_sample_size,
      5  				      method_opt       => 'FOR ALL COLUMNS SIZE 1',
      6  				      granularity      => 'AUTO',
      7  				      degree	       => 2,
      8  				      cascade	       => TRUE,
      9  				      no_invalidate    => TRUE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> SELECT table_name, num_rows, last_analyzed
      2    FROM user_tables
      3   WHERE table_name = 'T';
    
    TABLE_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    T         |         4|11-APR-2011 12:21:38 PM
    
    SQL> 
    SQL>   SELECT table_name,
      2  	      partition_name,
      3  	      partition_position,
      4  	      num_rows,
      5  	      last_analyzed
      6  	 FROM user_tab_partitions
      7  	WHERE table_name = 'T'
      8  ORDER BY partition_position;
    
    TABLE_NAME|PARTITION_NAME|PARTITION_POSITION|  NUM_ROWS|LAST_ANALYZED
    ----------|--------------|------------------|----------|-----------------------
    T         |P1            |                 1|         0|11-APR-2011 12:21:31 PM
    T         |SYS_P123420   |                 2|         1|11-APR-2011 12:21:37 PM
    T         |SYS_P123421   |                 3|         1|11-APR-2011 12:21:37 PM
    T         |SYS_P123422   |                 4|         1|11-APR-2011 12:21:37 PM
    T         |SYS_P123423   |                 5|         1|11-APR-2011 12:21:37 PM
    
    SQL> 
    SQL> SELECT index_name, num_rows, last_analyzed
      2    FROM user_indexes
      3   WHERE table_name = 'T';
    
    INDEX_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    PK_T      |         4|11-APR-2011 12:21:38 PM
    
    SQL> 
    SQL> EXEC dbms_lock.sleep(2);
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> prompt ==>No insert here, just gather stats
    ==>No insert here, just gather stats
    SQL> 
    SQL> BEGIN
      2    DBMS_STATS.gather_table_stats (ownname	       => USER,
      3  				      tabname	       => 'T',
      4  				      estimate_percent => DBMS_STATS.auto_sample_size,
      5  				      method_opt       => 'FOR ALL COLUMNS SIZE 1',
      6  				      granularity      => 'AUTO',
      7  				      degree	       => 2,
      8  				      cascade	       => TRUE,
      9  				      no_invalidate    => TRUE);
     10  END;
     11  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    SQL> SELECT table_name, num_rows, last_analyzed
      2    FROM user_tables
      3   WHERE table_name = 'T';
    
    TABLE_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    T         |         4|11-APR-2011 12:21:41 PM
    
    SQL> 
    SQL>   SELECT table_name,
      2  	      partition_name,
      3  	      partition_position,
      4  	      num_rows,
      5  	      last_analyzed
      6  	 FROM user_tab_partitions
      7  	WHERE table_name = 'T'
      8  ORDER BY partition_position;
    
    TABLE_NAME|PARTITION_NAME|PARTITION_POSITION|  NUM_ROWS|LAST_ANALYZED
    ----------|--------------|------------------|----------|-----------------------
    T         |P1            |                 1|         0|11-APR-2011 12:21:31 PM
    T         |SYS_P123420   |                 2|         1|11-APR-2011 12:21:41 PM
    T         |SYS_P123421   |                 3|         1|11-APR-2011 12:21:41 PM
    T         |SYS_P123422   |                 4|         1|11-APR-2011 12:21:41 PM
    T         |SYS_P123423   |                 5|         1|11-APR-2011 12:21:41 PM
    
    SQL> 
    SQL> SELECT index_name, num_rows, last_analyzed
      2    FROM user_indexes
      3   WHERE table_name = 'T';
    
    INDEX_NAME|  NUM_ROWS|LAST_ANALYZED
    ----------|----------|-----------------------
    PK_T      |         4|11-APR-2011 12:21:41 PM
    
    SQL> 
    SQL> exit
    
  9. ghassan

    DBA,
    I tried your testcase on an 11.2.0.2 (don’t have access to 11.1), and it does not reproduce.
    on your version, did you try on an 11.1, without specifying the ‘granularity’?

  10. DBA

    ghassan,
    I did not try that, but granularity by default is AUTO. Anyway it’s good to know that it is fixed in 11.2.0.2.

  11. DBA

    Greg, did you have a chance to test my small test case on 11.1? We applied SP9 to our 11.1.0.7 version and I am still not seeing the incremental stats work as expected.

  12. Pingback: איסוף סטטיסטיקות על טבלאות ב-11g | //TheOracles בלוג המומחים של אורקל
  13. Pingback: Log Buffer #106: A Carnival of the Vanities for DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s