Oracle Scratchpad

May 26, 2010

Visual Tuning

Filed under: Advertisements — Jonathan Lewis @ 7:47 pm BST May 26,2010

I’ll be doing a joint event with Kyke Hailey of Embarcadero in a few days time. It’s going to be a little unusual – a simultaneous webcast from opposite sides of the Atlantic – mixing my presentation about using a graphic method of tuning or designing efficient SQL with Kyle taking on the role of an active audience asking questions.

For more details and to register for this online event see this link

Update: the link now allows you to register to download the presentation and whitepaper, and to view a recording of the presentation. (See comment 7 below)


  1. Sounds interesting, very interesting!

    Comment by lascoltodelvenerdi — May 27, 2010 @ 7:53 am BST May 27,2010 | Reply

  2. Elias here at Embarcadero. The SQL Tuning Event Registration web page is live and you can register here:

    Comment by Elias Terman — May 27, 2010 @ 9:07 pm BST May 27,2010 | Reply

  3. I like visual tuning since it helps break down execution plans which can be quite long thus making for easier analysis.

    Comment by Ben Prusinski — June 2, 2010 @ 4:16 am BST Jun 2,2010 | Reply

  4. Jonathan, what will be the time in Europe (GMT) for this appearance, don’t want to miss it ?

    By the way, thanks for your 2-day presentation in Paris, too bad I miss the end, I tried to solve the last pages of the presentation by myself. But I could find some important clue.. I am still a beginner !!

    Comment by jcdauchy — June 10, 2010 @ 8:27 am BST Jun 10,2010 | Reply

    • According to my calculations it will be 7:00 pm in the UK, and 8:00 pm in France and other Westen European countries.

      A discovery I made recently is that Google has a convenient “world clock” function, so if you type in “what time {location}” you can get the current time at location. (If you do try this with location = San Francisco, don’t be misled by “San Francisco, Argentina”, I’m aiming for “San Francisco, California” ;) )

      Thanks for the comment about the seminar – I’ll have to improve the notes on the last few pages to help the people who have planes to catch.

      Comment by Jonathan Lewis — June 10, 2010 @ 9:06 am BST Jun 10,2010 | Reply

  5. Hope your calculation are right, otherwise Kyle will be waiting for you :)

    Comment by jcdauchy — June 10, 2010 @ 9:29 am BST Jun 10,2010 | Reply

  6. Thanks Jonathan, arrived at the end. Do you know if the presentation has been recorded and would it be possible to have the presentation with the comments ?

    I missed all the beginning, arrived for the questions.


    You mentionned 3-4 books, i did not get the name of the author, could you post some links to those books ?


    Comment by JC dauchy — June 10, 2010 @ 8:19 pm BST Jun 10,2010 | Reply

    • I believe the session has been recorded and people who were registered will be able to replay it. Also, I sent a pdf file of the slides to Embarcadero – I think there was a “print” icon hidden at the bottom corner of the LiveMeeting screen that would have allowed you to download it. I think it will also be made available, though, to attendees.

      The list of books is on my blog at:

      Comment by Jonathan Lewis — June 10, 2010 @ 8:50 pm BST Jun 10,2010 | Reply

  7. If you missed the event, the recording, and pdfs of the slides and “white paper” are available through the original link.

    The link will take you to a screen with a “Get Webinar Replay” button, and pressing this button will take you to a registration page. After registering, and supplying your email address, you will receive an email that includes buttons to “Watch Now” and “Get Whitepaper”.

    If you select the option to watch you get taken through a couple more screens that ask for your name and email address again, you get taken to a screen that allows you to download or watch the webinar. There is a warning that you need to use Internet Explorer 6 with SP1, or later, to view the recording. It’s possible that some other browsers will work – but I chose to get the download (a 20MB zip file) and found that I couldn’t get the extract to run under Google Chrome. Luckily I also had IE6 on the same machine.)

    Comment by Jonathan Lewis — June 17, 2010 @ 7:33 am BST Jun 17,2010 | Reply

  8. Hi Jonathan,

    I just saw the event offline. I have a question regarding my existing system which is a medium to large size warehouse. What should be the rule of Thumb when going for Index Compression?
    i.e. Only Fact table indexes or Dimension tables or All

    Btw i really liked the presentation. I learned a lot form it specially the Visual analysis technique.


    Comment by Manish — June 21, 2010 @ 7:29 am BST Jun 21,2010 | Reply

    • Manish,
      Thanks for the comment – glad you liked the presentation.

      Rule 1: don’t change anything unless you’re fairly sure the change is going to introduce a reasonable improvement that’s worth the risk. In a production system you may make an index more efficient and “desirable” by rebuilding it with compression, but this may make the optimizer switch to using it in some cases where it should be using another index. This means that an inherently sensible decision actually causes problems if you don’t do a huge amount of testing first.

      Rule 2: There is no rule 2, there is only a guideline. The longer the prefix that repeats, the lower the percentage of rows with repeats you need to make the prefix worthwhile; but if the prefix is only a couple of bytes long you need most of the data to have a couple of repeats before it’s likely to be worth compressing.

      The best job analysis is always to look at doing a “validate index” on a backup copy of production and then looking at index_stats for the “optimum compression count” and “optimum compression percent saved”, with a quick glance at index_histogram and a check of the data since this may give you some idea of the way in which the repeats occur.

      Comment by Jonathan Lewis — June 21, 2010 @ 7:28 pm BST Jun 21,2010 | Reply

  9. […] may also want to check out this ultimate SQL tune off by Kyle Hailey and Jonathan Lewis to know more about DB Optimizer […]

    Pingback by SQLTXPLAIN quick tips and tricks and DB Optimizer VST « Karl Arao's Blog — February 11, 2012 @ 4:23 pm GMT Feb 11,2012 | Reply

  10. […] you’re a regular follower or my blog you may recall Kyle Hailey and the joint webinar we did nearly two years ago on “Visual SQL Tuning” covering an approach I’ve […]

    Pingback by Delphix « Oracle Scratchpad — February 6, 2013 @ 7:19 pm GMT Feb 6,2013 | 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 )

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

%d bloggers like this: