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