Cardinality the heart of SQL tuning

I’m at the East Coast Oracle Users Conference and wanted to write a blog post about the first day, which is now done.  Rather than summarize all the sessions I thought I would focus on what struck me the most – namely the confirmation I received that cardinality is at the heart of SQL tuning.

I was reviewing my upcoming SQL tuning presentation and became afraid that I had focused too much on determining the number of rows, or cardinality, that queries or parts of queries returned.  But I attended two Tom Kyte sessions this morning that both reenforced the idea that I was on the right track.  One quote from a session was “A wrong plan is always caused by wrong cardinality estimates”.  He revised “always” to “almost certainly” which is still pretty strong.

My talk isn’t only about poorly performing queries where the optimizer has a bad cardinality estimate.  It is also about queries where the estimate is correct but no good plan exists.  I.e. You might need an index.  But, when I manually tune a query, regardless of why it is slow, I start with doing counts on various parts of the query to find out the true cardinality.

Evidently Oracle is putting research and development money into improving the optimizer’s cardinality estimates in future releases.  If Oracle and Tom Kyte see cardinality as central to proper query optimization and if my own experience confirms it then it makes a strong case to me that cardinality is at the heart of SQL tuning – whether automatically done by the optimizer or manually done by a human.

– Bobby

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I've been working as an Oracle database administrator and PeopleSoft administrator since 1994. I'm very interested in Oracle performance tuning.
This entry was posted in Uncategorized. Bookmark the permalink.

2 Responses to Cardinality the heart of SQL tuning

  1. Jameson says:

    THanks for sharing your thoughts on cardinality and it’s importance. Also found this page to be helpful:

    http://www.programmerinterview.com/index.php/database-sql/cardinality-in-sql/

  2. Bobby says:

    Jameson,

    Thanks for your comment. It is interesting that the article describes the number of unique values of a column as its cardinality. In Oracle terms this would be the number of distinct values or NDV. I guess the term has different definitions in different contexts. I just think of cardinality as a synonym for the number of rows a SQL returns or the number of rows that a part of the execution plan acts on. i.e. to me cardinality = number of rows.

    – Bobby

Leave a Reply