Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Interpreting Execution Plans

17 October 2009 2 Comments Written by Christian Antognini

An execution plan describes the operations carried out by the SQL engine to execute a SQL statement. Every time you have to analyze a performance problem related to a SQL statement, or simply question the decisions taken by the query optimizer, you must know the execution plan. Whenever you deal with an execution plan, you carry out three basic actions: you obtain it, you interpret it, and you judge its efficiency.

I have always found it surprising how little documentation there is about how to interpret execution plans, especially since there seem to be so many people who are unable to correctly read them. I addressed this problem not only in Chapter 6 of my book, Troubleshooting Oracle Performance, but also by presenting this very same topic at several conferences and user-group meeting (e.g. UKOUG, Orcan and SIOUG). The last time I presented it, it was few days ago at Oracle OpenWorld in San Francisco. By the way, big thanks to everyone who attended my presentation! It was really good to see a packed room. That said, I’m truly sorry that Oracle has not been able to provide us with a decent room. I was embarrassed for them…

The aim of this short post is to point out that I just uploaded the slides that I presented at different events here. Since the slides themselves contain few explanations and, therefore, are not very useful without additional information, I also added comments whenever necessary (e.g. where there is an execution plan). I hope you find that document useful.

Oracle Database, Query Optimizer, Speaking, TOP
Ad: Oracle Database 11g Release 2 Seminars
Hints for Direct-path Insert Statements

2 Comments

  1. ASWATH RAO ASWATH RAO
    17 October 2009    

    Christian, I attended your session at the Open world. It was an excellent session. As you had mentioned, the room was very small and was not able to hold every one. I have put this an issue in the Open world survey too and that way they can eliminate that room in the future. Thanks again.

    Reply
  2. Todor Botev Todor Botev
    30 November 2009    

    I find the classification of the operations really great! With such a map in your mind it’s much easier to understand what’s going on.

    A small remark:

    Page 15, paragraph 3:
    “Operation 5 is executed eight times, once for each distinct value in the column empno in the table emp after applying the filter imposed by the operation”

    It seems to me that Operation 5 is executed exactly 8 times because of the filter imposed by Operation 3. So the filter of Operation 3 gets applied and only the rows satisfying it go into the filer of Operation 5. Which means “eliminate early” and somehow makes sence.

    I think it is not a coinsidence that exactly 8 employees do not belong to department “SALES” – meaning they satisfy the filter of Operation 3. That’s why Operation 5 gets started 8 times – once for each of those employees.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.