V$SQL_HINT

A new feature in Oracle 11g Release 2 is the new V$SQL_HINT dynamic performance view.

As the name suggests V$SQL_HINT is a list of valid optimizer hints. To my knowledge these have never been publicly documented before. Indeed the Oracle 11.2 SQL Reference still only lists around 70 hints, albeit the most popular ones. In Oracle 11.2.0.1, 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 earliest version reported is 8.0.0 although hints existed prior to this release.

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 information is only currently available in Oracle 11.2 and above, but is relevant for all users back as far as Oracle 8i, the most signficant columns on this website (here)

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 a future release a V$SQL_HINT_PARAMETER view containing the position and type of each hint parameter would be very useful.