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. 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 11.2.0.1 (excluding the bug fixes) is available on my website here

V$SQL_HINT

There are some really useful features that I suspect the Oracle marketing department will never mention when promoting Oracle 11g Release 2. One of them is the new V$SQL_HINT dynamic performance view.

As the name suggests V$SQL_HINT is a list of valid optimizer hints. These have never been publicly documented before to my knowledge. Indeed the Oracle 11.2 SQL Reference still only lists around 70, albeit the most popular ones. V$SQL_HINT lists a total of 263 hints. The underlying fixed table X$QKSHT lists the same number of hints suggesting that there is no concept of hidden hints in the same way that, for example, there are hidden (underscore) parameters.

For each hint the table lists the SQL_FEATURE to which the hint belongs. There are 66 distinct features in Oracle 11.2.0.1 of which the CBO (QKSFM_CBO) is the most popular with 58 hints. 

Each hint also has a CLASS of which there are 167 in Oracle 11.2.0.1. The most popular class is ACCESS which includes 25 hints include FULL and the family of INDEX hints.

A limited number of hints also have an INVERSE. For example the inverse of UNNEST is NO_UNNEST and vice versa. The older format for inverse hints which did not include the underscore (for example NOPARALLEL as opposed to NO_PARALLEL) is not reported in this view.

Last but not least the number of the version in which each hint was first introduced is also included in the VERSION column. Strangely the earlier version reported is 8.0.0; I am sure that hints existed prior to Oracle 8i.

There are a couple of other columns notably VERSION_OUTLINE (probably the first version in which the hint could appear in an outline. However it is difficult to say definitively as at the time of writing the V$SQL_HINT dynamic performance view had not yet been included in the Oracle Database Reference for 11gR2.

As this data is only currently available in Oracle 11.2, but is relevant for all users back as far as Oracle 8i, I have decided to list the most signficant columns on my website (http://www.juliandyke.com/Optimisation/Hints/Hints.html)

Whilst V$SQL_HINT contains some very interesting information, there is no companion view listing the parameters available within hints (though there may be some information about the number and type of parameters encrypted in the PROPERTY column). In the next release a V$SQL_HINT_PARAMETER view containing the position and type of each hint parameter would be very useful.