Search

OakieTags

Who's online

There are currently 0 users and 46 guests online.

Recent comments

Affiliations

Understanding what a hint affects using the V$SQL_FEATURE views

You may have used the Oracle 11g V$SQL_HINT view already – it displays all the valid hints (both documented and undocumented ones) available in your Oracle version, for example:

SQL> @hint merge

NAME                                                             VERSION                   VERSION_OUTLINE           INVERSE
---------------------------------------------------------------- ------------------------- ------------------------- ----------------------------------------------------------------
MERGE_CONST_ON                                                   8.0.0
MERGE_AJ                                                         8.1.0                     8.1.7
MERGE_SJ                                                         8.1.0                     8.1.7
MV_MERGE                                                         9.0.0
MERGE                                                            8.1.0                     10.1.0                    NO_MERGE
NO_MERGE                                                         8.0.0                     10.1.0                    MERGE
USE_MERGE_CARTESIAN                                              11.1.0.6                  11.1.0.6
USE_MERGE                                                        8.1.0                     8.1.7                     NO_USE_MERGE
NO_USE_MERGE                                                     10.1.0.3                  10.1.0.3                  USE_MERGE

But there’s more, (semi)undocumented views like V$SQL_FEATURE and V$SQL_FEATURE_HIERARCHY do give us more information about what these hints relate to. For example, if you have ever wondered why is there a MERGE hint and then also a USE_MERGE hint, you can check what do these hints control using my hinth.sql (Hint Hierarchy) script:

SQL> @hinth MERGE
Display Hint feature hierarchy for hints like MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE                                                            ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM

So, the MERGE hints seem to affect the CBO’s query transformation code – (CBQT means Cost-Based Query Transformation and CVM means Complex View Merging, but more about that later).

SQL> @hinth USE_MERGE
Display Hint feature hierarchy for hints like USE_MERGE

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
USE_MERGE                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE

And the USE_MERGE hint is about controlling the use of a join method – the sort-merge join.

Let’s list all hints having NL in them:

SQL> @hinth %NL%
Display Hint feature hierarchy for hints like %NL%

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
INLINE_XMLTYPE_NT                                                ALL
NL_SJ                                                            ALL -> COMPILATION -> CBO
NL_AJ                                                            ALL -> COMPILATION -> CBO
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_USE_NL                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL                                                           ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX                                                ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_CONNECT_BY_CB_WHR_ONLY                                        ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY                                           ALL -> COMPILATION -> TRANSFORMATION
INLINE                                                           ALL -> COMPILATION -> TRANSFORMATION
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_NLJ_BATCHING                                                  ALL -> EXECUTION
NLJ_BATCHING                                                     ALL -> EXECUTION
NO_NLJ_PREFETCH                                                  ALL -> EXECUTION
NLJ_PREFETCH                                                     ALL -> EXECUTION

Plenty of interesting stuff here – the new hint TABLE_LOOKUP_BY_NL that has showed up recently seems to have to do with star transformations for example (I just learned this myself from this output).

Interestingly the NLJ_BATCHING and NLJ_PREFETCH hints are considered as execution phase hints apparently (that was my term, I’m thinking about hints (also) affecting a decision in the execution phase, not just during optimization). For example, normally the NLJ prefetch feature can be dynamically turned on & off during the query execution, I guess with a hint this feature would be always enabled (I’m not sure about this here, just trying to reason why a hint is shown to be related to “execution” phase).

If optimizer feature terms like CBQT and CVM do not immediately ring a bell, you can use the V$SQL_FEATURE view (or my sqlfh.sql script) to list some more info about what these SQL feature name abbreviations mean and where in the hierarchy does this particular feature stand.

The script below doesn’t accept any parameters, prints out the entire SQL feature hierarchy (except the temporary bugfix features you can see from V$SYSTEM_FIX_CONTROL):

SQL> @sqlfh

SQL_FEATURE                                             DESCRIPTION
------------------------------------------------------- ----------------------------------------------------------------
ALL                                                     A Universal Feature
  COMPILATION                                           SQL COMPILATION
    CBO                                                 SQL Cost Based Optimization
      ACCESS_PATH                                       Query access path
        AND_EQUAL                                       Index and-equal access path
        BITMAP_TREE                                     Bitmap tree access path
        FULL                                            Full table scan
        INDEX                                           Index
        INDEX_ASC                                       Index (ascending)
        INDEX_COMBINE                                   Combine index for bitmap access
        INDEX_DESC                                      Use index (descending)
        INDEX_FFS                                       Index fast full scan
        INDEX_JOIN                                      Index join
        INDEX_RS_ASC                                    Index range scan
        INDEX_RS_DESC                                   Index range scan descending
        INDEX_SS                                        Index skip scan
        INDEX_SS_ASC                                    Index skip scan ascending
        INDEX_SS_DESC                                   Index skip scan descending
        SORT_ELIM                                       Sort Elimination Via Index
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      CURSOR_SHARING                                    Cursor sharing
      DML                                               DML
      JOIN_METHOD                                       Join methods
        USE_HASH                                        Hash join
        USE_MERGE                                       Sort-merge join
        USE_MERGE_CARTESIAN                             Merge join cartesian
        USE_NL                                          Nested-loop join
        USE_NL_WITH_INDEX                               Nested-loop index join
      JOIN_ORDER                                        Join order
      OPT_MODE                                          Optimizer mode
        ALL_ROWS                                        All rows (optimizer mode)
        CHOOSE                                          Choose (optimizer mode)
        FIRST_ROWS                                      First rows (optimizer mode)
      OR_EXPAND                                         OR expansion
      OUTLINE                                           Outlines
      PARTITION                                         Partition
      PQ                                                Parallel Query
        PARALLEL                                        Parallel table
        PQ_DISTRIBUTE                                   PQ Distribution method
        PQ_MAP                                          PQ slave mapper
        PX_JOIN_FILTER                                  Bloom filtering for joins
      STAR_TRANS                                        Star Transformation
        TABLE_LOOKUP_BY_NL                              Table Lookup By Nested Loop
      STATS                                             Optimizer statistics
        CARDINALITY                                     Cardinality computation
        COLUMN_STATS                                    Basic column statistics
        CPU_COSTING                                     CPU costing
        DBMS_STATS                                      Statistics gathered by DBMS_STATS
        DYNAMIC_SAMPLING                                Dynamic sampling
        DYNAMIC_SAMPLING_EST_CDN                        Estimate CDN using dynamic sampling
        GATHER_PLAN_STATISTICS                          Gather plan statistics
        INDEX_STATS                                     Basic index statistics
        OPT_ESTIMATE                                    Optimizer estimates
        TABLE_STATS                                     Basic table statistics
    QUERY_REWRITE                                       query rewrite with materialized views
    RBO                                                 SQL Rule Based Optimization
    SQL_CODE_GENERATOR                                  SQL Code Generator
    SQL_PLAN_MANAGEMENT                                 SQL Plan Management
    TRANSFORMATION                                      Query Transformation
      CBQT                                              Cost Based Query Transformation
        CVM                                             Complex View Merging
        DIST_PLCMT                                      Distinct Placement
        JOINFAC                                         Join Factorization
        JPPD                                            Join Predicate Push Down
        PLACE_GROUP_BY                                  Group-By Placement
        PULL_PRED                                       pull predicates
        STAR_TRANS                                      Star Transformation
          TABLE_LOOKUP_BY_NL                            Table Lookup By Nested Loop
        TABLE_EXPANSION                                 Table Expansion
        UNNEST                                          unnest query block
      HEURISTIC                                         Heuristic Query Transformation
        CNT                                             Count(col) to count(*)
        COALESCE_SQ                                     coalesce subqueries
        CSE                                             Common Sub-Expression Elimination
        CVM                                             Complex View Merging
        FILTER_PUSH_PRED                                Push filter predicates
        FULL_OUTER_JOIN_TO_OUTER                        Join Conversion
        JPPD                                            Join Predicate Push Down
        OBYE                                            Order-by Elimination
        OLD_PUSH_PRED                                   Old push predicate algorithm (pre-10.1.0.3)
        OUTER_JOIN_TO_ANTI                              Join Conversion
        OUTER_JOIN_TO_INNER                             Join Conversion
        PRED_MOVE_AROUND                                Predicate move around
        SET_TO_JOIN                                     Transform set operations to joins
        SVM                                             Simple View Merging
        TABLE_ELIM                                      Table Elimination
        UNNEST                                          unnest query block
        USE_CONCAT                                      Or-optimization
    XML_REWRITE                                         XML Rewrite
      CHECK_ACL_REWRITE                                 Check ACL Rewrite
      COST_XML_QUERY_REWRITE                            Cost Based XML Query Rewrite
      XMLINDEX_REWRITE                                  XMLIndex Rewrite
  EXECUTION                                             SQL EXECUTION

I highlighted the CVM and CBQT lines above…

Just for reference (and if you’re too lazy to run these scripts yourself), I’ve pasted the full output of the hint feature hierarchy script too (executed in my 11.2.0.3 DB):

SQL> @hinth %
Display Hint feature hierarchy for hints like %

NAME                                                             PATH
---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
NO_XDB_FASTPATH_INSERT                                           ALL
XDB_FASTPATH_INSERT                                              ALL
NO_USE_HASH_GBY_FOR_PUSHDOWN                                     ALL
USE_HASH_GBY_FOR_PUSHDOWN                                        ALL
XMLINDEX_SEL_IDX_TBL                                             ALL
NO_DST_UPGRADE_INSERT_CONV                                       ALL
DST_UPGRADE_INSERT_CONV                                          ALL
NO_CONNECT_BY_ELIM_DUPS                                          ALL
CONNECT_BY_ELIM_DUPS                                             ALL
NO_MONITOR                                                       ALL
MONITOR                                                          ALL
NO_NATIVE_FULL_OUTER_JOIN                                        ALL
NATIVE_FULL_OUTER_JOIN                                           ALL
NO_CONNECT_BY_COMBINE_SW                                         ALL
CONNECT_BY_COMBINE_SW                                            ALL
OPT_PARAM                                                        ALL
OUTLINE_LEAF                                                     ALL
OUTLINE                                                          ALL
NO_CARTESIAN                                                     ALL
INCLUDE_VERSION                                                  ALL
RESTRICT_ALL_REF_CONS                                            ALL
NO_ACCESS                                                        ALL
HASH                                                             ALL
DRIVING_SITE                                                     ALL
CACHE_TEMP_TABLE                                                 ALL
QB_NAME                                                          ALL
NO_STATS_GSETS                                                   ALL
NO_USE_HASH_AGGREGATION                                          ALL
USE_HASH_AGGREGATION                                             ALL
NO_MODEL_PUSH_REF                                                ALL
MODEL_NO_ANALYSIS                                                ALL
SCN_ASCENDING                                                    ALL
TIV_GB                                                           ALL
PIV_GB                                                           ALL
TIV_SSF                                                          ALL
PIV_SSF                                                          ALL
NO_CONNECT_BY_FILTERING                                          ALL
CONNECT_BY_FILTERING                                             ALL
BYPASS_RECURSIVE_CHECK                                           ALL
SYS_RID_ORDER                                                    ALL
NO_BASETABLE_MULTIMV_REWRITE                                     ALL
NO_MULTIMV_REWRITE                                               ALL
REMOTE_MAPPED                                                    ALL
NO_GBY_PUSHDOWN                                                  ALL
GBY_PUSHDOWN                                                     ALL
IGNORE_OPTIM_EMBEDDED_HINTS                                      ALL
DB_VERSION                                                       ALL
OPTIMIZER_FEATURES_ENABLE                                        ALL
USE_WEAK_NAME_RESL                                               ALL
IGNORE_WHERE_CLAUSE                                              ALL
INLINE_XMLTYPE_NT                                                ALL
NESTED_TABLE_FAST_INSERT                                         ALL
NESTED_TABLE_SET_SETID                                           ALL
PRESERVE_OID                                                     ALL
NESTED_TABLE_GET_REFS                                            ALL
DEREF_NO_REWRITE                                                 ALL
NO_SQL_TUNE                                                      ALL
NO_MONITORING                                                    ALL
NO_OUTER_JOIN_TO_ANTI                                            ALL -> COMPILATION -> CBO
OUTER_JOIN_TO_ANTI                                               ALL -> COMPILATION -> CBO
NO_FULL_OUTER_JOIN_TO_OUTER                                      ALL -> COMPILATION -> CBO
FULL_OUTER_JOIN_TO_OUTER                                         ALL -> COMPILATION -> CBO
APPEND_VALUES                                                    ALL -> COMPILATION -> CBO
NUM_INDEX_KEYS                                                   ALL -> COMPILATION -> CBO
NO_DOMAIN_INDEX_FILTER                                           ALL -> COMPILATION -> CBO
DOMAIN_INDEX_FILTER                                              ALL -> COMPILATION -> CBO
NO_PARTIAL_COMMIT                                                ALL -> COMPILATION -> CBO
SKIP_UNQ_UNUSABLE_IDX                                            ALL -> COMPILATION -> CBO
X_DYN_PRUNE                                                      ALL -> COMPILATION -> CBO
ROWID                                                            ALL -> COMPILATION -> CBO
CLUSTER                                                          ALL -> COMPILATION -> CBO
NO_SWAP_JOIN_INPUTS                                              ALL -> COMPILATION -> CBO
SWAP_JOIN_INPUTS                                                 ALL -> COMPILATION -> CBO
INDEX_RRS                                                        ALL -> COMPILATION -> CBO
NO_SUBQUERY_PRUNING                                              ALL -> COMPILATION -> CBO
SUBQUERY_PRUNING                                                 ALL -> COMPILATION -> CBO
USE_SEMI                                                         ALL -> COMPILATION -> CBO
USE_ANTI                                                         ALL -> COMPILATION -> CBO
QUEUE_ROWP                                                       ALL -> COMPILATION -> CBO
QUEUE_CURR                                                       ALL -> COMPILATION -> CBO
CACHE_CB                                                         ALL -> COMPILATION -> CBO
NO_PARALLEL                                                      ALL -> COMPILATION -> CBO
CURSOR_SHARING_EXACT                                             ALL -> COMPILATION -> CBO
NO_BUFFER                                                        ALL -> COMPILATION -> CBO
BUFFER                                                           ALL -> COMPILATION -> CBO
NO_QKN_BUFF                                                      ALL -> COMPILATION -> CBO
BITMAP                                                           ALL -> COMPILATION -> CBO
RESTORE_AS_INTERVALS                                             ALL -> COMPILATION -> CBO
SAVE_AS_INTERVALS                                                ALL -> COMPILATION -> CBO
CUBE_GB                                                          ALL -> COMPILATION -> CBO
SYS_PARALLEL_TXN                                                 ALL -> COMPILATION -> CBO
OVERFLOW_NOMOVE                                                  ALL -> COMPILATION -> CBO
HWM_BROKERED                                                     ALL -> COMPILATION -> CBO
LOCAL_INDEXES                                                    ALL -> COMPILATION -> CBO
BYPASS_UJVC                                                      ALL -> COMPILATION -> CBO
NL_SJ                                                            ALL -> COMPILATION -> CBO
HASH_SJ                                                          ALL -> COMPILATION -> CBO
MERGE_SJ                                                         ALL -> COMPILATION -> CBO
NL_AJ                                                            ALL -> COMPILATION -> CBO
HASH_AJ                                                          ALL -> COMPILATION -> CBO
MERGE_AJ                                                         ALL -> COMPILATION -> CBO
SEMIJOIN_DRIVER                                                  ALL -> COMPILATION -> CBO
SKIP_EXT_OPTIMIZER                                               ALL -> COMPILATION -> CBO
DOMAIN_INDEX_NO_SORT                                             ALL -> COMPILATION -> CBO
DOMAIN_INDEX_SORT                                                ALL -> COMPILATION -> CBO
ORDERED_PREDICATES                                               ALL -> COMPILATION -> CBO
ORDERED                                                          ALL -> COMPILATION -> CBO
FBTSCAN                                                          ALL -> COMPILATION -> CBO
MERGE_CONST_ON                                                   ALL -> COMPILATION -> CBO
STREAMS                                                          ALL -> COMPILATION -> CBO
EXPR_CORR_CHECK                                                  ALL -> COMPILATION -> CBO
VECTOR_READ_TRACE                                                ALL -> COMPILATION -> CBO
VECTOR_READ                                                      ALL -> COMPILATION -> CBO
DML_UPDATE                                                       ALL -> COMPILATION -> CBO
SQLLDR                                                           ALL -> COMPILATION -> CBO
SYS_DL_CURSOR                                                    ALL -> COMPILATION -> CBO
NO_REF_CASCADE                                                   ALL -> COMPILATION -> CBO
REF_CASCADE_CURSOR                                               ALL -> COMPILATION -> CBO
NOAPPEND                                                         ALL -> COMPILATION -> CBO
APPEND                                                           ALL -> COMPILATION -> CBO
AND_EQUAL                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> AND_EQUAL
BITMAP_TREE                                                      ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE
FULL                                                             ALL -> COMPILATION -> CBO -> ACCESS_PATH -> FULL
NO_USE_INVISIBLE_INDEXES                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
USE_INVISIBLE_INDEXES                                            ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
NO_INDEX                                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX                                                            ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX_ASC                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_ASC
INDEX_COMBINE                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_COMBINE
INDEX_DESC                                                       ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_DESC
NO_INDEX_FFS                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_FFS                                                        ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_JOIN                                                       ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_JOIN
INDEX_RS_ASC                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_ASC
INDEX_RS_DESC                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_DESC
NO_INDEX_SS                                                      ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS                                                         ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS_ASC                                                     ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_ASC
INDEX_SS_DESC                                                    ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_DESC
NO_MERGE                                                         ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE                                                            ALL -> COMPILATION -> CBO -> CBQT -> CVM
NO_PLACE_DISTINCT                                                ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
PLACE_DISTINCT                                                   ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
NO_FACTORIZE_JOIN                                                ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
FACTORIZE_JOIN                                                   ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
NO_PLACE_GROUP_BY                                                ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
PLACE_GROUP_BY                                                   ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
NO_PULL_PRED                                                     ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
PULL_PRED                                                        ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
NO_FACT                                                          ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE                                                  ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE                                                     ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
NO_UNNEST                                                        ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
NO_BIND_AWARE                                                    ALL -> COMPILATION -> CBO -> CURSOR_SHARING
BIND_AWARE                                                       ALL -> COMPILATION -> CBO -> CURSOR_SHARING
RETRY_ON_ROW_CHANGE                                              ALL -> COMPILATION -> CBO -> DML
CHANGE_DUPKEY_ERROR_INDEX                                        ALL -> COMPILATION -> CBO -> DML
IGNORE_ROW_ON_DUPKEY_INDEX                                       ALL -> COMPILATION -> CBO -> DML
NO_USE_HASH                                                      ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
USE_HASH                                                         ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
NO_USE_MERGE                                                     ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE_CARTESIAN                                              ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE_CARTESIAN
NO_USE_NL                                                        ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL                                                           ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX                                                ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
LEADING                                                          ALL -> COMPILATION -> CBO -> JOIN_ORDER
ALL_ROWS                                                         ALL -> COMPILATION -> CBO -> OPT_MODE -> ALL_ROWS
CHOOSE                                                           ALL -> COMPILATION -> CBO -> OPT_MODE -> CHOOSE
FIRST_ROWS                                                       ALL -> COMPILATION -> CBO -> OPT_MODE -> FIRST_ROWS
OR_EXPAND                                                        ALL -> COMPILATION -> CBO -> OR_EXPAND
NO_PARALLEL_INDEX                                                ALL -> COMPILATION -> CBO -> PQ
PARALLEL_INDEX                                                   ALL -> COMPILATION -> CBO -> PQ
NO_STATEMENT_QUEUING                                             ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
STATEMENT_QUEUING                                                ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
SHARED                                                           ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
NOPARALLEL                                                       ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
PQ_DISTRIBUTE                                                    ALL -> COMPILATION -> CBO -> PQ -> PQ_DISTRIBUTE
PQ_NOMAP                                                         ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
PQ_MAP                                                           ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
NO_PX_JOIN_FILTER                                                ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
PX_JOIN_FILTER                                                   ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
NO_FACT                                                          ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> CBO -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
CARDINALITY                                                      ALL -> COMPILATION -> CBO -> STATS
TABLE_STATS                                                      ALL -> COMPILATION -> CBO -> STATS
INDEX_STATS                                                      ALL -> COMPILATION -> CBO -> STATS
COLUMN_STATS                                                     ALL -> COMPILATION -> CBO -> STATS
NO_CPU_COSTING                                                   ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
CPU_COSTING                                                      ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
DBMS_STATS                                                       ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS
DYNAMIC_SAMPLING                                                 ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN                                         ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING_EST_CDN
GATHER_PLAN_STATISTICS                                           ALL -> COMPILATION -> CBO -> STATS -> GATHER_PLAN_STATISTICS
OPT_ESTIMATE                                                     ALL -> COMPILATION -> CBO -> STATS -> OPT_ESTIMATE
RBO_OUTLINE                                                      ALL -> COMPILATION -> RBO
RULE                                                             ALL -> COMPILATION -> RBO
NO_PRUNE_GSETS                                                   ALL -> COMPILATION -> TRANSFORMATION
MODEL_DONTVERIFY_UNIQUENESS                                      ALL -> COMPILATION -> TRANSFORMATION
MODEL_PUSH_REF                                                   ALL -> COMPILATION -> TRANSFORMATION
MODEL_COMPILE_SUBQUERY                                           ALL -> COMPILATION -> TRANSFORMATION
MODEL_DYNAMIC_SUBQUERY                                           ALL -> COMPILATION -> TRANSFORMATION
MODEL_MIN_ANALYSIS                                               ALL -> COMPILATION -> TRANSFORMATION
NO_EXPAND_GSET_TO_UNION                                          ALL -> COMPILATION -> TRANSFORMATION
EXPAND_GSET_TO_UNION                                             ALL -> COMPILATION -> TRANSFORMATION
MV_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_COST_BASED                                         ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_COST_BASED                                            ALL -> COMPILATION -> TRANSFORMATION
INLINE                                                           ALL -> COMPILATION -> TRANSFORMATION
MATERIALIZE                                                      ALL -> COMPILATION -> TRANSFORMATION
REWRITE_OR_ERROR                                                 ALL -> COMPILATION -> TRANSFORMATION
NO_REWRITE                                                       ALL -> COMPILATION -> TRANSFORMATION
REWRITE                                                          ALL -> COMPILATION -> TRANSFORMATION
NO_SEMIJOIN                                                      ALL -> COMPILATION -> TRANSFORMATION
SEMIJOIN                                                         ALL -> COMPILATION -> TRANSFORMATION
ANTIJOIN                                                         ALL -> COMPILATION -> TRANSFORMATION
NO_PUSH_SUBQ                                                     ALL -> COMPILATION -> TRANSFORMATION
PUSH_SUBQ                                                        ALL -> COMPILATION -> TRANSFORMATION
NO_QUERY_TRANSFORMATION                                          ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_XCANONICAL                                                ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_TRANSFORM                                                 ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_CB_WHR_ONLY                                        ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY                                           ALL -> COMPILATION -> TRANSFORMATION
NO_TRANSFORM_DISTINCT_AGG                                        ALL -> COMPILATION -> TRANSFORMATION
TRANSFORM_DISTINCT_AGG                                           ALL -> COMPILATION -> TRANSFORMATION
PRECOMPUTE_SUBQUERY                                              ALL -> COMPILATION -> TRANSFORMATION
LIKE_EXPAND                                                      ALL -> COMPILATION -> TRANSFORMATION
NO_ORDER_ROLLUPS                                                 ALL -> COMPILATION -> TRANSFORMATION
GBY_CONC_ROLLUP                                                  ALL -> COMPILATION -> TRANSFORMATION
USE_TTT_FOR_GSETS                                                ALL -> COMPILATION -> TRANSFORMATION
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_PLACE_DISTINCT                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
PLACE_DISTINCT                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
FACTORIZE_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
NO_FACTORIZE_JOIN                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
PLACE_GROUP_BY                                                   ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
NO_PLACE_GROUP_BY                                                ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
PULL_PRED                                                        ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_PULL_PRED                                                     ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_FACT                                                          ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION                                           ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION                                              ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR                                                             ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
FACT                                                             ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
TABLE_LOOKUP_BY_NL                                               ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_TABLE_LOOKUP_BY_NL                                            ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE                                                  ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE                                                     ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
NO_UNNEST                                                        ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
NO_COALESCE_SQ                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
COALESCE_SQ                                                      ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
MERGE                                                            ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_MERGE                                                         ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_PUSH_PRED                                                     ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
PUSH_PRED                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
ELIMINATE_OBY                                                    ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
NO_ELIMINATE_OBY                                                 ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
OLD_PUSH_PRED                                                    ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OLD_PUSH_PRED
NO_OUTER_JOIN_TO_INNER                                           ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
OUTER_JOIN_TO_INNER                                              ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
SET_TO_JOIN                                                      ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
NO_SET_TO_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
ELIMINATE_JOIN                                                   ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_ELIMINATE_JOIN                                                ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_UNNEST                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
UNNEST                                                           ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
NO_EXPAND                                                        ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
USE_CONCAT                                                       ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
XML_DML_RWT_STMT                                                 ALL -> COMPILATION -> XML_REWRITE
NO_XML_DML_REWRITE                                               ALL -> COMPILATION -> XML_REWRITE
NO_XML_QUERY_REWRITE                                             ALL -> COMPILATION -> XML_REWRITE
FORCE_XML_QUERY_REWRITE                                          ALL -> COMPILATION -> XML_REWRITE
CHECK_ACL_REWRITE                                                ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_CHECK_ACL_REWRITE                                             ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_COST_XML_QUERY_REWRITE                                        ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
COST_XML_QUERY_REWRITE                                           ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
NO_XMLINDEX_REWRITE_IN_SELECT                                    ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT                                       ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_XMLINDEX_REWRITE                                              ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE                                                 ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_SUBSTRB_PAD                                                   ALL -> EXECUTION
NO_NLJ_BATCHING                                                  ALL -> EXECUTION
NLJ_BATCHING                                                     ALL -> EXECUTION
NO_NLJ_PREFETCH                                                  ALL -> EXECUTION
NLJ_PREFETCH                                                     ALL -> EXECUTION
CACHE                                                            ALL -> EXECUTION
NO_RESULT_CACHE                                                  ALL -> EXECUTION
RESULT_CACHE                                                     ALL -> EXECUTION
TRACING                                                          ALL -> EXECUTION
NOCACHE                                                          ALL -> EXECUTION
NO_LOAD                                                          ALL -> EXECUTION

305 rows selected.

Lots of hints to remember and to try out some day… nah, I’ll just run on modern Oracle versions and gather the stats properly ;-)