Continuing my investigations into dynamic performance views that are new in Oracle 220.127.116.11, 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 18.104.22.168 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 22.214.171.124
- 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 126.96.36.199 this view contains 501 rows. The PARENT_ID is NULL for 414 rows including all of the bug fixes. Surprisingly four of the features have two parents (QKSFM_UNNEST, QKSFM_JPPD, QKSFM_CVM and QKSFM_CBQT), the remaining 83 features just have one parent.
In summary 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 188.8.131.52 (excluding the bug fixes) is available on my website here