Oracle optimizer features enable and disable

With each version or release of Oracle Database, new features and bug fixes added in the optimizer, this is good initially but any code modification may include new bugs.

Right Decision, Wrong Decision Road SignWhen upgrading from one version of Oracle to a higher is possible that some queries run really bad, this can be solved with a workaround like (if we have migrated from 11gR2 10.2.0.4 for example):

Source   
ALTER session SET optimizer_features_enabled='10.2.0.4';

or to make it permanent:

Source   
ALTER system SET optimizer_features_enabled='10.2.0.4' scope=BOTH;

This instruction blocks off features. We can see the number of active characteristics:

Source   
SELECT optimizer_feature_enable,count(*)
FROM v$system_fix_control
GROUP BY optimizer_feature_enable ORDER BY 1 ASC;

OPTIMIZER_FEATURE_ENABLE

COUNT(*)

10.1.0

3

10.1.0.3

1

10.1.0.5

2

10.2.0.1

7

10.2.0.2

12

10.2.0.3

11

10.2.0.4

73

10.2.0.5

112

11.1.0.6

40

11.1.0.7

19

11.2.0.1

67

11.2.0.2

106

11.2.0.3

75

8.0.0

63

8.1.6

1

8.1.7

2

9.2.0

8

9.2.0.8

3

47

We can see more detail of each of the characteristics, for example:

Source   
SELECT *
FROM v$system_fix_control
WHERE optimizer_feature_enable='11.2.0.3';

BUGNO

VALUE

SQL_FEATURE

DESCRIPTION

OPTIMIZER_FEATURE_ENABLE

EVENT

IS_DEFAULT

9702850

1

QKSFM_ACCESS_PATH_9702850

adjust for NULLs only once for NOT inlists

11.2.0.3

0

1

9958518

1

QKSFM_CBO_9958518

relax type matching for dates in transitive predicate generation

11.2.0.3

0

1

8349119

1

QKSFM_FBA_8349119

parallel versions query for archiving for flashback archive

11.2.0.3

0

1

If we are fully sure our decisions must be implemented correctly and not trip time (something that should be analyzed), we can assume that this is a bug within one of optimizer improvements.

To detect the bug is very simple:

  1. Progressively raising the parameter optimizer_features_enable progressively to meet the problem.
  2. Examining improvements have been included in the optimizer examining table v$system_fix_control consultation with affected localized and go checking one by one (using the description field), if it fits our cases.

Once you detected the bug should disable this improvement (where BUGNO can be obtained from the previous query):

Source   
ALTER session SET "_fix_control"='BUGNO:OFF';

Or

Source   
ALTER system SET "_fix_control"='BUGNO:OFF';

The next step is to look at metalink related information and if necessary create a SR.

Leave a Reply