Here’s an interesting point that’s just been raised on the Oracle-L list-server:
Metalink bug: 5483301.8
If a column has a frequency histogram and a query uses an equality predicate over the column looking for a value that does not exist in the histogram then the CBO was estimating a cardinality of 1. This could favor Nested Loops too much.
The fix introduces a notable change in behavior: instead of estimating a cardinality of 1 then with this fix CBO estimates a cardinality of (0.5 * the lowest cardinality found in the histogram). This can result in plan changes.
Funnily enough, I had a client who ran into a problem assoiated with this cardinality effect just a couple of weeks ago – although their “real” problem was that the automatic stats collection routine in 10g had created a histogram that wasn’t needed. So I was nearly pleased to see the change – except that (a) it spoils the solution I’ve previously used for this problem and (b) any change is likely to be bad for some people.
The latter problem (one man’s fix is another man’s bug) appears as bug 6082745.8 – which says that if the patch for 5483301 causes you problems, you either have to drop the frequency histogram, or you could “unfix” the patch by setting parameter “_fix_control” to ‘5483301:off’