How to Read an Execution Plan: Databases for Developers: Performance #1
ฝัง
- เผยแพร่เมื่อ 2 มิ.ย. 2024
- An execution plan is one of the core tools for analyzing SQL performance.
But how do you get one? And how do you make sense of it once you've got one?
Watch this video to learn how to get and understand plans using Oracle SQL Developer.
For more on execution plans, read:
How to Readn an Execution Plan: blogs.oracle.com/oraclemagazi...
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 © 2018 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. - วิทยาศาสตร์และเทคโนโลยี
You're the best SQL teacher we have ever seen. The way you explain with examples is easy for a layman too to understand. Thank you very much for everything you offered to us so far. You should be global reach.
You're too kind :) Glad I could help
by far the best video on query performance on youtube. thank you!
found your channel recently , did not watch your videos yet but seems interesting and just wanted to encourage you and thank you (y)
He is making this video from his media room. Appreciate your contribution to the world.
Perfect! Very easy and joyful explanation of (usually) database subjects! Thanks!
You're welcome, thanks!
Big fan of your work from Egypt.. Thank so much for your effort
Thanks Mostafa! Stay tuned for more videos over the coming weeks :)
Thanks sir 🙏🙏🙏 really good explanation
Thanks for the amazing videos m8
In the first 8 seconds of your video, I decided to sub. Nice job bro.
Awesome, glad you find these useful!
I just wanted you to know that you’re a natural teacher. And that is really rare!
Thanks, glad you found this useful!
I loved the video! thank you SO much! :)
You're welcome!
Gr8 job!expecting more vedios on db security.
Working on PLSQL since 3 yrs. This is much needed skill. Eagerly waiting for next video. Also I didn't understand that subquery execution part.
What is it that you don't understand about subqueries?
Scalar subquery. I didn't see that query earlier. Now I watched it again it crisp clear. Pardon me.
I will open my laptop and get hands dirty with autotrace.
Looking forward to great learnings
Good day ☺️
Cool :)
You're amazing... I mean you really are amazing!
Thanks, glad you found this useful
Good explanation, easy to understand. Please make videos on oracle locks and how to fix it
Thanks!
What specifically do you want to know about locks?
I've got a video discussing issues with update and deadlocks th-cam.com/video/Flvj29UkKPo/w-d-xo.html
What else would you like to know?
@@TheMagicofSQL Thanks for your reply. I'll watch the video. If any doubt I'll comment. 👍
Thank you very much.......your videos are very useful to me.... I want to ask yo something... I am interested in watching your videos about performance, especially about execution plans....I see that you have some videos about that theme.... In what order do you suggest I see them?
Easiest is to follow the Databases for Developers: Performance playlist
If you want something more in-depth, I've built a free course around these videos which includes example scripts and quizzes. Join this at:
devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
thanks!
can this be applied for Sql server too? anw, thanks for very comprehensive video.
The principles are the same for all relational databases, though some of the terminology is different
You're the best !!!!
Thanks!
Hello Sir,
I have one doubt please guide me:
In the video at 6:25, number of rows from cuddly toys it's showing 5 in LAST_OUTPUT_ROWS column ,don't it should be 3. Later then 9 rows from pen make that to 7.Got confused here so I think there is some logic which I am missing so wanted to know whats the logic to arrive to those numbers.
LAST_OUTPUT_ROWS is how many rows this operation returns.
The query fetched three rows from COLOURS
It then fetched five rows from CUDDLY_TOYS and joined these to COLOURS
Only three rows from CUDDLY_TOYS matched a row in COLOURS - two were discarded. Which is why the HASH JOIN directly above COLOURS has three LAST_OUTPUT_ROWS
Does this help explain it?
@@TheMagicofSQL Those 3 rows will be matched with 9 rows in PEN,only 7 matched,So LAST_OUTPUT_ROWS has 7. Then those 7 will be matched with 24 rows in brick.
How did 56 came as LAST_OUTPUT_ROWS?
Can you put table contents in description?
Which join will be performed (INNER JOIN)?
select * from four_table_join? What is four_table_join? (I have seen select * from single_table. I have seen select * from A inner join B on A.x=B.y; But have not seen select * from four_table_join )Sorry to expect more basic thing from you.
Please help.
FOUR_TABLE_JOIN is a view that contains all the joins.
Some of those 7 rows from the hash join matched the same rows in BRICKS. For example, rows 1 & 2 from the join both match BRICKS rows 1 & 2, giving 4 in total
This video is taken from my free SQL performance tuning class - to understand this further I suggest you take it
devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
@@TheMagicofSQL Thanks a lot It's much clear to me know 🙂
Agree with Rohit, the provided example of four_table_join is not that clear and maybe for some, even can be found confusing.
Probably it is because the picture you providing (3:49), about that we have 3-colors, 3-toys, 11-bricks ...etc. and saying that we try to join all of them by color, is not what you later on showing in the details of four_table_join execution plan (6:29). four_table_join and it's data is a black box for a viewer, and thus it is hard for a beginner to comprehend how previously 7 resulted rows, hashjoined with 24 rows of bricks became 56, or 3 rows joined with 9 became 7...
Your autotrace looks simple. My is full if index and access prediction. Is there a way to make it more simple?
You can change what appears in the output by going to Tools -> Preferences -> Autotrace. There you'll find a wealth of properties you can enable/disable
hi , May I know how to find best join order and index for a huge query with a huge explain plan..
In general it's best to access the table that returns the fewest rows first. Indexes are effective when they locate few rows in the table.
I discuss these topics further in my free course, Databases for Developers: Performance
Sign up at devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
nice
👏🏻👏🏻👏🏻
So first question first, what are behind that tinny door ?
My tiny SQL elves of course ;)
can you do a video for teradata explain plain
I work for Oracle, so I won't be covering other databases in-depth
John den fbr pya na
for algorithm
:clapping: , :clapping: , :clapping:
Toshiba and not an Imac.. Great
The nodding zipper is flashing and is distracting :'(
.