データベース」カテゴリーアーカイブ

Oracleバージョンによるヒント句の変遷〜最新版〜

オンプレミス版Oracle12c R2リリース!

OTNでOracle12c R2がダウンロード出来るようになったので早速手元環境にインストールしてみた。

SQL> select BANNER from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE	12.2.0.1.0	Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

新しく追加されたヒント句を確認する

以前12cR1までのヒント句の変遷を追ってみたことがある。Oracleバージョンによるヒント句の変遷 参照

今回のバージョンアップで新たに追加されたヒント句を確認してみよう。

SQL> SELECT VERSION,NAME HINT_NAME,INVERSE,CLASS,SQL_FEATURE
  2  FROM V$SQL_HINT
  3  ORDER BY
  4   TO_NUMBER(REGEXP_REPLACE(REGEXP_REPLACE(VERSION,'\.','',1,2),'\.','',1,2),99.999) DESC
  5  ,CLASS,NAME;

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.2.0.1  BUSHY_JOIN                     NO_BUSHY_JOIN                  BUSHY_JOIN                     QKSFM_BUSHY_JOIN
          NO_BUSHY_JOIN                  BUSHY_JOIN                     BUSHY_JOIN                     QKSFM_BUSHY_JOIN
          CONTAINERS                                                    CONTAINERS                     QKSFM_ALL
          DATA_VALIDATE                                                 DATA_VALIDATE                  QKSFM_EXECUTION
          DIST_AGG_PROLLUP_PUSHDOWN      NO_DIST_AGG_PROLLUP_PUSHDOWN   DIST_AGG_PROLLUP_PUSHDOWN      QKSFM_PQ
          NO_DIST_AGG_PROLLUP_PUSHDOWN   DIST_AGG_PROLLUP_PUSHDOWN      DIST_AGG_PROLLUP_PUSHDOWN      QKSFM_PQ
          ELIMINATE_SQ                   NO_ELIMINATE_SQ                ELIMINATE_SQ                   QKSFM_ELIMINATE_SQ
          NO_ELIMINATE_SQ                ELIMINATE_SQ                   ELIMINATE_SQ                   QKSFM_ELIMINATE_SQ
          FRESH_MV                                                      FRESH_MV                       QKSFM_MVIEWS
          ORDER_SUBQ                                                    ORDER_SUBQ                     QKSFM_TRANSFORMATION
          NO_OR_EXPAND                   OR_EXPAND                      OR_EXPAND                      QKSFM_CBQT_OR_EXPANSION
          OR_EXPAND                      NO_OR_EXPAND                   OR_EXPAND                      QKSFM_CBQT_OR_EXPANSION
          SQL_SCOPE                                                     SQL_SCOPE                      QKSFM_COMPILATION
          NO_USE_DAGG_UNION_ALL_GSETS    USE_DAGG_UNION_ALL_GSETS       USE_DAGG_UNION_ALL_GSETS       QKSFM_GROUPING_SET_XFORM
          USE_DAGG_UNION_ALL_GSETS       NO_USE_DAGG_UNION_ALL_GSETS    USE_DAGG_UNION_ALL_GSETS       QKSFM_GROUPING_SET_XFORM
          NO_USE_HASH_GBY_FOR_DAGGPSHD   USE_HASH_GBY_FOR_DAGGPSHD      USE_HASH_GBY_FOR_DAGGPSHD      QKSFM_ALL
          USE_HASH_GBY_FOR_DAGGPSHD      NO_USE_HASH_GBY_FOR_DAGGPSHD   USE_HASH_GBY_FOR_DAGGPSHD      QKSFM_ALL
          NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT    USE_PARTITION_WISE_DISTINCT    QKSFM_PARTITION
          USE_PARTITION_WISE_DISTINCT    NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT    QKSFM_PARTITION
          NO_USE_PARTITION_WISE_GBY      USE_PARTITION_WISE_GBY         USE_PARTITION_WISE_GBY         QKSFM_PARTITION
          USE_PARTITION_WISE_GBY         NO_USE_PARTITION_WISE_GBY      USE_PARTITION_WISE_GBY         QKSFM_PARTITION
          XMLTSET_DML_ENABLE                                            XMLTSET_DML_ENABLE             QKSFM_ALL
********* ------------------------------
count                                 22

12cR2では新たに22個のヒント句が追加された。(トータルでは352個)

12cR1までのヒント一覧

12cR1までのヒント句を以下に再掲する。(バージョンの降順)

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.1.0.2  ADAPTIVE_PLAN                  NO_ADAPTIVE_PLAN               ADAPTIVE_PLAN                  QKSFM_ADAPTIVE_PLAN
          NO_ADAPTIVE_PLAN               ADAPTIVE_PLAN                  ADAPTIVE_PLAN                  QKSFM_ADAPTIVE_PLAN
          ANSI_REARCH                    NO_ANSI_REARCH                 ANSI_REARCH                    QKSFM_ANSI_REARCH
          NO_ANSI_REARCH                 ANSI_REARCH                    ANSI_REARCH                    QKSFM_ANSI_REARCH
          ELIM_GROUPBY                   NO_ELIM_GROUPBY                ELIM_GROUPBY                   QKSFM_TRANSFORMATION
          NO_ELIM_GROUPBY                ELIM_GROUPBY                   ELIM_GROUPBY                   QKSFM_TRANSFORMATION
          INMEMORY                       NO_INMEMORY                    INMEMORY                       QKSFM_EXECUTION
          NO_INMEMORY                    INMEMORY                       INMEMORY                       QKSFM_EXECUTION
          INMEMORY_PRUNING               NO_INMEMORY_PRUNING            INMEMORY_PRUNING               QKSFM_EXECUTION
          NO_INMEMORY_PRUNING            INMEMORY_PRUNING               INMEMORY_PRUNING               QKSFM_EXECUTION
          RESERVOIR_SAMPLING                                            RESERVOIR_SAMPLING             QKSFM_EXECUTION
          NO_USE_VECTOR_AGGREGATION      USE_VECTOR_AGGREGATION         USE_VECTOR_AGGREGATION         QKSFM_VECTOR_AGG
          USE_VECTOR_AGGREGATION         NO_USE_VECTOR_AGGREGATION      USE_VECTOR_AGGREGATION         QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM            VECTOR_TRANSFORM               VECTOR_TRANSFORM               QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM               NO_VECTOR_TRANSFORM            VECTOR_TRANSFORM               QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM_DIMS       VECTOR_TRANSFORM_DIMS          VECTOR_TRANSFORM_DIMS          QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM_DIMS          NO_VECTOR_TRANSFORM_DIMS       VECTOR_TRANSFORM_DIMS          QKSFM_VECTOR_AGG
          NO_VECTOR_TRANSFORM_FACT       VECTOR_TRANSFORM_FACT          VECTOR_TRANSFORM_FACT          QKSFM_VECTOR_AGG
          VECTOR_TRANSFORM_FACT          NO_VECTOR_TRANSFORM_FACT       VECTOR_TRANSFORM_FACT          QKSFM_VECTOR_AGG
********* ------------------------------
count                                 19

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
12.1.0.1  CUBE_AJ                                                       ANTIJOIN                       QKSFM_JOIN_METHOD
          AUTO_REOPTIMIZE                NO_AUTO_REOPTIMIZE             AUTO_REOPTIMIZE                QKSFM_AUTO_REOPT
          NO_AUTO_REOPTIMIZE             AUTO_REOPTIMIZE                AUTO_REOPTIMIZE                QKSFM_AUTO_REOPT
          BATCH_TABLE_ACCESS_BY_ROWID    NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID    QKSFM_EXECUTION
          NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID    BATCH_TABLE_ACCESS_BY_ROWID    QKSFM_EXECUTION
          BITMAP_AND                                                    BITMAP_AND                     QKSFM_BITMAP_TREE
          CLUSTERING                     NO_CLUSTERING                  CLUSTERING                     QKSFM_CLUSTERING
          NO_CLUSTERING                  CLUSTERING                     CLUSTERING                     QKSFM_CLUSTERING
          CLUSTER_BY_ROWID               NO_CLUSTER_BY_ROWID            CLUSTER_BY_ROWID               QKSFM_CBO
          NO_CLUSTER_BY_ROWID            CLUSTER_BY_ROWID               CLUSTER_BY_ROWID               QKSFM_CBO
          DATA_SECURITY_REWRITE_LIMIT    NO_DATA_SECURITY_REWRITE       DATA_SECURITY_REWRITE_LIMIT    QKSFM_DATA_SECURITY_REWRITE
          NO_DATA_SECURITY_REWRITE       DATA_SECURITY_REWRITE_LIMIT    DATA_SECURITY_REWRITE_LIMIT    QKSFM_DATA_SECURITY_REWRITE
          DECORRELATE                    NO_DECORRELATE                 DECORRELATE                    QKSFM_DECORRELATE
          NO_DECORRELATE                 DECORRELATE                    DECORRELATE                    QKSFM_DECORRELATE
          GATHER_OPTIMIZER_STATISTICS    NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS    QKSFM_DBMS_STATS
          NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS    GATHER_OPTIMIZER_STATISTICS    QKSFM_DBMS_STATS
          NO_USE_CUBE                    USE_CUBE                       JOIN                           QKSFM_USE_CUBE
          USE_CUBE                       NO_USE_CUBE                    JOIN                           QKSFM_USE_CUBE
          NO_PARTIAL_JOIN                PARTIAL_JOIN                   PARTIAL_JOIN                   QKSFM_PARTIAL_JOIN
          PARTIAL_JOIN                   NO_PARTIAL_JOIN                PARTIAL_JOIN                   QKSFM_PARTIAL_JOIN
          NO_PARTIAL_ROLLUP_PUSHDOWN     PARTIAL_ROLLUP_PUSHDOWN        PARTIAL_ROLLUP_PUSHDOWN        QKSFM_PQ
          PARTIAL_ROLLUP_PUSHDOWN        NO_PARTIAL_ROLLUP_PUSHDOWN     PARTIAL_ROLLUP_PUSHDOWN        QKSFM_PQ
          NO_PQ_CONCURRENT_UNION         PQ_CONCURRENT_UNION            PQ_CONCURRENT_UNION            QKSFM_PQ
          PQ_CONCURRENT_UNION            NO_PQ_CONCURRENT_UNION         PQ_CONCURRENT_UNION            QKSFM_PQ
          PQ_DISTRIBUTE_WINDOW                                          PQ_DISTRIBUTE_WINDOW           QKSFM_PQ
          PQ_FILTER                                                     PQ_FILTER                      QKSFM_PQ
          NO_PQ_REPLICATE                PQ_REPLICATE                   PQ_REPLICATE                   QKSFM_PQ_REPLICATE
          PQ_REPLICATE                   NO_PQ_REPLICATE                PQ_REPLICATE                   QKSFM_PQ_REPLICATE
          NO_PQ_SKEW                     PQ_SKEW                        PQ_SKEW                        QKSFM_PQ
          PQ_SKEW                        NO_PQ_SKEW                     PQ_SKEW                        QKSFM_PQ
          NO_PX_FAULT_TOLERANCE          PX_FAULT_TOLERANCE             PX_FAULT_TOLERANCE             QKSFM_PQ
          PX_FAULT_TOLERANCE             NO_PX_FAULT_TOLERANCE          PX_FAULT_TOLERANCE             QKSFM_PQ
          CUBE_SJ                                                       SEMIJOIN                       QKSFM_JOIN_METHOD
          USE_HIDDEN_PARTITIONS                                         USE_HIDDEN_PARTITIONS          QKSFM_PARTITION
          WITH_PLSQL                                                    WITH_PLSQL                     QKSFM_ALL
          NO_ZONEMAP                     ZONEMAP                        ZONEMAP                        QKSFM_ZONEMAP
          ZONEMAP                        NO_ZONEMAP                     ZONEMAP                        QKSFM_ZONEMAP
********* ------------------------------
count                                 37

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.4  DISABLE_PARALLEL_DML           ENABLE_PARALLEL_DML            ENABLE_PARALLEL_DML            QKSFM_DML
          ENABLE_PARALLEL_DML            DISABLE_PARALLEL_DML           ENABLE_PARALLEL_DML            QKSFM_DML
********* ------------------------------
count                                  2

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.3  FULL_OUTER_JOIN_TO_OUTER       NO_FULL_OUTER_JOIN_TO_OUTER    FULL_OUTER_JOIN_TO_OUTER       QKSFM_CBO
          NO_FULL_OUTER_JOIN_TO_OUTER    FULL_OUTER_JOIN_TO_OUTER       FULL_OUTER_JOIN_TO_OUTER       QKSFM_CBO
          NO_SEMI_TO_INNER               SEMI_TO_INNER                  NO_SEMI_TO_INNER               QKSFM_CBO
          NO_OUTER_JOIN_TO_ANTI          OUTER_JOIN_TO_ANTI             OUTER_JOIN_TO_ANTI             QKSFM_CBO
          OUTER_JOIN_TO_ANTI             NO_OUTER_JOIN_TO_ANTI          OUTER_JOIN_TO_ANTI             QKSFM_CBO
          SEMI_TO_INNER                  NO_SEMI_TO_INNER               SEMI_TO_INNER                  QKSFM_CBO
********* ------------------------------
count                                  6

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.2  NO_TABLE_LOOKUP_BY_NL          TABLE_LOOKUP_BY_NL             TABLE_LOOKUP_BY_NL             QKSFM_TABLE_LOOKUP_BY_NL
          TABLE_LOOKUP_BY_NL             NO_TABLE_LOOKUP_BY_NL          TABLE_LOOKUP_BY_NL             QKSFM_TABLE_LOOKUP_BY_NL
          NO_USE_HASH_GBY_FOR_PUSHDOWN   USE_HASH_GBY_FOR_PUSHDOWN      USE_HASH_GBY_FOR_PUSHDOWN      QKSFM_ALL
          USE_HASH_GBY_FOR_PUSHDOWN      NO_USE_HASH_GBY_FOR_PUSHDOWN   USE_HASH_GBY_FOR_PUSHDOWN      QKSFM_ALL
          NO_XDB_FASTPATH_INSERT         XDB_FASTPATH_INSERT            XDB_FASTPATH_INSERT            QKSFM_ALL
          XDB_FASTPATH_INSERT            NO_XDB_FASTPATH_INSERT         XDB_FASTPATH_INSERT            QKSFM_ALL
********* ------------------------------
count                                  6

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.2.0.1  APPEND_VALUES                  NOAPPEND                       APPEND_VALUES                  QKSFM_CBO
          COALESCE_SQ                    NO_COALESCE_SQ                 COALESCE_SQ                    QKSFM_COALESCE_SQ
          NO_COALESCE_SQ                 COALESCE_SQ                    COALESCE_SQ                    QKSFM_COALESCE_SQ
          CONNECT_BY_ELIM_DUPS           NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS           QKSFM_ALL
          NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS           CONNECT_BY_ELIM_DUPS           QKSFM_ALL
          DST_UPGRADE_INSERT_CONV        NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV        QKSFM_ALL
          NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV        DST_UPGRADE_INSERT_CONV        QKSFM_ALL
          EXPAND_TABLE                   NO_EXPAND_TABLE                EXPAND_TABLE                   QKSFM_TABLE_EXPANSION
          NO_EXPAND_TABLE                EXPAND_TABLE                   EXPAND_TABLE                   QKSFM_TABLE_EXPANSION
          FACTORIZE_JOIN                 NO_FACTORIZE_JOIN              FACTORIZE_JOIN                 QKSFM_JOINFAC
          NO_FACTORIZE_JOIN              FACTORIZE_JOIN                 FACTORIZE_JOIN                 QKSFM_JOINFAC
          NO_SUBSTRB_PAD                                                NO_SUBSTRB_PAD                 QKSFM_EXECUTION
          NO_PLACE_DISTINCT              PLACE_DISTINCT                 PLACE_DISTINCT                 QKSFM_DIST_PLCMT
          PLACE_DISTINCT                 NO_PLACE_DISTINCT              PLACE_DISTINCT                 QKSFM_DIST_PLCMT
          NO_STATEMENT_QUEUING           STATEMENT_QUEUING              STATEMENT_QUEUING              QKSFM_PARALLEL
          STATEMENT_QUEUING              NO_STATEMENT_QUEUING           STATEMENT_QUEUING              QKSFM_PARALLEL
          NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG         TRANSFORM_DISTINCT_AGG         QKSFM_TRANSFORMATION
          TRANSFORM_DISTINCT_AGG         NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG         QKSFM_TRANSFORMATION
          XMLINDEX_SEL_IDX_TBL                                          XMLINDEX_SEL_IDX_TBL           QKSFM_ALL
********* ------------------------------
count                                 19

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.1.0.7  BIND_AWARE                     NO_BIND_AWARE                  BIND_AWARE                     QKSFM_CURSOR_SHARING
          NO_BIND_AWARE                  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   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
11.1.0.6  INDEX_RS_ASC                                                  ACCESS                         QKSFM_INDEX_RS_ASC
          INDEX_RS_DESC                                                 ACCESS                         QKSFM_INDEX_RS_DESC
          NLJ_BATCHING                   NO_NLJ_BATCHING                ACCESS                         QKSFM_EXECUTION
          NLJ_PREFETCH                   NO_NLJ_PREFETCH                ACCESS                         QKSFM_EXECUTION
          NO_NLJ_BATCHING                NLJ_BATCHING                   ACCESS                         QKSFM_EXECUTION
          NO_NLJ_PREFETCH                NLJ_PREFETCH                   ACCESS                         QKSFM_EXECUTION
          CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              QKSFM_CHECK_ACL_REWRITE
          COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         QKSFM_COST_XML_QUERY_REWRITE
          DB_VERSION                                                    DB_VERSION                     QKSFM_ALL
          DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            QKSFM_CBO
          USE_MERGE_CARTESIAN                                           JOIN                           QKSFM_USE_MERGE_CARTESIAN
          MONITOR                        NO_MONITOR                     MONITOR                        QKSFM_ALL
          NO_MONITOR                     MONITOR                        MONITOR                        QKSFM_ALL
          NO_CHECK_ACL_REWRITE           CHECK_ACL_REWRITE              NO_CHECK_ACL_REWRITE           QKSFM_CHECK_ACL_REWRITE
          NO_COST_XML_QUERY_REWRITE      COST_XML_QUERY_REWRITE         NO_COST_XML_QUERY_REWRITE      QKSFM_COST_XML_QUERY_REWRITE
          NO_DOMAIN_INDEX_FILTER         DOMAIN_INDEX_FILTER            NO_DOMAIN_INDEX_FILTER         QKSFM_CBO
          NO_LOAD                                                       NO_LOAD                        QKSFM_EXECUTION
          NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER
          OUTER_JOIN_TO_INNER            NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER            QKSFM_OUTER_JOIN_TO_INNER
          NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY
          PLACE_GROUP_BY                 NO_PLACE_GROUP_BY              PLACE_GROUP_BY                 QKSFM_PLACE_GROUP_BY
          NO_RESULT_CACHE                RESULT_CACHE                   RESULT_CACHE                   QKSFM_EXECUTION
          RESULT_CACHE                   NO_RESULT_CACHE                RESULT_CACHE                   QKSFM_EXECUTION
          NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               SUBQUERY_PRUNING               QKSFM_CBO
          SUBQUERY_PRUNING               NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING               QKSFM_CBO
          NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          USE_INVISIBLE_INDEXES          QKSFM_INDEX
          USE_INVISIBLE_INDEXES          NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES          QKSFM_INDEX
          NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE_IN_SELECT     XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XMLINDEX_REWRITE               NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XMLINDEX_REWRITE_IN_SELECT     NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE               QKSFM_XMLINDEX_REWRITE
          XML_DML_RWT_STMT                                              XML_DML_RWT_STMT               QKSFM_XML_REWRITE
********* ------------------------------
count                                 32

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.5  CONNECT_BY_CB_WHR_ONLY         NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY         QKSFM_TRANSFORMATION
          NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY         CONNECT_BY_CB_WHR_ONLY         QKSFM_TRANSFORMATION
          GBY_PUSHDOWN                   NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   QKSFM_ALL
          NO_GBY_PUSHDOWN                GBY_PUSHDOWN                   GBY_PUSHDOWN                   QKSFM_ALL
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.4  CONNECT_BY_COMBINE_SW          NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          QKSFM_ALL
          NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW          CONNECT_BY_COMBINE_SW          QKSFM_ALL
********* ------------------------------
count                                  2

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.3  NUM_INDEX_KEYS                                                ACCESS                         QKSFM_CBO
          NATIVE_FULL_OUTER_JOIN         NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         QKSFM_ALL
          NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN         NATIVE_FULL_OUTER_JOIN         QKSFM_ALL
********* ------------------------------
count                                  3

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.2  CONNECT_BY_COST_BASED          NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION
          NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED          CONNECT_BY_COST_BASED          QKSFM_TRANSFORMATION
          CONNECT_BY_FILTERING           NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           QKSFM_ALL
          NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING           CONNECT_BY_FILTERING           QKSFM_ALL
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
10.2.0.1  BITMAP_TREE                                                   ACCESS                         QKSFM_BITMAP_TREE
          DBMS_STATS                                                    DBMS_STATS                     QKSFM_DBMS_STATS
          ELIMINATE_JOIN                 NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 QKSFM_TABLE_ELIM
          NO_ELIMINATE_JOIN              ELIMINATE_JOIN                 ELIMINATE_JOIN                 QKSFM_TABLE_ELIM
          ELIMINATE_OBY                  NO_ELIMINATE_OBY               ELIMINATE_OBY                  QKSFM_OBYE
          NO_ELIMINATE_OBY               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
          NO_PULL_PRED                   PULL_PRED                      PULL_PRED                      QKSFM_PULL_PRED
          PULL_PRED                      NO_PULL_PRED                   PULL_PRED                      QKSFM_PULL_PRED
          NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER
          PX_JOIN_FILTER                 NO_PX_JOIN_FILTER              PX_JOIN_FILTER                 QKSFM_PX_JOIN_FILTER
          RBO_OUTLINE                                                   RBO_OUTLINE                    QKSFM_RBO
          NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           USE_HASH_AGGREGATION           QKSFM_ALL
          USE_HASH_AGGREGATION           NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION           QKSFM_ALL
********* ------------------------------
count                                 25

VERSION   HINT_NAME                      INVERSE                        CLASS                          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                                             MODEL_MIN_ANALYSIS             QKSFM_ALL
          MODEL_PUSH_REF                 NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 QKSFM_TRANSFORMATION
          NO_MODEL_PUSH_REF              MODEL_PUSH_REF                 MODEL_PUSH_REF                 QKSFM_ALL
          NESTED_TABLE_FAST_INSERT                                      NESTED_TABLE_FAST_INSERT       QKSFM_ALL
          NO_INDEX_FFS                   INDEX_FFS                      NO_INDEX_FFS                   QKSFM_INDEX_FFS
          NO_INDEX_SS                    INDEX_SS                       NO_INDEX_SS                    QKSFM_INDEX_SS
          NO_PARTIAL_COMMIT                                             NO_PARTIAL_COMMIT              QKSFM_CBO
          NO_QUERY_TRANSFORMATION                                       NO_QUERY_TRANSFORMATION        QKSFM_TRANSFORMATION
          NO_USE_HASH                    USE_HASH                       NO_USE_HASH                    QKSFM_USE_HASH
          NO_USE_MERGE                   USE_MERGE                      NO_USE_MERGE                   QKSFM_USE_MERGE
          NO_USE_NL                      USE_NL                         NO_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
          NO_BASETABLE_MULTIMV_REWRITE   REWRITE                        REWRITE                        QKSFM_ALL
          NO_MULTIMV_REWRITE             REWRITE                        REWRITE                        QKSFM_ALL
          REWRITE_OR_ERROR                                              REWRITE                        QKSFM_TRANSFORMATION
          NO_SET_TO_JOIN                 SET_TO_JOIN                    SET_TO_JOIN                    QKSFM_SET_TO_JOIN
          SET_TO_JOIN                    NO_SET_TO_JOIN                 SET_TO_JOIN                    QKSFM_SET_TO_JOIN
          NO_PARALLEL                    SHARED                         SHARED                         QKSFM_CBO
          SKIP_UNQ_UNUSABLE_IDX                                         SKIP_UNQ_UNUSABLE_IDX          QKSFM_CBO
          NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            STAR_TRANSFORMATION            QKSFM_STAR_TRANS
          STREAMS                                                       STREAMS                        QKSFM_CBO
          NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               SWAP_JOIN_INPUTS               QKSFM_CBO
          COLUMN_STATS                                                  TABLE_STATS                    QKSFM_STATS
          INDEX_STATS                                                   TABLE_STATS                    QKSFM_STATS
          TABLE_STATS                                                   TABLE_STATS                    QKSFM_STATS
          TRACING                                                       TRACING                        QKSFM_EXECUTION
          USE_NL_WITH_INDEX              NO_USE_NL                      USE_NL_WITH_INDEX              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

VERSION   HINT_NAME                      INVERSE                        CLASS                          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           NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION
          NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION           EXPAND_GSET_TO_UNION           QKSFM_TRANSFORMATION
          FORCE_XML_QUERY_REWRITE        NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE        QKSFM_XML_REWRITE
          NO_XML_QUERY_REWRITE           FORCE_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
          NO_PUSH_SUBQ                   PUSH_SUBQ                      PUSH_SUBQ                      QKSFM_TRANSFORMATION
          NO_REF_CASCADE                 REF_CASCADE_CURSOR             REF_CASCADE_CURSOR             QKSFM_CBO
          REF_CASCADE_CURSOR             NO_REF_CASCADE                 REF_CASCADE_CURSOR             QKSFM_CBO
          SYS_DL_CURSOR                                                 SYS_DL_CURSOR                  QKSFM_CBO
          SYS_RID_ORDER                                                 SYS_RID_ORDER                  QKSFM_ALL
********* ------------------------------
count                                 13

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
9.0.0     INDEX_RRS                                                     ACCESS                         QKSFM_CBO
          INDEX_SS                       NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS
          INDEX_SS_ASC                   NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS_ASC
          INDEX_SS_DESC                  NO_INDEX_SS                    ACCESS                         QKSFM_INDEX_SS_DESC
          ANTIJOIN                                                      ANTIJOIN                       QKSFM_TRANSFORMATION
          BYPASS_RECURSIVE_CHECK                                        BYPASS_RECURSIVE_CHECK         QKSFM_ALL
          CARDINALITY                                                   CARDINALITY                    QKSFM_STATS
          CPU_COSTING                    NO_CPU_COSTING                 CPU_COSTING                    QKSFM_CPU_COSTING
          NO_CPU_COSTING                 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                         MATERIALIZE                    INLINE                         QKSFM_TRANSFORMATION
          MATERIALIZE                    INLINE                         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_NOMAP                       PQ_MAP                         QKSFM_PQ_MAP
          PQ_NOMAP                       PQ_MAP                         PQ_MAP                         QKSFM_PQ_MAP
          NO_SEMIJOIN                    SEMIJOIN                       SEMIJOIN                       QKSFM_TRANSFORMATION
          SEMIJOIN                       NO_SEMIJOIN                    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   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.6     LEADING                                                       LEADING                        QKSFM_JOIN_ORDER
          SYS_PARALLEL_TXN                                              SYS_PARALLEL_TXN               QKSFM_CBO
          NO_UNNEST                      UNNEST                         UNNEST                         QKSFM_UNNEST
          UNNEST                         NO_UNNEST                      UNNEST                         QKSFM_UNNEST
********* ------------------------------
count                                  4

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.5     INDEX_JOIN                                                    ACCESS                         QKSFM_INDEX_JOIN
          BUFFER                         NO_BUFFER                      BUFFER                         QKSFM_CBO
          NO_BUFFER                      BUFFER                         BUFFER                         QKSFM_CBO
          BYPASS_UJVC                                                   BYPASS_UJVC                    QKSFM_CBO
          CACHE_CB                       NOCACHE                        CACHE_CB                       QKSFM_CBO
          CUBE_GB                                                       CUBE_GB                        QKSFM_CBO
          DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              DOMAIN_INDEX_SORT              QKSFM_CBO
          DOMAIN_INDEX_SORT              DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT              QKSFM_CBO
          NESTED_TABLE_SET_SETID                                        NESTED_TABLE_SET_SETID         QKSFM_ALL
          NO_ACCESS                                                     NO_ACCESS                      QKSFM_ALL
          NO_INDEX                       INDEX                          NO_INDEX                       QKSFM_INDEX
          PQ_DISTRIBUTE                                                 PQ_DISTRIBUTE                  QKSFM_PQ_DISTRIBUTE
          RESTORE_AS_INTERVALS                                          RESTORE_AS_INTERVALS           QKSFM_CBO
          NO_REWRITE                     REWRITE                        REWRITE                        QKSFM_TRANSFORMATION
          REWRITE                        NO_REWRITE                     REWRITE                        QKSFM_TRANSFORMATION
          SAVE_AS_INTERVALS                                             SAVE_AS_INTERVALS              QKSFM_CBO
          SCN_ASCENDING                                                 SCN_ASCENDING                  QKSFM_ALL
********* ------------------------------
count                                 17

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.1.0     AND_EQUAL                                                     ACCESS                         QKSFM_AND_EQUAL
          FULL                                                          ACCESS                         QKSFM_FULL
          HASH                                                          ACCESS                         QKSFM_ALL
          INDEX_ASC                      NO_INDEX                       ACCESS                         QKSFM_INDEX_ASC
          INDEX_COMBINE                                                 ACCESS                         QKSFM_INDEX_COMBINE
          INDEX_DESC                     NO_INDEX                       ACCESS                         QKSFM_INDEX_DESC
          INDEX_FFS                                                     ACCESS                         QKSFM_INDEX_FFS
          HASH_AJ                                                       ANTIJOIN                       QKSFM_JOIN_METHOD
          MERGE_AJ                                                      ANTIJOIN                       QKSFM_JOIN_METHOD
          APPEND                         NOAPPEND                       APPEND                         QKSFM_CBO
          NOAPPEND                       APPEND                         APPEND                         QKSFM_CBO
          BITMAP                                                        BITMAP                         QKSFM_CBO
          CACHE                          NOCACHE                        CACHE                          QKSFM_EXECUTION
          NOCACHE                        CACHE                          CACHE                          QKSFM_EXECUTION
          DEREF_NO_REWRITE                                              DEREF_NO_REWRITE               QKSFM_ALL
          DRIVING_SITE                                                  DRIVING_SITE                   QKSFM_ALL
          FACT                           NO_FACT                        FACT                           QKSFM_STAR_TRANS
          NO_FACT                        FACT                           FACT                           QKSFM_STAR_TRANS
          USE_HASH                       NO_USE_HASH                    JOIN                           QKSFM_USE_HASH
          USE_MERGE                      NO_USE_MERGE                   JOIN                           QKSFM_USE_MERGE
          USE_NL                         NO_USE_NL                      JOIN                           QKSFM_USE_NL
          MERGE                          NO_MERGE                       MERGE                          QKSFM_CVM
          ALL_ROWS                                                      MODE                           QKSFM_ALL_ROWS
          CHOOSE                                                        MODE                           QKSFM_CHOOSE
          FIRST_ROWS                                                    MODE                           QKSFM_FIRST_ROWS
          RULE                                                          MODE                           QKSFM_RBO
          NESTED_TABLE_GET_REFS                                         NESTED_TABLE_GET_REFS          QKSFM_ALL
          ORDERED                                                       ORDERED                        QKSFM_CBO
          NO_EXPAND                      USE_CONCAT                     OR_EXPAND                      QKSFM_USE_CONCAT
          USE_CONCAT                     NO_EXPAND                      OR_EXPAND                      QKSFM_USE_CONCAT
          NO_PARALLEL_INDEX              PARALLEL_INDEX                 PARALLEL_INDEX                 QKSFM_PQ
          PARALLEL_INDEX                 NO_PARALLEL_INDEX              PARALLEL_INDEX                 QKSFM_PQ
          PIV_GB                                                        PIV_GB                         QKSFM_ALL
          TIV_GB                                                        PIV_GB                         QKSFM_ALL
          PIV_SSF                                                       PIV_SSF                        QKSFM_ALL
          TIV_SSF                                                       PIV_SSF                        QKSFM_ALL
          NO_PUSH_PRED                   PUSH_PRED                      PUSH_PRED                      QKSFM_FILTER_PUSH_PRED
          PUSH_PRED                      NO_PUSH_PRED                   PUSH_PRED                      QKSFM_FILTER_PUSH_PRED
          PUSH_SUBQ                      NO_PUSH_SUBQ                   PUSH_SUBQ                      QKSFM_TRANSFORMATION
          REMOTE_MAPPED                                                 REMOTE_MAPPED                  QKSFM_ALL
          HASH_SJ                                                       SEMIJOIN                       QKSFM_JOIN_METHOD
          MERGE_SJ                                                      SEMIJOIN                       QKSFM_JOIN_METHOD
          SEMIJOIN_DRIVER                                               SEMIJOIN_DRIVER                QKSFM_CBO
          NOPARALLEL                     SHARED                         SHARED                         QKSFM_PARALLEL
          SHARED                         NO_PARALLEL                    SHARED                         QKSFM_PARALLEL
          STAR                                                          STAR                           QKSFM_STAR_TRANS
          STAR_TRANSFORMATION            NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION            QKSFM_STAR_TRANS
          SWAP_JOIN_INPUTS               NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS               QKSFM_CBO
          USE_ANTI                                                      USE_ANTI                       QKSFM_CBO
          USE_SEMI                                                      USE_SEMI                       QKSFM_CBO
********* ------------------------------
count                                 50

VERSION   HINT_NAME                      INVERSE                        CLASS                          SQL_FEATURE
--------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
8.0.0     CLUSTER                                                       ACCESS                         QKSFM_CBO
          INDEX                          NO_INDEX                       ACCESS                         QKSFM_INDEX
          QUEUE_CURR                                                    ACCESS                         QKSFM_CBO
          QUEUE_ROWP                                                    ACCESS                         QKSFM_CBO
          ROWID                                                         ACCESS                         QKSFM_CBO
          NL_AJ                                                         ANTIJOIN                       QKSFM_JOIN_METHOD
          EXPR_CORR_CHECK                                               EXPR_CORR_CHECK                QKSFM_CBO
          NO_MERGE                       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
          NL_SJ                                                         SEMIJOIN                       QKSFM_JOIN_METHOD
********* ------------------------------
count                                 14

352行が選択されました。

IPアドレスの管理方法を考える①

ネットワーク・アドレスの格納を考える

構成管理データベースを考える時、ネットワーク管理情報を適切に格納することは重要である。Oracle以外のデータベースでは以下のように専用のデータ型や関数を提供している。

PostgreSQLの場合

PostgreSQLでは、IPv4アドレス、IPv6アドレス、MACアドレスを格納するデータ型を提供している。(8.9. ネットワークアドレス型

MySQLの場合

MySQLではIPv4 ネットワークアドレスのドット区切り表現を文字列から10進数、あるいはその逆の変換を実行する関数を提供している。(IPv6 ネットワークアドレスの変換関数も用意されている。)
inet_aton()
inet_ntoa()

Oracleでネットワークアドレス変換関数を作ってみる

翻って、Oracleでは悲しいくらいにネットワーク情報の格納手段が乏しい。ネットで検索したら以下の中国語サイトに面白い情報があったので、早速引用し試してみる。
Oracle SQL 模拟MySQL的inet_aton()和inet_ntoa()

inet_aton()

SQL> select
  2   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1')) * 16777216 +
  3   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2')) * 65536 +
  4   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3')) * 256 +
  5   to_number(regexp_replace(ip, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4'))   as IP_NUMBER
  6  from
  7   (select '192.168.11.1' as ip from dual);

 IP_NUMBER
----------
3232238337

dual表に任意のIPアドレスを指定すると、10進数に変換してくれるクエリーである。

さらに、OTN掲示板で見つけた「10進数を2進数に変換する」のスレッドから引用した、n進数に変換するTO_BASE関数(元ネタはAskTomらしい)で2進数に表示し直してみる。
つまり32bitで表現されるIPv4アドレスを32バイトの2進数文字列で表示する。

変換関数TO_BASE定義

CREATE OR REPLACE FUNCTION to_base( p_dec in number, p_base in number )
RETURN VARCHAR2
DETERMINISTIC
IS
  l_str   VARCHAR2(255) DEFAULT NULL;
  l_num   NUMBER        DEFAULT p_dec;
  l_hex   VARCHAR2(50)  DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
BEGIN
  IF ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then
     raise PROGRAM_ERROR;
  END IF;

  LOOP
    l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
    l_num := trunc( l_num/p_base );
    EXIT WHEN ( l_num = 0 );
  END LOOP;

  RETURN l_str;
END to_base;
/

IPアドレスの10進数表現を2進数に変換

SQL> col "BIN_8bit" for a8
SQL> select 3232238337 as "DEC",to_base(3232238337,2) as "BIN_8bit" from dual;

       DEC BIN_8bit  (参考:オクテットを10進数に変換)
---------- --------
3232238337 11000000   192
           10101000   168
           00001011    11
           00000001     1

inet_ntoa()

同様にIPアドレスの10進数表現をドット区切り表現に変換するクエリーは以下となる。

SQL> select
  2   trunc(ip/16777216)            ||'.'||
  3   trunc(mod(ip,16777216)/65536) ||'.'||
  4   trunc(mod(ip,65536)/256)      ||'.'||
  5   trunc(mod(ip,256))            as IP_ADDRESS
  6  from
  7   (select 3232238337 as ip from dual);

IP_ADDRESS
------------
192.168.11.1

PL/SQLによるinet_aton関数の実装

CREATE OR REPLACE FUNCTION INET_ATON (
  ip_addr IN VARCHAR2)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
  RETURN(
    to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\1')) * 16777216 +
    to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\2')) * 65536 +
    to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\3')) * 256 +
    to_number(regexp_replace(ip_addr, '([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})\.([0-9]{1,3})', '\4'))
  );
END;
/

実行例

SQL> select INET_ATON('192.168.11.1') IP_NUMBER from dual;

 IP_NUMBER
----------
3232238337

PL/SQLによるinet_ntoa関数の実装

CREATE OR REPLACE FUNCTION INET_NTOA (
  ip_number IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC
IS
BEGIN
  RETURN(
    trunc(ip_number/16777216)            ||'.'||
    trunc(mod(ip_number,16777216)/65536) ||'.'||
    trunc(mod(ip_number,65536)/256)      ||'.'||
    trunc(mod(ip_number,256))
  );
END;
/

実行例

SQL> select INET_NTOA(3232238337) IP_ADDRESS from dual;

IP_ADDRESS
----------------
192.168.11.1

ネットワーク・アドレス管理表を考える

上で作成した関数を利用して、IPアドレスを管理する表の実装を検討する。

CREATE TABLE addr_tbl
(ip_number NUMBER
,ip_binary VARCHAR2(128) GENERATED ALWAYS AS (substr(TO_BASE(ip_number,2),1,32)) VIRTUAL
,ip_addr   VARCHAR2(60)  GENERATED ALWAYS AS (substr(INET_NTOA(ip_number),1,15)) VIRTUAL);

ALTER TABLE ADDR_TBL ADD CONSTRAINT PK_ADDR_TBL PRIMARY KEY (IP_NUMBER);
  • IPアドレスはinet_aton関数で10進数表現した値を「ip_number」列に格納し、この列が主キーとなる。
  • さらに2進数表現した「ip_binary」列を仮想列で作成している。
  • 仮想列をユーザ定義関数で作成する場合、DETERMINISTICな関数である必要がある。
  • 人間に最もなじみの深いドット区切り表現も同様に「ip_addr」列として仮想列で作成する。
  • つまり実際にデータが格納されるのは「ip_number」列のみで、「ip_binary」列と「ip_addr」列は仮想列として関数により導出される。
SQL> CREATE TABLE addr_tbl
  2  (ip_number NUMBER
  3  ,ip_binary VARCHAR2(128) GENERATED ALWAYS AS (substr(TO_BASE(ip_number,2),1,32)) VIRTUAL
  4  ,ip_addr   VARCHAR2(60)  GENERATED ALWAYS AS (substr(INET_NTOA(ip_number),1,15)) VIRTUAL);

Table created.

SQL> ALTER TABLE ADDR_TBL ADD CONSTRAINT PK_ADDR_TBL PRIMARY KEY (IP_NUMBER);

Table altered.

SQL> desc ADDR_TBL
 Name          Null?    Type
 ------------- -------- ----------------------------
 IP_NUMBER     NOT NULL NUMBER
 IP_BINARY              VARCHAR2(128)
 IP_ADDR                VARCHAR2(60)

仮想列の定義内容は以下のように確認する。
2進数表現列は32バイトあれば足りるのだが、仮想列を定義する場合はVARCHAR2型128バイトと大きめに定義する必要がある。(これはいろいろ試行錯誤して確認した。)
同様にドット区切り表現列も15バイトではなく60バイトで定義する必要がある。

SQL> SELECT
  2   TABLE_NAME,COLUMN_NAME,DATA_TYPE||'('||DATA_LENGTH||')' "Type"
  3  ,VIRTUAL_COLUMN, DATA_DEFAULT
  4  FROM USER_TAB_COLS WHERE TABLE_NAME='ADDR_TBL';

TABLE_NAME   COLUMN_NAME  Type            VIR DATA_DEFAULT
------------ ------------ --------------- --- --------------------------------------------------
ADDR_TBL     IP_NUMBER    NUMBER(22)      NO
ADDR_TBL     IP_BINARY    VARCHAR2(128)   YES SUBSTR("TEST"."TO_BASE"("IP_NUMBER",2),1,32)
ADDR_TBL     IP_ADDR      VARCHAR2(60)    YES SUBSTR("TEST"."INET_NTOA"("IP_NUMBER"),1,15)

テストデータの格納

データはIP_NUMBER列のみにInsertするだけでよいことと、INET_ATON関数を使用してドット区切り表現でInsertしていることに注目!

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.1'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.2'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.3'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.4'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.11.5'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.1'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.2'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.3'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.4'));

1 row created.

SQL> insert into addr_tbl(IP_NUMBER) values (INET_ATON('192.168.21.5'));

1 row created.

SQL> commit;

Commit complete.

テーブル内容の表示

SQL> col IP_ADDR for a18
SQL> col IP_BINARY for a8
SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL;

 IP_NUMBER IP_ADDR            IP_BINAR
---------- ------------------ --------
3232238337 192.168.11.1       11000000
                              10101000
                              00001011
                              00000001

3232238338 192.168.11.2       11000000
                              10101000
                              00001011
                              00000010

3232238339 192.168.11.3       11000000
                              10101000
                              00001011
                              00000011

3232238340 192.168.11.4       11000000
                              10101000
                              00001011
                              00000100

3232238341 192.168.11.5       11000000
                              10101000
                              00001011
                              00000101

3232240897 192.168.21.1       11000000
                              10101000
                              00010101
                              00000001

3232240898 192.168.21.2       11000000
                              10101000
                              00010101
                              00000010

3232240899 192.168.21.3       11000000
                              10101000
                              00010101
                              00000011

3232240900 192.168.21.4       11000000
                              10101000
                              00010101
                              00000100

3232240901 192.168.21.5       11000000
                              10101000
                              00010101
                              00000101


10 rows selected.

指定されたアドレス範囲のIPアドレスを表示させる

SQL> set lines 140
SQL> set autot on
SQL> select IP_ADDR from ADDR_TBL
  2  where IP_NUMBER between INET_ATON('192.168.11.0') 
                         and INET_ATON('192.168.11.255');

IP_ADDR
------------------
192.168.11.1
192.168.11.2
192.168.11.3
192.168.11.4
192.168.11.5

Execution Plan
----------------------------------------------------------
Plan hash value: 1140719244

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    45 |     0   (0)| 00:00:01 |
|*  1 |  FILTER           |             |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| PK_ADDR_TBL |     1 |    45 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("INET_ATON"('192.168.11.0')<="INET_ATON"('192.168.11.255')) 2 - access("IP_NUMBER">="INET_ATON"('192.168.11.0') AND
              "IP_NUMBER"<="INET_ATON"('192.168.11.255'))
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         58  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

アドレス範囲条件の指定はドット区切り表現で行っているが、inet_aton()を介して10進数に変換しているのでインデックスレンジ検索となっていることがわかる。

検索条件にドット区切り表現をそのまま使うと…

SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL
  2  where IP_ADDR = '192.168.11.5';

 IP_NUMBER IP_ADDR            IP_BINAR
---------- ------------------ --------
3232238341 192.168.11.5       11000000
                              10101000
                              00001011
                              00000101

Execution Plan
----------------------------------------------------------
Plan hash value: 3135829337

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |   111 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| ADDR_TBL |     1 |   111 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("IP_ADDR"='192.168.11.5')

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
         38  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

「ip_addr」列にインデックスは設定されていないので、実行計画はフル・テーブル・スキャンとなる。

「ip_addr」列にインデックスを作成してみる

仮想列のよいところは、データがなくてもインデックスが作成できることである。これはファンクション・インデックスに似ている。
そこで、「ip_addr」列にユニーク・インデックスを作成してみる。

SQL> CREATE UNIQUE INDEX IX_IP_ADDR ON ADDR_TBL (IP_ADDR);

Index created.

SQL> select IP_NUMBER,IP_ADDR,IP_BINARY from ADDR_TBL
  2  where IP_ADDR = '192.168.11.5';

 IP_NUMBER IP_ADDR            IP_BINAR
---------- ------------------ --------
3232238341 192.168.11.5       11000000
                              10101000
                              00001011
                              00000101
Execution Plan
----------------------------------------------------------
Plan hash value: 3058182370

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |   111 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ADDR_TBL   |     1 |   111 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IX_IP_ADDR |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("IP_ADDR"='192.168.11.5')

Statistics
----------------------------------------------------------
         96  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

今度は、インデックスによる単一行検索となった。

1日分のAWRレポートをまとめて出力する

このエントリは「JPOUG Advent Calendar 2016」の13日目です。
昨日はcharade_oo4oさんの「Oracle on Hyper-V 2016」 でした。

複数のAWRレポートを作成するのは面倒

AWRスナップショットはデフォルトで1時間に1回取得されるので、レポート期間1時間のAWRレポートは1日分で24個になる。
1日分のレポートを1個ずつ作成するのは面倒なので、一度に作成する方法を考えてみた。

考慮する仕様は以下の3つ

  • 本日からn日前以降のレポートを全て出力する。(ただし本日分は含まない)
  • レポート期間は1時間で連続するスナップショットIDを指定する。
  • スナップショットはMMONプロセスで自動的に取得されたものを対象とする。(定常運用をイメージしているので、負荷テスト時のようにスナップショットをアドホックに取得していないことを前提。ちなみに手動で取得すると毎正時の自動取得がスキップされる場合があるので注意。)

作成用スクリプト

以下のPL/SQLスクリプトに適当な名前を付けて保存する。(例では「mkscr.sql」)

set echo off
set feedback off
set verify off
set trimspool on
set serveroutput on
spool getawrr.sql replace
DECLARE
  num_day NUMBER := &1;
  CURSOR sid_cur IS
    select
    -- es.DBID
     es.INSTANCE_NUMBER
    ,to_char(round(bs.END_INTERVAL_TIME,'mi'),'yyyy/mm/dd hh24') BEGIN_HOUR
    ,bs.SNAP_ID BEGIN_SNAP
    ,es.SNAP_ID END_SNAP
    from
     DBA_HIST_SNAPSHOT bs
    ,DBA_HIST_SNAPSHOT es
    where 1=1
    and bs.END_INTERVAL_TIME >= trunc(sysdate-num_day)
    and bs.END_INTERVAL_TIME <  trunc(sysdate)
--  and trunc(es.END_INTERVAL_TIME,'mi') = trunc(bs.END_INTERVAL_TIME,'mi') + 1/24
    and abs(round(es.END_INTERVAL_TIME,'mi') - round(bs.END_INTERVAL_TIME,'mi') < (1/24)*1.1
    and bs.SNAP_ID < es.SNAP_ID
    and bs.DBID = es.DBID
    and bs.INSTANCE_NUMBER = es.INSTANCE_NUMBER
    and bs.SNAP_FLAG = 0
    and es.SNAP_FLAG = 0
    order by
     bs.END_INTERVAL_TIME;
BEGIN
  FOR sid_rec IN sid_cur LOOP
    dbms_output.put_line('-- '||sid_rec.BEGIN_HOUR);
    dbms_output.put_line('define report_type=html');
    dbms_output.put_line('define num_days='||num_day);
    dbms_output.put_line('define begin_snap='||sid_rec.BEGIN_SNAP);
    dbms_output.put_line('define end_snap='||sid_rec.END_SNAP);
    dbms_output.put_line('define report_name=awrrpt_'||sid_rec.INSTANCE_NUMBER||'_'||sid_rec.BEGIN_SNAP||'_'||sid_rec.END_SNAP||'.html');
    dbms_output.put_line('@?/rdbms/admin/awrrpt.sql');
  END LOOP;
END;
/
spool off
set echo on
set feedback on
set verify on

補足説明

  • 8行目:n日前の「n」は実行時に引数で置換変数に渡す。
  • 20行目:n日前以降の条件。AWRリポジトリに残っている以上の日数も指定できるが、データがないので残っている分しかレポートはできない。
  • 22行目:1時間ごとにスナップショットを取得している場合。30分間隔であれば「1/48」に書き換える。
  • 23,24行目:22行目の条件だとスナップショット間隔がきっかり1時間でない場合のレポートが欠損してしまうため、10%(1時間であれば6分)未満の誤差にも対応した。
  • 27,28行目:PL/SQLパッケージ(DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT)により手動で取得されたスナップショットを除外している。(この条件はなくてもよいかも。)
  • 34行目:レポートファイル名は対話型で実行した場合のデフォルト名で出力するようにした。

実行例

実行方法は簡単。作成用スクリプトを実行(1行目)し、SQL*Plusを起動したカレント・ディレクトリに出力されたスクリプト(例では「getawrr.sql」、内容は上書きされるので最後に実行した内容が保存される。)を実行(35行目)するだけでよい。
注意点としては、日数を引数で必ず指定することである。(未指定の場合のハンドリングは特に考慮していない。)

SQL> @mkscr 1   <=== 必ず日数を引数で指定する SQL> set echo off
-- 2016/12/06 00
define report_type=html
define num_days=1
define begin_snap=4686
define end_snap=4687
define report_name=awrrpt_1_4686_4687.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 01
define report_type=html
define num_days=1
define begin_snap=4687
define end_snap=4688
define report_name=awrrpt_1_4687_4688.html
.................................................
-- 2016/12/06 22
define report_type=html
define num_days=1
define begin_snap=4708
define end_snap=4709
define report_name=awrrpt_1_4708_4709.html
@?/rdbms/admin/awrrpt.sql
-- 2016/12/06 23
define report_type=html
define num_days=1
define begin_snap=4709
define end_snap=4710
define report_name=awrrpt_1_4709_4710.html
@?/rdbms/admin/awrrpt.sql
SQL> set feedback on
SQL> set verify on
SQL> !ls -l getawrr.sql
-rw-r--r--. 1 oracle oinstall 4128 12月 7 17:24 2016 getawrr.sql
SQL> @getawrr.sql    <== 生成されたスクリプトを実行する
(以下、AWRレポート作成)

これで複数(1日分であれば24個)のAWRレポートが一気に作成される。(レポート内容によっては時間がかかるので、実行タイミングはDBサーバの負荷状況に留意したほうがよいだろう。)

特定の時間帯だけが必要であれば、該当部分をコピペで選択して実行してもよい。

カレントディレクトリに出力されるので、実行するディレクトリをどこにするかを考慮した方がよいかもしれない。

もっと手抜きして、作成用スクリプトの最終行に「@生成スクリプト名」を追記すれば、一気に作成まで行うことができる。(今回は生成スクリプトの内容を一度確認することも考慮して別に実行することとした。)

明日はYousuke Yadaさんです。

USE_INVISIBLE_INDEXESヒントについて(続編)

不可視索引のその後

先日、不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おうという記事を書いたのだが、以下の記述に関してどうやら違う挙動となるらしいことがわかった。


INDEXヒント+USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。


具体的には、複数の不可視索引が定義してある場合、INDEXヒントで明確に指定している不可視索引以外の不可視索引も使用されるようだ。

この部分を詳細に再検証してみたいと思う。

複数の不可視索引が存在する場合を検証する

検証環境

今回の検証で使用した環境は以下の通りである。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> show user
USER is "SH"

複数の索引を同時に使用するケースを考える

B*ツリー索引は、原則として1つの問合せブロックの中で1つだけ使用される。1つのSQL文の中で同時に2つ以上のB*ツリー索引を使うためには2つ以上の問合せブロックを組み合わせる必要がある。

今回の検証では、問合せ自体はなるべく簡単にしたいので、B*ツリー索引ではなくビットマップ索引を使用する。

SH.SALES表に定義してある(ビットマップ)索引の状況を確認すると以下のようになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      VISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      VISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

基本問合せ

基本となる問合せは以下のとおり。
2つの絞り込み条件により、SALES表にアクセスする。

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

2つのビットマップ索引を使い、それぞれ絞り込んだ結果を「BITMAP AND」操作(Id=4)により両方の条件を満たす集合を作り、件数に変換して結果を得ていることがわかる。(SALES表には一切アクセスしていない。)

索引SALES_CUST_BIXを不可視にする

次に、索引SALES_CUST_BIXを不可視に変更し、同じ問合せを行ってみよう。

SQL> alter index SALES_CUST_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

CUST_IDに比べ、CHANNEL_IDのカーディナリティが低いため、CUST_IDの絞り込みに索引が使えなくなった途端、実行計画はSALES表に対する全件検索へと変わっていることがわかる。

索引SALES_CHANNEL_BIXを不可視にする

引き続き、索引SALES_CHANNEL_BIXを不可視にする。

SQL> alter index SALES_CHANNEL_BIX invisible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

  COUNT(*)
----------
       159

Execution Plan
----------------------------------------------------------
Plan hash value: 3519235612

----------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |     8 |   489   (2)| 00:00:06 |       |       |
|   1 |  SORT AGGREGATE      |       |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL|       |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
|*  3 |    TABLE ACCESS FULL | SALES |    33 |   264 |   489   (2)| 00:00:06 |     1 |    28 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CUST_ID"=25939 AND "CHANNEL_ID"=3)

索引SALES_CUST_BIXが使用不可(不可視)となっていることで、既に実行計画は全件検索となっているので、実行計画に変化はない。

ここまでで、SALES表の索引のうち2つを不可視に変更したことになる。

SQL> select
  2   ui.TABLE_NAME
  3  ,ui.INDEX_NAME
  4  ,uic.COLUMN_NAME
  5  ,ui.INDEX_TYPE
  6  ,ui.VISIBILITY
  7  from
  8   USER_INDEXES     ui
  9  ,USER_IND_COLUMNS uic
 10  where ui.TABLE_NAME = 'SALES'
 11  and   ui.TABLE_NAME = uic.TABLE_NAME
 12  and   ui.INDEX_NAME = uic.INDEX_NAME
 13  order by
 14   ui.INDEX_NAME;

TABLE_NAME  INDEX_NAME         COLUMN_NAME  INDEX_TYPE  VISIBILIT
----------- ------------------ ------------ ----------- ---------
SALES       SALES_CHANNEL_BIX  CHANNEL_ID   BITMAP      INVISIBLE
SALES       SALES_CUST_BIX     CUST_ID      BITMAP      INVISIBLE
SALES       SALES_PROD_BIX     PROD_ID      BITMAP      VISIBLE
SALES       SALES_PROMO_BIX    PROMO_ID     BITMAP      VISIBLE
SALES       SALES_TIME_BIX     TIME_ID      BITMAP      VISIBLE

USE_INVISIBLE_INDEXESヒントを指定する(INDEXヒントは使用しない)

ここで、USE_INVISIBLE_INDEXESヒントを指定して問合せを実行してみる。
2つの不可視索引が使えるようになるので、最初と同じ実行計画となるはずである。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   count(*) from SALES
  3  where CUST_ID    = 25939
  4  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

想定通りの結果となった。

USE_INVISIBLE_INDEXESヒントとINDEXヒントを明示的に指定する

次に、INDEXヒントでSALES SALES_CUST_BIXのみの使用を明示的に指定してみる。
INDEXヒントで使用される索引を限定することが出来るのであれば、実行計画は別のものになることが予想される。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX) */
  3   count(*) from SALES
  4  where CUST_ID    = 25939
  5  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 228738440

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     1 |     8 |    58   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE               |                   |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL         |                   |    33 |   264 |    58   (0)| 00:00:01 |     1 |    28 |
|   3 |    BITMAP CONVERSION COUNT    |                   |    33 |   264 |    58   (0)| 00:00:01 |       |       |
|   4 |     BITMAP AND                |                   |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|*  6 |      BITMAP INDEX SINGLE VALUE| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("CUST_ID"=25939)
   6 - access("CHANNEL_ID"=3)

INDEXヒントに指定した索引とは別の索引SALES_CHANNEL_BIXも使用されていることがわかる。

つまりINDEXヒントだけでは使用される索引を特定することが出来ないことがわかった。

使用しない索引をNO_INDEXヒントで明示する

使用したくない方の不可視索引を明示的に指定するには、以下のようにNO_INDEXヒントを使う。

SQL> select /*+ USE_INVISIBLE_INDEXES
  2             INDEX(SALES SALES_CUST_BIX)
  3             NO_INDEX(SALES SALES_CHANNEL_BIX) */
  4   count(*) from SALES
  5  where CUST_ID    = 25939
  6  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

索引SALES_CUST_BIXのみを使用する実行計画となった。

索引SALES_CUST_BIXを可視に変更する

今まで不可視だった索引SALES_CUST_BIXを可視に変更して問合せを実行してみる。
この状態では索引SALES_CHANNEL_BIXのみが使用不可である。

SQL> alter index SALES_CUST_BIX visible;

Index altered.

SQL> select count(*) from SALES
  2  where CUST_ID    = 25939
  3  and   CHANNEL_ID = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 2288362790

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |     8 |    54   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                |     1 |     8 |            |          |       |       |
|   2 |   PARTITION RANGE ALL               |                |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |    33 |   264 |    54   (0)| 00:00:01 |     1 |    28 |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                |       |       |            |          |       |       |
|*  5 |      BITMAP INDEX SINGLE VALUE      | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("CHANNEL_ID"=3)
   5 - access("CUST_ID"=25939)

1つ前と同じ実行計画となっていることがわかる。

まとめ

  • USE_INVISIBLE_INDEXESヒントを指定するとSQL文単位で使える不可視索引が全てCBOの評価対象となるので、INDEXヒントで使用したい索引を特定しようとしても結果として無視される。
  • 複数の不可視索引を作成し順番にテストするような場合、使いたくない索引をNO_INDEXヒントで指定しないと意図したテストとならない可能性があるので注意が必要である。

これらは、マニュアルにもMy Oracle Supportにも記述されていなかったので、不可視索引を使いこなす場合に覚えておきたい事実である。

今回はここまで

インデックス領域を含まない全体バックアップ(RMAN)

マニュアルを読んでいて気がついた

先日、12cの概要マニュアルを何気なく読んでいたら以下の文章が目に入ってきた。

Oracle® Database概要
12cリリース1 (12.1)
B71299-07

索引記憶域
「索引セグメントの表領域は、所有者のデフォルト表領域またはCREATE INDEX文で明示的に指定された表領域です。管理を容易にするために、索引をその表とは別の表領域に格納できます。たとえば、索引のみを含む表領域は再構築できるため、これらの表領域をバックアップしないよう指定することによって、バックアップに必要な時間と記憶域を削減できます。」

バックアップ領域の問題

以前関わっていた某超巨大システムでは、バックアップ領域は常に問題を抱えていた。

時間の経過と共にデータ量が増加し、バックアップ時間が長くなるだけでなく、バックアップ先のディスク容量が逼迫して新たにストレージを追加するという問題が深刻になっていた。

データ量が多いシステムほどバックアップに関する悩みは大きいが、上に紹介したマニュアルの記述はまさに目からウロコであった。仮にインデックスが占めるサイズが全体の3割であれば、バックアップ容量と時間を一気に3割削減できることになる。これは大きい。

万一ストレージが全損してバックアップからリカバリすることになった場合、インデックスを再作成する手間と時間がかかるが、日常のバックアップ容量と時間を削減することのメリットの方が大きい。

テーブルとインデックスの表領域を分ける意味

昔は、テーブルとインデックスのI/O競合を避けるために、両者を格納する表領域を分けるということはごく当たり前に行われていた。

しかし、ASMが一般的になりSAMEアーキテクチャによって理論的にはI/Oホットスポットが発生しなくなってからは、管理が容易という理由でデータとインデックスを1つの大きな表領域で管理することが普通になった。

テーブル用とインデック用の表領域をサイジングを間違って空き容量にアンバランスを発生させたりということに頭を使うよりも、全部を1つにしてしまえば単純である。

ところが、バックアップ対象を分けるという意味では、テーブルとインデックス用の表領域を別個に設けるという考え方が新しく成り立つ。

インデックス用表領域を含まない全体バックアップ

インデックスをインデックス用表領域に移動する

それでは実際にインデックス表領域を全体バックアップに含まない方法を確認してみよう。

まず、インデックスをインデックス用表領域に移動させる。これは個々のインデックスに対して必要であるが、一度行ってしまえばよい。

SQL> alter index SCOTT.PK_EMP rebuild tablespace USER_INDEX;

Index altered.

RMANの起動とデフォルト設定の確認

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on 金 6月 3 06:11:07 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1439336626)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

除外表領域の指定

特定の表領域をバックアップ対象から外すためには「CONFIGURE EXCLUDE FOR TABLESPACE」コマンドによって設定する。

これによりハイライト行に示すように除外表領域が指定される。

RMAN> configure exclude for tablespace USER_INDEX;

Tablespace USER_INDEX will be excluded from future whole database backups
new RMAN configuration parameters are successfully stored

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
..........................................................
CONFIGURE EXCLUDE FOR TABLESPACE 'USER_INDEX';
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

バックアップの実行

除外設定を行った後は、通常と同様に「BACKUP DATABASE」コマンドで全体バックアップを取得する。

RMAN> backup database;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
file 6 is excluded from whole database backup
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp tag=TAG20160603T061125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

「LIST BACKUP」コマンドでバックアップの内容を確認すると、USER_INDEX表領域が含まれていないことがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

除外設定を無効にする

NOEXCLUDE句を指定すると除外設定を無効にしてバックアップを取得することができる。

バックアップのログにUSER_INDEX表領域が含まれている。(12行目)

RMAN> backup database noexclude;

Starting backup at 16-06-03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/opt/oracle/app/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/opt/oracle/app/oradata/orcl/system01.dbf
input datafile file number=00003 name=/opt/oracle/app/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oradata/orcl/example01.dbf
input datafile file number=00006 name=/opt/oracle/app/oradata/orcl/user_index01.dbf    --バックアップ対象となっている
input datafile file number=00004 name=/opt/oracle/app/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 16-06-03
channel ORA_DISK_1: finished piece 1 at 16-06-03
piece handle=/opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp tag=TAG20160603T065225 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-06-03

LIST BACKUPでバックアップの内容を確認すると、やはりUSER_INDEX表領域がバックアップに含まれていることがわかる。

RMAN> list backup;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T060715
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T060715_co18053x_.bkp
  SPFILE Included: Modification time: 16-06-02
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2328889      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.24G      DISK        00:00:49     16-06-03
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T061125_co187xbq_.bkp
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2329697    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    9.36M      DISK        00:00:04     16-06-03
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T061125
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T061125_co189s2r_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2329721      Ckp time: 16-06-03

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    1.24G      DISK        00:00:27     16-06-03
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_nnndf_TAG20160603T065225_co1bnslm_.bkp
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time Name
  ---- -- ---- ---------- -------- ----
  1       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/system01.dbf
  2       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/sysaux01.dbf
  3       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/undotbs01.dbf
  4       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/users01.dbf
  5       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/example01.dbf
  6       Full 2331933    16-06-03 /opt/oracle/app/oradata/orcl/user_index01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    9.36M      DISK        00:00:01     16-06-03
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20160603T065225
        Piece Name: /opt/oracle/app/flash_recovery_area/ORCL/backupset/2016_06_03/o1_mf_ncsnf_TAG20160603T065225_co1boxno_.bkp
  SPFILE Included: Modification time: 16-06-03
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 2332008      Ckp time: 16-06-03

今日はここまで。

不可視索引の裏でオプティマイザはどう動いているか?

オプティマイザの動作を確認する

前回の投稿では、不可視索引の実践的な使い方の提言を行った。

今回は、オプティマイザが不可視索引を使用する際に、内部でどのようなことが起きているのかを調べてみる。

コストベース・オプティマイザの挙動を調べるには10053トレースを取得する。
(10053トレースにはハード・パース時の実行計画算出過程が出力されるため、以下の検証はインスタンス再起動直後に実施した。)

トレースファイルの確認と識別文字列の設定

これはトレースを取得する際のTipsだが、識別文字列を設定しておくとトレースファイルをトレース出力ディレクトリで探すのが非常に楽になる。

ここでは「CBO」という識別文字列を設定する。

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
--------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_28863.trc

SQL> alter session set tracefile_identifier = 'CBO';

Session altered.

SQL> select VALUE from V$DIAG_INFO where NAME = 'Default Trace File';

VALUE
------------------------------------------------------------------
/opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_25629_CBO.trc

10053トレースの開始

以下のコマンドによりセッション単位で10053トレースの取得を開始する。

SQL> alter session set events '10053 trace name context forever';

Session altered.

デフォルト実行(不可視索引は使われない)

SQL> set autot on
SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    UNUSABLE
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: TableScan
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 1.09  Bytes: 0

optimizer_use_invisible_indexesパラメータの値はデフォルトの「false」なので、不可視索引EMP_HIRE_DATE_IXは「UNUSABLE」つまり使用されない状態であることがわかる。

オプティマイザが最終的に選択したアクセスパスはCost=3となるフル・テーブル・スキャンである。

ヒント句で不可視索引を指定

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |       2 (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |       2 (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |       1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = false
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
    User hint to use this index
....................................................
***************************************
 SINGLE TABLE ACCESS PATH 
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0

「User hint to use this index」という表示から、不可視索引EMP_HIRE_DATE_IXがヒント句で有効になっていることがわかる。

このインデックスを使用してCost=2となるアクセスパスが選択された。

USE_INVISIBLE_INDEXESパラメータをTRUEに変更

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_use_invisible_indexes      boolean     FALSE
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
optimizer_use_invisible_indexes      boolean     TRUE

SQL> select
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME
  3  ,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE
  4  from
  5   EMPLOYEES
  6  where HIRE_DATE <= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00

Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("HIRE_DATE"<=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

10053トレース抜粋

***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
  *************************************
  PARAMETERS WITH ALTERED VALUES
  ******************************
Compilation Environment Dump
_pga_max_size                       = 471840 KB
optimizer_use_invisible_indexes     = true
Bug Fix Control Environment
....................................................
  *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
....................................................
is_recur_flags                      = 0
optimizer_use_invisible_indexes     = true
flashback_data_archive_internal_cursor = 0
....................................................
***************************************
BASE STATISTICAL INFORMATION
***********************
....................................................
  Index: EMP_HIRE_DATE_IX  Col#: 6
    LVLS: 0  #LB: 1  #DK: 98  LB/K: 1.00  DB/K: 1.00  CLUF: 13.00
....................................................
***************************************
SINGLE TABLE ACCESS PATH 
....................................................
  Best:: AccessPath: IndexRange
  Index: EMP_HIRE_DATE_IX
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.09  Bytes: 0
....................................................

USE_INVISIBLE_INDEXESパラメータをALTER SESSIONコマンドにより「true」に変更している。これによりインデックスEMP_HIRE_DATE_IXは実行計画算出に使用されるようになる。

2番目の例と同様にCost=2のアクセスパスが選択された。

10053トレースの終了

10053トレースを終了させるためには以下のコマンドを実行させる。

SQL> alter session set events '10053 trace name context off';

Session altered.

今日はここまで。

不可視索引はUSE_INVISIBLE_INDEXESヒントと共に使おう

不可視索引とは

不可視索引とはオプティマイザから「見えない」という意味で不可視である。(12c概要 索引の使用可能性と可視性 参照)

オプティマイザに影響を与えないので、通常の索引(可視索引)作成で既存の実行計画を不用意に変えてしまうリスクを回避することができる、11g以降で実装されている機能である。

不可視索引の用途

本番運用が始まってから、「この列にインデックスが必要だ。」とか「このインデックスはどうも使われていないようなので削除しよう。」という定義変更のニーズが発生することは多々ある。

上記マニュアルには

  1. 索引を削除する前に削除をテストする場合
  2. アプリケーション全体に影響を与えることなく一時的に索引を使用する場合

という2つの使い方の例が示されているが、2.の「一時的」とはそのインデックスの有効性を確認するテストなので、(恒常的な)運用に乗せるためには

SQL> ALTER INDEX <インデックス名> VISIBLE;

として、不可視可視とする必要があると、マニュアルや多くのブログ記事等には書いてある。

というようなことを先日あるプロジェクトの人に話したところ「可視化した時点でアプリケーション全体に影響を与える可能性があるので望ましくない。SQL単位で可視化を制御する方法はないのか?」と質問された。

確かにもっともな意見である。普段いろいろ教える機会が多いのだが、実際に使う側の目線で本質的な問いを投げかけられるとハッとさせられる。

SQL単位でとなると答えはヒント句による制御しかない。

不可視索引関連のヒント

結論から先に言うと、不可視索引関連のヒントは

  • USE_INVISIBLE_INDEXES(NO_USE_INVISIBLE_INDEXES)

であるが、残念ながらSQLリファレンス・マニュアルにはこのヒントに関する記述がない。

(以前、Oracleバージョンによるヒント句の変遷 という記事を書いたのでそちらを参照してもらいたい。)

ヒント句を検証してみた

不可視索引の作成

検証を行うためにEMP表のHIRE_DATE列に不可視索引を作成する。

SQL> create index EMP_HIRE_DATE_IX on EMPLOYEES (HIRE_DATE) invisible;

Index created.

SQL> select TABLE_NAME,INDEX_NAME,VISIBILITY from user_indexes
  2  where VISIBILITY != 'VISIBLE'
  3  order by TABLE_NAME,INDEX_NAME;

TABLE_NAME                     INDEX_NAME                     VISIBILIT
------------------------------ ------------------------------ ---------
EMPLOYEES                      EMP_HIRE_DATE_IX               INVISIBLE

基本動作(不可視索引はそのままでは使われない)

不可視索引はデフォルトではオプティマイザに使用されないので、条件検索は全件検索からのフィルタ処理となる。(Id=1)

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  2  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更

不可視索引を使用するためにはALTER SESSIONコマンドにより、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータをセッション単位で変更する。
(ALTER SYSTEMコマンドによりインスタンスで使用可能に変更することもできるが、不可視索引として作成する意味がないので現実的ではない。)

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     TRUE

この状態で先ほどの問合せを実行すると、使用されなかったインデックスEMP_HIRE_DATE_IXが使用されるようになったことがわかる。

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES  
  2  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

ヒント句を試してみる

ヒント句を試す前に、セッションを再接続することでクリアし、OPTIMIZER_USE_INVISIBLE_INDEXESパラメータがデフォルトの「FALSE」に戻っていることを確認する。

SQL> conn hr/hr
Connected.
SQL> show parameter optimizer_use_invisible_indexes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE

INDEXヒントのみ

まず、INDEXヒントにより不可視索引を指定した場合どのような挙動になるのかを確認する。

SQL> select /*+ INDEX (employees emp_hire_date_ix) */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    27 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    27 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

オプティマイザがこのインデックスを使用するようになっていないため、ヒント句でインデックス名を指定したとしても無視されることがわかる。

USE_INVISIBLE_INDEXESヒントのみ

次に、USE_INVISIBLE_INDEXESヒントをヒント句で指定してみる。

SQL> select /*+ USE_INVISIBLE_INDEXES */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

今度は、インデックスを使用するようになった。

INDEXヒント+USE_INVISIBLE_INDEXESヒント

基本的にUSE_INVISIBLE_INDEXESヒントを指定するだけでよいのだが、もし複数の不可視索引が定義されていたりする場合は、どのインデックスを使用するべきかをINDEXヒントで明確に指定することができる。 (この部分は事実と異なる。NO_INDEXヒントにより使いたくない索引も明示的に指定する必要がある。 USE_INVISIBLE_INDEXESヒントについて(続編) 参照)

SQL> select /*+ USE_INVISIBLE_INDEXES INDEX(EMPLOYEES EMP_HIRE_DATE_IX)  */
  2   EMPLOYEE_ID,FIRST_NAME,LAST_NAME,to_char(HIRE_DATE,'yyyy/mm/dd hh24:mi') HIRE_DATE from EMPLOYEES
  3  where HIRE_DATE >= to_date('2008/04/21 00:00:00','yyyy/mm/dd hh24:mi:ss');

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 HIRE_DATE
----------- -------------------- ------------------------- ----------------
        167 Amit                 Banda                     2008/04/21 00:00
        173 Sundita              Kumar                     2008/04/21 00:00


Execution Plan
----------------------------------------------------------
Plan hash value: 3345584716

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    27 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES        |     1 |    27 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("HIRE_DATE">=TO_DATE(' 2008-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL文の可読性を向上させるために、使用したいインデックスを明示的に指定した方がよいかもしれない。

前々回、前回とWHERE句とインデックスの関係を調査する

という2つの記事を書いたが、新しく作成するインデックスが及ぼす影響を網羅性を担保しつつ調査することは、インデックスの数が多いほど大変な作業である。

ヒント句で有効にした不可視索引はアプリケーション全体に影響を与えることなく性能を改善することができる。不可視索引を使うには原則としてヒント句を共に使うべきであるというのが私の提言である。

今日はここまで。

インデックスとSQLの関係を調査する

前回の投稿他

前回の投稿ではsys.col_usage$表を使って、あるカラムに関するWHERE句(Predicate)の状況を分析する要領を紹介した。

一方、昨年「V$SQL_PLANでCRUD表モドキを作ってみる③」という記事を書いたのだが、応用編として


インデックス – SQL
V$SQL_PLANを使えば、テーブルとSQLの関係だけでなく、インデックスとSQLの関係を分析することもできます。
例えば、あるインデックスの定義を変更しようとする場合、1つのSQLだけに注目してしまうと他のSQLに影響があることに気づかず新たな問題を引き起こしてしまうかもしれません。
そのような場合、インデックスとSQLの相関表が役に立ちます。


ということを紹介しただけで終わっていた。

最近、実業務でインデックスとSQL(SQL_ID)の関係を一覧化する機会があったので、その要領を紹介しておこうと思う。

調査用SQL

基本はV$SQL_PLANでこれにDBA_INDEXESを結合して所有者とテーブルの情報を取得している。

21行目の「OPERATION = 'INDEX'」の条件でインデックス検索に関わるオペレーションに絞り込み
22行目(ハイライト行)で分析対象となるスキーマ名(例ではSCOTT)を指定している。

前述のCRUD表を作る試みでは、INSERT,SELECT,UPDATE,DELETEを区別していたが、インデックスとSQLの関係においては、WHERE条件によりインデックスを使用してデータにアクセスしたりフィルタリングしたりすることはINSERTを除き同じ挙動であるので、この調査用SQLではSELECT,UPDATE,DELETEの区別を特に行っていない。

select distinct
i.OWNER
,i.TABLE_NAME
,s.OBJECT_NAME INDEX_NAME
,s.SQL_ID
,s.PLAN_HASH_VALUE
,s.ID
,s.OPERATION ||' '|| s.OPTIONS operation
,case when s.ACCESS_PREDICATES is not null
then 'access: '||ACCESS_PREDICATES
when s.FILTER_PREDICATES is not null
then 'filter: '||FILTER_PREDICATES
else null
end as PREDICATES
from
V$SQL_PLAN  s
join
DBA_INDEXES i
on    s.OBJECT_OWNER = i.OWNER
and   s.OBJECT_NAME  = i.INDEX_NAME
where OPERATION      = 'INDEX'
and   i.OWNER        = 'SCOTT'
order by
i.OWNER
,i.TABLE_NAME
,s.OBJECT_NAME
,s.SQL_ID
,s.PLAN_HASH_VALUE
,s.ID
;

実行例

SQL> select distinct
2   i.OWNER
3  ,i.TABLE_NAME
4  ,s.OBJECT_NAME INDEX_NAME
5  ,s.SQL_ID
6  ,s.PLAN_HASH_VALUE
7  ,s.ID
8  ,s.OPERATION ||' '|| s.OPTIONS operation
9  ,case when s.ACCESS_PREDICATES is not null
10          then 'access: '||ACCESS_PREDICATES
11        when s.FILTER_PREDICATES is not null
12          then 'filter: '||FILTER_PREDICATES
13        else null
14   end as PREDICATES
15  from
16   V$SQL_PLAN  s
17  join
18   DBA_INDEXES i
19  on    s.OBJECT_OWNER = i.OWNER
20  and   s.OBJECT_NAME  = i.INDEX_NAME
21  where OPERATION      = 'INDEX'
22  and   i.OWNER        = 'SCOTT'
23  order by
24   i.OWNER
25  ,i.TABLE_NAME
26  ,s.OBJECT_NAME
27  ,s.SQL_ID
28  ,s.PLAN_HASH_VALUE
29  ,s.ID
30  ;

OWNER  TABLE_NAME  INDEX_NAME  SQL_ID        PLAN_HASH_VALUE         ID OPERATION            PREDICATES
------ ----------- ----------- ------------- --------------- ---------- -------------------- ---------------------
SCOTT  EMP         PK_EMP      0dmdbcy6g6qsf       169057108          2 INDEX RANGE SCAN     access: "EMPNO">0
SCOTT  EMP         PK_EMP      64xm083yya03t       204855851          2 INDEX UNIQUE SCAN    access: "EMPNO"=7876
SCOTT  EMP         PK_EMP      861msw504avnz      1749432681          2 INDEX FULL SCAN
SCOTT  EMP         PK_EMP      9gs6uhh2jvrb1      2949544139          2 INDEX UNIQUE SCAN    access: "EMPNO"=7369
SCOTT  EMP         PK_EMP      caggwncwwcugc      3659136155          2 INDEX UNIQUE SCAN    access: "EMPNO"=7369
SCOTT  EMP         PK_EMP      dq4cyhcs58mys      2400378433          2 INDEX FULL SCAN
SCOTT  EMP         PK_EMP      fmt934hxtucpa      2949544139          2 INDEX UNIQUE SCAN    access: "EMPNO"=7566

7 rows selected.

この例では、PK_EMPというインデックスのみであるが、インデックス毎にSQL_IDが複数存在している。1つのインデックスに紐付くSQLが多いものほど、インデックスを変更することによる影響範囲が広いことがわかる。

さらに、1つのSQL_IDに複数のPLAN_HASH_VALUEが存在している場合があれば、一つのPLAN_HASH_VALUEが複数のSQL_IDに紐付くこともある。(例:PLAN_HASH_VALUE=2949544139、ちなみにPLAN_HASH_VALUEとは実行計画に紐付くユニークな値である。)

つまり、SQL_IDとPLAN_HASH_VALUEは多対多の関係にある。

ID列は、実行計画ツリーから容易に当該インデックスの使用箇所を追跡しやすように表示させてみた。

OPERATION列はインデックス・スキャンの様々な種類を示している。

PREDICATES列は、「ACCESS/FILTER」の違いと実際のWHERE条件の抜粋を示している。

この情報と前回紹介したsys.col_usage$表を使ったWHERE句の分析結果を突き合わせることにより、パフォーマンス問題解決上有益な情報が得られるかもしれない。

今日はここまで

sys.col_usage$表でWHERE句を分析する

sys.col_usage$とは

sys.col_usage$を理解するためにはまずヒストグラムを理解する必要がある。

ヒストグラムとはCBOが使用する列分布情報を保持するものであり、列データの分布が不均一な場合はヒストグラムの情報を使用してより良い実行計画を選択する。

ヒストグラムは列データの偏りが高い場合に有用なので、次のような状況では有用ではなく、つまりヒストグラムを作成する意味がない。

  • WHERE句内で指定しない列
    • 絞り込み条件として使用しない列にヒストグラムを作っても無駄
  • 均一な分布
    • データの偏りがない場合
  • 一意な列を含む等価述語

OracleはDBMS_STATSパッケージよって統計情報を取得する際、ヒストグラムを取得すべき列を特定する情報を収集している。
この情報はSMONによって取得されsys.col_usage$表に保持される。

「CBOに関する統計情報は、バックグラウンドプロセスのシステムモニタ(SMON)によってテーブルに記録される。そのようなテーブルの1つ、COL_USAGE$テーブルは、SELECTクエリで使用される述語、つまり、WHERE節で使用される列、および、等号、LIKE、範囲など、述語の種類に関する情報を記録するのに使われる。10g Release 2では、SMONプロセスが20分ごとにこのテーブルを更新する。」
Oracleフォレンジック 第5部 無監査時のデータ窃盗の証拠調査 から

sys.col_usage$表のカラム

sys.col_usage$表各カラムには当該WHERE条件が実行された回数が格納される

  • EQUALITY_PREDS :等価条件
  • EQUIJOIN_PREDS:等価結合条件
  • NONEQUIJOIN_PREDS:不等価結合条件
  • RANGE_PREDS:範囲検索条件
  • LIKE_PREDS:LIKE(またはNOT LIKE)検索条件
  • NULL_PREDS:NULL(またはNOT NULL)検索条件

sys.col_usage$表でWHERE句の分析をする。

sys.col_usage$表は前述のとおり、本来はOracleがヒストグラム作成のために情報を格納する内部表であるが、この表を使えば、(スキーマ)、テーブル、カラム単位でWHERE句にどのような条件が指定されているかを一覧表示させることができる。
19〜33行目で表示させたくないスキーマを指定している。
(右スクロールしてCOLUMN_NAME列よりも右側を表示させる。)

SQL> select
  2   u.NAME             OWNER
  3  ,o.NAME             TABLE_NAME
  4  ,c.NAME             COLUMN_NAME
  5  ,us.EQUALITY_PREDS
  6  ,us.EQUIJOIN_PREDS
  7  ,us.NONEQUIJOIN_PREDS
  8  ,us.RANGE_PREDS
  9  ,us.LIKE_PREDS
10  ,us.NULL_PREDS
11  --,to_char(us.TIMESTAMP,'yyyy/mm/dd hh24:mi:ss') TIMESTAMP
12  from
13        sys.col_usage$ us
14   join sys.obj$       o  on us.OBJ#    = o.OBJ#
15   join sys.col$       c  on us.OBJ#    = c.OBJ#
16                         and us.INTCOL# = c.INTCOL#
17   join sys.user$      u  on o.OWNER#   = u.USER#
18  where u.NAME not in (
19   'APEX_030200'
20  ,'CTXSYS'
21  ,'DBSNMP'
22  ,'EXFSYS'
23  ,'FLOWS_FILES'
24  ,'IX'
25  ,'MDSYS'
26  ,'OLAPSYS'
27  ,'ORDDATA'
28  ,'ORDSYS'
29  ,'SYS'
30  ,'SYSMAN'
31  ,'SYSTEM'
32  ,'WMSYS'
33  ,'XDB'
34  )
35  order by
36   u.NAME
37  ,o.NAME
38  ,c.INTCOL#
39  ;
 
OWNER  TABLE_NAME                     COLUMN_NAME                    EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS
------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ----------
OE     CATEGORIES_TAB                 CATEGORY_ID                                 1              0                 0           0          0          0
OE     INVENTORIES                    PRODUCT_ID                                  1              0                 0           0          0          0
OE     INVENTORIES                    WAREHOUSE_ID                                0              1                 0           0          0          0
OE     PRODUCT_INFORMATION            CATEGORY_ID                                 1              0                 0           0          0          0
OE     WAREHOUSES                     WAREHOUSE_ID                                0              1                 0           0          0          0
SCOTT  EMP                            EMPNO                                       2              0                 0           1          0          0
SH     CHANNELS                       CHANNEL_ID                                  0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_CLASS_ID                            0              1                 0           0          0          0
SH     CHANNELS                       CHANNEL_TOTAL_ID                            0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_SUBREGION_ID                        0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_REGION_ID                           0              1                 0           0          0          0
SH     COUNTRIES                      COUNTRY_TOTAL_ID                            0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_ID                                     0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_CITY_ID                                0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_STATE_PROVINCE_ID                      0              1                 0           0          0          0
SH     CUSTOMERS                      COUNTRY_ID                                  0              1                 0           0          0          0
SH     CUSTOMERS                      CUST_TOTAL_ID                               0              1                 0           0          0          0
SH     PRODUCTS                       PROD_ID                                     0              1                 0           0          0          0
SH     PRODUCTS                       PROD_SUBCATEGORY_ID                         0              1                 0           0          0          0
SH     PRODUCTS                       PROD_CATEGORY_ID                            0              1                 0           0          0          0
SH     PRODUCTS                       PROD_TOTAL_ID                               0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_ID                                    0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_SUBCATEGORY_ID                        0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_CATEGORY_ID                           0              1                 0           0          0          0
SH     PROMOTIONS                     PROMO_TOTAL_ID                              0              1                 0           0          0          0
SH     SALES                          PROD_ID                                     1              1                 0           0          0          0
SH     SALES                          TIME_ID                                     1              1                 0           0          0          0
SH     TIMES                          TIME_ID                                     0              1                 0           0          0          0
SH     TIMES                          WEEK_ENDING_DAY_ID                          0              1                 0           0          0          0
SH     TIMES                          CALENDAR_MONTH_ID                           0              1                 0           0          0          0
SH     TIMES                          FISCAL_MONTH_ID                             0              1                 0           0          0          0
SH     TIMES                          CALENDAR_QUARTER_ID                         0              1                 0           0          0          0
SH     TIMES                          FISCAL_QUARTER_ID                           0              1                 0           0          0          0
SH     TIMES                          CALENDAR_YEAR_ID                            0              1                 0           0          0          0
SH     TIMES                          FISCAL_YEAR_ID                              0              1                 0           0          0          0
 
36 rows selected.

Flashback Dropの検証④(最終回)

Flashback Dropのまとめ

Flashback Dropに関して、基本機能と容量管理について簡単な検証で確認をしたが最後におさらいをする。

  • Flashback DropはRECYCLEBIN初期化パラメータがON(デフォルト)の場合に有効な機能であり、OFFの場合はOracle9iまでと同じ動作となる。
  • テーブルをPURGEオプションなしでDropすると、テーブルは物理的に削除されずにリサイクルビンで管理される。これはリサイクルビン用の表領域に移動されるのではなく、同じ表領域のままデータ・ディクショナリから見えなくなるような仕組みで管理されることを意味する。
  • リサイクルビンで管理されているテーブルは「BIN$」で始まる名前にRenameされる。
  • テーブルに紐付くインデックスおよびPK制約もリサイクルビンで管理される対象であり、同様に「BIN$」で始まる名前にRenameされる。
  • FK制約はDropとともに削除される
  • FLASHBACK TABLE <テーブル名> TO BEFORE DROP文でリサイクルビンにあるテーブルを削除直前に復元することができる。(名前は特に指定しないかぎり元に戻る)
  • 同時にインデックスとPK制約も復元される。(ただし名前は「BIN$」で始まる名前のまま。)従って一意制約は有効であり重複行をInsertしようとするとエラーになる。
  • FK制約は削除されているので復元されることはない。
  • リサイクルビンで管理されているオブジェクトは、セグメントを解放しない状態で表領域内に存在している。
  • 療養域の空きがなくなるまでセグメントは解放されないが、新たなオブジェクトのために領域が必要になると、削除時のSCNが古いセグメントから解放される。
  • この動作はユーザの操作を必要とせず自動的に行われる。

Flashback Dropは使える機能なのか?

上で述べたとおりFlashback Dropはデフォルトで使用できる機能であるが、積極的にオフにすべきなのだろうか?

検証した限り、私にはオフにする正当な理由が見つからない。というかオフにしてはいけない機能だと思う。

ところが、最近Amazon RDSでOracle11gR2 SE1環境をお試しで構築したところ、Recyclebin初期化パラメータが「OFF」になっていた。

パラメータグループを作成し、デフォルト値の「ON」に変更することは可能なはずなので問題はないのだが、なぜこのような設定値になっているのかは不明だ。

現行踏襲の呪縛

この検証シリーズは、JPOUG Tech Talk Night #6のライトニングトークで披露させていただいたネタなのだが、その中でこの「現行踏襲の呪縛」という言葉に対する反応が意外によかった。

つまり、アップグレードで多くの新機能が使えるようになっても、「新たなバグを引きたくない。」というような消極的な理由から、新機能を使わないということが現場では非常に多い。

「新機能にむやみに飛びつくのは素人だ。」と言わんばかりにベテランDBAが結果として間違った方向にミスリードし、小さな問題を大きくしてしまうとすればこれほど残念なことはない。

  • マニュアルをよく読んで誤解をしない。
  • 自分で検証して内部動作・仕様を確認する。

こういうことを怠って、自分の思い込みだけで判断するから、技術の進歩を素直に認めない空気が醸成されるのだ。

もう20年近く前のことで私がまだ経験の浅いエンジニアだったころ、Oracle Master の保有率が国内トップクラスというある開発会社に転職した時の話である。

「ウチの会社では外部キー制約は使わないことになっているから」と言われて面食らった。

リレーショナル・データベースというのものを正しく理解せずに実装しているシステムがどんな結末をたどるか、ということをこの会社にいる間に身をもって経験した。

ある地方におけるプロジェクトが炎上し、何人もの在京エンジニアが投入されたが皆討ち死にするような状況となった末、その会社は遠方で苦しんでいるメンバーに対し、残りの社員全員で励ましの寄せ書きを送ることにした。

今思えばとんでもないブラックな会社だったと思うが、強烈な違和感を持った私はやがて別の会社に転職をした。

その決断は間違っていなかったと確信している。

おまけ

大事なことを忘れたのだが、この検証はWindows7 64bit環境で以下のバージョンで実施した。

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 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

10gではメタデータがリサイクルビンに残っていた?

古い話だが、Flashback Dropが出た10gでは不思議な仕様だった。

以下は、オペレーション・ログからの抜粋だが、リサイクルビンの中で一番古いオブジェクトのUSER_RECYCLEBIN.SPACEが「0」のものがあることに気がついた。

試しにこのテーブルをフラッシュバックしたところ、テーブル構造(メタデータ)が復元されたにも関わらずデータが0件だった。

SQL> select ORIGINAL_NAME,TYPE,SPACE from user_recyclebin
  2  where CREATETIME = (select min(CREATETIME) from user_recyclebin);

ORIGINAL_NAME                    TYPE                           SPACE
-------------------------------- ------------------------- ----------
TABLE********                    TABLE                              0
INDEX************                INDEX                              0
PK_*************                 INDEX                              0

SQL> flashback table TABLE******** to before drop rename to TABLE********_RCV;

フラッシュバックが完了しました。

SQL> desc TABLE********_RCV
 名前                                        NULL?    型
 ----------------------------------------- -------- ------------------
 COL1                                      NOT NULL NUMBER(22)
 COL2                                               NUMBER(22)
 COL3                                               NUMBER(22)
 COL4                                               DATE
 COL5                                               DATE
 COL6                                               NUMBER(22)
 COL7                                               DATE

SQL> select * from TABLE********_RCV;

レコードが選択されませんでした。

よくよく確認したところ、メタデータが5万件以上もリサイクルビンに溜まっていた。

どうやら夜間バッチ処理の中でDROPを沢山発行していたようだった。(9iからのスクリプトでPURGEオプションなしでDROPを実行していた。)

これをPURGE RECYCLEBINコマンドで一気に削除したのだが、わずか数秒で終わってしまった。

今では見ることができない不思議な現象だった。

終わり