Oracle mechanics

14.12.2016

12c: наблюдаемые ограничения хранения в AWR и управления адаптивными планами

Filed under: Oracle,Plan Management — Игорь Усольцев @ 10:22
Tags: ,

Для демонстрации/проверки многообразия планов у меня есть простой скрипт SQL_PLAN_HASHS.SQL, вытаскивающий хеши планов из AWR и/или SqlArea по SQL_ID [и, опционально, по FULL_PLAN_HASH_VALUE и/или PLAN_HASH_VALUE], и один планов запросов SQL_ID=fkw6hb5mrw02t и PHV=3887377781 а течение последних нескольких дней показывал следующую примечательную картину:

12.1.0.2.@ SQL> @sql_plan_hashs fkw6hb5mrw02t "" 3887377781
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

— в SqlArea (SRC=SQL) и в DBA_HIST_SQL_PLAN (SRC=AWR) этот запрос сосуществует с одним и тем же PLAN_HASH_VALUE, но с разными FULL_PLAN_HASH_VALUE

При этом, если для «старого» FULL_PLAN_HASH_VALUE=2002632649 был зафиксирован лишь один PLAN_HASH_VALUE:

SQL> @sql_plan_hashs fkw6hb5mrw02t 2002632649
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           2002632649      3887377781 31.10.2015 23:32:38

, то для относительно «свежего» FULL_PLAN_HASH_VALUE=1266372674 наблюдается два отличных PHV:

SQL> @sql_plan_hashs fkw6hb5mrw02t 1266372674
 
SRC SQL_ID        FULL_PLAN_HASH_VALUE PLAN_HASH_VALUE TIMESTAMP
--- ------------- -------------------- --------------- --------------------
AWR fkw6hb5mrw02t           1266372674       864598493 04.10.2016 12:37:20
SQL fkw6hb5mrw02t           1266372674      3887377781 12.12.2016 17:47:16

— как и положено для адаптивного плана
В этом ничего удивительного нет, и я уже описывал подобную ситуацию сосуществования адаптивного и НЕадаптивного планов с совпадающими PHV, однако, в рассматриваемом случае проявляется пара новых проблем:

1) план с FULL_PLAN_HASH_VALUE=1266372674 и PHV=3887377781 наблюдается только в SqlArea Shared Pool, и, повторюсь, в течение достаточно длительного времени не сохраняется в AWR (не может быть сохранен в AWR в принципе?)

2) собственно сосуществование (и возможность управления) 2-мя различающимися адаптивными планами одного простого (без использования обзоров) запроса с совпадающими PHV и различными FULL_PLAN_HASH_VALUE

1) Проблема сохранения планов в DBA_HIST_SQL_PLAN/WRH$_SQL_PLAN (AWR)

Поскольку у каждого плана выполнения имеется строка с ID = 0:

SQL> select sql_id, plan_hash_value, min(id)
  2    from dba_hist_sql_plan
  3   group by sql_id, plan_hash_value
  4  having min(id) <> 0
  5  union all
  6  select sql_id, plan_hash_value, min(id)
  7    from gv$sql_plan
  8   group by sql_id, plan_hash_value
  9  having min(id) <> 0
 10  /

no rows selected

и в историческом хранилище DBA_HIST_SQL_PLAN пара (SQL_ID, PLAN_HASH_VALUE) фактически/функционально играет роль PK базовой таблицы WRH$_SQL_PLAN:

SQL> select sql_id, plan_hash_value, count(*)
  2    from dba_hist_sql_plan
  3   where id = 0
  4   group by sql_id, plan_hash_value
  5  having count(*) > 1
  6  /

no rows selected

— получается что Oracle 12c благодаря технологии Adaptive Plan уже умеет успешно генерировать отличающиеся в адаптивной части планы с совпадающими PHV (и с разными FULL_PLAN_HASH_VALUE), но не может сохранить это разнообразие в истории

И коственно связанная с этим:

2) проблема сосуществования и управления различающимися только по FULL_PLAN_HASH_VALUE несовпадающими адаптивными планами

Т.е. собственно план с PHV=3887377781, по которому выполнялся запрос, в обоих случах выглядит одинаково:

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr(   'fkw6hb5mrw02t',3887377781,format => '-predicate -note'));
SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('fkw6hb5mrw02t'           ,format => '-predicate -note'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
SQL_ID  fkw6hb5mrw02t, child number 0
-------------------------------------

Plan hash value: 3887377781

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |       |       |   621 (100)|          |       |       |
|   1 |  SORT AGGREGATE                                    |     1 |       |            |          |       |       |
|   2 |   VIEW                                             |    67 |       |   621   (1)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                                     |    67 |  7339 |   621   (1)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                      |       |       |            |          |       |       |
|   5 |      NESTED LOOPS                                  |     7 |   679 |    35   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                                 |     7 |   679 |    35   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                                |     7 |   616 |    28   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS OUTER                         |     7 |   448 |    21   (0)| 00:00:01 |       |       |
|   9 |          PARTITION LIST ALL                        |     7 |   315 |    14   (0)| 00:00:01 |     1 |     2 |
|  10 |           TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|     7 |   315 |    14   (0)| 00:00:01 |     1 |     2 |
|* 11 |            INDEX RANGE SCAN                        |     9 |       |     7   (0)| 00:00:01 |     1 |     2 |
|  12 |          TABLE ACCESS BY INDEX ROWID               |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|* 13 |           INDEX UNIQUE SCAN                        |     1 |       |     0   (0)|          |       |       |
|  14 |         TABLE ACCESS BY INDEX ROWID                |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|* 15 |          INDEX UNIQUE SCAN                         |     1 |       |     0   (0)|          |       |       |
|* 16 |        INDEX RANGE SCAN                            |     1 |       |     0   (0)|          |       |       |
|  17 |       MAT_VIEW ACCESS BY INDEX ROWID               |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|  18 |      NESTED LOOPS                                  |    60 |  6660 |   584   (0)| 00:00:01 |       |       |
|  19 |       NESTED LOOPS                                 |    60 |  6660 |   584   (0)| 00:00:01 |       |       |
|  20 |        NESTED LOOPS OUTER                          |    60 |  5880 |   524   (0)| 00:00:01 |       |       |
|* 21 |         HASH JOIN                                  |    60 |  4740 |   465   (0)| 00:00:01 |       |       |
|  22 |          NESTED LOOPS                              |    60 |  4200 |   437   (0)| 00:00:01 |       |       |
|  23 |           NESTED LOOPS                             |   279 |  4200 |   437   (0)| 00:00:01 |       |       |
|  24 |            TABLE ACCESS BY INDEX ROWID BATCHED     |   279 | 12834 |   158   (0)| 00:00:01 |       |       |
|* 25 |             INDEX RANGE SCAN                       |   180 |       |     5   (0)| 00:00:01 |       |       |
|* 26 |            INDEX UNIQUE SCAN                       |     1 |       |     0   (0)|          |       |       |
|  27 |           TABLE ACCESS BY INDEX ROWID              |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  28 |          MAT_VIEW ACCESS FULL                      |   626 |  5634 |    28   (0)| 00:00:01 |       |       |
|  29 |         TABLE ACCESS BY INDEX ROWID                |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|* 30 |          INDEX UNIQUE SCAN                         |     1 |       |     0   (0)|          |       |       |
|* 31 |        INDEX UNIQUE SCAN                           |     1 |       |     0   (0)|          |       |       |
|  32 |       TABLE ACCESS BY GLOBAL INDEX ROWID           |     1 |    13 |     1   (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------

, отличия заметны в полном формате ADAPTIVE

Так, адаптивное представление плана из SqlArea:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('fkw6hb5mrw02t',format => '+adaptive -predicate +outline'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
SQL_ID  fkw6hb5mrw02t, child number 0
-------------------------------------

Plan hash value: 3887377781

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |       |       |   621 (100)|          |       |       |
|   1 |  SORT AGGREGATE                                      |     1 |       |            |          |       |       |
|   2 |   VIEW                                               |    67 |       |   621   (1)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                                       |    67 |  7339 |   621   (1)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                        |       |       |            |          |       |       |
|-  5 |      HASH JOIN                                       |     7 |   679 |    35   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                                   |     7 |   679 |    35   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                                  |     7 |   679 |    35   (0)| 00:00:01 |       |       |
|-  8 |         STATISTICS COLLECTOR                         |       |       |            |          |       |       |
|   9 |          NESTED LOOPS                                |     7 |   616 |    28   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS OUTER                         |     7 |   448 |    21   (0)| 00:00:01 |       |       |
|  11 |            PARTITION LIST ALL                        |     7 |   315 |    14   (0)| 00:00:01 |     1 |     2 |
|  12 |             TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|     7 |   315 |    14   (0)| 00:00:01 |     1 |     2 |
|  13 |              INDEX RANGE SCAN                        |     9 |       |     7   (0)| 00:00:01 |     1 |     2 |
|  14 |            TABLE ACCESS BY INDEX ROWID               |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|  15 |             INDEX UNIQUE SCAN                        |     1 |       |     0   (0)|          |       |       |
|  16 |           TABLE ACCESS BY INDEX ROWID                |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  17 |            INDEX UNIQUE SCAN                         |     1 |       |     0   (0)|          |       |       |
|  18 |         INDEX RANGE SCAN                             |     1 |       |     0   (0)|          |       |       |
|  19 |        MAT_VIEW ACCESS BY INDEX ROWID                |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|- 20 |       MAT_VIEW ACCESS FULL                           |     1 |     9 |     1   (0)| 00:00:01 |       |       |
|  21 |      NESTED LOOPS                                    |    60 |  6660 |   584   (0)| 00:00:01 |       |       |
|  22 |       NESTED LOOPS                                   |    60 |  6660 |   584   (0)| 00:00:01 |       |       |
|  23 |        NESTED LOOPS OUTER                            |    60 |  5880 |   524   (0)| 00:00:01 |       |       |
|  24 |         HASH JOIN                                    |    60 |  4740 |   465   (0)| 00:00:01 |       |       |
|  25 |          NESTED LOOPS                                |    60 |  4200 |   437   (0)| 00:00:01 |       |       |
|  26 |           NESTED LOOPS                               |   279 |  4200 |   437   (0)| 00:00:01 |       |       |
|  27 |            TABLE ACCESS BY INDEX ROWID BATCHED       |   279 | 12834 |   158   (0)| 00:00:01 |       |       |
|  28 |             INDEX RANGE SCAN                         |   180 |       |     5   (0)| 00:00:01 |       |       |
|  29 |            INDEX UNIQUE SCAN                         |     1 |       |     0   (0)|          |       |       |
|  30 |           TABLE ACCESS BY INDEX ROWID                |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  31 |          MAT_VIEW ACCESS FULL                        |   626 |  5634 |    28   (0)| 00:00:01 |       |       |
|  32 |         TABLE ACCESS BY INDEX ROWID                  |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|  33 |          INDEX UNIQUE SCAN                           |     1 |       |     0   (0)|          |       |       |
|  34 |        INDEX UNIQUE SCAN                             |     1 |       |     0   (0)|          |       |       |
|  35 |       TABLE ACCESS BY GLOBAL INDEX ROWID             |     1 |    13 |     1   (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_px_single_server_enabled' 'false')
      OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
      OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      ...
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)
   - 5 Sql Plan Directives used for this statement

содержит 35 строк, а для плана из AWR:

SQL> SELECT * FROM TABLE(dbms_xplan.display_awr('fkw6hb5mrw02t',3887377781,format => '+adaptive +outline -predicate'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------
SQL_ID fkw6hb5mrw02t
--------------------

Plan hash value: 3887377781

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                     |       |       |   395 (100)|          |       |       |
|   1 |  SORT AGGREGATE                                      |     1 |       |            |          |       |       |
|   2 |   VIEW                                               |   109 |       |   395   (1)| 00:00:01 |       |       |
|   3 |    SORT UNIQUE                                       |   109 | 11660 |   395   (1)| 00:00:01 |       |       |
|   4 |     UNION-ALL                                        |       |       |            |          |       |       |
|-  5 |      HASH JOIN                                       |     8 |   752 |    50   (0)| 00:00:01 |       |       |
|   6 |       NESTED LOOPS                                   |     8 |   752 |    50   (0)| 00:00:01 |       |       |
|   7 |        NESTED LOOPS                                  |     8 |   752 |    50   (0)| 00:00:01 |       |       |
|-  8 |         STATISTICS COLLECTOR                         |       |       |            |          |       |       |
|   9 |          NESTED LOOPS                                |     8 |   680 |    26   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS OUTER                         |     8 |   488 |    18   (0)| 00:00:01 |       |       |
|  11 |            PARTITION LIST ALL                        |     8 |   352 |    10   (0)| 00:00:01 |     1 |     2 |
|  12 |             TABLE ACCESS BY LOCAL INDEX ROWID BATCHED|     8 |   352 |    10   (0)| 00:00:01 |     1 |     2 |
|  13 |              INDEX RANGE SCAN                        |     3 |       |     7   (0)| 00:00:01 |     1 |     2 |
|  14 |            TABLE ACCESS BY INDEX ROWID               |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|  15 |             INDEX UNIQUE SCAN                        |     1 |       |     0   (0)|          |       |       |
|  16 |           TABLE ACCESS BY INDEX ROWID                |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  17 |            INDEX UNIQUE SCAN                         |     1 |       |     0   (0)|          |       |       |
|  18 |         INDEX RANGE SCAN                             |     1 |       |     2   (0)| 00:00:01 |       |       |
|  19 |        MAT_VIEW ACCESS BY INDEX ROWID                |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|- 20 |       MAT_VIEW ACCESS FULL                           |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|  21 |      NESTED LOOPS                                    |   101 | 10908 |   343   (0)| 00:00:01 |       |       |
|  22 |       NESTED LOOPS                                   |   101 | 10908 |   343   (0)| 00:00:01 |       |       |
|- 23 |        HASH JOIN OUTER                               |   100 |  9500 |   243   (0)| 00:00:01 |       |       |
|  24 |         NESTED LOOPS OUTER                           |   100 |  9500 |   243   (0)| 00:00:01 |       |       |
|- 25 |          STATISTICS COLLECTOR                        |       |       |            |          |       |       |
|  26 |           HASH JOIN                                  |   100 |  7800 |   147   (0)| 00:00:01 |       |       |
|  27 |            NESTED LOOPS                              |   100 |  6900 |   119   (0)| 00:00:01 |       |       |
|  28 |             NESTED LOOPS                             |   100 |  6900 |   119   (0)| 00:00:01 |       |       |
|  29 |              TABLE ACCESS BY INDEX ROWID BATCHED     |   100 |  4500 |    19   (0)| 00:00:01 |       |       |
|  30 |               INDEX RANGE SCAN                       |    20 |       |     4   (0)| 00:00:01 |       |       |
|  31 |              INDEX UNIQUE SCAN                       |     1 |       |     0   (0)|          |       |       |
|  32 |             TABLE ACCESS BY INDEX ROWID              |     1 |    24 |     1   (0)| 00:00:01 |       |       |
|  33 |            MAT_VIEW ACCESS FULL                      |   310 |  2790 |    28   (0)| 00:00:01 |       |       |
|  34 |          TABLE ACCESS BY INDEX ROWID                 |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|  35 |           INDEX UNIQUE SCAN                          |     1 |       |     0   (0)|          |       |       |
|- 36 |         TABLE ACCESS FULL                            |     1 |    17 |     1   (0)| 00:00:01 |       |       |
|  37 |        INDEX UNIQUE SCAN                             |     1 |       |     0   (0)|          |       |       |
|  38 |       TABLE ACCESS BY GLOBAL INDEX ROWID             |     1 |    13 |     1   (0)| 00:00:01 | ROWID | ROWID |
----------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      ...
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan (rows marked '-' are inactive)
   - 3 Sql Plan Directives used for this statement

— 38 строк, и отличаются планы тем, что во 2-м случае нижняя часть/блок плана (как и 1-я часть/блок обоих планов) также являлась адаптивной:

|  21 |      NESTED LOOPS                                    |                             |
|  22 |       NESTED LOOPS                                   |                             |
|  23 |        NESTED LOOPS OUTER                            |                             |
|  24 |         HASH JOIN                                    |                             |
|  25 |          NESTED LOOPS                                |                             |
|  26 |           NESTED LOOPS                               |                             |
|  27 |            TABLE ACCESS BY INDEX ROWID BATCHED       | REVERSE                     |
|  28 |             INDEX RANGE SCAN                         | REVERSE_PID_IDX             |
|  29 |            INDEX UNIQUE SCAN                         | INVOICE_PK                  |
|  30 |           TABLE ACCESS BY INDEX ROWID                | INVOICE                     |
|  31 |          MAT_VIEW ACCESS FULL                        | PAYSYS                      |
|  32 |         TABLE ACCESS BY INDEX ROWID                  | OPERATION                   |
|  33 |          INDEX UNIQUE SCAN                           | CS_N_OPERATION_PK           |
|  34 |        INDEX UNIQUE SCAN                             | CONSUME_IDX_PK              |
|  35 |       TABLE ACCESS BY GLOBAL INDEX ROWID             | CONSUME                     |
--------------------------------------------------------------------------------------------

|  21 |      NESTED LOOPS                                    |                             |
|  22 |       NESTED LOOPS                                   |                             |
|- 23 |        HASH JOIN OUTER                               |                             | -- --//--
|  24 |         NESTED LOOPS OUTER                           |                             |
|- 25 |          STATISTICS COLLECTOR                        |                             | -- дополнительная адаптивная вариация
|  26 |           HASH JOIN                                  |                             |
|  27 |            NESTED LOOPS                              |                             |
|  28 |             NESTED LOOPS                             |                             |
|  29 |              TABLE ACCESS BY INDEX ROWID BATCHED     | REVERSE                     |
|  30 |               INDEX RANGE SCAN                       | REVERSE_PID_IDX             |
|  31 |              INDEX UNIQUE SCAN                       | INVOICE_PK                  |
|  32 |             TABLE ACCESS BY INDEX ROWID              | INVOICE                     |
|  33 |            MAT_VIEW ACCESS FULL                      | PAYSYS                      |
|  34 |          TABLE ACCESS BY INDEX ROWID                 | OPERATION                   |
|  35 |           INDEX UNIQUE SCAN                          | CS_N_OPERATION_PK           |
|- 36 |         TABLE ACCESS FULL                            | OPERATION                   | -- --//--
|  37 |        INDEX UNIQUE SCAN                             | CONSUME_IDX_PK              |
|  38 |       TABLE ACCESS BY GLOBAL INDEX ROWID             | CONSUME                     |
--------------------------------------------------------------------------------------------

— на том же наборе объектов бд

При этом в части Notes OTHER_XML планы запросов отличаются минимально:

SQL> SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
  2  '
  3     sql_profile:     ' || extractvalue(xmlval, '/*/info[@type = "sql_profile"]')||'
  4     sql_patch:       ' || extractvalue(xmlval, '/*/info[@type = "sql_patch"]')||'
  5     baseline:        ' ||   extractvalue(xmlval, '/*/info[@type = "baseline"]')||'
  6     outline:         ' ||   extractvalue(xmlval, '/*/info[@type = "outline"]')||'
  7     dyn_sampling:    ' ||   extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]')||'
  8     dop:             ' ||   extractvalue(xmlval, '/*/info[@type = "dop"]')||'
  9     dop_reason:      ' ||   extractvalue(xmlval, '/*/info[@type = "dop_reason"]')||'
 10     card_feedback:   ' ||   extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]')||'
 11     perf_feedback:   ' ||   extractvalue(xmlval, '/*/info[@type = "performance_feedback"]')||'
 12     adaptive_plan:   ' || extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')||'
 13     spd_used:        ' || extractvalue(xmlval, '/*/spd/cu')||'
 14     spd_valid:       ' || extractvalue(xmlval, '/*/spd/cv')||'
 15     gtt_sess_stat:   ' || extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]')||'
 16     db_version:      ' || extractvalue(xmlval, '/*/info[@type = "db_version"]')||'
 17     plan_hash_full:  ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')||'
 18     plan_hash:       ' || extractvalue(xmlval, '/*/info[@type = "plan_hash"]')||'
 19     plan_hash_2:     ' || extractvalue(xmlval, '/*/info[@type = "plan_hash_2"]')     as "GV$SQL_PLAN",
 20  '
 21     sql_profile:     ' || extractvalue(xmlval2, '/*/info[@type = "sql_profile"]')||'
 22     sql_patch:       ' || extractvalue(xmlval2, '/*/info[@type = "sql_patch"]')||'
 23     baseline:        ' ||   extractvalue(xmlval2, '/*/info[@type = "baseline"]')||'
 24     outline:         ' ||   extractvalue(xmlval2, '/*/info[@type = "outline"]')||'
 25     dyn_sampling:    ' ||   extractvalue(xmlval2, '/*/info[@type = "dynamic_sampling"]')||'
 26     dop:             ' ||   extractvalue(xmlval2, '/*/info[@type = "dop"]')||'
 27     dop_reason:      ' ||   extractvalue(xmlval2, '/*/info[@type = "dop_reason"]')||'
 28     card_feedback:   ' ||   extractvalue(xmlval2, '/*/info[@type = "cardinality_feedback"]')||'
 29     perf_feedback:   ' ||   extractvalue(xmlval2, '/*/info[@type = "performance_feedback"]')||'
 30     adaptive_plan:   ' || extractvalue(xmlval2, '/*/info[@type = "adaptive_plan"]')||'
 31     spd_used:        ' || extractvalue(xmlval2, '/*/spd/cu')||'
 32     spd_valid:       ' || extractvalue(xmlval2, '/*/spd/cv')||'
 33     gtt_sess_stat:   ' || extractvalue(xmlval2, '/*/info[@type = "gtt_session_st"]')||'
 34     db_version:      ' || extractvalue(xmlval2, '/*/info[@type = "db_version"]')||'
 35     plan_hash_full:  ' || extractvalue(xmlval2, '/*/info[@type = "plan_hash_full"]')||'
 36     plan_hash:       ' || extractvalue(xmlval2, '/*/info[@type = "plan_hash"]')||'
 37     plan_hash_2:     ' || extractvalue(xmlval2, '/*/info[@type = "plan_hash_2"]')   as "DBA_HIST_SQL_PLAN"
 38  from
 39   (select xmltype(other_xml) xmlval,
 40           xmltype(other_xml2) xmlval2
 41      from
 42     (select s1.other_xml other_xml
 43      from gv$sql_plan s1
 44     where s1.sql_id = 'fkw6hb5mrw02t'
 45       and s1.plan_hash_value = 3887377781
 46       and s1.other_xml is not null) v1,
 47     (select p2.other_xml other_xml2
 48      from dba_hist_sql_plan p2
 49        where p2.sql_id = 'fkw6hb5mrw02t'
 50       and p2.plan_hash_value = 3887377781
 51       and p2.other_xml is not null) v2)
 52  /
 
GV$SQL_PLAN                      DBA_HIST_SQL_PLAN
-------------------------------- -----------------------------------
                                 
   sql_profile:                     sql_profile:
   sql_patch:                       sql_patch:
   baseline:                        baseline:
   outline:                         outline:
   dyn_sampling:    2               dyn_sampling:    2
   dop:                             dop:
   dop_reason:                      dop_reason:
   card_feedback:                   card_feedback:
   perf_feedback:                   perf_feedback:
   adaptive_plan:   yes             adaptive_plan:   yes
   spd_used:        5               spd_used:        3           -- тут
   spd_valid:       5               spd_valid:       3
   gtt_sess_stat:                   gtt_sess_stat:
   db_version:      12.1.0.2        db_version:      12.1.0.2
   plan_hash_full:  1266372674      plan_hash_full:  2002632649  -- и тут
   plan_hash:       3887377781      plan_hash:       3887377781
   plan_hash_2:     2240103821      plan_hash_2:     2240103821

, и в части хинтов, составляющих Outline планов (также хранящихся в OTHER_XML) отличия вряд ли относятся/влияют на адаптивные части планов:

SQL> with plh1 as
  2   (select substr(extractvalue(value(d), '/hint'), 1, 512) as phv_3887377781_hist
  3      from xmltable('/*/outline_data/hint' passing
  4                    (select xmltype(other_xml) as xmlval
  5                       from dba_hist_sql_plan
  6                      where sql_id = 'fkw6hb5mrw02t'
  7                        and plan_hash_value = nvl('3887377781', 0)
  8                        and other_xml is not null)) d),
  9  plh2 as
 10   (select substr(extractvalue(value(d), '/hint'), 1, 512) as phv_3887377781_memo
 11      from xmltable('/*/outline_data/hint' passing
 12                    (select xmltype(other_xml) as xmlval
 13                       from gv$sql_plan
 14                      where sql_id = 'fkw6hb5mrw02t'
 15                        and plan_hash_value = nvl('3887377781', 0)
 16                        and other_xml is not null
 17                        and (inst_id, child_number) in
 18                            (select inst_id, child_number
 19                               from gv$sql_plan
 20                              where sql_id = 'fkw6hb5mrw02t'
 21                                and plan_hash_value = nvl('3887377781', 0)
 22                                and rownum <= 1))) d)
 23  select *
 24    from plh1
 25    full join plh2
 26      on plh1.phv_3887377781_hist = plh2.phv_3887377781_memo
 27  minus
 28  select *
 29    from plh1
 30    join plh2
 31      on plh1.phv_3887377781_hist = plh2.phv_3887377781_memo
 32  /
 
PHV_3887377781_HIST          PHV_3887377781_MEMO
---------------------------- -------------------------------------------------------
                             OPT_PARAM('_b_tree_bitmap_plans' 'false')
                             OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
                             OPT_PARAM('_optimizer_reduce_groupby_key' 'false')
                             OPT_PARAM('_px_single_server_enabled' 'false')

— лишь несколько посвящённых ещё не пропатченным багам параметров

Т.о. сосуществование таких несовпадающих адаптивных планов с одинаковыми PHV имхо указывает на недостаточную/поканеясную управляемость адаптивного поведения, при этом доступных хинтов ADAPTIVE_PLAN / NO_ADAPTIVE_PLAN, в отличие от вышеупомянутого случая когда один PHV используется НЕадаптивным и адаптивным планами, очевидно недостаточно

Реклама

Добавить комментарий »

Комментариев нет.

RSS feed for comments on this post. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Блог на WordPress.com.

%d такие блоггеры, как: