Home > DBA, NoCOUG, Oracle, SQL > Who Should Tune SQL: The DBA or The Developer?

Who Should Tune SQL: The DBA or The Developer?

I am working on a proposal for my second book—this time, an SQL book—for Apress and have been re-reading my collection of SQL books.

In Oracle SQL High-Performance Tuning, Guy Harrison says:

 “For instance, when trying to tune someone else’s SQL, DBAs will have to expend a fair amount of effort in determining what the SQL is trying to do. They will need to understand the underlying data at least as well as the author of the SQL and may be concerned that in tuning the SQL, they might inadvertently change the semantics (the meaning) of the SQL. Generally, only the author of the SQL has all of the knowledge required to tune the SQL. [emphasis added] All that might be missing is the necessary SQL tuning principles—and that’s the purpose of the book.”

In SQL Tuning, Dan Tow says:

 “I assumed for years that the developers of an application, who wrote the SQL, would always understand far better than I how to fix it. … Here is where SQL delivered me, at least, a great surprise: you do not need to understand other people’s SQL to tune it! [emphasis added] Treat SQL as a spec—a clear and unambiguous declaration of which rows of which tables the application requires at some particular point in a program. … You do not need to know why the application needs those rows, or even what those rows represent. … All you need to know or figure out is how to reach those rows faster, and you can learn this by just examining the SQL, tables, and indexes involved, with simple queries to the database that are completely independent of the semantic content of the data.”

The above seemingly opposite viewpoints can be reconciled. You don’t need to determine what the SQL statement is trying to do if your only goal is to find the best query execution plan for it. But you do need to understand what it does if you’re trying to rewrite it in ways that perform better; completely replacing the SQL statement with a PL/SQL program or a program written in a conventional programming language—such as C or Java—is also one of the options that is available to you.

For example, consider any one of the nine solutions that were found for the First International NoCOUG SQL Challenge. You don’t need to understand what it does if all you’re trying to do is to find the best query plan for it. But you do need to understand what it does if you’re trying to replace it with a faster or more scalable solution.

The first solution—by Laurent Schneider from Switzerland—used the CONNECT BY clause, and the SYS_CONNECT_BY_PATH and XMLQUERY functions.

The second solution—by Craig Martin from the USA—used logarithms and anti-logarithms in conjunction with CONNECT BY and SYS_CONNECT_BY_PATH.

The third solution—by Rob van Wijk from Netherlands—used the MODEL clause.

The fourth solution—by Vadim Tropashko from the USA—used recursive common table expressions.

The fifth and sixth solutions—by Alberto Dell’Era—used advanced mathematical techniques such as convolutions, Discrete Fourier Transforms, and the Fast Fourier Transform.

The seventh solution—by Fabien Contaminard from France—was based on the multinomial distribution.

The eighth solution—by a blogger named Cd-MaN from Romania—used pipelined table functions and recursion.

The ninth solution—by André Araujo from Australia—used binary arithmetic and common table expressions to solve the problem.

My own position is that SQL should be “efficient by design.” The conventional wisdom is that developers need not concern themselves with performance. In a series of articles that I wrote for the NoCOUG Journal (SQL Sucks!—Part I, SQL Sucks!—Part II, SQL Sucks!—Part III), I listed the following “dangerous beliefs”:

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.

What do you think?

Categories: DBA, NoCOUG, Oracle, SQL
  1. July 12, 2009 at 1:06 pm

    Personally, I work in the space between and overlapping both DBA and developer. At my current client tuning is, in general, my job.

    I’ve never worked with a DBA who was terribly good at writing SQL let alone tuning it (that doesn’t mean they don’t exist, they do, I know some, I’ve just never worked with them).

    Unfortunately what this does reflect is that an awful lot of DBAs are concerned purely with providing a database service, and the database service can be meeting it’s target/SLA even if the application is not. And in these cases, the DBAs will say that it is an application problem.

    At the same time, an awful lot of developers, even Oracle specialists, can’t tune either.

    It is possible to tune SQL without knowing what it’s meant to be doing. 100% agree. I call it tuning “blind.” It can nearly always be effective. However, I’ve lost count of the number of times I’ve tuned a piece of poorly performing SQL, taken the time to find out what it’s meant to be doing and found defects in the SQL. So, both are possible. Blind tuning can be much quicker, it doesn’t raise all those awkward questions that no-one can answer. But tuning with the requirements can be more effective.

    • Iggy Fernandez
      July 12, 2009 at 9:39 pm

      I once encountered an SQL statement that did not have a full set of join conditions but was producing the correct results inspite of the cartesian joins 🙂 The full table scans hurt performance quite a bit though.

  2. Gary
    July 12, 2009 at 4:21 pm

    The developer should have responsibility for performance checking the SQL but this depends on them having a fully-sized environment on which to test it (or at least comparable), with the same database version/patch level and preferably similar parameter settings. Having to ‘test’ on a cut-down or artificially generated dataset can really throw things off.

  3. July 12, 2009 at 8:35 pm

    Couldn’t agree more with the list of dangerous beliefs.
    And yes: SQL should be efficient by design.
    Unfortunately nowadays, whenever one mentions the word “design”, the stampede is immediate.
    What is “kewl” is to play at a screen with a development tool. Thinking about the “why” is completely secondary and to be avoided at all costs…

    • July 13, 2009 at 1:18 am

      Design? Noons, you’re so old skool and waterfall. Today’s new agile methodologies (or implementations of agile at least) remove any need for design 😉

  4. Mark Ashford
    July 13, 2009 at 2:43 pm

    I think that if there is time, SQL code reviews make sense on tough SQL and that multiple developers &/or dba’s should be involved. Everyone has their own insight into the data, why not get as many points of view as possible? One, also, gets the adding advantage of training people to be better SQL tuners as ideas are shared. Not sure putting the responsibility on either side exclusively makes for the best product, when a collaborative may get a better result.

    • Iggy Fernandez
      July 13, 2009 at 7:12 pm

      That has to be the last word. It’s been more than ten years since I was last asked to participate in a code review.

  5. APC
    July 18, 2009 at 10:51 pm

    An increasingly common problem these days is that there is no developer, at least when it comes to the SQL. The actual statements are generated by an ORM which produces “platform neutral” SELECT statements, frequently of stunning nastiness. The use of such tools mean that the developers don’t have ownership of the SQL. they probably don’t understand it and they certainly can’t tune it.

  1. July 17, 2009 at 11:57 am

Leave a Reply to Mark Ashford Cancel 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 )

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

%d bloggers like this: