V$SQL_HINT

The V$SQL_HINT dynamic performance view was introduced in Oracle 11.2

V$SQL_HINT is a complete list of valid optimizer hints. For example, in Oracle 11.2 the SQL Reference lists around 70 hints; in Oracle 11.2.0.1, V$SQL_HINT lists a total of 263 hints.

V$SQL_HINT is based on X$QKSHT

In Oracle 12.1.0.2 V$SQL_FEATURE has the following columns:

NameData Type
NAMEVARCHAR2(64)
SQL_FEATUREVARCHAR2(64)
CLASSVARCHAR2(64)
INVERSEVARCHAR2(64)
TARGET_LEVELNUMBER
PROPERTYNUMBER
VERSIONVARCHAR2(25)
VERSION_OUTLINEVARCHAR2(25)
CON_IDNUMBER

NAME is the name of the hint

SQL_FEATURE is the feature which implements the hint.

Each hint also has a CLASS of which there are 200 in Oracle 12.1.0.2 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. Similarly the inverse of PUSH_PRED is NO_PUSH_PRED. 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.

VERSION is the number of the Oracle version in which the hint was introduced. The earliest version reported is 8.0.0 although hints existed prior to this release. V$SQL_HINT was only introduced in Oracle 11.2 and above, but the information contained therein is relevant for all users back as far as Oracle 8i. The most signficant columns are included (here)

The following table shows the number of hints in various versions:

Version # Hints
11.2.0.1 263
11.2.0.3 273
11.2.0.4 277
12.1.0.1 314
12.1.0.2 332

The following table shows the number of new hints in versions since Oracle 8.0.0:

Version # Hints
8.0.0 14
8.1.0 50
8.1.5 18
8.1.6 4
8.1.7 2
9.0.0 26
9.2.0 13
10.1.0.3 42
10.2.0.1 35
10.2.0.2 4
10.2.0.3 3
10.2.0.4 2
10.2.0.5 4
11.1.0.6 32
11.1.0.7 5
11.2.0.1 19
11.2.0.2 6
11.2.0.3 6
11.2.0.4 2
12.1.0.1 37
12.1.0.2 18