Oracle Scratchpad

June 16, 2014

Execution Plans

Filed under: Execution plans,Oracle — Jonathan Lewis @ 5:22 pm BST Jun 16,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 5 parts to redgate so far; the active URLs below are the ones that they are currently online.

The latest episode has just been published, so I’ve popped this catalogue to the top of the stack.  Episode 7 is written, but waiting for final proof read and transfer to the blog; after which it really will be time to say something about Row and Cost estimates.






  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 BST 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 BST 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 BST 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 BST Jun 2,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

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 3,876 other followers