Home > DBA, Oracle, SQL > SQL Quiz: Five Popular Myths About SQL Performance

SQL Quiz: Five Popular Myths About SQL Performance


As a database administrator, I get a lot of requests for help with poorly performing SQL queries. Here are some popular myths about SQL performance. How many do you believe?

  1. The execution plans in the test and production databases are identical.
  2. Explain Plan and Autotrace display the execution plan used to execute the query.
  3. The execution plan does not change.
  4. The cost-based optimizer selects the optimal execution plan—the one with the lowest cost.
  5. A SQL query has an optimal execution plan—one with the lowest cost.

Deja Vu: Five Dangerous Beliefs About SQL Performance
Statistics—How and When
Databases – My Brilliant Career Tuning SQL

Categories: DBA, Oracle, SQL
  1. Narendra
    April 12, 2010 at 1:04 am

    While 1 to 3 are version independent, I don’t think 4 and 5 are myths, especially since 10gR2.

  2. Narendra
    April 12, 2010 at 1:04 am

    Narendra :

    While 1 to 3 are version independent (and are myths), I don’t think 4 and 5 are myths, especially since 10gR2.

  3. April 12, 2010 at 2:05 am

    4 is pretty funny indeed! The cheapest plan is just the cheapest according to what Oracle knows about the data. If you give Oracle additional info (stats, indexes to use/not use) it is likely Oracle will start using a better plan!

    In the good old //*+RULE*// days, the knowledge of the data was left to the developers, nowaday Oracle can do this (bind peeking, cost base, sampling and other techniques), so if the performance is bad, just blame Oracle😉

  4. Iggy Fernandez
    April 12, 2010 at 3:09 am

    Narendra,

    I’ll explain my reasoning for 5 in a followup post.

    Laurent,

    I ran into the situation described in Metalink note 420200.1 in an 11g Release 1 database while querying v$rman_backup_job_details. We got instantaneous results with the RULE hint in SQL*Plus but had to delete the statistics on X$KCCRSR–as advised in the note–because the query was being submitted by Grid Control.

  5. April 12, 2010 at 3:53 am

    cool! I love playing with X$ tables in prod😆 !

  6. Richard
    May 21, 2011 at 5:26 pm

    Hi Iggy,

    It would be great if you explain the above myths with examples.
    Thanks a million.

    Regards,
    Richard

  7. Iggy Fernandez
    May 21, 2011 at 5:34 pm

    Richard :

    It would be great if you explain the above myths with examples.

    Thanks for the reminder🙂 I’ll explain the above myths in my next blog post. Especially Myth #5🙂

  8. Iggy Fernandez
    December 6, 2011 at 12:57 pm

    Richard :

    It would be great if you explain the above myths with examples.
    Thanks a million.

    I’m addressing these myths in the Twelve Days of SQL series. Myth #5 is covered on Day 3. https://iggyfernandez.wordpress.com/2011/11/30/the-12-days-of-sql-day-1/

  9. March 28, 2015 at 11:23 am

    The statement “The cost-based optimizer selects the optimal execution plan—the one with the lowest cost” should be clarified.

    If we disable “Query Transformer” then Oracle will choose plan with the lowest cost.
    Otherwise some non-cost-based transformations can lead to plans with not the lowest cost.
    You can find an example here: https://sqlmdx.wordpress.com/2013/11/14/query-transformations-in-oracle-view-merging/

    On the other hand, it’s obvious that lowest cost does not guarantee minimal execution time in all cases.
    But there is strong correlation between cost and execution time.
    There was a nice post by JL about that:
    https://jonathanlewis.wordpress.com/2011/01/10/cost-again/

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: