The Essentials: How to Read Oracle Execution Plans
How to read an Oracle execution plan? Learn the essentials in this 1/2 day instructor-led online course taught by Oracle Certified Master John Watson!
What are the essentials, the building blocks of learning about SQL execution plans?
- The various techniques for capturing execution plans
- How to interpret (read) plans, e.g. order of the steps in the plan, iterative operations, join methods and join order, common access methods
- Identify and fix some frequently encountered issues
Watch instructor John Watson talk about the class Agenda
Because an execution plan reveals how the Oracle Cost Based Optimizer (CBO) has decided to execute your query, and that is the essential role in query performance, being able to capture and understand query SQL execution plans is a vital skill for anyone developing or maintaining applications where performance is important. This course is a great place to start!
You may think of it as “how to read an execution plan” or “how to interpret an execution plan”. However you say it, if you’re interested in SQL performance, you need to know the essential components of the plan (capture, order of steps, join order, etc) in order to figure out how the Oracle Cost Based Optimizer has decided to execute your query. This course is a great place to start!
Class Structure of Oracle SQL Execution Plans
The class is an online, instructor-led class. The course consists of four hours of succinct lecture and plentiful demonstrations. A hands-on lab is assigned at the end of the initial 4-hour session. The instructor and students will reconvene for a 1-hour session (at a mutually agreed upon date/time) to review workshop solutions and answer student questions. Between the end of the initial 4-hour session and the 1-hour review, the students can email the instructor with questions about the workshop.
The scripts used by John in demonstration are made available to the students for future use.
This is a fast-paced course. Students must have the prerequisite knowledge to benefit from this course. The demonstrations are done with the most common and accessible query tool, SQL*Plus.
Prerequisite Knowledge
- Basic understanding of SQL, indexes, views and subqueries
- Required prerequisite tutorial (free)
FAQs of Oracle SQL Execution Plans
-
What does the Oracle SQL execution plan cost mean?It has no absolute meaning. It is used for comparing different ways of executing the same statement. Theoretically, a lower cost plan will be faster than a higher cost plan.
-
What’s the best way to display the execution plan for an Oracle SQL statement?Whichever way works for you in your circumstances. Under the covers, most of them use functions in the DBMS_XPLAN package.
-
How to I figure out what the sql plan is telling me?
There is a method to follow to pick out the vital information. This works whether the plan is just half a dozen operations or many hundreds. Watch our related video for the details: https://youtu.be/mMAgUz-60u8
-
What is the order of the steps in a Oracle Execution Plan?
The starting point is to determine the join order: the sequence in which the various row sources are joined. This may not be the sequence in which the tables are first addressed, or the sequence in which they appear in the plan.
-
What are the operations in the execution plan?
The operations are the individual steps, such as searching an index or sorting a set of rows. Some operations will be iterated, some can run (in effect) concurrently.
-
How do I know if an Execution Plan is "bad"?
Plans are not intrinsically "good" or "bad". An end user is not going to complain that (for example) "there are too many full table scans", he is going to complain that "this screen refresh takes a minute, I need it in a second." If a plan fulfills the user's need, then it is good enough.
-
How do I change an execution plan?A plan is developed by the Cost Based Optimizer. If it is making a mistake, you can give the CBO orders with (for example) hints or baselines. Or you make changes to the SQL, the data structures, the environment, the available statistics, many more things, that will let the CBO develop the best plans on its own.
Course Features
- Lectures 0
- Quizzes 0
- Duration Lifetime access
- Skill level All levels
- Language English
- Students 21
- Assessments Yes
Using Explain Plan and the Autotrace facility
- Generate execution plans for SQL statements with the Explain Plan command
- View the plans with dbms_xplan.display
- Use SQL*Plus Autotrace to generate execution plans and statement statistics
Extracting plans from the library cache with dbms_xplan
- Use dbms_xplan.display_cursor to extract plans from the library cache
- Capture and view actual and well as predicted operation cardinalities
Interpreting execution plans
- Determine the sequence in which operations start
- Work out the table join order
- Identify iterative operations
- Understand the different join methods and access methods
Identifying (and fixing) some common problems
- Inappropriate use of certain join methods and access methods
- Poor choice of driving table and join order
Login with Google