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:
Name | Data Type |
NAME | VARCHAR2(64) |
SQL_FEATURE | VARCHAR2(64) |
CLASS | VARCHAR2(64) |
INVERSE | VARCHAR2(64) |
TARGET_LEVEL | NUMBER |
PROPERTY | NUMBER |
VERSION | VARCHAR2(25) |
VERSION_OUTLINE | VARCHAR2(25) |
CON_ID | NUMBER |
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.
The following table shows the number of hints in various versions:
The following table shows the number of new hints in versions since
Oracle 8.0.0:
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
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