What are Optimizer Statistics? Databases for Developers: Performance #2

[@abruenin236]

Your way to explain with bricks, toys, cards and whatever makes understanding complicated things so much easier. These are hands down the best trainings videos I know, many thanks.

[@TheMagicofSQL]

Thanks, glad you find these useful!

[@furtivesock]

Love the fact that your videos have all subtitles! It is so rare and helps a lot, really.

[@mohamed.macaloumo9763]

The best explanation on the optimizer I have ever seen! Can't wait to see more! Thanks again Chris to share your knowledge.

[@TheMagicofSQL]

Thanks! Stay tuned, there's more coming :)

[@chennakesavalu5392]

Hi Chris Saxon sir, Your explaining about performance tuning tips good especially explain plan, statistics, cardinality and selectivity. Can you please do the more videos on sql and plsql performance tuning. Thank you very much sir sharing your knowledge...

[@TheMagicofSQL]

Thanks, there are several more videos in this series coming soon. Stay tuned...

[@mostafayahia9647]

First of all i think you deserve more than just a like thank you

[@dheerajvarthi4657]

Thanks a lot 🙏

[@mexicanmomo]

Hi Chris, If I understand correctly oracle or any other DB does these back ground jobs to create these statistic. Since these are data driven jobs, anything can go wrong and that leads to performance issue in queries especially you have large tables, multiple joins etc . How does Oracle deal with this ? or it is left to the one who writes the query ? Even if I tune the query, when records increase or variety of data increases, query can under perform. what is the solution then ?

[@TheMagicofSQL]

If something "goes wrong" with the stats gathering job, this is something you should take up with support. That said, even "perfect" stats have limitations which can lead to incorrect row estimates => slow plans. The reasons for this and what to do about this a large topic that's too big to cover in a comment! Some of the other videos in this performance series & the channel generally discuss various details.

[@norm1124]

Great Video, thank you

[@nada4945]

Great video! How can I set my preferences to gather extended statistics for all tables and thus avoid skews?

[@TheMagicofSQL]

Skew is in the data itself - extended stats just helps the optimizer spot this in some cases If you want to get into the details of this, check the docs: docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/managing-extended-statistics.html#GUID-BD0F0B71-DD8B-44A0-888E-495830FC09A4

[@SunilKumarNoothi]

Hi Chris, two question when we added yellow color - we have four colors and mentioned that 4 colors can't be fit into 3 buckets. a) how come 3 buckets are arrived ? b) when distinct colors increased to 4 in our case (3 to 4) why did # of buckets didn't increased? BTW - feeling great by involving in this Databases for Developers: Performance course.

[@TheMagicofSQL]

It's just for example. By default in Oracle Database you get 254 buckets; the database will keep creating new buckets up to this number. You can override this with your own upper limit (up to 2,048).

[@SunilKumarNoothi]

@@TheMagicofSQL ok Thanks Chris. so technically we can't have more than 2048 buckets. Got it 👍

[@TheMagicofSQL]

Correct, though in most cases it's best to stick with the default of 254

[@vishvendradeepak1419]

@@TheMagicofSQL Hi Chris, if the no. of distinct values is around 1200 then I think we should increase the bucket size. As this will give more accurate estimates.

[@NewYork0110J]

Does table statistics stores min and max values of the column. So when you ask max value of the column of the table. Does query checks into statistics value or do have full table scan again to get the value.

[@TheMagicofSQL]

Yes, the stats record the min/max values for each column. Remember these figures are a snapshot though, so will often be out-of-date. So using these to answer min/max (col) queries will give incorrect results in general! If there's an index on the target column, the database can to a min/max value search of the index - this is very fast.