Learn the essentials about an Oracle SQL Execution plan 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!
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.
- Basic understanding of SQL, indexes, views and subqueries
- Required prerequisite tutorial (free)
- Lectures 0
- Quizzes 0
- Duration Lifetime access
- Skill level All levels
- Language English
- Students 0
- Certificate Yes
- 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