A short memo about _fix_control
While reading Jonathan Lewis blog about Histogram change I noticed interesting comment by Randolf Geist in which he mentioned a “feature” introduced in 10.2.0.2 – an undocumented parameter _fix_control that can be used to turn off/on a particular bug fix. I was not aware of this parameter until now.
Usually this sort of the parameter could be handy when some bug fix introduces more headache to a DBA than merits. One such example for a “bug fix” is the one Randolf and Jonathan discussed, Bug 5483301: Cardinality of 1 when predicate value non-existent in frequency histogram.
We could turn off this fix with the command:
alter system set "_fix_control"='5483301:off' scope=both; or for the session alter session set "_fix_control"='5483301:off';
Be careful using this parameter (or any other underscore parameter!) on your production servers. A quick search on Google revealed some “testimonials” such as the one at Pythian (see “Undocumented parameter _fix_control Or How to break your database”), where someone with the name Abel (apparently working in Oracle support) shed some light on this feature. According to Abel every bug fix that could be turned off must be registered (check v$system_fix_control), unless Oracle developer that wrote the bug fix code doesn’t want to register the patch or simply forgets to do the registration. Abel listed bug fixes related to the wrong results as the ones that are not registered on purpose. Forgotten bug numbers that users found should be reported to Oracle, so that they can take care about registration. Sweet.
That’s why it’s highly recommended that you beforehand check if particular bug fix is registered or not:
SQL> select bugno, value, description, optimizer_feature_enable from v$system_fix_control where bugno=5483301; BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ---------- --------------------- ------------------------- 5483301 1 Use min repeat count 10.2.0.4 in freq histogram to compute the density
Personally, I would use _fix_control as my last resort, turning off individual fixes sounds to me a risky business due to the fact that such combination is very likely not regression tested. On the other hand, it can be handy when trying to prove some thesis on non-production servers, a real time saver compared with fresh server installation with lower patch set version.