Oracle Scratchpad

May 17, 2009

Philosophy – 2

Filed under: CBO,Performance,Philosophy,Tuning — Jonathan Lewis @ 5:51 pm BST May 17,2009

Here’s another of those ideas that are so fundamental that you should always keep them in mind when dealing with an Oracle database.

The fundamental strategy embedded in the optimizer is based on just two key points:  

  •  How much data are you after.
  • Where did you put it.

If you “tune” SQL by fiddling with it until it goes fast enough then you’ve missed the point and you’re wasting time.

 If you start your tuning exercise by thinking about how much data you’re supposed to collect and how randomly scattered it is, then you’re going to minimise the time you spend working out the best way of acquiring that data efficiently.

[The Philosophy Series]


  1. This is a philosephy I really became to understand after reading your book and especially the chapter on the clustering factor as well as after attending a one day class of Tapio Lahdenmaki on index design. Never before I realized so much that the scattering of data in a table makes so much difference. Recently we made considerable improvements when we analyzed our (Siebel) OLTP application and online redefined a number of important tables, sorting them on the most used access ‘paths’. DBMS_REDEFINITION (one of my favorite built-in packages) makes that so easy and online and the benefit was visible immediately. I plan to write a post about that soon on my own blog…

    Comment by Toine — May 18, 2009 @ 8:48 am BST May 18,2009 | Reply

    • (And with “analyzed our application” I do not mean gathering statistics ;-), I mean we really looked at the application’s SQL and the way tables were mostly accessed)

      Comment by Toine — May 18, 2009 @ 8:50 am BST May 18,2009 | Reply

  2. If it is about “where you put your data”, then IOTs and Clusters will help you keep related data in one place. Favour a good clustering factor, so to speak.

    But do keep the Philosophy series simple, readable, understandable.
    Things may not always be simple, but Simple Fundamentals are Best.

    Comment by PdV — May 18, 2009 @ 10:01 pm BST May 18,2009 | Reply

RSS feed for comments on this post.

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.

Powered by