What to Look for in Execution Plans: Databases for Developers: Performance #3

You've got the plan for your SQL statement. But what do you need to change to make it faster?
This video shows you how to add I/O information to the plan. This enables you to see which steps consume the most resources, and thus where you need to focus your tuning efforts.
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.c...
Test your SQL Skills on the Oracle Dev Gym: devgym.oracle....
============================
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.

[@chocOneOOne]

as usual Chris' lego game is on point

[@atulgupta9301]

Very well explanation

[@amitkaushik3569]

Nice explanation.

[@albertogonzalez5114]

very useful, thanks!

[@VEERSINGH-er9so]

Great explanation !

[@a.useronly2266]

Amazing

[@hindimotivation5231]

I am not able to understand the autotrace part where view part individual elapsed time is 728 micro seconds. How this was calculated , Please elaborate. Thankyou.

[@hindimotivation5231]

One more doubt , in 3rd sort operation why it dont have any elpsed time as you said in video.

[@TheMagicofSQL]

I'm not sure exactly what you're referring to - please clarify. In any case, this value comes from the database measuring how long the operation took.

[@TheMagicofSQL]

Again, I'm not sure exactly what you're referring to.

[@ikacherful]

Somewhat newbie DBA here. This information is beyond helpful - thank you so so much Chris!! I have a question about Oracle SQL Developer - is it possible to run autotrace without actually executing the SQL statement as well (I'm working with big tables in production)? I know it is possible through SQLPlus via traceonly option, just cannot tell if it's possible in Oracle SQL Developer as well. -Thank you

[@TheMagicofSQL]

To get the plan's execution stats you have to run the SQL statement! set autotrace traceonly in SQL*Plus does execute the query, it just suppresses the output. The autotrace option in SQL Developer also executes the query, but there's no option to see the results. If you have a long-running query and you want to see the plan before it finishes, check out the SQL monitor. You can access this in SQL Dev via Tools -> Monitor SQL. Note you need appropriate licenses to use this!

[@ikacherful]

@@TheMagicofSQL I see, I will definitely try this option. Thank you!!! I really appreciate it.

[@ThuNguyen-g6b9b]

goddang this is gold

[@TheMagicofSQL]

Thanks! Glad you found this useful