HomeЛюди и блогиRelated VideosMore From: The Magic of SQL

Query Tuning 101: How to Compare Execution Plans

85 ratings | 14458 views
When you're tuning SQL query there's two important questions to keep in mind: * Have my changes made any difference? * If they have, is performance better or worse? In this video we'll look at how you can use SQL Developer to compare execution plans. This will enable you to differences between them and determine which plan performs better. ============================ The Magic of SQL with Chris Saxon Copyright © 2015 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.
Html code for embedding videos on your blog
Text Comments (10)
Károly László (1 year ago)
Hi, I watched and found really-really useful. I have only one question: How can I open the statistics window of the execution plan(s)? Unfortunately, I do not have the two small black narrow at the status line below the executions plans…. What shall I do?
Emanuel Oliveira (6 months ago)
SOLUTION: sharing a little confusion on my part that hopefully may helps others, I was wrongly using Explain Plan > pin > Explain Plan > Compare, but what we want is to do the same but using AutoTrace, which gives explain plan and statistics as well.
Emanuel Oliveira (6 months ago)
I also can't see stats in explain compare.. i have the 4 options enabled Tools -> Preferences > Database > Autotrace/Explain Plan, an Im using sql developer 18.1.0.095 *build 095.1630), any suggestion ?
The Magic of SQL (1 year ago)
Not sure, I'll need to see exactly what you're doing! Any chance you can capture a video and upload it somewhere? Also: 17.2 is the latest version. Try upgrading to see if that helps.
The Magic of SQL (1 year ago)
You can use dbms_monitor to capture all the SQL run by a particular session. This writes the execution details to trace files on the DB server. You can find further explanation of this at: https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof
Károly László (1 year ago)
...and one additional question: What is the technics if I want to catch/monitor/log/save the native SQL commands of the application developed by us? I have already tried the Tools/Monitor session, but it does not include the SQL statements of our apps. RealTime SQL Monitor contains only a part of the required commands and a lot of useless entries…. Practically what I want: - „Switch On” the trace function (e.g. in SQL developer or SQL*Plus) - Launch the application and try some functionalities with real data (e.g. the slow queries) - As soon as I think I have enough measurement: „Switch Off” the trace function and…. - Start analyzing/tuning the SQL commands (e.g. with SQL developer/Explain Plan, etc.) Simple saying: How can I collect SQL commands of an application on server level?
Sanjesh Kumar (2 years ago)
Hi, Your videos are great and very well organized.
The Magic of SQL (2 years ago)
Thanks Sanjesh, glad you find them useful!

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.