How to Read an Execution Plan: Databases for Developers: Performance #1

[@therasammaldaniel5800]

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.

[@TheMagicofSQL]

You're too kind :) Glad I could help

[@Symmetryful]

I just wanted you to know that you’re a natural teacher. And that is really rare!

[@TheMagicofSQL]

Thanks, glad you found this useful!

[@Thedirasa]

He is making this video from his media room. Appreciate your contribution to the world.

[@macpaz]

by far the best video on query performance on youtube. thank you!

[@francisantony12]

This helped me a lot. Thank you for this video

[@TheMagicofSQL]

You're welcome! Glad this is useful

[@kopaczp]

Perfect! Very easy and joyful explanation of (usually) database subjects! Thanks!

[@TheMagicofSQL]

You're welcome, thanks!

[@mostafayahia9647]

Big fan of your work from Egypt.. Thank so much for your effort

[@TheMagicofSQL]

Thanks Mostafa! Stay tuned for more videos over the coming weeks :)

[@lancemartin6]

In the first 8 seconds of your video, I decided to sub. Nice job bro.

[@TheMagicofSQL]

Awesome, glad you find these useful!

[@mustaphachatbi5952]

found your channel recently , did not watch your videos yet but seems interesting and just wanted to encourage you and thank you (y)

[@sanketmandlik]

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.

[@TheMagicofSQL]

What is it that you don't understand about subqueries?

[@sanketmandlik]

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 ☺️

[@TheMagicofSQL]

Cool :)

[@MeerAthil]

Good explanation, easy to understand. Please make videos on oracle locks and how to fix it

[@TheMagicofSQL]

Thanks! What specifically do you want to know about locks? I've got a video discussing issues with update and deadlocks kzbin.info/www/bejne/fJ3Zm2VvitB-htE What else would you like to know?

[@MeerAthil]

@@TheMagicofSQL Thanks for your reply. I'll watch the video. If any doubt I'll comment. 👍

[@Kivencito]

I loved the video! thank you SO much! :)

[@TheMagicofSQL]

You're welcome!

[@jinan.es.]

You're amazing... I mean you really are amazing!

[@TheMagicofSQL]

Thanks, glad you found this useful

[@payalmadhavi7162]

Gr8 job!expecting more vedios on db security.

[@nileshsahastrabuddhe1182]

Thanks sir 🙏🙏🙏 really good explanation

[@nhatquang3807]

can this be applied for Sql server too? anw, thanks for very comprehensive video.

[@TheMagicofSQL]

The principles are the same for all relational databases, though some of the terminology is different

[@rohitpuranikratlami]

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.

[@TheMagicofSQL]

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?

[@sharvarirg984]

@@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.

[@TheMagicofSQL]

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

[@rohitpuranikratlami]

@@TheMagicofSQL Thanks a lot It's much clear to me know 🙂

[@tiglymigly2846]

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...

[@tiagorsacxs1]

You're the best !!!!

[@TheMagicofSQL]

Thanks!

[@masterprattu]

Thanks for the amazing videos m8

[@jorgebedoyarpo]

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?

[@TheMagicofSQL]

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

[@sirknumbskull3418]

Your autotrace looks simple. My is full if index and access prediction. Is there a way to make it more simple?

[@TheMagicofSQL]

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

[@prashanthr130482]

hi , May I know how to find best join order and index for a huge query with a huge explain plan..

[@TheMagicofSQL]

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

[@personalchaoyu8732]

So first question first, what are behind that tinny door ?

[@TheMagicofSQL]

My tiny SQL elves of course ;)

[@albertogonzalez5114]

thanks!

[@kizitoebelechukwu8387]

can you do a video for teradata explain plain

[@TheMagicofSQL]

I work for Oracle, so I won't be covering other databases in-depth

[@sameerp510]

John den fbr pya na

[@caetanogodinat1989]

nice

[@juno7893]

👏🏻👏🏻👏🏻

[@nviknesh8112]

Toshiba and not an Imac.. Great

[@danielskiba6705]

The nodding zipper is flashing and is distracting :'(

[@m.a.6999]

for algorithm

[@solifugo]

:clapping: , :clapping: , :clapping:

[@dothanhlong1847]

.