今週の名言
「世間で頭角をあらわす人物は、自分の望む環境を自ら捜し求める人物であり、もしそれが見つからない時は自分で創り出す人物である。」
ジョージ・バーナード・ショー
今回もトリビアネタ
Oracleエラーを数える回ではとんだ墓穴を掘ってしまいましたが、懲りずに今回はヒント句の種類、しかもOracleのバージョンが進化していく間にどんなヒント句が追加されてきたのかという変遷をたどってみます。
環境は12cR1
今回使用する環境はOracle12cR1です。
SQL> SELECT BANNER FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production PL/SQL Release 12.1.0.2.0 - Production CORE 12.1.0.2.0 Production TNS for Linux: Version 12.1.0.2.0 - Production NLSRTL Version 12.1.0.2.0 - Production
使用するビューはV$SQL_HINT
V$SQL_HINTは11gからあるようですが、ヒント句にどんなのがあるのかを確認できるなかなか面白いビューです。
なぜか12cになってもアンドキュメントなビューなので気になって調べてみました。
ただ、一覧表示させるだけではつまらないので、バージョンごとにまとめて集計してみました。
Oracle8
SQL> SELECT VERSION,CLASS,NAME HINT_NAME,INVERSE,SQL_FEATURE
2 FROM V$SQL_HINT
3 ORDER BY
4 TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(VERSION,'\.','',1,2),'\.','',1,2),99.999)
5 ,CLASS,NAME;
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.0.0 ACCESS CLUSTER QKSFM_CBO
INDEX NO_INDEX QKSFM_INDEX
QUEUE_CURR QKSFM_CBO
QUEUE_ROWP QKSFM_CBO
ROWID QKSFM_CBO
ANTIJOIN NL_AJ QKSFM_JOIN_METHOD
EXPR_CORR_CHECK EXPR_CORR_CHECK QKSFM_CBO
MERGE NO_MERGE MERGE QKSFM_CVM
MERGE_CONST_ON MERGE_CONST_ON QKSFM_CBO
NO_MONITORING NO_MONITORING QKSFM_ALL
NO_ORDER_ROLLUPS NO_ORDER_ROLLUPS QKSFM_TRANSFORMATION
NO_STATS_GSETS NO_STATS_GSETS QKSFM_ALL
ORDERED_PREDICATES ORDERED_PREDICATES QKSFM_CBO
SEMIJOIN NL_SJ QKSFM_JOIN_METHOD
********** ******************************** --------------------------------
count 14
INDEXヒントは8.0.0からあります。
NL_AJ, NL_SJ, NO_MERGE なんかもありますね。
この頃はヒントはまだ14個しかなかったのでしょうか。
Oracle8i
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.0 ACCESS AND_EQUAL QKSFM_AND_EQUAL
FULL QKSFM_FULL
HASH QKSFM_ALL
INDEX_ASC NO_INDEX QKSFM_INDEX_ASC
INDEX_COMBINE QKSFM_INDEX_COMBINE
INDEX_DESC NO_INDEX QKSFM_INDEX_DESC
INDEX_FFS QKSFM_INDEX_FFS
ANTIJOIN HASH_AJ QKSFM_JOIN_METHOD
MERGE_AJ QKSFM_JOIN_METHOD
APPEND APPEND NOAPPEND QKSFM_CBO
NOAPPEND APPEND QKSFM_CBO
BITMAP BITMAP QKSFM_CBO
CACHE CACHE NOCACHE QKSFM_EXECUTION
NOCACHE CACHE QKSFM_EXECUTION
DEREF_NO_REWRITE DEREF_NO_REWRITE QKSFM_ALL
DRIVING_SITE DRIVING_SITE QKSFM_ALL
FACT FACT NO_FACT QKSFM_STAR_TRANS
NO_FACT FACT QKSFM_STAR_TRANS
JOIN USE_HASH NO_USE_HASH QKSFM_USE_HASH
USE_MERGE NO_USE_MERGE QKSFM_USE_MERGE
USE_NL NO_USE_NL QKSFM_USE_NL
MERGE MERGE NO_MERGE QKSFM_CVM
MODE ALL_ROWS QKSFM_ALL_ROWS
CHOOSE QKSFM_CHOOSE
FIRST_ROWS QKSFM_FIRST_ROWS
RULE QKSFM_RBO
NESTED_TABLE_GET_REFS NESTED_TABLE_GET_REFS QKSFM_ALL
ORDERED ORDERED QKSFM_CBO
PARALLEL_INDEX NO_PARALLEL_INDEX PARALLEL_INDEX QKSFM_PQ
PARALLEL_INDEX NO_PARALLEL_INDEX QKSFM_PQ
PIV_GB PIV_GB QKSFM_ALL
TIV_GB QKSFM_ALL
PIV_SSF PIV_SSF QKSFM_ALL
TIV_SSF QKSFM_ALL
PUSH_PRED NO_PUSH_PRED PUSH_PRED QKSFM_FILTER_PUSH_PRED
PUSH_PRED NO_PUSH_PRED QKSFM_FILTER_PUSH_PRED
PUSH_SUBQ PUSH_SUBQ NO_PUSH_SUBQ QKSFM_TRANSFORMATION
REMOTE_MAPPED REMOTE_MAPPED QKSFM_ALL
SEMIJOIN HASH_SJ QKSFM_JOIN_METHOD
MERGE_SJ QKSFM_JOIN_METHOD
SEMIJOIN_DRIVER SEMIJOIN_DRIVER QKSFM_CBO
SHARED NOPARALLEL SHARED QKSFM_PARALLEL
SHARED NO_PARALLEL QKSFM_PARALLEL
STAR STAR QKSFM_STAR_TRANS
STAR_TRANSFORMATION STAR_TRANSFORMATION NO_STAR_TRANSFORMATION QKSFM_STAR_TRANS
SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS NO_SWAP_JOIN_INPUTS QKSFM_CBO
USE_ANTI USE_ANTI QKSFM_CBO
USE_CONCAT NO_EXPAND USE_CONCAT QKSFM_USE_CONCAT
USE_CONCAT NO_EXPAND QKSFM_USE_CONCAT
USE_SEMI USE_SEMI QKSFM_CBO
********** ******************************** --------------------------------
count 50
Oracle8iから一気に増えます。
FULLやINDEX関連のヒント、HASHヒントなんかもあります。RULEもありますね。
Oracle8i(8.1.5〜)
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.5 ACCESS INDEX_JOIN QKSFM_INDEX_JOIN
BUFFER BUFFER NO_BUFFER QKSFM_CBO
NO_BUFFER BUFFER QKSFM_CBO
BYPASS_UJVC BYPASS_UJVC QKSFM_CBO
CACHE CACHE_TEMP_TABLE NOCACHE QKSFM_ALL
CACHE_CB CACHE_CB NOCACHE QKSFM_CBO
CUBE_GB CUBE_GB QKSFM_CBO
DOMAIN_INDEX_SORT DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT QKSFM_CBO
DOMAIN_INDEX_SORT DOMAIN_INDEX_NO_SORT QKSFM_CBO
NESTED_TABLE_SET_SETID NESTED_TABLE_SET_SETID QKSFM_ALL
NO_ACCESS NO_ACCESS QKSFM_ALL
NO_INDEX NO_INDEX INDEX QKSFM_INDEX
PQ_DISTRIBUTE PQ_DISTRIBUTE QKSFM_PQ_DISTRIBUTE
RESTORE_AS_INTERVALS RESTORE_AS_INTERVALS QKSFM_CBO
REWRITE NO_REWRITE REWRITE QKSFM_TRANSFORMATION
REWRITE NO_REWRITE QKSFM_TRANSFORMATION
SAVE_AS_INTERVALS SAVE_AS_INTERVALS QKSFM_CBO
SCN_ASCENDING SCN_ASCENDING QKSFM_ALL
********** ******************************** --------------------------------
count 18
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.6 LEADING LEADING QKSFM_JOIN_ORDER
SYS_PARALLEL_TXN SYS_PARALLEL_TXN QKSFM_CBO
UNNEST NO_UNNEST UNNEST QKSFM_UNNEST
UNNEST NO_UNNEST QKSFM_UNNEST
********** ******************************** --------------------------------
count 4
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
8.1.7 LIKE_EXPAND LIKE_EXPAND QKSFM_TRANSFORMATION
OR_EXPAND OR_EXPAND QKSFM_OR_EXPAND
********** ******************************** --------------------------------
count 2
8.1.5以降はだいぶ枯れてきた感があります。LEADINGヒントはRULEベース的にFROM句の後のテーブルを記述した順に結合するヒントですが8.1.6からなんですね。
Oracle9i
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.0.0 ACCESS INDEX_RRS QKSFM_CBO
INDEX_SS NO_INDEX_SS QKSFM_INDEX_SS
INDEX_SS_ASC NO_INDEX_SS QKSFM_INDEX_SS_ASC
INDEX_SS_DESC NO_INDEX_SS QKSFM_INDEX_SS_DESC
ANTIJOIN ANTIJOIN QKSFM_TRANSFORMATION
BYPASS_RECURSIVE_CHECK BYPASS_RECURSIVE_CHECK QKSFM_ALL
CARDINALITY CARDINALITY QKSFM_STATS
CPU_COSTING CPU_COSTING NO_CPU_COSTING QKSFM_CPU_COSTING
NO_CPU_COSTING CPU_COSTING QKSFM_CPU_COSTING
CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT QKSFM_CBO
DML_UPDATE DML_UPDATE QKSFM_CBO
GBY_CONC_ROLLUP GBY_CONC_ROLLUP QKSFM_TRANSFORMATION
HWM_BROKERED HWM_BROKERED QKSFM_CBO
INLINE INLINE MATERIALIZE QKSFM_TRANSFORMATION
MATERIALIZE INLINE QKSFM_TRANSFORMATION
LOCAL_INDEXES LOCAL_INDEXES QKSFM_CBO
MV_MERGE MV_MERGE QKSFM_TRANSFORMATION
NO_PRUNE_GSETS NO_PRUNE_GSETS QKSFM_TRANSFORMATION
OVERFLOW_NOMOVE OVERFLOW_NOMOVE QKSFM_CBO
PQ_MAP PQ_MAP PQ_NOMAP QKSFM_PQ_MAP
PQ_NOMAP PQ_MAP QKSFM_PQ_MAP
SEMIJOIN NO_SEMIJOIN SEMIJOIN QKSFM_TRANSFORMATION
SEMIJOIN NO_SEMIJOIN QKSFM_TRANSFORMATION
SKIP_EXT_OPTIMIZER SKIP_EXT_OPTIMIZER QKSFM_CBO
SQLLDR SQLLDR QKSFM_CBO
USE_TTT_FOR_GSETS USE_TTT_FOR_GSETS QKSFM_TRANSFORMATION
********** ******************************** --------------------------------
count 26
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
9.2.0 DYNAMIC_SAMPLING DYNAMIC_SAMPLING QKSFM_DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN QKSFM_DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION NO_EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION
NO_EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION
FORCE_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE NO_XML_QUERY_REWRITE QKSFM_XML_REWRITE
NO_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE QKSFM_XML_REWRITE
IGNORE_WHERE_CLAUSE IGNORE_WHERE_CLAUSE QKSFM_ALL
NO_QKN_BUFF NO_QKN_BUFF QKSFM_CBO
PUSH_SUBQ NO_PUSH_SUBQ PUSH_SUBQ QKSFM_TRANSFORMATION
REF_CASCADE_CURSOR NO_REF_CASCADE REF_CASCADE_CURSOR QKSFM_CBO
REF_CASCADE_CURSOR NO_REF_CASCADE QKSFM_CBO
SYS_DL_CURSOR SYS_DL_CURSOR QKSFM_CBO
SYS_RID_ORDER SYS_RID_ORDER QKSFM_ALL
********** ******************************** --------------------------------
count 13
Oracle9iR1は当時400種類以上の新機能ができたなんて聞きましたが、ヒント句は8iの時と比べると半分くらいしか追加されていません。
マテリアライズド・ビュー関連のヒントが目立ちますが、ダイナミックサンプリングなんかも9iR2からですね。
Oracle10gR1
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.1.0.3 FBTSCAN FBTSCAN QKSFM_CBO
GATHER_PLAN_STATISTICS GATHER_PLAN_STATISTICS QKSFM_GATHER_PLAN_STATISTICS
IGNORE_OPTIM_EMBEDDED_HINTS IGNORE_OPTIM_EMBEDDED_HINTS QKSFM_ALL
INCLUDE_VERSION INCLUDE_VERSION QKSFM_ALL
MODEL_DONTVERIFY_UNIQUENESS MODEL_DONTVERIFY_UNIQUENESS QKSFM_TRANSFORMATION
MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS QKSFM_TRANSFORMATION
MODEL_NO_ANALYSIS QKSFM_ALL
MODEL_PUSH_REF MODEL_PUSH_REF NO_MODEL_PUSH_REF QKSFM_TRANSFORMATION
NO_MODEL_PUSH_REF MODEL_PUSH_REF QKSFM_ALL
NESTED_TABLE_FAST_INSERT NESTED_TABLE_FAST_INSERT QKSFM_ALL
NO_INDEX_FFS NO_INDEX_FFS INDEX_FFS QKSFM_INDEX_FFS
NO_INDEX_SS NO_INDEX_SS INDEX_SS QKSFM_INDEX_SS
NO_PARTIAL_COMMIT NO_PARTIAL_COMMIT QKSFM_CBO
NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION QKSFM_TRANSFORMATION
NO_USE_HASH NO_USE_HASH USE_HASH QKSFM_USE_HASH
NO_USE_MERGE NO_USE_MERGE USE_MERGE QKSFM_USE_MERGE
NO_USE_NL NO_USE_NL USE_NL QKSFM_USE_NL
OPAQUE_TRANSFORM OPAQUE_TRANSFORM QKSFM_TRANSFORMATION
OPAQUE_XCANONICAL OPAQUE_XCANONICAL QKSFM_TRANSFORMATION
OPTIMIZER_FEATURES_ENABLE OPTIMIZER_FEATURES_ENABLE QKSFM_ALL
OPT_ESTIMATE OPT_ESTIMATE QKSFM_OPT_ESTIMATE
QB_NAME QB_NAME QKSFM_ALL
RESTRICT_ALL_REF_CONS RESTRICT_ALL_REF_CONS QKSFM_ALL
REWRITE NO_BASETABLE_MULTIMV_REWRITE REWRITE QKSFM_ALL
NO_MULTIMV_REWRITE REWRITE QKSFM_ALL
REWRITE_OR_ERROR QKSFM_TRANSFORMATION
SET_TO_JOIN NO_SET_TO_JOIN SET_TO_JOIN QKSFM_SET_TO_JOIN
SET_TO_JOIN NO_SET_TO_JOIN QKSFM_SET_TO_JOIN
SHARED NO_PARALLEL SHARED QKSFM_CBO
SKIP_UNQ_UNUSABLE_IDX SKIP_UNQ_UNUSABLE_IDX QKSFM_CBO
STAR_TRANSFORMATION NO_STAR_TRANSFORMATION STAR_TRANSFORMATION QKSFM_STAR_TRANS
STREAMS STREAMS QKSFM_CBO
SWAP_JOIN_INPUTS NO_SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS QKSFM_CBO
TABLE_STATS COLUMN_STATS QKSFM_STATS
INDEX_STATS QKSFM_STATS
TABLE_STATS QKSFM_STATS
TRACING TRACING QKSFM_EXECUTION
USE_NL_WITH_INDEX USE_NL_WITH_INDEX NO_USE_NL QKSFM_USE_NL_WITH_INDEX
USE_WEAK_NAME_RESL USE_WEAK_NAME_RESL QKSFM_ALL
VECTOR_READ VECTOR_READ QKSFM_CBO
VECTOR_READ_TRACE VECTOR_READ_TRACE QKSFM_CBO
X_DYN_PRUNE X_DYN_PRUNE QKSFM_CBO
********** ******************************** --------------------------------
count 42
Oracle10gからはまたグッと増えてきました。
特に統計情報関連のヒントが気になります。
Oracle10gR2
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.1 ACCESS BITMAP_TREE QKSFM_BITMAP_TREE
DBMS_STATS DBMS_STATS QKSFM_DBMS_STATS
ELIMINATE_JOIN ELIMINATE_JOIN NO_ELIMINATE_JOIN QKSFM_TABLE_ELIM
NO_ELIMINATE_JOIN ELIMINATE_JOIN QKSFM_TABLE_ELIM
ELIMINATE_OBY ELIMINATE_OBY NO_ELIMINATE_OBY QKSFM_OBYE
NO_ELIMINATE_OBY ELIMINATE_OBY QKSFM_OBYE
INLINE_XMLTYPE_NT INLINE_XMLTYPE_NT QKSFM_ALL
MODEL_COMPILE_SUBQUERY MODEL_COMPILE_SUBQUERY QKSFM_TRANSFORMATION
MODEL_DYNAMIC_SUBQUERY MODEL_DYNAMIC_SUBQUERY QKSFM_TRANSFORMATION
NO_CARTESIAN NO_CARTESIAN QKSFM_ALL
NO_SQL_TUNE NO_SQL_TUNE QKSFM_ALL
NO_XML_DML_REWRITE NO_XML_DML_REWRITE QKSFM_XML_REWRITE
OLD_PUSH_PRED OLD_PUSH_PRED QKSFM_OLD_PUSH_PRED
OPT_PARAM OPT_PARAM QKSFM_ALL
OUTLINE OUTLINE QKSFM_ALL
OUTLINE_LEAF OUTLINE_LEAF QKSFM_ALL
PRECOMPUTE_SUBQUERY PRECOMPUTE_SUBQUERY QKSFM_TRANSFORMATION
PRESERVE_OID PRESERVE_OID QKSFM_ALL
PULL_PRED NO_PULL_PRED PULL_PRED QKSFM_PULL_PRED
PULL_PRED NO_PULL_PRED QKSFM_PULL_PRED
PX_JOIN_FILTER NO_PX_JOIN_FILTER PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER
PX_JOIN_FILTER NO_PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER
RBO_OUTLINE RBO_OUTLINE QKSFM_RBO
USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION USE_HASH_AGGREGATION QKSFM_ALL
USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION QKSFM_ALL
********** ******************************** --------------------------------
count 25
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.2 CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED NO_CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION
NO_CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION
CONNECT_BY_FILTERING CONNECT_BY_FILTERING NO_CONNECT_BY_FILTERING QKSFM_ALL
NO_CONNECT_BY_FILTERING CONNECT_BY_FILTERING QKSFM_ALL
********** ******************************** --------------------------------
count 4
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.3 ACCESS NUM_INDEX_KEYS QKSFM_CBO
NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN QKSFM_ALL
NO_NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN QKSFM_ALL
********** ******************************** --------------------------------
count 3
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.4 CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW NO_CONNECT_BY_COMBINE_SW QKSFM_ALL
NO_CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW QKSFM_ALL
********** ******************************** --------------------------------
count 2
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
10.2.0.5 CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY NO_CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION
NO_CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION
GBY_PUSHDOWN GBY_PUSHDOWN NO_GBY_PUSHDOWN QKSFM_ALL
NO_GBY_PUSHDOWN GBY_PUSHDOWN QKSFM_ALL
********** ******************************** --------------------------------
count 4
10gR2ともなると、普段ほとんど使わないようなヒントばかりになってきますね！
これ以降はコメントのしようがないので、そのまま表示させます。
Oracle11g以降
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.6 ACCESS INDEX_RS_ASC QKSFM_INDEX_RS_ASC
INDEX_RS_DESC QKSFM_INDEX_RS_DESC
NLJ_BATCHING NO_NLJ_BATCHING QKSFM_EXECUTION
NLJ_PREFETCH NO_NLJ_PREFETCH QKSFM_EXECUTION
NO_NLJ_BATCHING NLJ_BATCHING QKSFM_EXECUTION
NO_NLJ_PREFETCH NLJ_PREFETCH QKSFM_EXECUTION
CHECK_ACL_REWRITE CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE
COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE
DB_VERSION DB_VERSION QKSFM_ALL
DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER QKSFM_CBO
JOIN USE_MERGE_CARTESIAN QKSFM_USE_MERGE_CARTESIAN
MONITOR MONITOR NO_MONITOR QKSFM_ALL
NO_MONITOR MONITOR QKSFM_ALL
NO_CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE
NO_COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE
NO_DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER QKSFM_CBO
NO_LOAD NO_LOAD QKSFM_EXECUTION
OUTER_JOIN_TO_INNER NO_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER
OUTER_JOIN_TO_INNER NO_OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER
PLACE_GROUP_BY NO_PLACE_GROUP_BY PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY
PLACE_GROUP_BY NO_PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY
RESULT_CACHE NO_RESULT_CACHE RESULT_CACHE QKSFM_EXECUTION
RESULT_CACHE NO_RESULT_CACHE QKSFM_EXECUTION
SUBQUERY_PRUNING NO_SUBQUERY_PRUNING SUBQUERY_PRUNING QKSFM_CBO
SUBQUERY_PRUNING NO_SUBQUERY_PRUNING QKSFM_CBO
USE_INVISIBLE_INDEXES NO_USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES QKSFM_INDEX
USE_INVISIBLE_INDEXES NO_USE_INVISIBLE_INDEXES QKSFM_INDEX
XMLINDEX_REWRITE NO_XMLINDEX_REWRITE XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE
NO_XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE
XMLINDEX_REWRITE NO_XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT NO_XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE
XML_DML_RWT_STMT XML_DML_RWT_STMT QKSFM_XML_REWRITE
********** ******************************** --------------------------------
count 32
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.1.0.7 BIND_AWARE BIND_AWARE NO_BIND_AWARE QKSFM_CURSOR_SHARING
NO_BIND_AWARE BIND_AWARE QKSFM_CURSOR_SHARING
CHANGE_DUPKEY_ERROR_INDEX CHANGE_DUPKEY_ERROR_INDEX QKSFM_DML
IGNORE_ROW_ON_DUPKEY_INDEX IGNORE_ROW_ON_DUPKEY_INDEX QKSFM_DML
RETRY_ON_ROW_CHANGE RETRY_ON_ROW_CHANGE QKSFM_DML
********** ******************************** --------------------------------
count 5
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.1 APPEND_VALUES APPEND_VALUES NOAPPEND QKSFM_CBO
COALESCE_SQ COALESCE_SQ NO_COALESCE_SQ QKSFM_COALESCE_SQ
NO_COALESCE_SQ COALESCE_SQ QKSFM_COALESCE_SQ
CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS NO_CONNECT_BY_ELIM_DUPS QKSFM_ALL
NO_CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS QKSFM_ALL
DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV NO_DST_UPGRADE_INSERT_CONV QKSFM_ALL
NO_DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV QKSFM_ALL
EXPAND_TABLE EXPAND_TABLE NO_EXPAND_TABLE QKSFM_TABLE_EXPANSION
NO_EXPAND_TABLE EXPAND_TABLE QKSFM_TABLE_EXPANSION
FACTORIZE_JOIN FACTORIZE_JOIN NO_FACTORIZE_JOIN QKSFM_JOINFAC
NO_FACTORIZE_JOIN FACTORIZE_JOIN QKSFM_JOINFAC
NO_SUBSTRB_PAD NO_SUBSTRB_PAD QKSFM_EXECUTION
PLACE_DISTINCT NO_PLACE_DISTINCT PLACE_DISTINCT QKSFM_DIST_PLCMT
PLACE_DISTINCT NO_PLACE_DISTINCT QKSFM_DIST_PLCMT
STATEMENT_QUEUING NO_STATEMENT_QUEUING STATEMENT_QUEUING QKSFM_PARALLEL
STATEMENT_QUEUING NO_STATEMENT_QUEUING QKSFM_PARALLEL
TRANSFORM_DISTINCT_AGG NO_TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION
TRANSFORM_DISTINCT_AGG NO_TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION
XMLINDEX_SEL_IDX_TBL XMLINDEX_SEL_IDX_TBL QKSFM_ALL
********** ******************************** --------------------------------
count 19
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.2 TABLE_LOOKUP_BY_NL NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL NO_TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL
USE_HASH_GBY_FOR_PUSHDOWN NO_USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL
USE_HASH_GBY_FOR_PUSHDOWN NO_USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL
XDB_FASTPATH_INSERT NO_XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT QKSFM_ALL
XDB_FASTPATH_INSERT NO_XDB_FASTPATH_INSERT QKSFM_ALL
********** ******************************** --------------------------------
count 6
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.3 FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER NO_FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO
NO_FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO
NO_SEMI_TO_INNER NO_SEMI_TO_INNER SEMI_TO_INNER QKSFM_CBO
OUTER_JOIN_TO_ANTI NO_OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI QKSFM_CBO
OUTER_JOIN_TO_ANTI NO_OUTER_JOIN_TO_ANTI QKSFM_CBO
SEMI_TO_INNER SEMI_TO_INNER NO_SEMI_TO_INNER QKSFM_CBO
********** ******************************** --------------------------------
count 6
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
11.2.0.4 ENABLE_PARALLEL_DML DISABLE_PARALLEL_DML ENABLE_PARALLEL_DML QKSFM_DML
ENABLE_PARALLEL_DML DISABLE_PARALLEL_DML QKSFM_DML
********** ******************************** --------------------------------
count 2
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.1 ANTIJOIN CUBE_AJ QKSFM_JOIN_METHOD
AUTO_REOPTIMIZE AUTO_REOPTIMIZE NO_AUTO_REOPTIMIZE QKSFM_AUTO_REOPT
NO_AUTO_REOPTIMIZE AUTO_REOPTIMIZE QKSFM_AUTO_REOPT
BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION
NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION
BITMAP_AND BITMAP_AND QKSFM_BITMAP_TREE
CLUSTERING CLUSTERING NO_CLUSTERING QKSFM_CLUSTERING
NO_CLUSTERING CLUSTERING QKSFM_CLUSTERING
CLUSTER_BY_ROWID CLUSTER_BY_ROWID NO_CLUSTER_BY_ROWID QKSFM_CBO
NO_CLUSTER_BY_ROWID CLUSTER_BY_ROWID QKSFM_CBO
DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT NO_DATA_SECURITY_REWRITE QKSFM_DATA_SECURITY_REWRITE
NO_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT QKSFM_DATA_SECURITY_REWRITE
DECORRELATE DECORRELATE NO_DECORRELATE QKSFM_DECORRELATE
NO_DECORRELATE DECORRELATE QKSFM_DECORRELATE
GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS
NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS
JOIN NO_USE_CUBE USE_CUBE QKSFM_USE_CUBE
USE_CUBE NO_USE_CUBE QKSFM_USE_CUBE
PARTIAL_JOIN NO_PARTIAL_JOIN PARTIAL_JOIN QKSFM_PARTIAL_JOIN
PARTIAL_JOIN NO_PARTIAL_JOIN QKSFM_PARTIAL_JOIN
PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ
PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ
PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION QKSFM_PQ
PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION QKSFM_PQ
PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW QKSFM_PQ
PQ_FILTER PQ_FILTER QKSFM_PQ
PQ_REPLICATE NO_PQ_REPLICATE PQ_REPLICATE QKSFM_PQ_REPLICATE
PQ_REPLICATE NO_PQ_REPLICATE QKSFM_PQ_REPLICATE
PQ_SKEW NO_PQ_SKEW PQ_SKEW QKSFM_PQ
PQ_SKEW NO_PQ_SKEW QKSFM_PQ
PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE QKSFM_PQ
PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE QKSFM_PQ
SEMIJOIN CUBE_SJ QKSFM_JOIN_METHOD
USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS QKSFM_PARTITION
WITH_PLSQL WITH_PLSQL QKSFM_ALL
ZONEMAP NO_ZONEMAP ZONEMAP QKSFM_ZONEMAP
ZONEMAP NO_ZONEMAP QKSFM_ZONEMAP
********** ******************************** --------------------------------
count 37
VERSION CLASS HINT_NAME INVERSE SQL_FEATURE
---------- -------------------------------- -------------------------------- -------------------------------- --------------------------------
12.1.0.2 ADAPTIVE_PLAN ADAPTIVE_PLAN NO_ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN
ANSI_REARCH ANSI_REARCH NO_ANSI_REARCH QKSFM_ANSI_REARCH
NO_ANSI_REARCH ANSI_REARCH QKSFM_ANSI_REARCH
ELIM_GROUPBY ELIM_GROUPBY NO_ELIM_GROUPBY QKSFM_TRANSFORMATION
NO_ELIM_GROUPBY ELIM_GROUPBY QKSFM_TRANSFORMATION
INMEMORY INMEMORY NO_INMEMORY QKSFM_EXECUTION
NO_INMEMORY INMEMORY QKSFM_EXECUTION
INMEMORY_PRUNING INMEMORY_PRUNING NO_INMEMORY_PRUNING QKSFM_EXECUTION
NO_INMEMORY_PRUNING INMEMORY_PRUNING QKSFM_EXECUTION
USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG
USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG
VECTOR_TRANSFORM NO_VECTOR_TRANSFORM VECTOR_TRANSFORM QKSFM_VECTOR_AGG
VECTOR_TRANSFORM NO_VECTOR_TRANSFORM QKSFM_VECTOR_AGG
VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG
VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG
VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG
VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG
********** ******************************** --------------------------------
count 18
332行が選択されました。
Oracle12cでは全部で332ものヒント句が存在していることがわかります。
SQL_FEATUREとは
上の一覧で一番右端のカラムは「SQL_FEATURE」となっていますが、これは何でしょうか？
マニュアルに掲載されている「V$SYS_OPTIMIZER_ENV」の記述を参照すると、手がかりが見つかります。
「SQL_FEATURE＝関連機能の制御ID」とあるので、オプティマイザというのは多くの機能の集合体であって、初期化パラメータやヒント句は個々の機能（SQL_FEATURE）を制御するものなのだろうということが推察できます。
SQL> SELECT SQL_FEATURE,ID,NAME,ISDEFAULT,VALUE,DEFAULT_VALUE FROM V$SYS_OPTIMIZER_ENV
2 ORDER BY SQL_FEATURE,ID;
SQL_FEATURE ID NAME ISD VALUE DEFAULT_VALUE
-------------------------------- ---------- ---------------------------------------- --- --------------- -------------
QKSFM_ADAPTIVE_PLAN 411 optimizer_adaptive_features YES true true
QKSFM_ALL 11 cpu_count YES 2 2
12 active_instance_count YES 1 1
14 hash_area_size YES 131072 131072
15 bitmap_merge_area_size YES 1048576 1048576
16 sort_area_size YES 65536 65536
17 sort_area_retained_size YES 0 0
24 pga_aggregate_target YES 286720 KB 286720 KB
35 parallel_query_mode YES enabled enabled
36 parallel_dml_mode YES disabled disabled
37 parallel_ddl_mode YES enabled enabled
38 optimizer_mode YES all_rows all_rows
101 workarea_size_policy YES auto auto
167 optimizer_secure_view_merging YES true true
212 result_cache_mode YES MANUAL MANUAL
218 transaction_isolation_level YES read_commited read_commited
257 is_recur_flags YES 0 0
264 db_file_multiblock_read_count YES 69 69
275 total_cpu_count YES 2 2
286 dst_upgrade_insert_conv YES true true
378 PMO_altidx_rebuild YES 0 0
389 total_processor_group_count YES 1 1
QKSFM_AUTO_REOPT 353 optimizer_adaptive_reporting_only YES false false
QKSFM_CBO 2 parallel_execution_enabled YES true true
9 optimizer_features_enable YES 12.1.0.2 12.1.0.2
13 parallel_threads_per_cpu YES 2 2
48 cursor_sharing YES exact exact
66 optimizer_index_cost_adj YES 100 100
67 optimizer_index_caching YES 0 0
105 optimizer_dynamic_sampling YES 2 2
112 statistics_level YES typical typical
114 skip_unusable_indexes YES true true
228 optimizer_use_pending_statistics YES false false
238 optimizer_capture_sql_plan_baselines YES false false
239 optimizer_use_sql_plan_baselines YES true true
441 optimizer_inmemory_aware YES true true
QKSFM_COMPILATION 466 inmemory_size YES 0 0
QKSFM_EXECUTION 262 cell_offload_processing YES true true
267 cell_offload_compaction YES ADAPTIVE ADAPTIVE
268 cell_offload_plan_display YES AUTO AUTO
453 inmemory_force YES default default
454 inmemory_query YES enable enable
QKSFM_INDEX 258 optimizer_use_invisible_indexes YES false false
QKSFM_PARTITION 323 deferred_segment_creation YES true true
QKSFM_PQ 245 parallel_degree_policy YES manual manual
246 parallel_degree YES 0 0
247 parallel_min_time_threshold YES 10 10
256 parallel_query_default_dop YES 0 0
272 parallel_degree_limit YES 65535 65535
273 parallel_force_local YES false false
274 parallel_max_degree YES 4 4
289 parallel_autodop YES 0 0
290 parallel_ddldml YES 0 0
317 parallel_execution_message_size YES 16384 16384
369 parallel_degree_level YES 100 100
432 parallel_dblink YES 0 0
QKSFM_STAR_TRANS 50 star_transformation_enabled YES false false
QKSFM_TRANSFORMATION 70 query_rewrite_enabled YES true true
71 query_rewrite_integrity YES enforced enforced
59行が選択されました。
まとめ
アクセスパスを固定化するためにヒント句でチューニングするということはよく行われていると思います。
しかし、12cで332種類（11gR2でも約280個）ものヒントがあることを考えると、ヒント句によるチューニングはオプティマイザのほんの一部を制御しているに過ぎないことを理解すべきです。
しかも、バージョンによってヒント句の数は全然違う、つまりオプティマイザの内部仕様はどんどん変わっているので、以前のバージョンで最適であった実行計画が今のバージョンでも最適である保証はないと考えるのが自然なのではないでしょうか。
今回はここまで。
ピンバック: 不可視索引の使い方（USE_INVISIBLE_INDEXESヒントについて） | サイクル＆オラクル
ピンバック: 不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おう | サイクル＆オラクル
ピンバック: Oracleバージョンによるヒント句の変遷〜最新版〜 | サイクル＆オラクル