Thank you Stefan. This is exactly what I was looking for.
Thanks
Amir
-----Original Message-----
From: Stefan Koehler <contact@xxxxxxxx>
Sent: Saturday, July 13, 2019 5:29 AM
To: Hameed, Amir <Amir.Hameed@xxxxxxxxx>; ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Index sample size when gathering table statistics
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