A Fix for Check Constraints That Harm Cardinalities
Posted by David Aldridge on 2008-05-21
I wrote a while ago about the harmful effect of check constraints on query optimization. I’ll pause while you catch up on that …
Anyway, this appears to have been addressed in 10.2.0.4, and it’s now safe to go back into the water on applying check constraints. Bug 5891471.
The bug description also notes that you can set event 10195 to disable transitive predicate generation using check constraints.
I haven’t tested any of this — I will if I get time. Unless someoneelse wants to …
Neil Johnson said
After an upgrade from 8.1.7 to 10.2.0.1 we saw the following query parsing 50 times a second:
select condition from cdef$ where rowid=:1
This was causing a significant CPU drain (Bug 4900129) and event 10195 helped greatly (though we didn’t see the benefit until we got rid of the many histograms that were created automatically when we weren’t looking :))
David Aldridge said
8.1.7 to 10.2.0.1 — a bold jump, and I don’t envy you the amount of changes in the optimzer that you had to cope with.
One of the major gotchas on 10g seems to be the automatic statistics gathering job — is that what was generating those histograms?
Mark Brady said
I think the defaults for the auto stats gather is a histogram on EVERY column. n’est-ce pas?
Neil Johnson said
It was the automatic statistics gathering job that created the histograms. How they were missed in testing I don’t know – shame on us! Anyway METHOD_OPT = 1 fixed almost all our issues.
(apologies for late reply by the way – forgot I’d posted)