Home > DBA, Oracle, SQL > SQL Ridicu-List: Let He Who Is Without Sin Cast The First Stone

SQL Ridicu-List: Let He Who Is Without Sin Cast The First Stone


Kerry Osborne encountered a fairly odd SQL query which performed a LIKE comparison without actually using the LIKE preposition. If there was a SQL “Ridicu-List” it would easily make the cut. Let he who is without sin cast the first stone.

But most of us are guilty of the same sins. The lesser sin of the programmer was the crime of ignorance but few of us are all-knowing. The greater sin of the programmer was to ignore the performance aspect but who amongst us has not done the same on at least one occasion? Jonathan Lewis recalls a teachable moment when he brought a production database to its knees by querying a fixed table.

Some years ago, I wrote an article for the NoCOUG Journal in which I listed five “dangerous beliefs” centered round the theme that programmers have no responsibility for the performance of SQL statements:

Dangerous Belief #1: DBAs bear chief responsibility for the performance of SQL statements.

Dangerous Belief #2: Applications should be designed without reference to the way data is stored, e.g., index-organized tables, hash clusters, partitions, etc.

Dangerous Belief #3: Application programmers should not tailor their SQL statements to make use of existing indexes. DBAs should instead create traps to catch badly performing SQL at runtime and create new indexes as necessary to make them perform better.

Dangerous Belief #4: It is not necessary to review the Query Execution Plan of an SQL statement before releasing it into a production environment. It is further not necessary to freeze the Query Execution Plan of an SQL statement before releasing it into a production environment. It is desirable that Query Execution Plans change in response to changes in the statistical information that the query optimizer relies upon. Such changes are always for the better.

Dangerous Belief #5: The most common cause of poorly performing SQL is the failure of the DBA to collect statistical information on the distribution of data for the use of the query optimizer. This statistical information should be refreshed frequently.

There is no one to blame for the prevalence of these beliefs except ourselves since we’ve been spreading them for decades.

Categories: DBA, Oracle, SQL
  1. January 3, 2011 at 6:33 pm

    Good post Iggy. I really like the title.

  2. January 5, 2011 at 11:18 am

    I propose a new “dangerous belief” be considered, because I am seeing a lot of this on my latest development project:

    1) Data models should not consider common query access to meet business objectives. Modelling to third normal form is more than sufficient. Poorly performing SQL can be addressed by the developers and DBAs at runtime.

    Explanation:

    Your very accurate dangerous beliefs above are at least in part the product of the above. With purchased products and third party applications, it’s already too late to consider #1, but because so many people use off the shelf packages today, they fail to consider it when designing in house. If common query patterns to meet business requirements were considered by the physical designers, most of the needed structures (including indexes and partitioning) would already be in place, precluding the need for the tedious analysis which follows from the errant beliefs.

    Thanks for reading.

    Good day!

  3. Iggy Fernandez
    January 5, 2011 at 1:41 pm

    Well said, Michael. Performance should be considered sooner rather than later during the software development life cycle because performance is also a deliverable of software development.

  4. Paresh
    January 9, 2011 at 9:26 pm

    Hi Iggy,

    Are you trying to say that we should *always* (the key word to my questions is are you saying *always* do this:
    1) Freeze execution plan of all SQL statements as they are being released into production environment?
    2) And do not allow the plan to change as per stats collected by the optimizer

    If that is the case can you clarify why you think so.

  5. Iggy Fernandez
    January 10, 2011 at 12:17 am

    The Oracle optimizer generates execution plans at execution time. The execution plan may change if the statistics (or any other inputs to the optimization process) change and not provably for the better. The “dangerous belief” is that such changes to execution plans are *always* for the better. Hints, stored outlines, and SQL profiles can be used to improve the stability of execution plans for critical queries.

    Paresh :

    Are you trying to say that we should *always* (the key word to my questions is are you saying *always* do this:
    1) Freeze execution plan of all SQL statements as they are being released into production environment?
    2) And do not allow the plan to change as per stats collected by the optimizer

    If that is the case can you clarify why you think so.

    That’s not what I was trying to say.

    P.S. It is instructive to compare the behavior of other database engines with that of Oracle. The DB2 optimizer generates execution plans for static SQL at compile time instead of execution time but the behavior can be changed for individual statements using the REOPT option.

    http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001935.htm

    BIND command

    Invokes the bind utility, which prepares SQL statements stored in the bind file generated by the precompiler, and creates a package that is stored in the database.

    http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0001990.htm

    REBIND command

    Allows the user to recreate a package stored in the database without the need for a bind file.

    http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/c0011580.htm

    Bind option REOPT enables reoptimization of SQL statements

    The bind option REOPT enables reoptimization of static and dynamic SQL statements. Specifically, the access path for an SQL statement containing host variables, special registers, or parameter markers is optimized using the values of these variables, rather than the default estimates determined by the compiler. This optimization takes place at query execution time when these values are available.

    You can set the bind option REOPT to one of the following three values:

    NONE

    The values of any host variables, parameter markers, or special registers in an SQL statement are not used to optimize this statement’s access path. The default estimates for these variables are used instead. This is the default behavior.

    ONCE

    When the statement is first executed, the access path for a given SQL statement is optimized using the actual values for any host variables, parameter markers, or special registers. This access path will be used in all subsequent executions of the statement.

    ALWAYS

    Each time that a statement containing host variables, parameter markers, or special registers is executed, the values of these variables are used to optimize the access path for the statement.

    http://publib.boulder.ibm.com/infocenter/db2luw/v9r8/index.jsp?topic=/com.ibm.db2.luw.admin.perf.doc/doc/c0055082.html

    Using the REOPT bind option with input variables in complex queries

  6. Paresh
    January 10, 2011 at 7:38 pm

    Hi Iggy,

    Thanks for clarifying my query. It is interesting to see/compare with how DB2 optimizer works which I am not very familiar with.

    Paresh

  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: