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
