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) ;
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,
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Jul 12 2019 - 16:39:47 CEST