Understanding what a hint affects using the V$SQL_FEATURE views

Tanel Poder

2013-04-01

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


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS