Index sample size when gathering table statistics

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 12 Jul 2019 14:39:47 +0000
Message-ID: <BL0PR11MB3169332444C8E8AB190AE862F4F20_at_BL0PR11MB3169.namprd11.prod.outlook.com>

Hi,
I am using the following command to gather statistics on a table in database versions 11.2.0.4 and 12.1.0.2: exec dbms_stats.gather_table_stats(ownname=>'ONT',tabname=>'OE_ORDER_LINES_HISTORY',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, degree=>4, cascade=>true) ;

What I have noticed is that in 11.2.0.4, it is not collecting index statistics with 100% sample size for all indexes. In this case, the sample size for index OE_ORDER_LINES_HISTORY_N1 is not ~18%:

TABLE_NAME                     'TABLESTATISTICS'              LAST_ANALYZED       NUM_ROWS  SAMPLE_SIZE


------------------------------ ------------------------------ --------------- ------------ ------------


OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N1      12-JUL-19          1,072,998      194,663
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N2      12-JUL-19          1,099,219    1,099,219
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N4      12-JUL-19          1,099,219    1,099,219
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N5      12-JUL-19                  0            0
OE_ORDER_LINES_HISTORY         Table Statistics               12-JUL-19          1,099,219    1,099,219

However, in 12.1.0.2, it is collecting statistics on all indexes with 100% sample size:
TABLE_NAME                     INDEX_NAME                     LAST_ANALYZED       NUM_ROWS  SAMPLE_SIZE


------------------------------ ------------------------------ --------------- ------------ ------------


OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N1      12-JUL-19            821,585      821,585
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N2      12-JUL-19            821,585      821,585
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N4      12-JUL-19            821,585      821,585
OE_ORDER_LINES_HISTORY         OE_ORDER_LINES_HISTORY_N5      12-JUL-19                  0            0

I initially noticed this while working on another database which was also 11.2.0.4 where the same command was collecting very little percentage of sample size for indexes (obfuscating names here):
TABLE_NAME           INDEX_NAME                     LAST_ANAL     NUM_ROWS  SAMPLE_SIZE SAMPLE_PCT


-------------------- ------------------------------ --------- ------------ ------------ ----------


TABLE1               INDEX1                       10-JUL-19   13,793,500      407,250       2.95
                     INDEX2                         10-JUL-19   14,721,257      305,894       2.08
                     INDEX3                        10-JUL-19   14,057,991      508,858       3.62
                     INDEX4                        10-JUL-19   14,497,801      301,338       2.08
                     INDEX5                         10-JUL-19        6,371        6,371     100.00
                     INDEX6                        10-JUL-19   13,977,053      221,025       1.58
                     INDEX7                        10-JUL-19   13,929,165      434,294       3.12

I am curious to know why Oracle is collecting statistics on indexes this way in 11.2.0.4 where it is using a sample size of 100% on some indexes and a very little sample size on the other.

Thank you,
Amir

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jul 12 2019 - 16:39:47 CEST