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