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 …
2008-05-22 at 6:33 am
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 :))
2008-05-22 at 7:31 pm
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?
2008-06-19 at 10:55 am
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)