Are you an Oracle Developer or DBA and need to tune Oracle SQL SELECT statements? Then you need to know how to read an Oracle execution plan, the first step in learning SQL Tuning! Learn the essentials in this fast-paced, intensive 6-hour instructor-led online course taught by Oracle Certified Master John Watson and Oracle Tech Dave Anderson! John is one of the worlds best SQL tuners! You may already know about John from his books on Amazon.
This 6-hour accelerated class is delivered in 3 2-hour sessions!
Who Should Attend this Class?
This is an excellent, 5-star class designed for:
- APEX Developers who encounter unacceptable page response times due to poorly performing SELECT statements
- DBA’s who find queries in AWR or Statspack that are consuming too much of the server resources
- PL/SQL Developers who have poorly performing embedded SELECT statements
- Anyone who wants to unlock the “secrets” of SQL Tuning and Oracle execution plans!
Why Attend?
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! Another reason to choose this class is because students love it! Be sure to read their reviews (see the “Reviews” tab, above)!
Want to Start Right Now?
Get started today with this highly acclaimed tutorial! Created by John, this 13-minute video will provide the basics of reading an execution plan including finding the 1st step executed and the order of all steps in the plan. “The first operation with no child is the first operation to start.”
You Say Tomato…
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 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!
What Will You Learn?
What are the essentials, the building blocks of learning about SQL execution plans?
- The various techniques for capturing execution plans (CAPTURE)
- How to interpret (read) plans, e.g. determining the order of the steps in the plan and understanding iterative operations, join methods and join order, common access methods (INTERPRET)
- Identify and fix issues (SOLUTION)
Seems pretty simple. But the Cost Based Optimizer is a complex piece of software! Look at all of the things you will learn in class!
- What an execution plan is
- How SQL is executed by Oracle including Parse, Bind, Execute and Fetch steps
- The purpose and goals of the Cost Based Optimizer (CBO)
- What the COST of a plan is and what it is used for. Hint: It is not dollars and cents.
- Various ways of generating an execution plan, e.g. EXPLAIN PLAN and SQL*Plus Autotrace
- How to capture the actual plan with DBMS_XPLAN.DISPLAY and DISPLAY_CURSOR
- How to format an execution plan for readability
- How to read/interpret the plan including
- Which step executes first and the order of execution for all steps
- The order in which the steps are executed
- The join order
- The join methods, e.g. MERGE_JOIN and NESTED LOOPS, and when one is typically better than the other
- The access method, e.g. Full Table Scan and (Unique) Index Access and when one is typically better than the other
- Identifying iterative operations in the plan
- Understanding “operations”, i.e. various join methods and access methods
- The critical things to look for in an execution plan
- The value of up-to-date object and system statistics
- The difference between plan statistics and object statistics
- The impact of bind variables on a plan
- What bind variable peeking is and its impact on plans
- What dynamic sampling is and its affect on execution plans
- What adaptive plans are and the impact on the plan and SQL
- How indexes can help (or hurt) SQL execution
- The difference between ALL_ROWS and FIRST_ROWS optimization mode
- Tuning Joins: Getting the join order, join method and access method right
- What cardinality is and how to use it to get efficient plans
- Solving problems: Silver bullets and red flags! You will learn several things that could appear in an execution plan that might indicate poor performance.
- If there is such a thing as a silver bullet to tune Oracle SQL, it’s the join order. You will learn to identify the join order and test various join orders to find the most performant order.
- When reading the execution plan you will learn to spot “red flags”, i.e. steps that warrant a closer look. e.g. Incorrect values in the “Rows” column of the plan or “Cross Join” in the Operation could indicate a missing join condition.
- Avoiding some common programmer “errors”, SQL formulations that result in poor performance
- Are correlated subqueries good or bad?
- Why lots of constraints are usually a good thing!
- The never-ending problem with NULLS
- How histograms help solve the problem of skewed data
- How extended statistics inform CBO about relationships between columns
- And much more…
Class Structure
The class is an online, instructor-led class. The course consists of six hours of fast-paced lecture and plentiful demonstrations, delivered over 3 days in 2-hour sessions. Hands-on labs are assigned during class and at the end of each 2-hour session. Students can email the instructor between sessions to get help with labs and questions.
Other things we think you should know:
- Students rate this class with 5 stars. Please be sure to read the reviews!
- We encourage students to bring an execution plan from a troublesome query to class. John and Dave will review and provide suggestions.
- After class students have access to a recorded class.
- The demonstrations are delivered with the most common and accessible query tool, SQL*Plus.
- The scripts used by John in demonstration are made available to the students for future use.
- In addition to the class itself, students are provided many prerecorded tutorials that provide a deeper dive on a subject!
Please remember: This is a fast-paced course. Students must have the prerequisite knowledge to benefit from this course.
Prerequisite Knowledge
- Solid command of Oracle SQL, including:
- Some familiarity with ANSI Join syntax, e.g. NATURAL JOIN
- Subqueries including correlated subqueries
- Some familiarity with basic data warehouse concepts.
- Some familiarity with indexes. Watch some of this 7-part tutorial (Youtube) for help with this prerequisite.
- Familiarity with the Oracle Data Dictionary. Watch this tutorial (YouTube) for help with prerequisite.
- Experience with PL/SQL packages, procedure and function calls
- Basic understanding of views
- Recommended prerequisite tutorial – Oracle SQL Tuning – How to Read Execution Plans (free, above)
Technical Requirements to Attend Class
None, other than a computer to connect to the class. SkillBuilders supplies the software you need to attend the class. Additionally, every student is provided a database to do the labs and experiment. The lab environment (database) is available to students for a full business week. Note that – optionally – you may want to use your favorite developer tool for the labs.
Minimum requirements for GotoTraining.com (GTT) –
- 2GB of RAM (minimum), 4GB or more of RAM (recommended)
- Webcam device (if sharing)
- Microphone and speakers (USB headset recommended)
If you would like to download the GTT app, visit the Download GTT page. If you don’t download the app, GTT will run in your browser.
For a brief primer on using GTT, visit the GTT Training page.
Your instructor, John or Dave, will email you a link to join class each day of class.
Related Classes
Students interested in a deeper dive into SQL Tuning should check out this alternate course:
Oracle SQL Tuning for Developers and DBAs 20 Hours over 5 Days.
If you are a developer and want a less intense presentation, consider this class:
Oracle SQL Tuning – Core Skills for Developers 8 Hours over 2 Days.
FAQs of Oracle SQL Execution Plans
-
What does the Oracle SQL execution plan cost mean?
It is used by the cost based optimizer for comparing different ways of executing the same statement. CBO parses your query, creates possibly hundreds of different ways to execute the query, then chooses the version with the lowest cost. And, if you are experimenting with different codings of a query, you can use Cost to some degree to gauge which version of our query is best. Theoretically, a lower cost plan will be faster than a higher cost plan, but not always. Best to test the various versions to ensure which version is most performant.
-
What’s the best way to display the execution plan for an Oracle SQL statement?
A common method is EXPLAIN PLAN command followed by DBMS_XPLAN.DISPLAY to format and view the plan. But keep in mind that EXPLAIN PLAN parses your query to create the predicted plan on the current server. There are several reasons the predicted plan is not the same as the actual plan. For example, if you’re not testing on your production server (and who is!) the actual plan could be different. To get the actual plan used, consider DBMS_XPLAN.DISPLAY_CURSOR or DBMS_XPLAN.DISPLAY_AWR. For more information, we recommend studying the DBMS_XPLAN package in the Oracle PL/SQL Packages Reference doc.
-
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/bcVbR_FPWKk?feature=shared
-
What is the order of the steps in a Oracle Execution Plan?
Refer to our 13 minute video https://youtu.be/bcVbR_FPWKk?feature=shared
-
What are the operations in the execution plan?
The operations are the individual steps in a plan, such as searching an index or sorting a set of rows. Some operations will be iterated (e.g. Nested Loop), some can run (in effect) concurrently. Refer to this 13-minute video for more information https://youtu.be/bcVbR_FPWKk?feature=shared
-
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 24
- Assessments Yes
Introduction: The Tuning Methodology
- What the Course Covers
- The SQL Execution Cycle: Parse Bind Execute Fetch
- Tuning SELECT Statements, the Basic Execution Cycle
- Optimization Concepts, “Best” Execution Plan, DB Time
- Introduction to the Cost Based Optimizer
- Critical Decisions made by CBO
- Tuning Method, What to ask if query is too slow
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
- How to read the Execution Plan
- Determining the Correct Order of Execution for the Plan Steps
- Use SQL*Plus Autotrace to generate execution plans and statement statistics
- The COST of an Operation and Statement
- Why EXPLAIN PLAN might not tell the truth
- How and Why use AUTOTRACE
Extracting Plans from the Library Cache with DBMS_XPLAN
- The Limitations of EXPLAIN PLAN
- How and Why Use DBMS_XPLAN.DISPLAY_CURSOR to extract plans from the library cache
- Capture and view actual as well as predicted operation cardinalities
- Bind Variables impact on Execution Plans
- Statistics Feedback feature for plan error correction
- Understanding Adaptive Plans
- Gathering Execution Plan Statistics
- Various Functions in the DBMS_XPLAN Package
- Comparing Cardinality Estimates with Reality
- Typical Use of DBMS_XPLAN
- Exploring the FORMAT Argument
Decisions: Join Order, Join Method, Access Method
- The Importance of 3 Critical Decisions for Join Operations
- Determine the sequence in which operations start
- Work out the table join order
- Using the Filtered Rows Percentage Methodology
- Understanding Cardinality
- General Rules about Join Order
- Testing Different Join Orders
- Helpful Hints for Testing
- Understanding Join Methods
- Understanding Access Methods
- Helpful Hints for Testing
- Why Oh Why Does CBO Get it Wrong?
- Understanding Nested Loop Join
- Understanding Hash Join
- Understanding Sort-Merge Joins
- Understanding Semi and Anti Joins
- Understanding Cartesian Joins
- Understanding Outer Joins
- Understanding Access Methods
- Understanding Full Table Scan and Index Fast Full Scan
- Understanding Index Unique Scan
- Understanding Index Range Scan
- Understanding Index Full Scan
- Understanding Index Join
- Understanding Index Skip Scan
Solving Frequently Seen Problems
- Common Issues
- Common “Red Flags” in Execution Plans
- The Too Many Indexes Problem
- Overuse of Nested Loop Joins
- Exploring Correlated Subqueries
- Understanding Projected Subqueries
- Understand How Constraints Help the CBO
- Semantically Equivalent SQLs
- Inadequate Object Statistics
- An Introduction to Histograms
- An Introduction to Extended Statistics
- Understanding Dynamic Statistics, OPTIMIZER_DYNAMIC_SAMPLING
