V$SQL_FEATURE

V$SQL_FEATURE reports the optimizer features and fixes included in the kernel.

V$SQL_FEATURE is based on X$QKSFM

In Oracle 12.1.0.2 V$SQL_FEATURE has the following columns:

NameData Type
SQL_FEATUREVARCHAR2(64)
DESCRIPTIONVARCHAR2(64)
PROPERTYNUMBER
CON_IDNUMBER

SQL_FEATURE is the name of the feature

DESCRIPTION contains a short description

PROPERTY - in Oracle 12.1.0.2 this column is only set to a non-zero value for 12 features, all of which are query transformations.

The majority of features listed in V$SQL_FEATURE are actually bug fixes. In Oracle 11.2.0.1 a total of 407 fixes are listed. This leaves us with 90 actual features.

Fixes can be identified from the DESCRIPTION column using

WHERE description LIKE 'Fix%' 

Individual fixes can be enabled and disabled. For example to disable fix 6776808 use:

ALTER SESSION SET "_fix_control"='6776808:off';

The following table summarizes the number of features and fixes in various releases:

Version Feature Fix Total
11.2.0.1 90 407 497
11.2.0.3 94 654 748
11.2.0.4 98 846 944
12.1.0.1 110 885 996
12.1.0.2 115 1071 1186

V$SQL_FEATURE has two related dynamic performance views:

V$SQL_FEATURE_DEPENDENCY
V$SQL_FEATURE_HIERARCHY

A list of the optimizer features reported by V$SQL_FEATURE in Oracle 11.2.0.1 (excluding the bug fixes) is available here