HomeОбразованиеRelated VideosMore From: SkillBuilders

Oracle SQL Tuning - How to Read Execution Plans (Explain Plan)

312 ratings | 66317 views
In this free tutorial you will learn how to generate and read (interpret) an execution plan in Oracle Databases. See more FREE Oracle Tuning tutorials at http://skillbuilders.com/free-oracle-tutorials. Understanding what the Oracle Database does with your SQL is essential to tuning - and the execution plan is the key. Oracle Certified Master DBA John Watson will provide a brief introduction (4 minutes) - which includes John's tuning methodology, then demonstrate EXPLAIN PLAN, SQL*Plus AUTOTRACE and DBMS_XPLAN.DISPLAY_CURSOR. In the tutorial, John will teach you: - How to read an execution plan - Find the 1st step in the plan - Decipher the order of the steps in the plan - That EXPLAIN PLAN can be very misleading Prerequisites: To get the most from this tutorial, you should: 1 Know how to code SQL 2 Be familiar with SQL*Plus 3 Know - in very general terms - what an execution plan is. 4 Have a basic understanding of the Library Cache (this is where Oracle Database stores parsed SQL statements) 5 Have a basic understanding of the Cost Based Optimizer (this is the part of the database that parses your SQL, creates an execution plan. Hopefully the correct - most efficient - plan).
Html code for embedding videos on your blog
Text Comments (35)
Shubhra Pandey (3 months ago)
Awesome Work!
Tamito (7 months ago)
Excellent explainetion. Thanks a lot
John Harrington (1 year ago)
God, showing the bleeding plan should be the default behavior of EXPLAIN PLAN. Why on earth would you execute an EP and not want to see it?
Sandeysh Patil (2 years ago)
Awesome video John. After long time finally i came across something meaning in Sql Tuning world :-). Simple, concise and to the point. By any chance have you written any book on Oracle Sql/ PLSQL tuning which is available in market to purchase?
Mohammad Zahid (2 years ago)
Great SQL Tuning video and explanation. Thank you for posting.
SkillBuilders (2 years ago)
Thanks Mohammad. John is teaching an online class...more info here http://www.skillbuilders.com/oracle-sql-tuning-training-for-beginners
sagar patil (2 years ago)
This is the most helpful tutorial I have ever seen on YouTube.. (or any other media). Excellent... Thank you very much for sharing this video. Looking for some more videos in parts for begging to pro level.
SkillBuilders (2 years ago)
+sagar patil Thank you ! Check out http://www.skillbuilders.com/free-oracle-tutorials for dozens more free tutorials
Sumeet Singh Arora (2 years ago)
Notice the Note under Execution Plan - "this is an adaptive plan". New feature of Oracle 12c. That's why Explain Plan is lying :)
Joe Smith (3 years ago)
I'm a novice training for a certification exam.I asked myself, why ever use explain plan when GATHER_PLAN_STATISTICS appears superior. Well, I think I know why now. Explain plan has been around for awhile and was the best starting point with available tools in the past. With the release of 10g came the GATHER_PLAN_STATISTICS hint, which seems to eliminate the need for EXPLAIN PLAN (at first glance, from a novice perspective.
Joe Smith (3 years ago)
Golden info. Thanks!
Kaley Crum (3 years ago)
Questions: I'm trying to come up with my own examples of when Explain Plan lies, and I can't seem to get it to lie to me. How did you force explain plan to lie to you?
John Watson (3 years ago)
+Kaley Crum It's an adaptive plan. Both plans pre-computed, and the choice made at execution time. Use the format=>'adaptive' argument to the dbms_xplan functions, and you'll see both.
Piet Van de Weghe (3 years ago)
Thx, but the dark bar at the bottom of the screen is a bit annoying ..
Piet Van de Weghe (3 years ago)
Quite shocking to learn that the explain plan of yet a simple statement, is so wrong ... ;)
Shirantha Fernando (3 years ago)
can you show a practicle for bad query tuned by using execution plan and indexing
Yogendra Pahariya (3 years ago)
Complex topic yet well presented with ease within 15 min... Excellent job !! Thanks Skillbuilder... :-)
Praveen kaligotla (4 years ago)
Very Effective and Clear Descriptive Provided. Thank you Skill Builders Team for Providing.. Thanks a lot. Praveen K.
Ankit Padhiyar (4 years ago)
wow...  I never knew that explain plan lies ... i was thinking that it gives perfect values what oracl does in real.. JOHN THANK YOU...
Deepti Pabbisetty (4 years ago)
Very informative. Thanks a lot for posting this video.
Phi. Tes (4 years ago)
Could you post the link to the direct follow up video?
SkillBuilders (4 years ago)
There is no absolute "follow up", but this is a good next step....http://skillbuilders.com/how-to-tune-oracle-sql
Philipp Schmitt (4 years ago)
Hi, which one is the follow up video to this video?
SkillBuilders (4 years ago)
There is no absolute "follow up", but this is a good next step....http://skillbuilders.com/how-to-tune-oracle-sql
Namaste India (4 years ago)
Thanks sir From INDIA
SkillBuilders (4 years ago)
You are welcome.  Please be sure to share this and all our tutorials with your colleagues.  http://www.skillbuilders.com/oracle-tutorials
Oladapo Adetunji (4 years ago)
This is a great video on sql Tuning!!! It helped a lot thanks!!! Also can i get a link to the second video?
SkillBuilders (4 years ago)
There are many more FREE Oracle Tuning tutorials at http://www.skillbuilders.com/oracle-tutorials
Rohit Pakale (4 years ago)
where i will get next videos?
SkillBuilders (4 years ago)
See http://skillbuilders.com/how-to-tune-oracle-sql 
SkillBuilders (4 years ago)
More FREE Oracle Tuning tutorials at http://www.skillbuilders.com/oracle-tutorials
Qingxiang Jia (4 years ago)
Thank you for the video. It's a life saver and explain many key points about the exec plan that isn't well explained in Oracle documentation or the textbook. Thank you again.
satish ramapragada (2 years ago)
Qingxiang Jia
satish ramapragada (2 years ago)
SkillBuilders
SkillBuilders (4 years ago)
Thanks ! 

Would you like to comment?

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