Oracle Scratchpad

May 4, 2009

IOUG Day 1

Filed under: CBO,Execution plans,Oracle,Performance,Statistics,Troubleshooting,Tuning — Jonathan Lewis @ 10:00 pm BST May 4,2009

I attended four sessions today (one of them was a 2-hour session), and the highlight of the day for me was a lunchtime “quick tip” from Michelle Deng of Sanofi Aventis who gave a 30-minute talk with the title “Cardinality Analysis – a life saver for DBAs and Developers”.

Michelle used a recent example from a production system to take us through the process of identifying the source of SQL performance problems by comparing the optimizer’s prediction of the number of rows to be returned by each line of the execution plan (E-rows), and the actual number returned at run-time (A-rows).

The example was made more interesting by the fact that it wasn’t feasible to run the query to find the A-rows value, so she had to deduce (and confirm) the probable culprit by examining the E-rows and using the predicates section of the plan to check the data distribuition of individual tables.

If your job has anything to do with tuning SQL and you have access to the online IOUG presentions, make sure you download and view this one.


Following a comment from lascoltodelvenerdi, I’ve asked Michelle Deng if I can post a copy of her presentation with some notes on my blog, and she has given her permission for me to do so. The result is at this URL


  1. Hope it will be downloadable soon!

    Comment by lascoltodelvenerdi — May 5, 2009 @ 8:26 am BST May 5,2009 | Reply

    • lascoltodelvenerdi,

      Unfortunatly the downloads are only available for conference attendees. We can only hope that Michelle will publish it independently.

      Comment by Jonathan Lewis — May 5, 2009 @ 6:06 pm BST May 5,2009 | Reply

    • I’ve asked Michelle if I can post a copy of her presentation on this blog and she’s said yes; so I’ll write up a short note to go with it as soon as I can and post it some time in the next few days.

      Comment by Jonathan Lewis — May 6, 2009 @ 9:20 pm BST May 6,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

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

Website Powered by