What are Optimizer Statistics? Databases for Developers: Performance #2
Vložit
- čas přidán 8. 07. 2024
- To choose an execution plan, the optimizer needs to estimate how many rows it'll read from each table.
But what does it base these estimates on?
The table statistics!
Watch this video to learn what these are and how they affect execution plans.
Need help with SQL?
Ask us over on AskTOM: asktom.oracle.com
Twitter: / chrisrsaxon
Daily SQL Twitter tips: / sqldaily
All Things SQL blog: blogs.oracle.com/sql/
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle.com/
============================
The Magic of SQL with Chris Saxon
Copyright © 2020 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement. - Věda a technologie
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.
Thanks, glad you find these useful!
Love the fact that your videos have all subtitles! It is so rare and helps a lot, really.
The best explanation on the optimizer I have ever seen!
Can't wait to see more!
Thanks again Chris to share your knowledge.
Thanks! Stay tuned, there's more coming :)
First of all i think you deserve more than just a like thank you
Great Video, thank you
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...
Thanks, there are several more videos in this series coming soon. Stay tuned...
Thanks a lot 🙏
Great video! How can I set my preferences to gather extended statistics for all tables and thus avoid skews?
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
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.
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.
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 ?
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.
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.
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).
@@TheMagicofSQL ok Thanks Chris. so technically we can't have more than 2048 buckets. Got it 👍
Correct, though in most cases it's best to stick with the default of 254
@@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.