V$SQL_FEATURE
Continuing my investigations into dynamic performance views that are new in
Oracle 11.2.0.1, this week I have been looking at V$SQL_FEATURE. This view
contains a list of 497 optimizer features.
V$SQL_FEATURE contains three columns; SQL_FEATURE which is the name of the
feature, DESCRIPTION which contains a short description and PROPERTY which
is only set to a non-zero value for nine features, all of which appear to be
query transformations.
On closer inspection, 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.
V$SQL_FEATURE has two related dynamic performance views:
-
V$SQL_FEATURE_DEPENDENCY lists dependencies between features. This view
contains two columns, SQL_FEATURE (the name of the feature) and
DEPEND_ON (the name of the feature on which it depends).
Only one feature (QKSFM_OR_EXPAND) is listed as having a dependency
(QKSFM_JPPD). I suspect this view has not been fully implemented in
Oracle 11.2.0.1
-
V$SQL_FEATURE_HIERARCHY lists the hierarchy of features. This view
contains two columns, SQL_FEATURE (the name of the feature) and PARENT_ID
(the name of any parent). In Oracle 11.2.0.1 this view contains 501 rows.
The PARENT_ID is NULL for 414 rows including all of the bug fixes.
Four of the features have two parents (QKSFM_UNNEST, QKSFM_JPPD, QKSFM_CVM
and QKSFM_CBQT), the remaining 83 features just have one parent.
These are not the most useful set of dynamic performance views for DBAs, but
[they will definitely be of interest to students and researchers of the
optimizer. A list of the optimizer features reported by V$SQL_FEATURE in
Oracle 11.2.0.1 (excluding the bug fixes) is available here
