Presenting on professional conferences frequently brings out important points that were not highlighted well enough.
RMOUG 2015 was no different.
After I presented my techniques for performance tuning by accounting for the confidence of cardinality estimates (slides 35-46 ), an attendee asked why my way of optimization was better than well-established optimization methods, such as tuning by cardinality feedback.
Well, that was a good question!
The short answer is that the methods I presented are better when Oracle gets low confidence cardinality estimates, i.e. it is forced to guess, because the selectivity varies greatly across executions and Oracle has no way to account for that.
Since the matter got s bit abstract, let’s go through an example:
Let’s have a column Name, that would contain the names of people. Let see what our options for dealing with predicate, such as
Name like ‘%<Specific Name>%’
This predicate can be very selective, if the name is something like… IOTZOV. That is, predicate
name like ‘%IOTZOV%’
would return very few records.
The very same predicate can be not that selective, if the name is something like SMITH. That is, predicate
name like ‘%SMITH%’
could return a few records.
If there is no way for Oracle to figure out that one name (IOTZOV) is much more selective than another (SMITH) then the techniques I proposed for accounting for the confidence of cardinality estimates are probably the best choice.
If there were a way for Oracle to figure out that one name (IOTZOV) is much more selective than another (SMITH), then Oracle would have probably gotten a good plan anyway.
If all names (IOTZOV, SMITH) have similar selectivity, but Oracle cannot figure it out for whatever reason, then we can use other techniques to feed Oracle the correct info. In this case, the other optimization techniques can lead to faster execution plans than the confidence of cardinality techniques I proposed.