In my previous post, I wrote about the time I made a mistake specifying a hint via a SQL Patch, meaning it “didn’t work”. This leads to an understanding about the classification of hints – some hints are used for the Optimizer/CBO, but other hints have different reasons for being used. For example APPEND is behavioural (i.e. don’t change how the code optimizes, change how it inserts)
However, when I did some googling, I came across this blog post by Jonathan Lewis, which goes a long way towards explaining something about hints and where to find the information, so I’m not going to repeat any of that. However, I am going to steal his SQL and add a tiny enhancement.
There are a number of columns in V$SQL_HINT, and the enigmatic one is Properties. It’s a whole load of bits set to represent different characteristics of the hint. The one I cared about in the previous post was, which hints are affected by “ignore_optim_embedded_hints“. And the answer to that can be found on bit 4
[you need to perform bitand(property,16) to test the 4th bit and see if it’s a 0 or a 1]
If bit 4 of V$SQL_HINT.PROPERTIES is set, then the hint is an Optimizer/CBO hint and will be disabled by the hint ignore_optim_embedded_hints (which doesn’t have bit4 set and therefore doesn’t disable itself)
Here’s the amended code
rem Script: sql_hints.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2007
rem Addendum: Neil Chandler, added property bit 4 decode
rem Dated: Jul 2024, only 17 years later
set linesize 255
set pagesize 100
set trimspool on
set tab off
column name format a32
column inverse format a32
column sql_feature format a30
column version format a8
column outline format a8
column class format a35
column target format 999
column prop format 999
column bit0 format a6
column bit1 format a6
column bit2 format a6
column bit3 format a6
column bit4 format a6
column bit5 format a4
column bit6 format a4
column bit7 format a4
column bit8 format a4
--break on version skip 1
compute count of version on version
spool sql_hints
select
version,
version_outline outline,
name,
decode(bitand(property,16),16,'Optim*') bit4,
inverse,
sql_feature,
class,
decode(bitand(target_level,1),1,'State') bit0,
decode(bitand(target_level,2),2,'QBlock') bit1,
decode(bitand(target_level,4),4,'Object') bit2,
decode(bitand(target_level,8),8,'Join') bit3,
target_level target,
decode(bitand(property,16),16,'Optim*') bit4,
decode(bitand(property,32),32,'5set') bit5,
decode(bitand(property,64),64,'6set') bit6,
decode(bitand(property,128),128,'7set') bit7,
decode(bitand(property,256),256,'8set') bit8,
property prop
from
v$sql_hint
order by
to_number(substr(version,1,3)),
version,
to_number(substr(version_outline,1,3)) nulls last,
version_outline nulls last,
name
/
prompt *Optimizer/CBO level hint - will be disabled by "ignore_optim_embedded_hints"
spool off
…and the output
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT4 BIT5 BIT6 BIT7 BIT8 PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ ------ ---- ---- ---- ---- ----
8.0.0 8.1.7 CLUSTER QKSFM_CBO ACCESS Object 4 Optim* 8set 272
8.0.0 8.1.7 INDEX NO_INDEX QKSFM_INDEX ACCESS Object 4 Optim* 5set 8set 304
8.0.0 8.1.7 ROWID QKSFM_CBO ACCESS Object 4 Optim* 8set 272
8.0.0 10.1.0 NO_MERGE MERGE QKSFM_CVM MERGE QBlock Object 6 Optim* 16
8.0.0 EXPR_CORR_CHECK QKSFM_CBO EXPR_CORR_CHECK State 1 0
8.0.0 MERGE_CONST_ON QKSFM_CBO MERGE_CONST_ON State 1 0
8.0.0 NL_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 Optim* 16
8.0.0 NL_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 Optim* 16
8.0.0 NO_MONITORING QKSFM_ALL NO_MONITORING State 1 0
8.0.0 NO_ORDER_ROLLUPS QKSFM_TRANSFORMATION NO_ORDER_ROLLUPS QBlock 2 0
8.0.0 NO_STATS_GSETS QKSFM_ALL NO_STATS_GSETS QBlock 2 0
8.0.0 ORDERED_PREDICATES QKSFM_CBO ORDERED_PREDICATES QBlock 2 Optim* 16
8.0.0 QUEUE_CURR QKSFM_CBO ACCESS Object 4 8set 256
8.0.0 QUEUE_ROWP QKSFM_CBO ACCESS Object 4 8set 256
8.1.0 8.1.5 BITMAP QKSFM_CBO BITMAP QBlock 2 8set 256
8.1.0 8.1.5 NO_PUSH_PRED PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED QBlock Object 6 Optim* 16
8.1.0 8.1.5 PUSH_PRED NO_PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED QBlock Object 6 Optim* 16
8.1.0 8.1.5 RULE QKSFM_RBO MODE State 1 Optim* 16
8.1.0 8.1.7 AND_EQUAL QKSFM_AND_EQUAL ACCESS Object 4 Optim* 5set 8set 304
8.1.0 8.1.7 DRIVING_SITE QKSFM_ALL DRIVING_SITE Object 4 8set 256
8.1.0 8.1.7 FACT NO_FACT QKSFM_STAR_TRANS FACT Object 4 Optim* 8set 272
8.1.0 8.1.7 FULL QKSFM_FULL ACCESS Object 4 Optim* 8set 272
8.1.0 8.1.7 HASH QKSFM_ALL ACCESS Object 4 Optim* 8set 272
8.1.0 8.1.7 HASH_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 Optim* 16
8.1.0 8.1.7 HASH_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 Optim* 16
8.1.0 8.1.7 INDEX_COMBINE QKSFM_INDEX_COMBINE ACCESS Object 4 Optim* 5set 7set 8set 432
8.1.0 8.1.7 INDEX_DESC NO_INDEX QKSFM_INDEX_DESC ACCESS Object 4 Optim* 5set 8set 304
8.1.0 8.1.7 INDEX_FFS QKSFM_INDEX_FFS ACCESS Object 4 Optim* 5set 8set 304
8.1.0 8.1.7 MERGE_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 Optim* 16
8.1.0 8.1.7 MERGE_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 Optim* 16
8.1.0 8.1.7 NO_EXPAND USE_CONCAT QKSFM_USE_CONCAT OR_EXPAND QBlock 2 Optim* 16
8.1.0 8.1.7 NO_FACT FACT QKSFM_STAR_TRANS FACT Object 4 Optim* 8set 272
8.1.0 8.1.7 ORDERED QKSFM_CBO ORDERED QBlock 2 Optim* 16
8.1.0 8.1.7 SEMIJOIN_DRIVER QKSFM_CBO SEMIJOIN_DRIVER QBlock 2 Optim* 16
8.1.0 8.1.7 STAR_TRANSFORMATION NO_STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION QBlock Object 6 Optim* 16
8.1.0 8.1.7 SWAP_JOIN_INPUTS NO_SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS Object 4 Optim* 8set 272
8.1.0 8.1.7 USE_CONCAT NO_EXPAND QKSFM_USE_CONCAT OR_EXPAND QBlock 2 Optim* 16
8.1.0 8.1.7 USE_HASH NO_USE_HASH QKSFM_USE_HASH JOIN Object 4 Optim* 6set 7set 8set 464
8.1.0 8.1.7 USE_MERGE NO_USE_MERGE QKSFM_USE_MERGE JOIN Object 4 Optim* 6set 8set 336
8.1.0 8.1.7 USE_NL NO_USE_NL QKSFM_USE_NL JOIN Object 4 Optim* 6set 8set 336
8.1.0 10.1.0 MERGE NO_MERGE QKSFM_CVM MERGE QBlock Object 6 Optim* 16
8.1.0 10.2.0.1 ALL_ROWS QKSFM_ALL_ROWS MODE State 1 Optim* 16
8.1.0 10.2.0.1 FIRST_ROWS QKSFM_FIRST_ROWS MODE State 1 Optim* 16
8.1.0 10.2.0.5 PUSH_SUBQ NO_PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ QBlock 2 Optim* 16
8.1.0 APPEND NOAPPEND QKSFM_CBO APPEND State 1 0
8.1.0 CACHE NOCACHE QKSFM_EXECUTION CACHE Object 4 8set 256
8.1.0 CHOOSE QKSFM_CHOOSE MODE State 1 Optim* 16
8.1.0 DEREF_NO_REWRITE QKSFM_ALL DEREF_NO_REWRITE State 1 0
8.1.0 INDEX_ASC NO_INDEX QKSFM_INDEX_ASC ACCESS Object 4 Optim* 5set 8set 304
8.1.0 NESTED_TABLE_GET_REFS QKSFM_ALL NESTED_TABLE_GET_REFS State 1 0
8.1.0 NOAPPEND APPEND QKSFM_CBO APPEND State 1 0
8.1.0 NOCACHE CACHE QKSFM_EXECUTION CACHE Object 4 8set 256
8.1.0 NOPARALLEL SHARED QKSFM_PARALLEL SHARED State Object 5 8set 256
8.1.0 NO_PARALLEL_INDEX PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX Object 4 5set 8set 288
8.1.0 PARALLEL_INDEX NO_PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX Object 4 5set 8set 288
8.1.0 PIV_GB QKSFM_ALL PIV_GB QBlock 2 0
8.1.0 PIV_SSF QKSFM_ALL PIV_SSF QBlock 2 0
8.1.0 REMOTE_MAPPED QKSFM_ALL REMOTE_MAPPED QBlock 2 Optim* 8set 272
8.1.0 SHARED NO_PARALLEL QKSFM_PARALLEL SHARED State Object 5 8set 256
8.1.0 STAR QKSFM_STAR_TRANS STAR QBlock 2 Optim* 16
8.1.0 TIV_GB QKSFM_ALL PIV_GB QBlock 2 0
8.1.0 TIV_SSF QKSFM_ALL PIV_SSF QBlock 2 0
8.1.0 USE_ANTI QKSFM_CBO USE_ANTI Object 4 Optim* 8set 272
8.1.0 USE_SEMI QKSFM_CBO USE_SEMI Object 4 Optim* 8set 272
8.1.5 8.1.7 NO_ACCESS QKSFM_ALL NO_ACCESS Object 4 8set 256
8.1.5 8.1.7 NO_INDEX INDEX QKSFM_INDEX NO_INDEX Object 4 Optim* 5set 8set 304
8.1.5 8.1.7 NO_REWRITE REWRITE QKSFM_TRANSFORMATION REWRITE QBlock 2 Optim* 16
8.1.5 8.1.7 PQ_DISTRIBUTE QKSFM_PQ_DISTRIBUTE PQ_DISTRIBUTE Object 4 Optim* 8set 272
8.1.5 8.1.7 REWRITE NO_REWRITE QKSFM_TRANSFORMATION REWRITE QBlock 2 Optim* 16
8.1.5 10.1.0.3 INDEX_JOIN QKSFM_INDEX_JOIN ACCESS Object 4 Optim* 5set 8set 304
8.1.5 10.2.0.1 DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT QKSFM_CBO DOMAIN_INDEX_SORT QBlock 2 0
8.1.5 10.2.0.1 DOMAIN_INDEX_SORT DOMAIN_INDEX_NO_SORT QKSFM_CBO DOMAIN_INDEX_SORT QBlock 2 0
8.1.5 BUFFER NO_BUFFER QKSFM_CBO BUFFER QBlock 2 0
8.1.5 BYPASS_UJVC QKSFM_CBO BYPASS_UJVC QBlock 2 0
8.1.5 CACHE_CB NOCACHE QKSFM_CBO CACHE_CB Object 4 8set 256
8.1.5 CUBE_GB QKSFM_CBO CUBE_GB QBlock 2 0
8.1.5 NESTED_TABLE_SET_SETID QKSFM_ALL NESTED_TABLE_SET_SETID State 1 0
8.1.5 NO_BUFFER BUFFER QKSFM_CBO BUFFER QBlock 2 0
8.1.5 RESTORE_AS_INTERVALS QKSFM_CBO RESTORE_AS_INTERVALS QBlock 2 0
8.1.5 SAVE_AS_INTERVALS QKSFM_CBO SAVE_AS_INTERVALS QBlock 2 0
8.1.5 SCN_ASCENDING QKSFM_ALL SCN_ASCENDING State 1 0
8.1.6 10.1.0 NO_UNNEST UNNEST QKSFM_UNNEST UNNEST QBlock 2 Optim* 16
8.1.6 10.1.0 UNNEST NO_UNNEST QKSFM_UNNEST UNNEST QBlock 2 Optim* 16
8.1.6 10.1.0.3 LEADING QKSFM_JOIN_ORDER LEADING Join 8 Optim* 8set 272
8.1.6 SYS_PARALLEL_TXN QKSFM_CBO SYS_PARALLEL_TXN QBlock 2 0
9.0.0 10.2.0.1 INDEX_SS NO_INDEX_SS QKSFM_INDEX_SS ACCESS Object 4 Optim* 5set 8set 304
9.0.0 10.2.0.1 INDEX_SS_DESC NO_INDEX_SS QKSFM_INDEX_SS_DESC ACCESS Object 4 Optim* 5set 8set 304
9.0.0 10.2.0.1 PQ_MAP PQ_NOMAP QKSFM_PQ_MAP PQ_MAP Object 4 Optim* 8set 272
9.0.0 10.2.0.1 PQ_NOMAP PQ_MAP QKSFM_PQ_MAP PQ_MAP Object 4 Optim* 8set 272
9.0.0 18.1.0 INLINE MATERIALIZE QKSFM_TRANSFORMATION INLINE QBlock 2 Optim* 16
9.0.0 18.1.0 MATERIALIZE INLINE QKSFM_TRANSFORMATION INLINE QBlock 2 Optim* 16
9.0.0 ANTIJOIN QKSFM_TRANSFORMATION ANTIJOIN QBlock 2 Optim* 16
9.0.0 BYPASS_RECURSIVE_CHECK QKSFM_ALL BYPASS_RECURSIVE_CHECK QBlock 2 0
9.0.0 CARDINALITY QKSFM_STATS CARDINALITY QBlock Object Join 14 Optim* 8set 272
9.0.0 CPU_COSTING NO_CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING QBlock 2 Optim* 16
9.0.0 CURSOR_SHARING_EXACT QKSFM_CBO CURSOR_SHARING_EXACT QBlock 2 0
9.0.0 DML_UPDATE QKSFM_CBO DML_UPDATE State 1 0
9.0.0 GBY_CONC_ROLLUP QKSFM_TRANSFORMATION GBY_CONC_ROLLUP QBlock 2 0
9.0.0 HWM_BROKERED QKSFM_CBO HWM_BROKERED QBlock 2 0
9.0.0 INDEX_RRS QKSFM_CBO ACCESS Object 4 Optim* 5set 8set 304
9.0.0 INDEX_SS_ASC NO_INDEX_SS QKSFM_INDEX_SS_ASC ACCESS Object 4 Optim* 5set 8set 304
9.0.0 LOCAL_INDEXES QKSFM_CBO LOCAL_INDEXES QBlock 2 0
9.0.0 MV_MERGE QKSFM_TRANSFORMATION MV_MERGE QBlock 2 0
9.0.0 NO_CPU_COSTING CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING QBlock 2 Optim* 16
9.0.0 NO_PRUNE_GSETS QKSFM_TRANSFORMATION NO_PRUNE_GSETS QBlock 2 0
9.0.0 NO_SEMIJOIN SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN QBlock 2 Optim* 16
9.0.0 OVERFLOW_NOMOVE QKSFM_CBO OVERFLOW_NOMOVE QBlock 2 0
9.0.0 SEMIJOIN NO_SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN QBlock 2 Optim* 16
9.0.0 SKIP_EXT_OPTIMIZER QKSFM_CBO SKIP_EXT_OPTIMIZER QBlock 2 Optim* 16
9.0.0 SQLLDR QKSFM_CBO SQLLDR State 1 0
9.0.0 USE_TTT_FOR_GSETS QKSFM_TRANSFORMATION USE_TTT_FOR_GSETS QBlock 2 0
9.2.0 10.1.0 EXPAND_GSET_TO_UNION NO_EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION QBlock 2 0
9.2.0 10.1.0 NO_EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION QBlock 2 0
9.2.0 10.2.0.5 NO_PUSH_SUBQ PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ QBlock 2 Optim* 16
9.2.0 11.1.0.6 FORCE_XML_QUERY_REWRITE NO_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE State 1 0
9.2.0 11.1.0.6 NO_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE State 1 0
9.2.0 DYNAMIC_SAMPLING QKSFM_DYNAMIC_SAMPLING DYNAMIC_SAMPLING QBlock Object 6 Optim* 8set 272
9.2.0 DYNAMIC_SAMPLING_EST_CDN QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN Object 4 Optim* 8set 272
9.2.0 IGNORE_WHERE_CLAUSE QKSFM_ALL IGNORE_WHERE_CLAUSE State 1 0
9.2.0 NO_QKN_BUFF QKSFM_CBO NO_QKN_BUFF QBlock 2 0
9.2.0 NO_REF_CASCADE REF_CASCADE_CURSOR QKSFM_CBO REF_CASCADE_CURSOR State 1 0
9.2.0 REF_CASCADE_CURSOR NO_REF_CASCADE QKSFM_CBO REF_CASCADE_CURSOR State 1 0
9.2.0 SYS_DL_CURSOR QKSFM_CBO SYS_DL_CURSOR State 1 0
9.2.0 SYS_RID_ORDER QKSFM_ALL SYS_RID_ORDER QBlock 2 0
10.1.0.3 10.1.0.3 NO_BASETABLE_MULTIMV_REWRITE REWRITE QKSFM_ALL REWRITE QBlock 2 Optim* 16
10.1.0.3 10.1.0.3 NO_INDEX_FFS INDEX_FFS QKSFM_INDEX_FFS NO_INDEX_FFS Object 4 Optim* 5set 8set 304
10.1.0.3 10.1.0.3 NO_INDEX_SS INDEX_SS QKSFM_INDEX_SS NO_INDEX_SS Object 4 Optim* 5set 8set 304
10.1.0.3 10.1.0.3 NO_MULTIMV_REWRITE REWRITE QKSFM_ALL REWRITE QBlock 2 Optim* 16
10.1.0.3 10.1.0.3 NO_SET_TO_JOIN SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN QBlock 2 Optim* 16
10.1.0.3 10.1.0.3 NO_STAR_TRANSFORMATION STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION QBlock Object 6 Optim* 16
10.1.0.3 10.1.0.3 NO_SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS Object 4 Optim* 8set 272
10.1.0.3 10.1.0.3 NO_USE_HASH USE_HASH QKSFM_USE_HASH NO_USE_HASH Object 4 Optim* 6set 8set 336
10.1.0.3 10.1.0.3 NO_USE_MERGE USE_MERGE QKSFM_USE_MERGE NO_USE_MERGE Object 4 Optim* 6set 8set 336
10.1.0.3 10.1.0.3 NO_USE_NL USE_NL QKSFM_USE_NL NO_USE_NL Object 4 Optim* 6set 8set 336
10.1.0.3 10.1.0.3 SET_TO_JOIN NO_SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN QBlock 2 Optim* 16
10.1.0.3 10.2.0.1 IGNORE_OPTIM_EMBEDDED_HINTS QKSFM_ALL IGNORE_OPTIM_EMBEDDED_HINTS State 1 0
10.1.0.3 10.2.0.1 OPTIMIZER_FEATURES_ENABLE QKSFM_ALL OPTIMIZER_FEATURES_ENABLE State 1 Optim* 8set 272
10.1.0.3 COLUMN_STATS QKSFM_STATS TABLE_STATS State 1 Optim* 8set 272
10.1.0.3 FBTSCAN QKSFM_CBO FBTSCAN State 1 0
10.1.0.3 GATHER_PLAN_STATISTICS QKSFM_GATHER_PLAN_STATISTICS GATHER_PLAN_STATISTICS State 1 0
10.1.0.3 INCLUDE_VERSION QKSFM_ALL INCLUDE_VERSION State 1 0
10.1.0.3 INDEX_STATS QKSFM_STATS TABLE_STATS State 1 Optim* 8set 272
10.1.0.3 MODEL_DONTVERIFY_UNIQUENESS QKSFM_TRANSFORMATION MODEL_DONTVERIFY_UNIQUENESS QBlock 2 0
10.1.0.3 MODEL_MIN_ANALYSIS QKSFM_TRANSFORMATION MODEL_MIN_ANALYSIS QBlock 2 0
10.1.0.3 MODEL_NO_ANALYSIS QKSFM_ALL MODEL_MIN_ANALYSIS QBlock 2 0
10.1.0.3 MODEL_PUSH_REF NO_MODEL_PUSH_REF QKSFM_TRANSFORMATION MODEL_PUSH_REF QBlock 2 0
10.1.0.3 NESTED_TABLE_FAST_INSERT QKSFM_ALL NESTED_TABLE_FAST_INSERT State 1 0
10.1.0.3 NO_MODEL_PUSH_REF MODEL_PUSH_REF QKSFM_ALL MODEL_PUSH_REF QBlock 2 0
10.1.0.3 NO_PARALLEL SHARED QKSFM_CBO SHARED State Object 5 8set 256
10.1.0.3 NO_PARTIAL_COMMIT QKSFM_CBO NO_PARTIAL_COMMIT State 1 0
10.1.0.3 NO_QUERY_TRANSFORMATION QKSFM_TRANSFORMATION NO_QUERY_TRANSFORMATION State 1 Optim* 16
10.1.0.3 OPAQUE_TRANSFORM QKSFM_TRANSFORMATION OPAQUE_TRANSFORM State 1 0
10.1.0.3 OPAQUE_XCANONICAL QKSFM_TRANSFORMATION OPAQUE_XCANONICAL State 1 0
10.1.0.3 OPT_ESTIMATE QKSFM_OPT_ESTIMATE OPT_ESTIMATE QBlock Object Join 14 Optim* 8set 272
10.1.0.3 QB_NAME QKSFM_ALL QB_NAME QBlock 2 8set 256
10.1.0.3 RESTRICT_ALL_REF_CONS QKSFM_ALL RESTRICT_ALL_REF_CONS State 1 0
10.1.0.3 REWRITE_OR_ERROR QKSFM_TRANSFORMATION REWRITE QBlock 2 0
10.1.0.3 SKIP_UNQ_UNUSABLE_IDX QKSFM_CBO SKIP_UNQ_UNUSABLE_IDX State 1 0
10.1.0.3 STREAMS QKSFM_CBO STREAMS State 1 0
10.1.0.3 TABLE_STATS QKSFM_STATS TABLE_STATS State 1 Optim* 8set 272
10.1.0.3 TRACING QKSFM_EXECUTION TRACING State 1 0
10.1.0.3 USE_NL_WITH_INDEX NO_USE_NL QKSFM_USE_NL_WITH_INDEX USE_NL_WITH_INDEX Object 4 Optim* 5set 8set 304
10.1.0.3 USE_WEAK_NAME_RESL QKSFM_ALL USE_WEAK_NAME_RESL State 1 0
10.1.0.3 VECTOR_READ QKSFM_CBO VECTOR_READ State 1 0
10.1.0.3 VECTOR_READ_TRACE QKSFM_CBO VECTOR_READ_TRACE State 1 0
10.1.0.3 X_DYN_PRUNE QKSFM_CBO X_DYN_PRUNE QBlock 2 0
10.2.0.1 10.2.0.1 BITMAP_TREE QKSFM_BITMAP_TREE ACCESS Object 4 Optim* 5set 8set 304
10.2.0.1 10.2.0.1 ELIMINATE_JOIN NO_ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN Object 4 Optim* 16
10.2.0.1 10.2.0.1 ELIMINATE_OBY NO_ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY QBlock 2 Optim* 16
10.2.0.1 10.2.0.1 NO_ELIMINATE_JOIN ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN Object 4 Optim* 16
10.2.0.1 10.2.0.1 NO_ELIMINATE_OBY ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY QBlock 2 Optim* 16
10.2.0.1 10.2.0.1 NO_PULL_PRED PULL_PRED QKSFM_PULL_PRED PULL_PRED Object 4 Optim* 16
10.2.0.1 10.2.0.1 OLD_PUSH_PRED QKSFM_OLD_PUSH_PRED OLD_PUSH_PRED QBlock Object 6 Optim* 16
10.2.0.1 10.2.0.1 OPT_PARAM QKSFM_ALL OPT_PARAM State 1 Optim* 8set 272
10.2.0.1 10.2.0.1 OUTLINE QKSFM_ALL OUTLINE QBlock 2 0
10.2.0.1 10.2.0.1 OUTLINE_LEAF QKSFM_ALL OUTLINE_LEAF QBlock 2 0
10.2.0.1 10.2.0.1 PULL_PRED NO_PULL_PRED QKSFM_PULL_PRED PULL_PRED Object 4 Optim* 16
10.2.0.1 10.2.0.1 RBO_OUTLINE QKSFM_RBO RBO_OUTLINE State 1 0
10.2.0.1 10.2.0.5 NO_USE_HASH_AGGREGATION USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION QBlock 2 0
10.2.0.1 10.2.0.5 USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION QBlock 2 0
10.2.0.1 11.1.0.6 NO_PX_JOIN_FILTER PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER Object 4 Optim* 6set 8set 336
10.2.0.1 11.1.0.6 NO_XML_DML_REWRITE QKSFM_XML_REWRITE NO_XML_DML_REWRITE State 1 0
10.2.0.1 11.1.0.6 PX_JOIN_FILTER NO_PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER Object 4 Optim* 6set 8set 336
10.2.0.1 DBMS_STATS QKSFM_DBMS_STATS DBMS_STATS State 1 0
10.2.0.1 INLINE_XMLTYPE_NT QKSFM_ALL INLINE_XMLTYPE_NT State 1 0
10.2.0.1 MODEL_COMPILE_SUBQUERY QKSFM_TRANSFORMATION MODEL_COMPILE_SUBQUERY QBlock 2 0
10.2.0.1 MODEL_DYNAMIC_SUBQUERY QKSFM_TRANSFORMATION MODEL_DYNAMIC_SUBQUERY QBlock 2 0
10.2.0.1 NO_CARTESIAN QKSFM_ALL NO_CARTESIAN Object 4 Optim* 6set 8set 336
10.2.0.1 NO_SQL_TUNE QKSFM_ALL NO_SQL_TUNE State 1 0
10.2.0.1 PRECOMPUTE_SUBQUERY QKSFM_TRANSFORMATION PRECOMPUTE_SUBQUERY QBlock 2 0
10.2.0.1 PRESERVE_OID QKSFM_ALL PRESERVE_OID State 1 0
10.2.0.2 10.2.0.2 CONNECT_BY_COST_BASED NO_CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED QBlock 2 Optim* 16
10.2.0.2 10.2.0.2 CONNECT_BY_FILTERING NO_CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING QBlock 2 Optim* 16
10.2.0.2 10.2.0.2 NO_CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED QBlock 2 Optim* 16
10.2.0.2 10.2.0.2 NO_CONNECT_BY_FILTERING CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING QBlock 2 Optim* 16
10.2.0.3 10.2.0.3 NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN QBlock 2 Optim* 16
10.2.0.3 10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN QBlock 2 Optim* 16
10.2.0.3 10.2.0.3 NUM_INDEX_KEYS QKSFM_CBO ACCESS Object 4 Optim* 5set 8set 304
10.2.0.4 10.2.0.4 CONNECT_BY_COMBINE_SW NO_CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW QBlock 2 Optim* 16
10.2.0.4 10.2.0.4 NO_CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW QBlock 2 Optim* 16
10.2.0.5 10.2.0.5 CONNECT_BY_CB_WHR_ONLY NO_CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY QBlock 2 Optim* 16
10.2.0.5 10.2.0.5 GBY_PUSHDOWN NO_GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN QBlock 2 Optim* 16
10.2.0.5 10.2.0.5 NO_CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY QBlock 2 Optim* 16
10.2.0.5 10.2.0.5 NO_GBY_PUSHDOWN GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN QBlock 2 Optim* 16
11.1.0.6 11.1.0.6 COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE State 1 0
11.1.0.6 11.1.0.6 DB_VERSION QKSFM_ALL DB_VERSION State 1 Optim* 8set 272
11.1.0.6 11.1.0.6 DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER QKSFM_CBO DOMAIN_INDEX_FILTER Object 4 Optim* 5set 8set 304
11.1.0.6 11.1.0.6 INDEX_RS_ASC QKSFM_INDEX_RS_ASC ACCESS Object 4 Optim* 5set 8set 304
11.1.0.6 11.1.0.6 INDEX_RS_DESC QKSFM_INDEX_RS_DESC ACCESS Object 4 Optim* 5set 8set 304
11.1.0.6 11.1.0.6 NLJ_BATCHING NO_NLJ_BATCHING QKSFM_EXECUTION ACCESS Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 NLJ_PREFETCH NO_NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 NO_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE State 1 0
11.1.0.6 11.1.0.6 NO_DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER QKSFM_CBO NO_DOMAIN_INDEX_FILTER Object 4 Optim* 5set 8set 304
11.1.0.6 11.1.0.6 NO_NLJ_BATCHING NLJ_BATCHING QKSFM_EXECUTION ACCESS Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 NO_NLJ_PREFETCH NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 NO_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QBlock Object 6 Optim* 16
11.1.0.6 11.1.0.6 NO_PLACE_GROUP_BY PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY QBlock 2 Optim* 16
11.1.0.6 11.1.0.6 NO_SUBQUERY_PRUNING SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 NO_USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES State 1 0
11.1.0.6 11.1.0.6 NO_XMLINDEX_REWRITE XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 11.1.0.6 NO_XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 11.1.0.6 OUTER_JOIN_TO_INNER NO_OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QBlock Object 6 Optim* 16
11.1.0.6 11.1.0.6 PLACE_GROUP_BY NO_PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY QBlock 2 Optim* 16
11.1.0.6 11.1.0.6 SUBQUERY_PRUNING NO_SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING Object 4 Optim* 8set 272
11.1.0.6 11.1.0.6 USE_INVISIBLE_INDEXES NO_USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES State 1 0
11.1.0.6 11.1.0.6 USE_MERGE_CARTESIAN QKSFM_USE_MERGE_CARTESIAN JOIN Object 4 Optim* 6set 8set 336
11.1.0.6 11.1.0.6 XMLINDEX_REWRITE NO_XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 11.1.0.6 XMLINDEX_REWRITE_IN_SELECT NO_XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 11.1.0.6 XML_DML_RWT_STMT QKSFM_XML_REWRITE XML_DML_RWT_STMT State 1 0
11.1.0.6 CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE CHECK_ACL_REWRITE State 1 0
11.1.0.6 MONITOR NO_MONITOR QKSFM_ALL MONITOR State 1 0
11.1.0.6 NO_CHECK_ACL_REWRITE CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE State 1 0
11.1.0.6 NO_LOAD QKSFM_EXECUTION NO_LOAD State 1 0
11.1.0.6 NO_MONITOR MONITOR QKSFM_ALL MONITOR State 1 0
11.1.0.6 NO_RESULT_CACHE RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE QBlock 2 0
11.1.0.6 RESULT_CACHE NO_RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE QBlock 2 0
11.1.0.7 BIND_AWARE NO_BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE State 1 0
11.1.0.7 CHANGE_DUPKEY_ERROR_INDEX QKSFM_DML CHANGE_DUPKEY_ERROR_INDEX Object 4 5set 8set 288
11.1.0.7 IGNORE_ROW_ON_DUPKEY_INDEX QKSFM_DML IGNORE_ROW_ON_DUPKEY_INDEX Object 4 5set 8set 288
11.1.0.7 NO_BIND_AWARE BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE State 1 0
11.1.0.7 RETRY_ON_ROW_CHANGE QKSFM_DML RETRY_ON_ROW_CHANGE State 1 0
11.2.0.1 11.2.0.1 COALESCE_SQ NO_COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 CONNECT_BY_ELIM_DUPS NO_CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 EXPAND_TABLE NO_EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE Object 4 Optim* 16
11.2.0.1 11.2.0.1 FACTORIZE_JOIN NO_FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 NO_COALESCE_SQ COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 NO_CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 NO_EXPAND_TABLE EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE Object 4 Optim* 16
11.2.0.1 11.2.0.1 NO_FACTORIZE_JOIN FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 NO_PLACE_DISTINCT PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 NO_TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG QBlock 2 0
11.2.0.1 11.2.0.1 PLACE_DISTINCT NO_PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT QBlock 2 Optim* 16
11.2.0.1 11.2.0.1 TRANSFORM_DISTINCT_AGG NO_TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG QBlock 2 0
11.2.0.1 23.1.0.1 NO_DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV State 1 0
11.2.0.1 APPEND_VALUES NOAPPEND QKSFM_CBO APPEND_VALUES State 1 0
11.2.0.1 DST_UPGRADE_INSERT_CONV NO_DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV State 1 0
11.2.0.1 NO_STATEMENT_QUEUING STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING State 1 0
11.2.0.1 NO_SUBSTRB_PAD QKSFM_EXECUTION NO_SUBSTRB_PAD State 1 0
11.2.0.1 STATEMENT_QUEUING NO_STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING State 1 0
11.2.0.1 XMLINDEX_SEL_IDX_TBL QKSFM_ALL XMLINDEX_SEL_IDX_TBL State 1 0
11.2.0.2 11.2.0.2 NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL Object 4 Optim* 16
11.2.0.2 11.2.0.2 NO_USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN QBlock 2 0
11.2.0.2 11.2.0.2 TABLE_LOOKUP_BY_NL NO_TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL Object 4 Optim* 16
11.2.0.2 11.2.0.2 USE_HASH_GBY_FOR_PUSHDOWN NO_USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN QBlock 2 0
11.2.0.2 NO_XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT State 1 0
11.2.0.2 XDB_FASTPATH_INSERT NO_XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT State 1 0
11.2.0.3 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER NO_FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER Object 4 Optim* 8set 272
11.2.0.3 11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER Object 4 Optim* 8set 272
11.2.0.3 11.2.0.3 NO_OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI Object 4 Optim* 8set 272
11.2.0.3 11.2.0.3 NO_SEMI_TO_INNER SEMI_TO_INNER QKSFM_CBO NO_SEMI_TO_INNER Object 4 Optim* 8set 272
11.2.0.3 11.2.0.3 OUTER_JOIN_TO_ANTI NO_OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI Object 4 Optim* 8set 272
11.2.0.3 11.2.0.3 SEMI_TO_INNER NO_SEMI_TO_INNER QKSFM_CBO SEMI_TO_INNER Object 4 Optim* 8set 272
11.2.0.4 DISABLE_PARALLEL_DML ENABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML State 1 0
11.2.0.4 ENABLE_PARALLEL_DML DISABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML State 1 0
12.1.0.1 12.1.0.1 BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 BITMAP_AND QKSFM_BITMAP_TREE BITMAP_AND Object 4 Optim* 5set 48
12.1.0.1 12.1.0.1 CLUSTERING NO_CLUSTERING QKSFM_CLUSTERING CLUSTERING State 1 0
12.1.0.1 12.1.0.1 CLUSTER_BY_ROWID NO_CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 CUBE_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 CUBE_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 DATA_SECURITY_REWRITE_LIMIT NO_DATA_SECURITY_REWRITE QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT State 1 0
12.1.0.1 12.1.0.1 DECORRELATE NO_DECORRELATE QKSFM_DECORRELATE DECORRELATE QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 NO_CLUSTERING CLUSTERING QKSFM_CLUSTERING CLUSTERING State 1 0
12.1.0.1 12.1.0.1 NO_CLUSTER_BY_ROWID CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 NO_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT State 1 0
12.1.0.1 12.1.0.1 NO_DECORRELATE DECORRELATE QKSFM_DECORRELATE DECORRELATE QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 NO_PARTIAL_JOIN PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 NO_PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 NO_PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION State QBlock 3 0
12.1.0.1 12.1.0.1 NO_PQ_REPLICATE PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 NO_PQ_SKEW PQ_SKEW QKSFM_PQ PQ_SKEW Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 NO_PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE State 1 0
12.1.0.1 12.1.0.1 NO_USE_CUBE USE_CUBE QKSFM_USE_CUBE JOIN Object 4 Optim* 6set 8set 336
12.1.0.1 12.1.0.1 NO_ZONEMAP ZONEMAP QKSFM_ZONEMAP ZONEMAP Object 4 8set 256
12.1.0.1 12.1.0.1 PARTIAL_JOIN NO_PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION State QBlock 3 0
12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW QKSFM_PQ PQ_DISTRIBUTE_WINDOW QBlock 2 Optim* 16
12.1.0.1 12.1.0.1 PQ_FILTER QKSFM_PQ PQ_FILTER QBlock 2 0
12.1.0.1 12.1.0.1 PQ_REPLICATE NO_PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 PQ_SKEW NO_PQ_SKEW QKSFM_PQ PQ_SKEW Object 4 Optim* 8set 272
12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE State 1 0
12.1.0.1 12.1.0.1 USE_CUBE NO_USE_CUBE QKSFM_USE_CUBE JOIN Object 4 Optim* 6set 8set 336
12.1.0.1 12.1.0.1 ZONEMAP NO_ZONEMAP QKSFM_ZONEMAP ZONEMAP Object 4 8set 256
12.1.0.1 AUTO_REOPTIMIZE NO_AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE State 1 0
12.1.0.1 GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS State 1 0
12.1.0.1 NO_AUTO_REOPTIMIZE AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE State 1 0
12.1.0.1 NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS State 1 0
12.1.0.1 USE_HIDDEN_PARTITIONS QKSFM_PARTITION USE_HIDDEN_PARTITIONS QBlock 2 0
12.1.0.1 WITH_PLSQL QKSFM_ALL WITH_PLSQL State 1 0
12.1.0.2 12.1.0.2 ADAPTIVE_PLAN NO_ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN State 1 Optim* 16
12.1.0.2 12.1.0.2 ANSI_REARCH NO_ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 ELIM_GROUPBY NO_ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 INMEMORY NO_INMEMORY QKSFM_EXECUTION INMEMORY QBlock Object 6 6set 64
12.1.0.2 12.1.0.2 INMEMORY_PRUNING NO_INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING QBlock Object 6 6set 64
12.1.0.2 12.1.0.2 NO_ADAPTIVE_PLAN ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN State 1 Optim* 16
12.1.0.2 12.1.0.2 NO_ANSI_REARCH ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 NO_ELIM_GROUPBY ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 NO_INMEMORY INMEMORY QKSFM_EXECUTION INMEMORY QBlock Object 6 6set 64
12.1.0.2 12.1.0.2 NO_INMEMORY_PRUNING INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING QBlock Object 6 6set 64
12.1.0.2 12.1.0.2 NO_USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 NO_VECTOR_TRANSFORM VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 NO_VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS Object 4 Optim* 6set 80
12.1.0.2 12.1.0.2 NO_VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT Object 4 Optim* 6set 80
12.1.0.2 12.1.0.2 USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 VECTOR_TRANSFORM NO_VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM QBlock 2 Optim* 16
12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS Object 4 Optim* 6set 80
12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT Object 4 Optim* 6set 80
12.1.0.2 RESERVOIR_SAMPLING QKSFM_EXECUTION RESERVOIR_SAMPLING State 1 0
12.2.0.1 12.2.0.1 BUSHY_JOIN NO_BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 CONTAINERS QKSFM_ALL CONTAINERS State 1 0
12.2.0.1 12.2.0.1 DIST_AGG_PROLLUP_PUSHDOWN NO_DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 ELIMINATE_SQ NO_ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 NO_BUSHY_JOIN BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 NO_DIST_AGG_PROLLUP_PUSHDOWN DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 NO_ELIMINATE_SQ ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 NO_OR_EXPAND OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 NO_USE_DAGG_UNION_ALL_GSETS USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
12.2.0.1 12.2.0.1 NO_USE_HASH_GBY_FOR_DAGGPSHD USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD QBlock 2 0
12.2.0.1 12.2.0.1 NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT QBlock 2 0
12.2.0.1 12.2.0.1 NO_USE_PARTITION_WISE_GBY USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY QBlock 2 0
12.2.0.1 12.2.0.1 ORDER_SUBQ QKSFM_TRANSFORMATION ORDER_SUBQ QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 OR_EXPAND NO_OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND QBlock 2 Optim* 16
12.2.0.1 12.2.0.1 USE_DAGG_UNION_ALL_GSETS NO_USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
12.2.0.1 12.2.0.1 USE_HASH_GBY_FOR_DAGGPSHD NO_USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD QBlock 2 0
12.2.0.1 12.2.0.1 USE_PARTITION_WISE_DISTINCT NO_USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT QBlock 2 0
12.2.0.1 12.2.0.1 USE_PARTITION_WISE_GBY NO_USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY QBlock 2 0
12.2.0.1 DATA_VALIDATE QKSFM_EXECUTION DATA_VALIDATE State 1 0
12.2.0.1 FRESH_MV QKSFM_MVIEWS FRESH_MV State 1 0
12.2.0.1 SQL_SCOPE QKSFM_COMPILATION SQL_SCOPE State 1 0
12.2.0.1 XMLTSET_DML_ENABLE QKSFM_ALL XMLTSET_DML_ENABLE State 1 0
18.1.0 18.1.0 ANSWER_QUERY_USING_STATS NO_ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QBlock 2 Optim* 16
18.1.0 18.1.0 NO_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QBlock 2 Optim* 16
18.1.0 18.1.0 NO_PUSH_HAVING_TO_GBY PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY QBlock 2 0
18.1.0 18.1.0 NO_REORDER_WIF REORDER_WIF QKSFM_PARTITION REORDER_WIF QBlock 2 0
18.1.0 18.1.0 NO_USE_PARTITION_WISE_WIF USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF QBlock 2 0
18.1.0 18.1.0 PUSH_HAVING_TO_GBY NO_PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY QBlock 2 0
18.1.0 18.1.0 REORDER_WIF NO_REORDER_WIF QKSFM_PARTITION REORDER_WIF QBlock 2 0
18.1.0 18.1.0 USE_PARTITION_WISE_WIF NO_USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF QBlock 2 0
18.1.0 AV_CACHE QKSFM_EXECUTION AV_CACHE QBlock 2 0
18.1.0 CURRENT_INSTANCE QKSFM_ALL CURRENT_INSTANCE State 1 0
18.1.0 MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE State 1 0
18.1.0 PDB_LOCAL_ONLY QKSFM_DML PDB_LOCAL_ONLY State 1 0
18.1.0 SKIP_PROXY QKSFM_ALL SKIP_PROXY State 1 0
18.1.0 SUPPRESS_LOAD QKSFM_DDL SUPPRESS_LOAD State 1 0
18.1.0 SYSTEM_STATS QKSFM_ALL SYSTEM_STATS State 1 Optim* 8set 272
19.1.0 19.1.0 NO_PQ_EXPAND_TABLE PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE Object 4 Optim* 16
19.1.0 19.1.0 NO_USE_SCALABLE_GBY_INVDIST USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST QBlock 2 0
19.1.0 19.1.0 PQ_EXPAND_TABLE NO_PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE Object 4 Optim* 16
19.1.0 19.1.0 USE_SCALABLE_GBY_INVDIST NO_USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST QBlock 2 0
19.1.0 JSON_LENGTH QKSFM_EXECUTION JSON_LENGTH State 1 0
19.1.0 QUARANTINE QKSFM_EXECUTION QUARANTINE State 1 0
21.1.0 21.1.0 DAGG_OPTIM_GSETS NO_DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
21.1.0 21.1.0 FORCE_JSON_TABLE_TRANSFORM NO_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM State 1 0
21.1.0 21.1.0 HASHSET_BUILD QKSFM_EXECUTION HASHSET_BUILD QBlock 2 Optim* 16
21.1.0 21.1.0 NO_DAGG_OPTIM_GSETS DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
21.1.0 21.1.0 NO_JSON_TABLE_TRANSFORM FORCE_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM State 1 0
21.1.0 21.1.0 NO_OBY_GBYPD_SEPARATE OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE QBlock 2 Optim* 16
21.1.0 21.1.0 NO_PQ_NONLEAF_SKEW PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW Object 4 Optim* 8set 272
21.1.0 21.1.0 NO_SET_GBY_PUSHDOWN SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN QBlock 2 Optim* 16
21.1.0 21.1.0 OBY_GBYPD_SEPARATE NO_OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE QBlock 2 Optim* 16
21.1.0 21.1.0 ORDER_KEY_VECTOR_USE QKSFM_VECTOR_AGG ORDER_KEY_VECTOR_USE QBlock 2 Optim* 8set 272
21.1.0 21.1.0 OSON_GET_CONTENT QKSFM_JSON OSON_GET_CONTENT State 1 0
21.1.0 21.1.0 PQ_NONLEAF_SKEW NO_PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW Object 4 Optim* 8set 272
21.1.0 21.1.0 SET_GBY_PUSHDOWN NO_SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN QBlock 2 Optim* 16
21.1.0 ANALYTIC_VIEW_SQL QKSFM_COMPILATION ANALYTIC_VIEW_SQL QBlock 2 0
21.1.0 DENORM_AV QKSFM_COMPILATION DENORM_AV QBlock 2 0
23.1.0 23.1.0 JSON_QRYOVERGEN_REWRITE NO_JSON_QRYOVERGEN_REWRITE QKSFM_JSON_REWRITE JSON_QRYOVERGEN_REWRITE QBlock 2 0
23.1.0 23.1.0 JSON_REFERENCE_FOR_UPDATE QKSFM_JSON JSON_REFERENCE_FOR_UPDATE State 1 0
23.1.0 23.1.0 NO_JSON_QRYOVERGEN_REWRITE JSON_QRYOVERGEN_REWRITE QKSFM_JSON_REWRITE JSON_QRYOVERGEN_REWRITE QBlock 2 0
23.1.0 23.1.0 NO_PUSH_GBY_INTO_UNION_ALL PUSH_GBY_INTO_UNION_ALL QKSFM_PUSH_GBY_INTO_UNION_ALL PUSH_GBY_INTO_UNION_ALL QBlock 2 Optim* 16
23.1.0 23.1.0 NO_SUBGROUP SUBGROUP QKSFM_SUBGROUP SUBGROUP QBlock 2 Optim* 16
23.1.0 23.1.0 NO_SUBSUME SUBSUME QKSFM_SUBSUME SUBSUME QBlock 2 Optim* 16
23.1.0 23.1.0 PUSH_GBY_INTO_UNION_ALL NO_PUSH_GBY_INTO_UNION_ALL QKSFM_PUSH_GBY_INTO_UNION_ALL PUSH_GBY_INTO_UNION_ALL QBlock 2 Optim* 16
23.1.0 23.1.0 SUBGROUP NO_SUBGROUP QKSFM_SUBGROUP SUBGROUP QBlock 2 Optim* 16
23.1.0 23.1.0 SUBSUME NO_SUBSUME QKSFM_SUBSUME SUBSUME QBlock 2 Optim* 16
23.1.0 CELL_TRACE QKSFM_ALL CELL_TRACE State 1 8set 256
23.1.0 COMPRESS_IMMEDIATE NO_COMPRESS_IMMEDIATE QKSFM_EXECUTION COMPRESS_IMMEDIATE State 1 0
23.1.0 DR_IN_RECUR NO_DR_IN_RECUR QKSFM_EXECUTION DR_IN_RECUR State 1 0
23.1.0 JOIN_SPECIFIC_MV NO_JOIN_SPECIFIC_MV QKSFM_MVIEWS JOIN_SPECIFIC_MV QBlock 2 0
23.1.0 MULTI_APPEND NO_MULTI_APPEND QKSFM_EXECUTION MULTI_APPEND State 1 0
23.1.0 NO_COMPRESS_IMMEDIATE COMPRESS_IMMEDIATE QKSFM_EXECUTION COMPRESS_IMMEDIATE State 1 0
23.1.0 NO_DR_IN_RECUR DR_IN_RECUR QKSFM_EXECUTION DR_IN_RECUR State 1 0
23.1.0 NO_JOIN_SPECIFIC_MV JOIN_SPECIFIC_MV QKSFM_MVIEWS JOIN_SPECIFIC_MV QBlock 2 0
23.1.0 NO_MULTI_APPEND MULTI_APPEND QKSFM_EXECUTION MULTI_APPEND State 1 0
406 rows selected.
*Optimizer/CBO level hint - will be disabled by "ignore_optim_embedded_hints"
Hints are badly named. They should really be called Directives. Oracle does not “ignore” them, but there are many scenarios when they may not be applied; such as invalid syntax, the schema changed (e.g. a hinted index was dropped) so it was not possible to apply, or the SQL was subject to a transformation meaning what you were hinting no longer exists.
When running dbms_xplan.display_cursor, adding +HINT_REPORT to the output was a welcome addition explaining which hints were used, unused or have syntax errors:
select * from table(dbms_xplan.display_cursor(’30fdw3fv86zva’,null,’BASIC +HINT_REPORT’));
Neil Chandler's DB Blog 
Leave a reply to SQL Patch, and a Parallel mistake – Neil Chandler's DB Blog Cancel reply