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:
Name | Data Type |
SQL_FEATURE | VARCHAR2(64) |
DESCRIPTION | VARCHAR2(64) |
PROPERTY | NUMBER |
CON_ID | NUMBER |
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