Oracle mechanics

12.10.2014

История одного запроса: SPM, трансформации и подсказки

Filed under: Oracle,Plan Management,SQL Tuning — Игорь Усольцев @ 23:29
Tags: ,

Типичный случай — план запроса «неожиданно» измененился, и не в лучшую сторону:

11.2.0.4.@ SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  REASON1
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- ------------------
   2     9 07.10 14:50    07.10 17:57        1076192531       465674203            132     1 Y          N          Y          N                  Y                N                NLS Settings(0)  |
   2     5 07.10 16:04    07.10 17:04            263416      4268563287            246     3 Y          N          Y          N                  N                N                NLS Settings(0)  |

, при этом V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS=Y (обозначенное как OPTIMIZER_STATS) означает, что неудачный выбор формально более выгодного плана был динамически предопределён технологией cardinality feedback (CF):

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8dvbszd8kj04m', 1,format => '+note'));
...
Note
-----
   - cardinality feedback used for this statement

Часть 1. SQL Plan Management (SPM)


Создание baseline на основе доступного в SGA хорошего плана 4268563287:

SQL> @create_bsline 8dvbszd8kj04m 4268563287 "OEBSQUERY#720"
Baseline SQL_59e907f73ed2bab7 SQL_PLAN_5mu87ywzd5fpr762f5e75 was [re]created
for SQL_ID=8dvbszd8kj04m, SQL_PLAN_HASH=4268563287

, судя по последовавшему в V$SQL_SHARED_CURSOR замечанию SQL Tune Base Object Different(3) было отмечено оптимизатором , однако, судя по-прежнему пустому V$SQL.SQL_PLAN_BASELINE созданный baseline успешно использован не был:(

SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE REASON1                              SQL_PLAN_BASELINE
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- --------------- ------------------------------------ -----------------
   1     1 07.10 18:19    07.10 18:19            449834       492529681             31     2 Y          N          Y          Y                  N                N
   1     1 07.10 18:13    07.10 18:13            480031      4268563287            246     1 Y          N          Y          N                  N                N               SQL Tune Base Object Different(3)  |
   1     1 07.10 18:02    07.10 18:02            366931       492529681             31     0 Y          N          N          Y                  N                N               Optimizer mismatch(13)  |
   2     9 07.10 14:50    07.10 17:57        1076192531       465674203            132     1 Y          N          Y          N                  Y                N               NLS Settings(0)  |
   2     5 07.10 16:04    07.10 17:04            263416      4268563287            246     3 Y          N          Y          N                  N                N               NLS Settings(0)  |

— вместо чего опять же под влиянием CF был сгенерирован фантастически недорогой план 492529681 с удовлетворительным временем выполнения, который можно взять за основу и ещё раз попытаться пересоздать baseline:

SQL> @create_bsline 8dvbszd8kj04m 492529681 "OEBSQUERY#720"
Baseline SQL_59e907f73ed2bab7 SQL_PLAN_5mu87ywzd5fpr708d8cb3 was [re]created
for SQL_ID=8dvbszd8kj04m, SQL_PLAN_HASH=492529681

Нужный план продолжает генерироваться и использоваться (в 07.10 18:39, например), но абсолютно без помощи baseline:(

SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE REASON1                              SQL_PLAN_BASELINE
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- --------------- ------------------------------------ -----------------
   1     1 07.10 18:39    07.10 18:39            411472       492529681             31     2 Y          N          Y          Y                  N                N
   1     1 07.10 18:13    07.10 18:13            480031      4268563287            246     1 Y          N          Y          N                  N                N               SQL Tune Base Object Different(3)  |                  --Note: cardinality feedback used for this statement
   1     1 07.10 18:02    07.10 18:02            366931       492529681             31     0 Y          N          N          Y                  N                N               Optimizer mismatch(13)  |
   2     9 07.10 14:50    07.10 17:57        1076192531       465674203            132     1 Y          N          Y          N                  Y                N               SQL Tune Base Object Different(3)  |                  --Note: cardinality feedback used for this statement
   2     5 07.10 16:04    07.10 17:04            263416      4268563287            246     3 Y          N          Y          N                  N                N               SQL Tune Base Object Different(3)  |                  --Note: cardinality feedback used for this statement

— скорее опять же под влиянием CF и, возможно, действительно низкой стоимости (в качестве комметария Note: cardinality feedback… я отметил планы, для которых dbms_xplan.display_cursor в секции Note формально подтверждает использование CF)

Т.е. «стабильности нет».., а учитывая, что использование baseline в зоне действия технологий Cardinality Feedback и Adaptive Cursor Sharing (что также не стоит исключать, учитывая значение V$SQL.IS_BIND_SENSITIVE = BIND_SENSE = Y) и различные особенности использования SPM в практических приложениях на примере OEBS, решительно создаю SQL Patch для полного исключения воздействия вредных в данном случае механизмов адаптивной оптимизации:

SQL> begin
  2    for reco in (select sql_fulltext from v$sqlarea where sql_id = '8dvbszd8kj04m')
  3      loop
  4      dbms_sqldiag_internal.i_create_patch(
  5        sql_text => reco.sql_fulltext,
  6        hint_text => 'opt_param(''_OPTIMIZER_USE_FEEDBACK'' ''FALSE'') NO_BIND_AWARE',
  7        name => 'OEBSQUERY#720');
  8      end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> -- , после чего, кстати, неплохо проверить что SQL Patch действительно был создан
SQL> -- , т.к. прямое их создание не документировано, не гарантировано и практически не всегда получается
SQL> @SQLPATCH_HINTS "OEBSQUERY#720"

SQL_PATCH_HINTS
----------------------------------------------------------
opt_param('_OPTIMIZER_USE_FEEDBACK' 'FALSE') NO_BIND_AWARE

Как только SQL Patch успешно создан, он начинает применяться вместе с последним созданным baseline-ом:

SQL> @shared_cu 8dvbszd8kj04m

INST EXECS LAST_LOAD_TIME LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL_INVALID_MISMATCH REASON1                                                                          SQL_PLAN_BASELINE              SQL_PATCH
---- ----- -------------- ---------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ------------------ ---------------- ---------------- --------------------- -------------------------------------------------------------------------------- ------------------------------ -------------
   1     5 12.10 02:16    12.10 05:15             95291       492529681             31     3 N          N          Y          N                  N                N                Y                                                                                                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   1     7 11.10 00:46    11.10 05:00            114852       492529681           1272     2 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(3)  |                                         SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   1     9 10.10 17:12    10.10 20:29            102851       492529681            530     0 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   1     1 10.10 17:57    10.10 17:57            517593       492529681             31     1 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   2     2 12.10 02:22    12.10 12:21            419316       492529681           1272     4 N          N          Y          N                  N                N                Y                     NLS Settings(0)  |                                                               SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   2    15 12.10 00:48    12.10 05:47            111067       492529681           1272     3 N          N          Y          N                  N                N                Y                     NLS Settings(0)  |                                                               SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   2    13 11.10 00:46    11.10 05:47            120418       492529681           1272     2 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   2    19 10.10 17:23    11.10 03:43            117955       492529681            530     0 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720
   2     2 10.10 19:51    10.10 19:59            418979       492529681             29     1 N          N          Y          N                  N                N                N                     Rolling Invalidate Window Exceeded(2)  |  already_processed                      SQL_PLAN_5mu87ywzd5fpr708d8cb3 OEBSQUERY#720

, при этом курсор стал нечувствительным к значениям связанных переменных (BIND_SENSE = N) и Oracle не пытается использовать — CF (USE_FEEDBACK_STATS = N = OPTIMIZER_STATS), даже несмотря на нестабильность стоимости получающихся планов (от 29 до 1272), т.е. чудесным образом получилось то, что и требовалось;)

Часть 2. Трансформации

При внимательном рассмотрении зафиксированного плана выполнения:

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  8dvbszd8kj04m, child number 1
-------------------------------------
SELECT /*+ Leading(EVT,AEH) use_nl(evt,aeh) index(evt xla_events_n2) */

Plan hash value: 492529681 -- зафиксированный план

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                         |       |       |    29 (100)|          |       |       |
|   1 |  SORT GROUP BY                               |                         |     2 |   346 |    29   (4)| 00:00:01 |       |       |
|   2 |   FILTER                                     |                         |       |       |         |             |       |       |
|   3 |    NESTED LOOPS                              |                         |     2 |   346 |    28   (0)| 00:00:01 |       |       |
|   4 |     NESTED LOOPS                             |                         |     2 |   346 |    28   (0)| 00:00:01 |       |       |
|   5 |      VIEW                                    | VW_JF_SET$D94ECE6B      |     2 |   302 |    26   (0)| 00:00:01 |       |       |
|   6 |       UNION-ALL                              |                         |       |       |         |             |       |       |
|   7 |        NESTED LOOPS                          |                         |     1 |   231 |    12   (0)| 00:00:01 |       |       |
|   8 |         NESTED LOOPS                         |                         |     1 |   231 |    12   (0)| 00:00:01 |       |       |
|   9 |          NESTED LOOPS                        |                         |     1 |   211 |     8   (0)| 00:00:01 |       |       |
|  10 |           NESTED LOOPS                       |                         |     1 |   168 |     6   (0)| 00:00:01 |       |       |
|  11 |            MERGE JOIN CARTESIAN              |                         |     1 |   109 |     5   (0)| 00:00:01 |       |       |
|  12 |             TABLE ACCESS BY INDEX ROWID      | FND_LOOKUP_VALUES       |     1 |    57 |     4   (0)| 00:00:01 |       |       |
|  13 |              INDEX RANGE SCAN                | FND_LOOKUP_VALUES_U1    |     1 |       |     3   (0)| 00:00:01 |       |       |
|  14 |             BUFFER SORT                      |                         |    44 |  2288 |     1   (0)| 00:00:01 |       |       |
|  15 |              INDEX RANGE SCAN                | XLA_EVENT_TYPES_B_U1    |    44 |  2288 |     1   (0)| 00:00:01 |       |       |
|  16 |            TABLE ACCESS BY INDEX ROWID       | XLA_EVENT_CLASSES_TL    |     1 |    59 |     1   (0)| 00:00:01 |       |       |
|  17 |             INDEX UNIQUE SCAN                | XLA_EVENT_CLASSES_TL_U1 |     1 |       |     0   (0)|          |       |       |
|  18 |           PARTITION LIST SINGLE              |                         |     1 |    43 |     2   (0)| 00:00:01 |   KEY |   KEY |
|  19 |            TABLE ACCESS BY LOCAL INDEX ROWID | XLA_EVENTS              |     1 |    43 |     2   (0)| 00:00:01 |   KEY |   KEY |
|  20 |             INDEX RANGE SCAN                 | XLA_EVENTS_N2           |     1 |       |     1   (0)| 00:00:01 |   KEY |   KEY |
|  21 |          PARTITION LIST SINGLE               |                         |     1 |       |     1   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  22 |           INDEX RANGE SCAN                   | XLA_AE_HEADERS_N2       |     1 |       |     1   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  23 |         TABLE ACCESS BY LOCAL INDEX ROWID    | XLA_AE_HEADERS          |     1 |    20 |     4   (0)| 00:00:01 |     1 |     1 |
|  24 |        FILTER                                |                         |       |       |         |             |       |       |
|  25 |         NESTED LOOPS                         |                         |     1 |   229 |    14   (0)| 00:00:01 |       |       |
|  26 |          NESTED LOOPS                        |                         |     1 |   229 |    14   (0)| 00:00:01 |       |       |
|  27 |           NESTED LOOPS                       |                         |     1 |   210 |     9   (0)| 00:00:01 |       |       |
|  28 |            NESTED LOOPS                      |                         |     1 |   168 |     6   (0)| 00:00:01 |       |       |
|  29 |             MERGE JOIN CARTESIAN             |                         |     1 |   109 |     5   (0)| 00:00:01 |       |       |
|  30 |              TABLE ACCESS BY INDEX ROWID     | FND_LOOKUP_VALUES       |     1 |    57 |     4   (0)| 00:00:01 |       |       |
|  31 |               INDEX RANGE SCAN               | FND_LOOKUP_VALUES_U1    |     1 |       |     3   (0)| 00:00:01 |       |       |
|  32 |              BUFFER SORT                     |                         |     1 |    52 |     1   (0)| 00:00:01 |       |       |
|  33 |               INDEX RANGE SCAN               | XLA_EVENT_TYPES_B_U1    |     1 |    52 |     1   (0)| 00:00:01 |       |       |
|  34 |             TABLE ACCESS BY INDEX ROWID      | XLA_EVENT_CLASSES_TL    |     1 |    59 |     1   (0)| 00:00:01 |       |       |
|  35 |              INDEX UNIQUE SCAN               | XLA_EVENT_CLASSES_TL_U1 |     1 |       |     0   (0)|          |       |       |
|  36 |            PARTITION LIST SINGLE             |                         |     1 |    42 |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  37 |             TABLE ACCESS BY LOCAL INDEX ROWID| XLA_EVENTS              |     1 |    42 |     3   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  38 |              INDEX RANGE SCAN                | XLA_EVENTS_N2           |     1 |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  39 |           PARTITION LIST SINGLE              |                         |     1 |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  40 |            INDEX RANGE SCAN                  | XLA_AE_HEADERS_N2       |     1 |       |     2   (0)| 00:00:01 |KEY(AP)|KEY(AP)|
|  41 |          TABLE ACCESS BY LOCAL INDEX ROWID   | XLA_AE_HEADERS          |     1 |    19 |     5   (0)| 00:00:01 |     1 |     1 |
|  42 |      INDEX UNIQUE SCAN                       | GL_LEDGERS_U2           |     1 |       |     0   (0)|          |       |       |
|  43 |     TABLE ACCESS BY INDEX ROWID              | GL_LEDGERS              |     1 |    22 |     1   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_use_feedback' 'false') -- подсказка из созданного SQL patch
      ALL_ROWS
...
      FACTORIZE_JOIN(@"SET$B1662CF7"("GLL"@"SET$B1662CF7_2" "GLL"@"SET$B1662CF7_1"))
...
      EXPAND_TABLE(@"SEL$5428C7F1" "XLA_AE_HEADERS"@"SEL$2")
...
      END_OUTLINE_DATA
  */

Note
-----
   - SQL patch "OEBSQUERY#720" used for this statement                        -- следы применения SPN
   - SQL plan baseline SQL_PLAN_5mu87ywzd5fpr708d8cb3 used for this statement -- --//--

— кроме очевидного факта (Note) срабатывания элементов SPM (SQL patch & baseline) можно заметить искусственно созданный оптимизатором обзор вида VW_JF_SET$D94ECE6B, что отчётливо сигнализирует об удачном применении оптимизатором преобразования Join Factorization, но эта трансформация применяется только к запросам с конструкцией UNION ALL !, а в рассматриваемом запросе 8dvbszd8kj04m никаких UNION быть не может — запрос несложный и основан на стандартных таблицах OEBS:

SELECT /*+ Leading(EVT,AEH) use_nl(evt,aeh) index(evt xla_events_n2) */
  COUNT(DISTINCT EVT.ENTITY_ID)                         ENTITY_COUNT
...
FROM XLA_EVENTS                           EVT
   , XLA_EVENT_TYPES_B                    XET
   , XLA_EVENT_CLASSES_TL                 XEC
   , XLA_AE_HEADERS                       AEH
   , GL_LEDGERS                           GLL
   , XLA_LOOKUPS                          XLK
WHERE EVT.APPLICATION_ID            = :APPLICATION_ID
...
GROUP BY
...

Однако, как можно заметить из приведённой выше секции Outline Data, к запросу со стандартно (by OEBS) патрицированными таблицами применяеются два преобразования:

  1. Table Expansion, определяется подсказкой EXPAND_TABLE(@»SEL$5428C7F1″ «XLA_AE_HEADERS»@»SEL$2»)
  2. Join Factorization — подсказкой FACTORIZE_JOIN(@»SET$B1662CF7″(«GLL»@»SET$B1662CF7_2» «GLL»@»SET$B1662CF7_1»))

— сначала CBO преобразует запрос к виду с UNION ALL с использованием Table Expansion, а затем получившийся UNION ALL запрос оптимизируется методом Join Factorization — что можно было бы назвать сказкой сказок преобразованием преобразований и выглядит, судя по успешному результату, воистину впечатляюще!

Это смелое предположение легко подтверждается: отключение первой из трансформаций Table Expansion не только удаляет любые намёки на Join Factorization, но и сводит план к более простому, но менее эффективному в терминах стоимости плану безо всяких UNION ALL:

SQL> SELECT--+ opt_param('_optimizer_table_expansion' 'false')
...

Execution Plan
----------------------------------------------------------
Plan hash value: 71038008

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                         |     1 |   249 |   668   (1)| 00:00:09 |       |       |
|   1 |  SORT GROUP BY                             |                         |     1 |   249 |   668   (1)| 00:00:09 |       |       |
|*  2 |   FILTER                                   |                         |       |       |         |             |       |       |
|   3 |    NESTED LOOPS                            |                         |     1 |   249 |   667   (0)| 00:00:09 |       |       |
|   4 |     NESTED LOOPS                           |                         |     1 |   249 |   667   (0)| 00:00:09 |       |       |
|*  5 |      HASH JOIN                             |                         |     1 |   227 |   666   (0)| 00:00:08 |       |       |
|   6 |       TABLE ACCESS BY INDEX ROWID          | FND_LOOKUP_VALUES       |     1 |    57 |     4   (0)| 00:00:01 |       |       |
|*  7 |        INDEX RANGE SCAN                    | FND_LOOKUP_VALUES_U1    |     1 |       |     3   (0)| 00:00:01 |       |       |
|   8 |       NESTED LOOPS                         |                         |    96 | 16320 |   662   (0)| 00:00:08 |       |       |
|   9 |        NESTED LOOPS                        |                         |    96 | 16320 |   662   (0)| 00:00:08 |       |       |
|* 10 |         HASH JOIN                          |                         |    92 | 13708 |   202   (0)| 00:00:03 |       |       |
|* 11 |          HASH JOIN                         |                         |    12 |  1332 |     5   (0)| 00:00:01 |       |       |
|  12 |           TABLE ACCESS BY INDEX ROWID      | XLA_EVENT_CLASSES_TL    |    12 |   708 |     3   (0)| 00:00:01 |       |       |
|* 13 |            INDEX RANGE SCAN                | XLA_EVENT_CLASSES_TL_U1 |    12 |       |     2   (0)| 00:00:01 |       |       |
|* 14 |           INDEX RANGE SCAN                 | XLA_EVENT_TYPES_B_U1    |    44 |  2288 |     2   (0)| 00:00:01 |       |       |
|  15 |          PARTITION LIST SINGLE             |                         |   583 | 22154 |   197   (0)| 00:00:03 |   KEY |   KEY |
|* 16 |           TABLE ACCESS BY LOCAL INDEX ROWID| XLA_EVENTS              |   583 | 22154 |   197   (0)| 00:00:03 |   KEY |   KEY |
|* 17 |            INDEX RANGE SCAN                | XLA_EVENTS_N2           |   246 |       |     6   (0)| 00:00:01 |   KEY |   KEY |
|  18 |         PARTITION LIST SINGLE              |                         |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 19 |          INDEX RANGE SCAN                  | XLA_AE_HEADERS_N2       |     1 |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|  20 |        TABLE ACCESS BY LOCAL INDEX ROWID   | XLA_AE_HEADERS          |     1 |    21 |     5   (0)| 00:00:01 |     1 |     1 |
|* 21 |      INDEX UNIQUE SCAN                     | GL_LEDGERS_U2           |     1 |       |     0   (0)| 00:00:01 |       |       |
|  22 |     TABLE ACCESS BY INDEX ROWID            | GL_LEDGERS              |     1 |    22 |     1   (0)| 00:00:01 |       |       |
--------------------------------------------------------------------------------------------------------------------------------------

Часть 3. Пользовательские подсказки

Напоследок, last but not least, интересно посмотреть на влияние подсказок:

/*+ Leading(EVT,AEH) use_nl(evt,aeh) index(evt xla_events_n2) */

, когда-то добавленных в текс запроса, видимо, с самыми добрыми намерениями — что с ними, используются ли они вместе или отдельно от SPM?

Если вернуться к зафиксированному средствами SPM плану 492529681, трейс CBO показывает:

Dumping Hints
=============
  atom_hint=(@=0x7f518bc9f0a0 err=20 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("AEH") )
  atom_hint=(@=0x7f518bc9edc8 err=20 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("EVT" "XLA_EVENTS_N2") ) -- USED!
  atom_hint=(@=0x7f518bc9dbb0 err=20 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("EVT") )
  atom_hint=(@=0x7f518bc9de78 err=20 resol=1 used=0 token=501 org=1 lvl=4 txt=LEADING ("EVT" "AEH") )
********** WARNING: SOME HINTS HAVE ERRORS *********
====================== END SQL Statement Dump ======================
SPM: kkopmCheckSmbUpdate (enter) xscP=0x7f518c787388, pmExCtx=0x11fef06c0, ciP=0x1a7f4e150, dtCtx=0xc0d4670

, что из подсказок пользователя в действительности используется лишь одна, которая про INDEX, остальные, видимо, не сочетаются с зафиксированным планом, при этом любопытно, что неканонически прописанная подсказка use_nl(evt,aeh) с запятой воспринята оптимизатором как пара подсказок USE_NL («AEH») и USE_NL («EVT»), ни одна из которых, впрочем, в результате не используется

Без применения SPM для того же запроса формируется схожий план, с той же парой CBO преобразований:

SPM: statement not found in SMB
...
sql=SELECT/*+ LEADING(EVT, AEH)  USE_NL(EVT, AEH)  INDEX(EVT  XLA_EVENTS_N2) */
/* Added fake comment for not use SPM */
...
============
Plan Table
============
--------------------------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation                                      | Name                   | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
--------------------------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT                               |                        |       |       |    29 |           |       |       |
| 1   |  SORT GROUP BY                                 |                        |     1 |   250 |    29 |  00:00:01 |       |       |
| 2   |   FILTER                                       |                        |       |       |       |           |       |       |
| 3   |    NESTED LOOPS                                |                        |     2 |   346 |    28 |  00:00:01 |       |       |
| 4   |     NESTED LOOPS                               |                        |     2 |   346 |    28 |  00:00:01 |       |       |
| 5   |      VIEW                                      | VW_JF_SET$D94ECE6B     |     2 |   302 |    26 |  00:00:01 |       |       |
| 6   |       UNION-ALL                                |                        |       |       |       |           |       |       |
| 7   |        NESTED LOOPS                            |                        |     1 |   228 |    15 |  00:00:01 |       |       |
| 8   |         NESTED LOOPS                           |                        |     1 |   228 |    15 |  00:00:01 |       |       |
| 9   |          NESTED LOOPS                          |                        |     1 |   208 |     9 |  00:00:01 |       |       |
| 10  |           NESTED LOOPS                         |                        |     1 |   168 |     6 |  00:00:01 |       |       |
| 11  |            MERGE JOIN CARTESIAN                |                        |     1 |   109 |     5 |  00:00:01 |       |       |
| 12  |             TABLE ACCESS BY INDEX ROWID        | FND_LOOKUP_VALUES      |     1 |    57 |     4 |  00:00:01 |       |       |
| 13  |              INDEX RANGE SCAN                  | FND_LOOKUP_VALUES_U1   |     1 |       |     3 |  00:00:01 |       |       |
| 14  |             BUFFER SORT                        |                        |    44 |  2288 |     1 |  00:00:01 |       |       |
| 15  |              INDEX RANGE SCAN                  | XLA_EVENT_TYPES_B_U1   |    44 |  2288 |     1 |  00:00:01 |       |       |
| 16  |            TABLE ACCESS BY INDEX ROWID         | XLA_EVENT_CLASSES_TL   |     1 |    59 |     1 |  00:00:01 |       |       |
| 17  |             INDEX UNIQUE SCAN                  | XLA_EVENT_CLASSES_TL_U1|     1 |       |     0 |           |       |       |
| 18  |           PARTITION LIST SINGLE                |                        |     1 |    40 |     3 |  00:00:01 | KEY   | KEY   |
| 19  |            TABLE ACCESS BY LOCAL INDEX ROWID   | XLA_EVENTS             |     1 |    40 |     3 |  00:00:01 | KEY   | KEY   |
| 20  |             INDEX RANGE SCAN                   | XLA_EVENTS_N2          |     1 |       |     2 |  00:00:01 | KEY   | KEY   |
| 21  |          PARTITION LIST SINGLE                 |                        |     1 |       |     2 |  00:00:01 | KEY(AP)| KEY(AP)|
| 22  |           INDEX RANGE SCAN                     | XLA_AE_HEADERS_N2      |     1 |       |     2 |  00:00:01 | KEY(AP)| KEY(AP)|
| 23  |         TABLE ACCESS BY LOCAL INDEX ROWID      | XLA_AE_HEADERS         |     1 |    20 |     6 |  00:00:01 | 1     | 1     |
| 24  |        FILTER                                  |                        |       |       |       |           |       |       |
| 25  |         NESTED LOOPS                           |                        |     1 |   229 |    11 |  00:00:01 |       |       |
| 26  |          NESTED LOOPS                          |                        |     1 |   229 |    11 |  00:00:01 |       |       |
| 27  |           NESTED LOOPS                         |                        |     1 |   187 |     8 |  00:00:01 |       |       |
| 28  |            NESTED LOOPS                        |                        |     1 |   130 |     6 |  00:00:01 |       |       |
| 29  |             NESTED LOOPS                       |                        |     1 |   111 |     3 |  00:00:01 |       |       |
| 30  |              INDEX RANGE SCAN                  | XLA_EVENT_TYPES_B_U1   |     1 |    52 |     2 |  00:00:01 |       |       |
| 31  |              TABLE ACCESS BY INDEX ROWID       | XLA_EVENT_CLASSES_TL   |     1 |    59 |     1 |  00:00:01 |       |       |
| 32  |               INDEX UNIQUE SCAN                | XLA_EVENT_CLASSES_TL_U1|     1 |       |     0 |           |       |       |
| 33  |             PARTITION LIST SINGLE              |                        |     1 |    19 |     3 |  00:00:01 | KEY(AP)| KEY(AP)|
| 34  |              TABLE ACCESS BY LOCAL INDEX ROWID | XLA_AE_HEADERS         |     1 |    19 |     3 |  00:00:01 | KEY(AP)| KEY(AP)|
| 35  |               INDEX RANGE SCAN                 | XLA_AE_HEADERS_N4      |     1 |       |     2 |  00:00:01 | KEY(AP)| KEY(AP)|
| 36  |            TABLE ACCESS BY INDEX ROWID         | FND_LOOKUP_VALUES      |     1 |    57 |     2 |  00:00:01 |       |       |
| 37  |             INDEX UNIQUE SCAN                  | FND_LOOKUP_VALUES_U1   |     1 |       |     1 |  00:00:01 |       |       |
| 38  |           PARTITION LIST SINGLE                |                        |     1 |       |     2 |  00:00:01 | KEY(AP)| KEY(AP)|
| 39  |            INDEX RANGE SCAN                    | XLA_EVENTS_N2          |     1 |       |     2 |  00:00:01 | KEY(AP)| KEY(AP)|
| 40  |          TABLE ACCESS BY LOCAL INDEX ROWID     | XLA_EVENTS             |     1 |    42 |     3 |  00:00:01 | 1     | 1     |
| 41  |      INDEX UNIQUE SCAN                         | GL_LEDGERS_U2          |     1 |       |     0 |           |       |       |
| 42  |     TABLE ACCESS BY INDEX ROWID                | GL_LEDGERS             |     1 |    22 |     1 |  00:00:01 |       |       |
--------------------------------------------------------------------------------+-----------------------------------+---------------+
...
Content of other_xml column
===========================
...
  parse_schema   : APPS
  plan_hash      : 272366856
  plan_hash_2    : 912509186
...
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
...
      FACTORIZE_JOIN(@"SET$B1662CF7"("GLL"@"SET$B1662CF7_2" "GLL"@"SET$B1662CF7_1")) -- те же
...
      EXPAND_TABLE(@"SEL$5428C7F1" "XLA_AE_HEADERS"@"SEL$2")                         -- преобразования
...
      LEADING(@"SEL$6E91DA20" "LV"@"SEL$3" "XET"@"SEL$1" "XEC"@"SEL$1" "EVT"@"SEL$1" "XLA_AE_HEADERS"@"SEL$2") -- лишь в завершающей части совпадает с хинтом
...
      LEADING(@"SEL$24AFDF4C" "XET"@"SEL$1" "XEC"@"SEL$1" "XLA_AE_HEADERS"@"SEL$2" "LV"@"SEL$3" "EVT"@"SEL$1") -- не совпадает с хинтом никак
...
    END_OUTLINE_DATA
  */

В этом случае оптимизатор в придачу к подсказке INDEX отмечает как пригодную/использует хинт LEADING:

Dumping Hints
=============
  atom_hint=(@=0x7fa52f1f1688 err=0 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("AEH") )
  atom_hint=(@=0x7fa52f1f5c48 err=0 resol=1 used=1 token=83 org=1 lvl=3 txt=INDEX ("EVT" "XLA_EVENTS_N2") )
  atom_hint=(@=0x7fa52f1f0630 err=0 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL ("EVT") )
  atom_hint=(@=0x7fa52f1f08f8 err=0 resol=1 used=1 token=501 org=1 lvl=4 txt=LEADING ("EVT" "XLA_AE_HEADERS") )
====================== END SQL Statement Dump ======================

, что мне кажется достаточно странным, учитывая, что подсказки LEADING в секции Outline Data как и собственно план выполнения как-то неочевидно согласуются с рукотворной подсказкой по определению:

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan

, разве что в первом блоке запроса, да и то в качестве suffix)

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

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

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 такие блоггеры, как: