Hello Amir,
I think these 2 blog posts might answer your question.
-
https://blogs.oracle.com/optimizer/how-does-autosamplesize-work-in-oracle-database-11g
(Section "Effect of auto sample size on index stats gathering")
-
https://blogs.oracle.com/optimizer/how-does-auto_sample_size-work-in-oracle-database-12c
(Section "Effect of auto sample size on index stats gathering")
However if you wanna know in detail you can also trace DBMS_STATS and have a
look at your own (
https://www.pythian.com/blog/options-for-tracing-oracle-dbms_stats/ ;).
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> hat am 12. Juli 2019 um 16:39--
geschrieben:
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