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 ;-)