Oracle Scratchpad

September 1, 2014

Execution Plans

Filed under: Execution plans,Oracle — Jonathan Lewis @ 8:40 am GMT Sep 1,2014

This is the index to a series of articles I’ve been writing for redgate, published on their AllThingsOracle site, about generating and reading execution plans. I’ve completed a few articles that haven’t yet been published, but I’ll add their URLs when they’re available.

I don’t really know how many parts it’s going to end up as – there’s an awful lot that that you could say about reading execution plans, even when you’re trying to cover just the basics; every time I’ve started writing an episode in the series it’s turned into two episodes.  I’ve delivered 10 parts to redgate so far; the active URLs below are the ones that they are currently online.

Chapter 9 has just been published, so I’ve popped this catalogue to the top of the stack.  Episode 10 is written, but waiting for its final proof read.

It’s only going to take a couple more installments and I’ll have finished the basic introduction to execution plans – so I’m looking for some ideas of what people really need to know about reading execution plans. If you have any suggestions about what features, or functions, or patterns of execution plans need a more detailed execution, please put them into writing in the comments and I’ll start work on addressing the commonest requirements.



  1. Hi Jonathan,

    Thanks for this really insightful series! Can’t wait for Parts 5,6 and further.


    Comment by Rman — May 25, 2014 @ 6:03 am GMT May 25,2014 | Reply

  2. Hi Jonathan,
    Thank you so much for this series. I still need to read parts 4 and 5 but so far, it has been
    so interesting to me.
    I have just bought CBO Fundamentals’s ebook on Apress and I can’t wait to read it !
    I have a question when you find time to answer.
    If the same table has to be :
    1) loaded through sql*loader ( 500000 rows )
    2) used
    3) loaded through sql*loader ( 510000 rows )
    4) used

    Do we need to collect oracle statistics after each sql*loader even though the number of rows is nearly the same after each loading ?
    I know that the CBO does not rely solely on the number of rows because it would be so simple this way.
    I am quite sure the CBO is going to take into account the way data is located in the different rows of every table and in the related indexes and
    thus will collect different statistics after each loading.
    If you have a part of the CBO book where I could find the answer, thank you in advance.

    Jean-michel A., Nemours, FRANCE

    Comment by alzingre — May 30, 2014 @ 11:37 am GMT May 30,2014 | Reply

    • Jean-michel,

      The answer to that question does depend on what stats you need and which version of Oracle you are using. 12c, for example, will collect stats on a direct path load into an empty table – but won’t be collecting index stats.

      Your question, though, seems to be aimed more at the case of: I load the table, I use it, I empty it, I reload it with fairly similar data and repeat the cycle. In this case you may find that once you’ve collect stats once they are sufficiently representative of the stats that would appear on each reload – in which case you wouldn’t need to collect new stats.

      You have to be careful, though, as you may have a column which is time-dependent and used in queries – which means the high value for that column OUGHT to increase with each data load – so, even though everything else need not change , the stats on that one column need to be maintained. If that’s the case you might have to re-gather stats each time, or you could use the dbms_stats.set_column_stats() procedure to modify the stats on that one column.

      Since you’ve referenced the indexes you’re clearly thinking of clustering factor as well. Again if the pattern of data stays roughly the same the clustering_factor be reasonably stable, but if data ordering can vary dramatically then you might have to recollect stats one (some of) your indexes to reflect this fact if you want the best plans.

      Comment by Jonathan Lewis — June 2, 2014 @ 1:07 pm GMT Jun 2,2014 | Reply

      • Thank you Jonathan for your quick answer.
        We have a 10Gr2.
        We currently run stats on a whole schema but I am interested in gathering them on specific columns just like you mention it. I still need to know which columns the application is using.
        We do not have any 12c database.

        Jean-michel A.

        Comment by jean-michel alzingre — June 2, 2014 @ 8:34 pm GMT Jun 2,2014 | Reply

  3. Hi Jonathan,

    Any plans for parts 8, 9 and 10 ?


    Comment by Rohit — August 12, 2014 @ 6:59 pm GMT Aug 12,2014 | Reply

    • Rohit,

      Yes – but I’m having trouble finding time to do any writing at present.
      Part 8 is drafted and is where I start to talk about the numbers (cost, rows, etc.)

      Comment by Jonathan Lewis — August 15, 2014 @ 8:55 am GMT Aug 15,2014 | Reply

  4. Fascinating! How does Google Now know that I`m looking forward for new article in series? It has just given me a link!

    Comment by Yuri — August 20, 2014 @ 10:19 am GMT Aug 20,2014 | Reply

  5. […] response to my series on reading execution plans was an email request asking me to clarify what I meant by the “order of operation” of […]

    Pingback by Order of Operation | Oracle Scratchpad — September 3, 2014 @ 9:42 am GMT Sep 3,2014 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at


Get every new post delivered to your Inbox.

Join 4,422 other followers