Flat DBA: 3月 2011

先日のことですが実行計画が開発環境と本番環境で異なることが判明し、
どうしても開発環境のものと合わせるということになりました。

本番環境：


--------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Rows
| Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |     1
|    65 |     6  (67)| 00:00:01 |
|   1 |  HASH GROUP BY                    |                    |     1
|    65 |     6  (67)| 00:00:01 |
|*  2 |   HASH JOIN OUTER                 |                    |     1
|    65 |     5  (60)| 00:00:01 |
|   3 |    VIEW                           |                    |     1
|    39 |     2  (50)| 00:00:01 |
|   4 |     HASH GROUP BY                 |                    |     1
|   434 |     2  (50)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                    |     1
|   434 |     1   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                    |     1
|   414 |     0   (0)| 00:00:01 |
|   7 |        NESTED LOOPS               |                    |     1
|   388 |     0   (0)| 00:00:01 |
|*  8 |         FIXED TABLE FULL          | X$KCCTF            |     1
|    91 |     0   (0)| 00:00:01 |
|*  9 |         FIXED TABLE FIXED INDEX   | X$KCCFN (ind:1)    |     1
|   297 |     0   (0)| 00:00:01 |
|* 10 |        FIXED TABLE FIXED INDEX    | X$KCVFHTMP (ind:1) |     1
|    26 |     0   (0)| 00:00:01 |
|  11 |       TABLE ACCESS CLUSTER        | TS$                |     1
|    20 |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN          | I_TS#              |     1
|       |     0   (0)| 00:00:01 |
|  13 |    VIEW                           |                    |     1
|    26 |     2  (50)| 00:00:01 |
|  14 |     HASH GROUP BY                 |                    |     1
|   105 |     2  (50)| 00:00:01 |
|  15 |      NESTED LOOPS                 |                    |     1
|   105 |     1   (0)| 00:00:01 |
|  16 |       NESTED LOOPS                |                    |     1
|    66 |     1   (0)| 00:00:01 |
|* 17 |        FIXED TABLE FULL           | X$KCCTS            |     1
|    43 |     0   (0)| 00:00:01 |
|* 18 |        TABLE ACCESS BY INDEX ROWID| TS$                |     1
|    23 |     1   (0)| 00:00:01 |
|* 19 |         INDEX UNIQUE SCAN         | I_TS1              |     1
|       |     0   (0)| 00:00:01 |
|* 20 |       FIXED TABLE FIXED INDEX     | X$KTSTFC (ind:1)   |     1
|    39 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

開発環境：

<br />--------------------------------------------------------------------------------------------------------<br />| Id  | Operation                         | Name               | Rows<br />| Bytes | Cost (%CPU)| Time     |<br />--------------------------------------------------------------------------------------------------------<br />|   0 | SELECT STATEMENT                  |                    |     1<br />|    65 |     6  (67)| 00:00:01 |<br />|   1 |  HASH GROUP BY                    |                    |     1<br />|    65 |     6  (67)| 00:00:01 |<br />|*  2 |   HASH JOIN OUTER                 |                    |     1<br />|    65 |     5  (60)| 00:00:01 |<br />|   3 |    VIEW                           |                    |     1<br />|    39 |     2  (50)| 00:00:01 |<br />|   4 |     SORT GROUP BY                 |                    |     1<br />|   434 |     2  (50)| 00:00:01 |<br />|   5 |      NESTED LOOPS                 |                    |     1<br />|   434 |     1   (0)| 00:00:01 |<br />|   6 |       NESTED LOOPS                |                    |     1<br />|   414 |     0   (0)| 00:00:01 |<br />|   7 |        NESTED LOOPS               |                    |     1<br />|   388 |     0   (0)| 00:00:01 |<br />|*  8 |         FIXED TABLE FULL          | X$KCCTF            |     1<br />|    91 |     0   (0)| 00:00:01 |<br />|*  9 |         FIXED TABLE FIXED INDEX   | X$KCCFN (ind:1)    |     1<br />|   297 |     0   (0)| 00:00:01 |<br />|* 10 |        FIXED TABLE FIXED INDEX    | X$KCVFHTMP (ind:1) |     1<br />|    26 |     0   (0)| 00:00:01 |<br />|  11 |       TABLE ACCESS CLUSTER        | TS$                |     1<br />|    20 |     1   (0)| 00:00:01 |<br />|* 12 |        INDEX UNIQUE SCAN          | I_TS#              |     1<br />|       |     0   (0)| 00:00:01 |<br />|  13 |    VIEW                           |                    |     1<br />|    26 |     2  (50)| 00:00:01 |<br />|  14 |     SORT GROUP BY                 |                    |     1<br />|   105 |     2  (50)| 00:00:01 |<br />|  15 |      NESTED LOOPS                 |                    |     1<br />|   105 |     1   (0)| 00:00:01 |<br />|  16 |       NESTED LOOPS                |                    |     1<br />|    66 |     1   (0)| 00:00:01 |<br />|* 17 |        FIXED TABLE FULL           | X$KCCTS            |     1<br />|    43 |     0   (0)| 00:00:01 |<br />|* 18 |        TABLE ACCESS BY INDEX ROWID| TS$                |     1<br />|    23 |     1   (0)| 00:00:01 |<br />|* 19 |         INDEX UNIQUE SCAN         | I_TS1              |     1<br />|       |     0   (0)| 00:00:01 |<br />|* 20 |       FIXED TABLE FIXED INDEX     | X$KTSTFC (ind:1)   |     1<br />|    39 |     0   (0)| 00:00:01 |<br />--------------------------------------------------------------------------------------------------------<br />

上記２つの実行計画の差は、ID4,14の部分にあるGROUP BYの方式です。
これをどうにかするためにあれこれ画策しましたが、いい方法がありません。

まぁヒントを追加するしかないと思ってはいたのですが、そんなヒントはあるのか？
と思いながら先輩からアドバイスをもらうと

「v$SQL_HINTを確認せよ」との指示が

で、確認するとこんな感じ(11.2.0.2)

<br />NAME                           CLASS                                                            VERSION<br />------------------------------ ---------------------------------------------------------------- -------------------------<br />APPEND                         APPEND                                                           8.1.0<br />NOAPPEND                       APPEND                                                           8.1.0<br />NO_MONITORING                  NO_MONITORING                                                    8.0.0<br />NO_SQL_TUNE                    NO_SQL_TUNE                                                      10.2.0.1<br />DEREF_NO_REWRITE               DEREF_NO_REWRITE                                                 8.1.0<br />NESTED_TABLE_GET_REFS          NESTED_TABLE_GET_REFS                                            8.1.0<br />PRESERVE_OID                   PRESERVE_OID                                                     10.2.0.1<br />NESTED_TABLE_SET_SETID         NESTED_TABLE_SET_SETID                                           8.1.5<br />NESTED_TABLE_FAST_INSERT       NESTED_TABLE_FAST_INSERT                                         10.1.0.3<br />INLINE_XMLTYPE_NT              INLINE_XMLTYPE_NT                                                10.2.0.1<br />REF_CASCADE_CURSOR             REF_CASCADE_CURSOR                                               9.2.0<br />NO_REF_CASCADE                 REF_CASCADE_CURSOR                                               9.2.0<br />FORCE_XML_QUERY_REWRITE        FORCE_XML_QUERY_REWRITE                                          9.2.0<br />NO_XML_QUERY_REWRITE           FORCE_XML_QUERY_REWRITE                                          9.2.0<br />IGNORE_WHERE_CLAUSE            IGNORE_WHERE_CLAUSE                                              9.2.0<br />OPAQUE_TRANSFORM               OPAQUE_TRANSFORM                                                 10.1.0.3<br />OPAQUE_XCANONICAL              OPAQUE_XCANONICAL                                                10.1.0.3<br />SYS_DL_CURSOR                  SYS_DL_CURSOR                                                    9.2.0<br />SQLLDR                         SQLLDR                                                           9.0.0<br />DML_UPDATE                     DML_UPDATE                                                       9.0.0<br />VECTOR_READ                    VECTOR_READ                                                      10.1.0.3<br />VECTOR_READ_TRACE              VECTOR_READ_TRACE                                                10.1.0.3<br />EXPR_CORR_CHECK                EXPR_CORR_CHECK                                                  8.0.0<br />STREAMS                        STREAMS                                                          10.1.0.3<br />MERGE_CONST_ON                 MERGE_CONST_ON                                                   8.0.0<br />USE_WEAK_NAME_RESL             USE_WEAK_NAME_RESL                                               10.1.0.3<br />NO_QUERY_TRANSFORMATION        NO_QUERY_TRANSFORMATION                                          10.1.0.3<br />OPTIMIZER_FEATURES_ENABLE      OPTIMIZER_FEATURES_ENABLE                                        10.1.0.3<br />DB_VERSION                     DB_VERSION                                                       11.1.0.6<br />FBTSCAN                        FBTSCAN                                                          10.1.0.3<br />GATHER_PLAN_STATISTICS         GATHER_PLAN_STATISTICS                                           10.1.0.3<br />IGNORE_OPTIM_EMBEDDED_HINTS    IGNORE_OPTIM_EMBEDDED_HINTS                                      10.1.0.3<br />TABLE_STATS                    TABLE_STATS                                                      10.1.0.3<br />INDEX_STATS                    TABLE_STATS                                                      10.1.0.3<br />COLUMN_STATS                   TABLE_STATS                                                      10.1.0.3<br />RULE                           MODE                                                             8.1.0<br />CHOOSE                         MODE                                                             8.1.0<br />FIRST_ROWS                     MODE                                                             8.1.0<br />ALL_ROWS                       MODE                                                             8.1.0<br />ORDERED                        ORDERED                                                          8.1.0<br />ORDERED_PREDICATES             ORDERED_PREDICATES                                               8.0.0<br />DOMAIN_INDEX_SORT              DOMAIN_INDEX_SORT                                                8.1.5<br />DOMAIN_INDEX_NO_SORT           DOMAIN_INDEX_SORT                                                8.1.5<br />SKIP_EXT_OPTIMIZER             SKIP_EXT_OPTIMIZER                                               9.0.0<br />CPU_COSTING                    CPU_COSTING                                                      9.0.0<br />NO_CPU_COSTING                 CPU_COSTING                                                      9.0.0<br />OLD_PUSH_PRED                  OLD_PUSH_PRED                                                    10.2.0.1<br />PUSH_PRED                      PUSH_PRED                                                        8.1.0<br />NO_PUSH_PRED                   PUSH_PRED                                                        8.1.0<br />PULL_PRED                      PULL_PRED                                                        10.2.0.1<br />NO_PULL_PRED                   PULL_PRED                                                        10.2.0.1<br />ELIMINATE_OBY                  ELIMINATE_OBY                                                    10.2.0.1<br />NO_ELIMINATE_OBY               ELIMINATE_OBY                                                    10.2.0.1<br />ELIMINATE_JOIN                 ELIMINATE_JOIN                                                   10.2.0.1<br />NO_ELIMINATE_JOIN              ELIMINATE_JOIN                                                   10.2.0.1<br />OUTER_JOIN_TO_INNER            OUTER_JOIN_TO_INNER                                              11.1.0.6<br />NO_OUTER_JOIN_TO_INNER         OUTER_JOIN_TO_INNER                                              11.1.0.6<br />PUSH_SUBQ                      PUSH_SUBQ                                                        8.1.0<br />NO_PUSH_SUBQ                   PUSH_SUBQ                                                        9.2.0<br />GBY_PUSHDOWN                   GBY_PUSHDOWN                                                     10.2.0.5<br />NO_GBY_PUSHDOWN                GBY_PUSHDOWN                                                     10.2.0.5<br />USE_CONCAT                     USE_CONCAT                                                       8.1.0<br />NO_EXPAND                      USE_CONCAT                                                       8.1.0<br />SEMIJOIN_DRIVER                SEMIJOIN_DRIVER                                                  8.1.0<br />ANTIJOIN                       ANTIJOIN                                                         9.0.0<br />MERGE_AJ                       ANTIJOIN                                                         8.1.0<br />HASH_AJ                        ANTIJOIN                                                         8.1.0<br />NL_AJ                          ANTIJOIN                                                         8.0.0<br />SEMIJOIN                       SEMIJOIN                                                         9.0.0<br />NO_SEMIJOIN                    SEMIJOIN                                                         9.0.0<br />MERGE_SJ                       SEMIJOIN                                                         8.1.0<br />HASH_SJ                        SEMIJOIN                                                         8.1.0<br />NL_SJ                          SEMIJOIN                                                         8.0.0<br />STAR                           STAR                                                             8.1.0<br />REMOTE_MAPPED                  REMOTE_MAPPED                                                    8.1.0<br />STAR_TRANSFORMATION            STAR_TRANSFORMATION                                              8.1.0<br />NO_STAR_TRANSFORMATION         STAR_TRANSFORMATION                                              10.1.0.3<br />REWRITE                        REWRITE                                                          8.1.5<br />NO_REWRITE                     REWRITE                                                          8.1.5<br />NO_MULTIMV_REWRITE             REWRITE                                                          10.1.0.3<br />NO_BASETABLE_MULTIMV_REWRITE   REWRITE                                                          10.1.0.3<br />REWRITE_OR_ERROR               REWRITE                                                          10.1.0.3<br />BYPASS_UJVC                    BYPASS_UJVC                                                      8.1.5<br />UNNEST                         UNNEST                                                           8.1.6<br />NO_UNNEST                      UNNEST                                                           8.1.6<br />LOCAL_INDEXES                  LOCAL_INDEXES                                                    9.0.0<br />HWM_BROKERED                   HWM_BROKERED                                                     9.0.0<br />OVERFLOW_NOMOVE                OVERFLOW_NOMOVE                                                  9.0.0<br />SYS_PARALLEL_TXN               SYS_PARALLEL_TXN                                                 8.1.6<br />SYS_RID_ORDER                  SYS_RID_ORDER                                                    9.2.0<br />BYPASS_RECURSIVE_CHECK         BYPASS_RECURSIVE_CHECK                                           9.0.0<br />MATERIALIZE                    INLINE                                                           9.0.0<br />INLINE                         INLINE                                                           9.0.0<br />CONNECT_BY_COST_BASED          CONNECT_BY_COST_BASED                                            10.2.0.2<br />NO_CONNECT_BY_COST_BASED       CONNECT_BY_COST_BASED                                            10.2.0.2<br />CONNECT_BY_FILTERING           CONNECT_BY_FILTERING                                             10.2.0.2<br />NO_CONNECT_BY_FILTERING        CONNECT_BY_FILTERING                                             10.2.0.2<br />SET_TO_JOIN                    SET_TO_JOIN                                                      10.1.0.3<br />NO_SET_TO_JOIN                 SET_TO_JOIN                                                      10.1.0.3<br />PIV_SSF                        PIV_SSF                                                          8.1.0<br />TIV_SSF                        PIV_SSF                                                          8.1.0<br />PIV_GB                         PIV_GB                                                           8.1.0<br />TIV_GB                         PIV_GB                                                           8.1.0<br />CUBE_GB                        CUBE_GB                                                          8.1.5<br />SAVE_AS_INTERVALS              SAVE_AS_INTERVALS                                                8.1.5<br />RESTORE_AS_INTERVALS           RESTORE_AS_INTERVALS                                             8.1.5<br />BITMAP                         BITMAP                                                           8.1.0<br />SCN_ASCENDING                  SCN_ASCENDING                                                    8.1.5<br />NO_QKN_BUFF                    NO_QKN_BUFF                                                      9.2.0<br />BUFFER                         BUFFER                                                           8.1.5<br />NO_BUFFER                      BUFFER                                                           8.1.5<br />CURSOR_SHARING_EXACT           CURSOR_SHARING_EXACT                                             9.0.0<br />MV_MERGE                       MV_MERGE                                                         9.0.0<br />EXPAND_GSET_TO_UNION           EXPAND_GSET_TO_UNION                                             9.2.0<br />NO_EXPAND_GSET_TO_UNION        EXPAND_GSET_TO_UNION                                             9.2.0<br />MODEL_MIN_ANALYSIS             MODEL_MIN_ANALYSIS                                               10.1.0.3<br />MODEL_NO_ANALYSIS              MODEL_MIN_ANALYSIS                                               10.1.0.3<br />MODEL_DYNAMIC_SUBQUERY         MODEL_DYNAMIC_SUBQUERY                                           10.2.0.1<br />MODEL_COMPILE_SUBQUERY         MODEL_COMPILE_SUBQUERY                                           10.2.0.1<br />MODEL_PUSH_REF                 MODEL_PUSH_REF                                                   10.1.0.3<br />NO_MODEL_PUSH_REF              MODEL_PUSH_REF                                                   10.1.0.3<br />MODEL_DONTVERIFY_UNIQUENESS    MODEL_DONTVERIFY_UNIQUENESS                                      10.1.0.3<br />NO_PRUNE_GSETS                 NO_PRUNE_GSETS                                                   9.0.0<br />USE_TTT_FOR_GSETS              USE_TTT_FOR_GSETS                                                9.0.0<br />USE_HASH_AGGREGATION           USE_HASH_AGGREGATION                                             10.2.0.1<br />NO_USE_HASH_AGGREGATION        USE_HASH_AGGREGATION                                             10.2.0.1<br />GBY_CONC_ROLLUP                GBY_CONC_ROLLUP                                                  9.0.0<br />NO_STATS_GSETS                 NO_STATS_GSETS                                                   8.0.0<br />NO_ORDER_ROLLUPS               NO_ORDER_ROLLUPS                                                 8.0.0<br />QB_NAME                        QB_NAME                                                          10.1.0.3<br />DYNAMIC_SAMPLING               DYNAMIC_SAMPLING                                                 9.2.0<br />CARDINALITY                    CARDINALITY                                                      9.0.0<br />LEADING                        LEADING                                                          8.1.6<br />MERGE                          MERGE                                                            8.1.0<br />NO_MERGE                       MERGE                                                            8.0.0<br />FULL                           ACCESS                                                           8.1.0<br />SHARED                         SHARED                                                           8.1.0<br />NOPARALLEL                     SHARED                                                           8.1.0<br />NO_PARALLEL                    SHARED                                                           10.1.0.3<br />CACHE_CB                       CACHE_CB                                                         8.1.5<br />CACHE                          CACHE                                                            8.1.0<br />NOCACHE                        CACHE                                                            8.1.0<br />CACHE_TEMP_TABLE               CACHE                                                            8.1.5<br />DRIVING_SITE                   DRIVING_SITE                                                     8.1.0<br />QUEUE_CURR                     ACCESS                                                           8.0.0<br />QUEUE_ROWP                     ACCESS                                                           8.0.0<br />USE_MERGE_CARTESIAN            JOIN                                                             11.1.0.6<br />USE_MERGE                      JOIN                                                             8.1.0<br />NO_USE_MERGE                   NO_USE_MERGE                                                     10.1.0.3<br />USE_HASH                       JOIN                                                             8.1.0<br />NO_USE_HASH                    NO_USE_HASH                                                      10.1.0.3<br />USE_NL                         JOIN                                                             8.1.0<br />USE_NL_WITH_INDEX              USE_NL_WITH_INDEX                                                10.1.0.3<br />NO_USE_NL                      NO_USE_NL                                                        10.1.0.3<br />USE_ANTI                       USE_ANTI                                                         8.1.0<br />USE_SEMI                       USE_SEMI                                                         8.1.0<br />PQ_DISTRIBUTE                  PQ_DISTRIBUTE                                                    8.1.5<br />SUBQUERY_PRUNING               SUBQUERY_PRUNING                                                 11.1.0.6<br />NO_SUBQUERY_PRUNING            SUBQUERY_PRUNING                                                 11.1.0.6<br />LIKE_EXPAND                    LIKE_EXPAND                                                      8.1.7<br />OR_EXPAND                      OR_EXPAND                                                        8.1.7<br />PQ_MAP                         PQ_MAP                                                           9.0.0<br />PQ_NOMAP                       PQ_MAP                                                           9.0.0<br />AND_EQUAL                      ACCESS                                                           8.1.0<br />INDEX_ASC                      ACCESS                                                           8.1.0<br />INDEX                          ACCESS                                                           8.0.0<br />INDEX_DESC                     ACCESS                                                           8.1.0<br />NO_INDEX                       NO_INDEX                                                         8.1.5<br />INDEX_FFS                      ACCESS                                                           8.1.0<br />NO_INDEX_FFS                   NO_INDEX_FFS                                                     10.1.0.3<br />INDEX_SS                       ACCESS                                                           9.0.0<br />INDEX_SS_ASC                   ACCESS                                                           9.0.0<br />INDEX_SS_DESC                  ACCESS                                                           9.0.0<br />NO_INDEX_SS                    NO_INDEX_SS                                                      10.1.0.3<br />INDEX_COMBINE                  ACCESS                                                           8.1.0<br />BITMAP_TREE                    ACCESS                                                           10.2.0.1<br />INDEX_JOIN                     ACCESS                                                           8.1.5<br />INDEX_RRS                      ACCESS                                                           9.0.0<br />HASH                           ACCESS                                                           8.1.0<br />PARALLEL_INDEX                 PARALLEL_INDEX                                                   8.1.0<br />NO_PARALLEL_INDEX              PARALLEL_INDEX                                                   8.1.0<br />SWAP_JOIN_INPUTS               SWAP_JOIN_INPUTS                                                 8.1.0<br />NO_SWAP_JOIN_INPUTS            SWAP_JOIN_INPUTS                                                 10.1.0.3<br />FACT                           FACT                                                             8.1.0<br />NO_FACT                        FACT                                                             8.1.0<br />NO_ACCESS                      NO_ACCESS                                                        8.1.5<br />DYNAMIC_SAMPLING_EST_CDN       DYNAMIC_SAMPLING_EST_CDN                                         9.2.0<br />CLUSTER                        ACCESS                                                           8.0.0<br />ROWID                          ACCESS                                                           8.0.0<br />OPT_ESTIMATE                   OPT_ESTIMATE                                                     10.1.0.3<br />X_DYN_PRUNE                    X_DYN_PRUNE                                                      10.1.0.3<br />RESTRICT_ALL_REF_CONS          RESTRICT_ALL_REF_CONS                                            10.1.0.3<br />SKIP_UNQ_UNUSABLE_IDX          SKIP_UNQ_UNUSABLE_IDX                                            10.1.0.3<br />TRACING                        TRACING                                                          10.1.0.3<br />NO_PARTIAL_COMMIT              NO_PARTIAL_COMMIT                                                10.1.0.3<br />INCLUDE_VERSION                INCLUDE_VERSION                                                  10.1.0.3<br />NO_CARTESIAN                   NO_CARTESIAN                                                     10.2.0.1<br />PX_JOIN_FILTER                 PX_JOIN_FILTER                                                   10.2.0.1<br />NO_PX_JOIN_FILTER              PX_JOIN_FILTER                                                   10.2.0.1<br />PRECOMPUTE_SUBQUERY            PRECOMPUTE_SUBQUERY                                              10.2.0.1<br />RBO_OUTLINE                    RBO_OUTLINE                                                      10.2.0.1<br />OUTLINE                        OUTLINE                                                          10.2.0.1<br />OUTLINE_LEAF                   OUTLINE_LEAF                                                     10.2.0.1<br />OPT_PARAM                      OPT_PARAM                                                        10.2.0.1<br />DBMS_STATS                     DBMS_STATS                                                       10.2.0.1<br />DOMAIN_INDEX_FILTER            DOMAIN_INDEX_FILTER                                              11.1.0.6<br />NO_DOMAIN_INDEX_FILTER         NO_DOMAIN_INDEX_FILTER                                           11.1.0.6<br />RESULT_CACHE                   RESULT_CACHE                                                     11.1.0.6<br />NO_RESULT_CACHE                RESULT_CACHE                                                     11.1.0.6<br />NO_XML_DML_REWRITE             NO_XML_DML_REWRITE                                               10.2.0.1<br />XML_DML_RWT_STMT               XML_DML_RWT_STMT                                                 11.1.0.6<br />PLACE_GROUP_BY                 PLACE_GROUP_BY                                                   11.1.0.6<br />NO_PLACE_GROUP_BY              PLACE_GROUP_BY                                                   11.1.0.6<br />INDEX_RS_ASC                   ACCESS                                                           11.1.0.6<br />INDEX_RS_DESC                  ACCESS                                                           11.1.0.6<br />NO_LOAD                        NO_LOAD                                                          11.1.0.6<br />NUM_INDEX_KEYS                 ACCESS                                                           10.2.0.3<br />CHECK_ACL_REWRITE              CHECK_ACL_REWRITE                                                11.1.0.6<br />NO_CHECK_ACL_REWRITE           NO_CHECK_ACL_REWRITE                                             11.1.0.6<br />CONNECT_BY_COMBINE_SW          CONNECT_BY_COMBINE_SW                                            10.2.0.4<br />NO_CONNECT_BY_COMBINE_SW       CONNECT_BY_COMBINE_SW                                            10.2.0.4<br />XMLINDEX_REWRITE               XMLINDEX_REWRITE                                                 11.1.0.6<br />NO_XMLINDEX_REWRITE            XMLINDEX_REWRITE                                                 11.1.0.6<br />XMLINDEX_REWRITE_IN_SELECT     XMLINDEX_REWRITE                                                 11.1.0.6<br />NO_XMLINDEX_REWRITE_IN_SELECT  XMLINDEX_REWRITE                                                 11.1.0.6<br />NATIVE_FULL_OUTER_JOIN         NATIVE_FULL_OUTER_JOIN                                           10.2.0.3<br />NO_NATIVE_FULL_OUTER_JOIN      NATIVE_FULL_OUTER_JOIN                                           10.2.0.3<br />COST_XML_QUERY_REWRITE         COST_XML_QUERY_REWRITE                                           11.1.0.6<br />NO_COST_XML_QUERY_REWRITE      NO_COST_XML_QUERY_REWRITE                                        11.1.0.6<br />NLJ_PREFETCH                   ACCESS                                                           11.1.0.6<br />NO_NLJ_PREFETCH                ACCESS                                                           11.1.0.6<br />NLJ_BATCHING                   ACCESS                                                           11.1.0.6<br />NO_NLJ_BATCHING                ACCESS                                                           11.1.0.6<br />MONITOR                        MONITOR                                                          11.1.0.6<br />NO_MONITOR                     MONITOR                                                          11.1.0.6<br />USE_INVISIBLE_INDEXES          USE_INVISIBLE_INDEXES                                            11.1.0.6<br />NO_USE_INVISIBLE_INDEXES       USE_INVISIBLE_INDEXES                                            11.1.0.6<br />BIND_AWARE                     BIND_AWARE                                                       11.1.0.7<br />NO_BIND_AWARE                  BIND_AWARE                                                       11.1.0.7<br />IGNORE_ROW_ON_DUPKEY_INDEX     IGNORE_ROW_ON_DUPKEY_INDEX                                       11.1.0.7<br />CHANGE_DUPKEY_ERROR_INDEX      CHANGE_DUPKEY_ERROR_INDEX                                        11.1.0.7<br />RETRY_ON_ROW_CHANGE            RETRY_ON_ROW_CHANGE                                              11.1.0.7<br />COALESCE_SQ                    COALESCE_SQ                                                      11.2.0.1<br />NO_COALESCE_SQ                 COALESCE_SQ                                                      11.2.0.1<br />TRANSFORM_DISTINCT_AGG         TRANSFORM_DISTINCT_AGG                                           11.2.0.1<br />NO_TRANSFORM_DISTINCT_AGG      TRANSFORM_DISTINCT_AGG                                           11.2.0.1<br />CONNECT_BY_ELIM_DUPS           CONNECT_BY_ELIM_DUPS                                             11.2.0.1<br />NO_CONNECT_BY_ELIM_DUPS        CONNECT_BY_ELIM_DUPS                                             11.2.0.1<br />DST_UPGRADE_INSERT_CONV        DST_UPGRADE_INSERT_CONV                                          11.2.0.1<br />NO_DST_UPGRADE_INSERT_CONV     DST_UPGRADE_INSERT_CONV                                          11.2.0.1<br />STATEMENT_QUEUING              STATEMENT_QUEUING                                                11.2.0.1<br />NO_STATEMENT_QUEUING           STATEMENT_QUEUING                                                11.2.0.1<br />CONNECT_BY_CB_WHR_ONLY         CONNECT_BY_CB_WHR_ONLY                                           10.2.0.5<br />NO_CONNECT_BY_CB_WHR_ONLY      CONNECT_BY_CB_WHR_ONLY                                           10.2.0.5<br />EXPAND_TABLE                   EXPAND_TABLE                                                     11.2.0.1<br />NO_EXPAND_TABLE                EXPAND_TABLE                                                     11.2.0.1<br />XMLINDEX_SEL_IDX_TBL           XMLINDEX_SEL_IDX_TBL                                             11.2.0.1<br />FACTORIZE_JOIN                 FACTORIZE_JOIN                                                   11.2.0.1<br />NO_FACTORIZE_JOIN              FACTORIZE_JOIN                                                   11.2.0.1<br />APPEND_VALUES                  APPEND_VALUES                                                    11.2.0.1<br />PLACE_DISTINCT                 PLACE_DISTINCT                                                   11.2.0.1<br />NO_PLACE_DISTINCT              PLACE_DISTINCT                                                   11.2.0.1<br />NO_SUBSTRB_PAD                 NO_SUBSTRB_PAD                                                   11.2.0.1<br />USE_HASH_GBY_FOR_PUSHDOWN      USE_HASH_GBY_FOR_PUSHDOWN                                        11.2.0.2<br />NO_USE_HASH_GBY_FOR_PUSHDOWN   USE_HASH_GBY_FOR_PUSHDOWN                                        11.2.0.2<br />XDB_FASTPATH_INSERT            XDB_FASTPATH_INSERT                                              11.2.0.2<br />NO_XDB_FASTPATH_INSERT         XDB_FASTPATH_INSERT                                              11.2.0.2<br />TABLE_LOOKUP_BY_NL             TABLE_LOOKUP_BY_NL                                               11.2.0.2<br />NO_TABLE_LOOKUP_BY_NL          TABLE_LOOKUP_BY_NL                                               11.2.0.2<br />

ほほう。

NO_USE_HASH_AGGREGATIONがあった。

autoextend について確認してみた。

このパラメータは表領域の作成の際に指定するパラメータで、いわゆる自動拡張を採用するかどうかを決定する属性となっている。
自分が気になったのは、nextのサイズを変更するとどれくらいパフォーマンスに影響がでるのか確認をしたかった。
というか、そもそもは"data file init write"待機イベントがどう変化するかをみてみたい!!

今回実行するinsertスクリプトを実行すると700M程度のセグメントサイズとなる。

環境 oracle 11gR2(11.2.0.1) on Linux

以下のパラメータで表領域を作成する

size 10M autoextend on next 1M
size 10M autoextend on next 100M
size 10M autoextend on next 1G
size 1G

以下の表を作成

SQL> create table hoge (c1 number,c2 char(2000),c3 char(2000),c4 char(2000)) tablespace hoge;

以下のinsertスクリプトを実行

<br />declare<br />i number := 0;<br />j number := 0;<br />begin<br />        for i in 1..100000 loop<br />               <br />                insert /* append */ into hoge values (i,'a','b','c');<br />        end loop;<br />--      commit;<br />end;<br />/<br />

結果

SQL> @insert

PL/SQLプロシージャが正常に完了しました。

経過: 00:01:52.30
SQL> @insert

PL/SQLプロシージャが正常に完了しました。

経過: 00:03:16.03
SQL> @insert

PL/SQLプロシージャが正常に完了しました。

経過: 00:03:39.99
SQL> @insert

PL/SQLプロシージャが正常に完了しました。

経過: 00:04:07.14

上記の実行時間だけみると実行都度に拡張させたほうが良いようにみえますが、
今回のdata file init write待機をかなり削減できるていることは確認ができました。
※ashviewerでお見せしようと思いましたが断念

つまり、その他の待機が発生しているということです。
たとえば、４のパターンではbuffer busy waitsが発生するようになっていました。

と、思ったら途中でrecovery_file_destがいっぱいになってしまった。。
以下は番外編。

<br />[ora11201d@server132 archivelog]$ du -sh *<br />16K     2011_02_25<br />8.0K    2011_02_26<br />8.0K    2011_02_27<br />8.0K    2011_02_28<br />8.0K    2011_03_01<br />1.5G    2011_03_05<br />154M    2011_03_06<br />2.2G    2011_03_07<br />[ora11201d@server132 archivelog]$ rm -rf *<br />

で、お掃除、お掃除

<br />[ora11201d@server132 archivelog]$ rman target /<br /><br />Recovery Manager: Release 11.2.0.1.0 - Production on 月 3月 7 20:24:30 2011<br /><br />Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.<br /><br />ターゲット・データベース: ORA11201 (データベースID=1446091489)に接続されました<br /><br />RMAN> crosscheck archivelog all;<br /><br />リカバリ・カタログのかわりにターゲット・データベース制御ファイルを使用しています<br />チャネル: ORA_DISK_1が割り当てられました<br />チャネルORA_DISK_1: SID=47 デバイス・タイプ=DISK<br />アーカイブ・ログの検証に失敗しました<br />アーカイブ・ログ・ファイル名=/home/ora11201d/app/oracle/flash_recovery_area/ORA11201D/archivelog/2011_03_05/o1_mf_1_678153_6q3kzjw0_.arc レコードID=568 スタンプ=744992389<br />アーカイブ・ログの検証に失敗しました<br /><br />RMAN> delete expired archivelog all;<br /><br />チャネル: ORA_DISK_1がリリースされました<br />チャネル: ORA_DISK_1が割り当てられました<br />チャネルORA_DISK_1: SID=47 デバイス・タイプ=DISK<br />データベースdb_unique_name ORA11201Dのアーカイブ・ログ・コピーのリスト<br /><br />アーカイブ・ログを削除しました<br />アーカイブ・ログ・ファイル名=/home/ora11201d/app/oracle/flash_recovery_area/ORA11201D/archivelog/2011_03_07/o1_mf_1_678240_6q9ho1mp_.arc レコードID=654 スタンプ=745186611<br />88EXPIREDオブジェクトを削除しました<br />

Flat DBA

2011年3月11日金曜日

V$SQL_HINT

2011年3月7日月曜日

autoextendについてちょっと検証

2011年3月3日木曜日

Greenplum無償公開

バインド変数を使いたくないのだ

大阪にて

Labels

ブログアーカイブ

フォロワー

このブログを検索