12c: In-Memory деградация и адаптивная реоптимизация

24.04.2015

12c: In-Memory деградация и адаптивная реоптимизация

Filed under: CBO,Oracle — Игорь Усольцев @ 00:59
Tags: Oracle 12c, plan management

Вслед за опцией Oracle Database In-Memory версия 12.1.0.2 предлагает соответствующий In-Memory Advisor (в качестве инструментария настоящих DBA 2.0, полагаю), в соответствии с рекомендациями которого была предпринята попытка поместить указанные советчиком таблицы в предварительно выделенную (restart required) область SGA:

12.1.0.2.@ SQL> @param inmemory_size
 
NAME           VALUE        DSC
-------------- ------------ -------------------------------
inmemory_size  21474836480  size in bytes of in-memory area

Нежданно-негаданно запрос, в числе прочих отражённый, и, что было бы логично, учтённый/просчитанный IM Advisor-ом, стал выполняться значительно (в разы) медленнее. Т.е. на лицо деградация производительности после/в рез-те переноса нескольких таблиц запроса в быстрое IM хранилище. В общем случае не вижу в этом большой проблемы, т.к. запрос объективно непростой (2000+ строк плана), можно сказать, штучный, построение большого плана само по себе являет непростую задачу, и дополнительный учёт возможностей INMEMORY доступа эту задачу отнюдь не облегчает, полагаю)

Попытка отключить опцию для всего запроса простыми подсказками:

SQL> select/*+ NO_INMEMORY NO_INMEMORY_PRUNING PARALLEL(8)*/ *  from v_complicated_view; -- 230ywhm3knw1c
select/*+ NO_INMEMORY NO_INMEMORY_PRUNING PARALLEL(8)*/ *  from v_complicated_view
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P00B, instance orcl:orcl1 (1)
ORA-01555: snapshot too old: rollback segment number 99 with name "_SYSSMU99_1282318135$" too small

Elapsed: 11:20:53.32

— не сработала, в точном соответствии с документацией простые хинты отключают использование INMEMORY только на уровне конкретной таблицы плана запроса в виде:

/*+ INMEMORY / NO_INMEMORY ([@queryblock] tablespec)*/

, что учитывая кол-во INMEMORY операций:

SQL> select sql_id, plan_hash_value, operation || ' ' || options, count(*)
  2    from v$sql_plan
  3   where sql_id in (select sql_id from v$sql where sql_text like 'select/*+ NO_INMEMORY NO_INMEMORY_PRUNING PARALLEL(8)*/ *  from v_complicated_view%')
  4     and options like 'INMEMORY%'
  5   group by sql_id, plan_hash_value, operation || ' ' || options
  6  /
 
SQL_ID        PLAN_HASH_VALUE OPERATION||''||OPTIONS               COUNT(*)
------------- --------------- ---------------------------------- ----------
230ywhm3knw1c       326961058 MAT_VIEW ACCESS INMEMORY FULL              30

представляется, по крайней мере, грамматически громоздкой задачей и можно попробовать отключить использование INMEMORY на уровне всего запроса соотв.параметром:

SQL> @param inmemory_query
 

NAME            VALUE   IS_DEF   IS_MOD     DSC
--------------- ------- -------- ---------- -----------------------------------------------
inmemory_query  ENABLE  TRUE     FALSE      Specifies whether in-memory queries are allowed

, к счастью, доступным через OPT_PARAM:

SQL> select/*+ opt_param('inmemory_query' 'disable') parallel(8)*/ *  from v_complicated_view;

1456 rows selected.

Elapsed: 00:39:44.86

Execution Plan
----------------------------------------------------------
Plan hash value: 3849258944

— ок, отключение INMEMORY позволило вернуть удовлетворительное время выполнения, которое, однако, при следующем выполнении вновь деградирует:

SQL> /

1456 rows selected.

Elapsed: 02:54:43.10

, что подтверждается данными ASH:

SQL> select sql_exec_id, sql_plan_hash_value, min(sample_time), max(sample_time)
  2    from v$active_session_history
  3   where sql_id = '748k5gyqkujsh'
  4     and sql_exec_id is not null
  5   group by sql_exec_id, sql_plan_hash_value
  6   order by min(sample_time)
  7  /
 
SQL_EXEC_ID SQL_PLAN_HASH_VALUE MIN(SAMPLE_TIME)   MAX(SAMPLE_TIME)
----------- ------------------- ------------------ -----------------
   16777218          3849258944 14.04.15 15:36:03  14.04.15 16:15:47
   16777219          1207514233 14.04.15 16:16:32  14.04.15 19:10:47

и указывает на использование нового плана 1207514233:

SQL> @shared_cu12 748k5gyqkujsh
 
EXECS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT ADAPT USE_FEEDBACK_STATS OPTIMIZER_STATS REASON1
----- -------------------- -------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----- ------------------ --------------- -------------------------------
    1 2015-04-14/15:35:37  2015-04-14/15:35:37  14.04.2015 16:15:51   8182420274      3849258944     5475017895     0 N          N          N          Y           Y                  N               Auto Reoptimization Mismatch(1)
    1 2015-04-14/15:35:37  2015-04-14/16:16:05  14.04.2015 17:38:05   9460059809      1207514233      215303468     1 N          N          Y          N           N                  N

, созданного под влиянием Automatic Reoptimization (v$sql.is_reoptimizable = ‘Y’ — столбец REOPT), запущенной на основании данных Statistics Feedback — V$SQL_SHARED_CURSOR.USE_FEEDBACK_STATS = ‘Y’

Все подсказки, сформированные Automatic Reoptimization имеют тип OPT_ESTIMATE:

SQL> select regexp_substr(hint_text, '[^ ]+', 1, 1) as HINT, count(*)
  2    from V$SQL_REOPTIMIZATION_HINTS
  3   WHERE sql_id = '748k5gyqkujsh'
  4   group by regexp_substr(hint_text, '[^ ]+', 1, 1)
  5  /
 
HINT                                 COUNT(*)
---------------------------------- ----------
OPT_ESTIMATE                               94

и время жизни, ограниченное временем нахождения курсора в Shared Pool, по истечении которого хинты пропадают:

SQL> select * from GV$SQL_REOPTIMIZATION_HINTS where sql_id = '748k5gyqkujsh';

no rows selected

и цикл повторяется — вновь запущенный запрос 748k5gyqkujsh будет выполняться со старый «удовлетворительно быстрым» планом 3849258944, вновь созданным ещё без влияния Automatic Reoptimization

При повторных выполнениях в качестве коственного свидетельства применения Automatic Reoptimization можно найти замечание / Note об использовании statistics feedback в соотв.секции dbms_xplan.display_cursor:

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('748k5gyqkujsh', 1, format => '+note'));

SQL_ID  748k5gyqkujsh, child number 1
-------------------------------------
select/*+ OPT_PARAM('inmemory_query' 'DISABLE') parallel(8)*/ *  from 
v_complicated_view
 
Plan hash value: 1207514233
...
Note
-----
   - dynamic statistics used: dynamic sampling (level=AUTO)
   - Degree of Parallelism is 8 because of hint
   - statistics feedback used for this statement

Или напрямую в столбце V$SQL_PLAN.OTHER_XML:

SQL> SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
  2  --  extractvalue(xmlval, '/*/info[@type = "sql_profile"]'),
  3  --  extractvalue(xmlval, '/*/info[@type = "sql_patch"]'),
  4  --  extractvalue(xmlval, '/*/info[@type = "baseline"]'),
  5  --  extractvalue(xmlval, '/*/info[@type = "outline"]'),
  6    extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]') as dynamic_sampling,
  7    extractvalue(xmlval, '/*/info[@type = "dop"]')              as dop,
  8    extractvalue(xmlval, '/*/info[@type = "dop_reason"]')       as dop_reason,
  9  --  extractvalue(xmlval, '/*/info[@type = "pdml_reason"]'),
 10  --  extractvalue(xmlval, '/*/info[@type = "idl_reason"]'),
 11  --  extractvalue(xmlval, '/*/info[@type = "queuing_reason"]'),
 12  --  extractvalue(xmlval, '/*/info[@type = "px_in_memory"]'),
 13  --  extractvalue(xmlval, '/*/info[@type = "px_in_memory_imc"]'),
 14  --  extractvalue(xmlval, '/*/info[@type = "row_shipping"]'),
 15  --  extractvalue(xmlval, '/*/info[@type = "index_size"]'),
 16  --  extractvalue(xmlval, '/*/info[@type = "result_checksum"]'),
 17    extractvalue(xmlval, '/*/info[@type = "cardinality_feedback"]') as cardinality_feedback,
 18    extractvalue(xmlval, '/*/info[@type = "performance_feedback"]') as performance_feedback,
 19  --  extractvalue(xmlval, '/*/info[@type = "xml_suboptimal"]'),
 20    extractvalue(xmlval, '/*/info[@type = "adaptive_plan"]')        as adaptive_plan,
 21    extractvalue(xmlval, '/*/spd/cu')                               as "Used spd Count",
 22    extractvalue(xmlval, '/*/spd/cv')                               as "Valid spd Count",
 23  --  extractvalue(xmlval, '/*/info[@type = "gtt_session_st"]'),
 24    extractvalue(xmlval, '/*/info[@type = "plan_hash"]')            as plan_hash,
 25    extractvalue(xmlval, '/*/info[@type = "plan_hash_full"]')       as plan_hash_full
 26  from
 27   (select xmltype(other_xml) xmlval from v$sql_plan where sql_id = '748k5gyqkujsh' and plan_hash_value = 1207514233 and other_xml is not null)
 28  /
 
DYNAMIC_SAMPLING DOP DOP_REASON CARDINALITY_FEEDBACK PERFORMANCE_FEEDBACK ADAPTIVE_PLAN Used spd Count Valid spd Count PLAN_HASH  PLAN_HASH_FULL
---------------- --- ---------- -------------------- -------------------- ------------- -------------- --------------- ---------- --------------
11               8   hint       yes                                                     0              22              1207514233 925430904

— под старым добрым именем CARDINALITY_FEEDBACK, т.о. Automatic Reoptimization можно транслировать как развитие технологии/идеи Cardinality Feedback в форме Statistics Feedback версии 12c, тем более что Automatic Reoptimization редко встречается в отсутствие Statistics Feedback :)

12.1.0.2.@ SQL> select is_reoptimizable, use_feedback_stats
  2    from gv$sql_shared_cursor c
  3    join gv$sql s
  4   using (inst_id, sql_id, child_address)
  5   where is_reoptimizable = 'Y'
  6     and is_reoptimizable <> use_feedback_stats
  7  /

no rows selected

Из интересного предыдущий запрос из v$sql_plan даёт указание на кол-во доступных Sql Plan Directives (Valid spd Count) и использованных директив (Used spd Count) при построении плана

Самое время проверить, действительно ли параметр _optimizer_gather_feedback управляет процессом Automatic Reoptimization?

SQL> select/*+ opt_param('inmemory_query' 'disable') opt_param('_optimizer_gather_feedback' 'false') parallel(8)*/ *  from v_complicated_view;

1456 rows selected.

Elapsed: 00:42:58.34

После нескольких выполнений видно, что, действительно, негативные эффекты Automatic Reoptimization, как, впрочем, и Statistics Feedback устранёны, и запрос стабильно выполняется:

SQL> @shared_cu12 fa3jnkm8yvxrf
 
INST EXECS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT ADAPT USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON1 SQL_PLAN_BASELINE SQL_PATCH SQL_PROFILE IS_OBSOLETE
---- ----- -------------------- -------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----- ------------------ ---------------- ---------------- ---- ------- ----------------- --------- ----------- -----------
   1     2 2015-04-14/12:20:44  2015-04-14/12:20:44  14.04.2015 13:26:40   5704943135      3849258944     5475017895     0 N          N          Y          N           N                  N                N                N                                                    N

P.S. Если не остановить процесс автоматической реоптимизации, можно убедиться, что для процесса не установлены какие-либо разумные пределы совершенства:

SQL> @shared_cu12 748k5gyqkujsh
 
INST    EXECS FIRST_LOAD_TIME      LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT ADAPT USE_FEEDBACK_STATS OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON1                            SQL_PLAN_BASELINE SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
---- -------- -------------------- -------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----- ------------------ ---------------- ---------------- ---- ---------------------------------- ----------------- --------- ---------------- ----------- -----------
   1        1 2015-04-15/12:49:11  2015-04-15/12:49:11  15.04.2015 13:25:21   6089338539      3849258944     5461793759     0 N          N          N          Y           Y                  N                N                N    Auto Reoptimization Mismatch(1)  |                                                          N
   1        1 2015-04-15/12:49:11  2015-04-15/13:37:58  15.04.2015 15:57:37  12054440952      1207514233      215326808     1 N          N          N          Y           Y                  N                N                N    Auto Reoptimization Mismatch(1)  |                                                          N
   1        0 2015-04-15/12:49:11  2015-04-15/16:22:00  15.04.2015 16:23:02    298407251       184234219      215319802     2 N          N          Y          N           N                  N                N                N                                                                                                N

— видно, что для 3-х выполнений сгенерировано 3 плана, из которых пока самым удачным был неоптимизированный:

SQL> select sql_exec_id,
  2         sql_plan_hash_value,
  3         max(sample_time) - min(sample_time) as duration,
  4         count(*) as ash_rows,
  5         count(distinct session_id || ' ' || session_serial#) as px_coun
  6    from v$active_session_history
  7   where sql_id = '748k5gyqkujsh'
  8     and sql_exec_id is not null
  9   group by sql_exec_id, sql_plan_hash_value
 10   order by min(sample_time)
 11  /
 
SQL_EXEC_ID SQL_PLAN_HASH_VALUE DURATION               ASH_ROWS    PX_COUN
----------- ------------------- -------------------- ---------- ----------
   16777216          3849258944 +000000000 00:35:53        6134        524
   16777217          1207514233 +000000000 02:19:16       12072        242
   16777218           184234219 +000000000 01:03:23        9563        539

, при этом кол-во REOPTIMIZATION HINTS только нарастает:

SQL> select child_number, regexp_substr(hint_text, '[^ ]+', 1, 1) as HINT, count(*)
  2    from V$SQL_REOPTIMIZATION_HINTS
  3   WHERE sql_id = '748k5gyqkujsh'
  4   group by child_number, regexp_substr(hint_text, '[^ ]+', 1, 1)
  5  /
 
CHILD_NUMBER HINT                                       COUNT(*)
------------ ---------------------------------------- ----------
           0 OPT_ESTIMATE                                     94 -- хинтов для 2-го выполнения
           1 OPT_ESTIMATE                                    117 -- хинтов для 3-го выполнения

с пересечением по:

SQL> select count(*)
  2    from V$SQL_REOPTIMIZATION_HINTS rh1
  3    join V$SQL_REOPTIMIZATION_HINTS rh2
  4      on rh1.sql_id = rh2.sql_id
  5     and rh1.child_number = 0
  6     and rh2.child_number = 1
  7     and rh1.hint_text = rh2.hint_text
  8   WHERE rh1.sql_id = '748k5gyqkujsh'
  9  /
 
  COUNT(*)
----------
        74

уточняющим хинтам, т.е. у процесса реоптимизации, похоже, отсутствует механизм самоограничения/самоотключения)

P.P.S. Судя по активному использованию в описанной адаптивной фиче Oracle 12c, плоходокументированная подсказка OPT_ESTIMATE ещё долго останется в тренде, но теперь её синтаксис легко доступен через V$SQL_REOPTIMIZATION_HINTS:

SQL> select distinct regexp_substr(hint_text, '[^ ]+', 1, 1) as HINT,
  2                  regexp_substr(hint_text, '[^ ]+', 1, 3) as IDENTIFIER,
  3                  regexp_substr(hint_text, '(\ )([A-Z_]+)(\=)', 1, 1) as ADJUSTMENT
  4    from GV$SQL_REOPTIMIZATION_HINTS
  5   order by 1, 2, 3
  6  /
 
HINT                    IDENTIFIER            ADJUSTMENT
----------------------- --------------------- ----------
OPT_ESTIMATE            GROUP_BY               ROWS=
OPT_ESTIMATE            INDEX_FILTER           MIN=
OPT_ESTIMATE            INDEX_FILTER           ROWS=
OPT_ESTIMATE            INDEX_SCAN             MIN=
OPT_ESTIMATE            JOIN                   MIN=
OPT_ESTIMATE            JOIN                   ROWS=
OPT_ESTIMATE            QUERY_BLOCK            ROWS=
OPT_ESTIMATE            TABLE                  MIN=
OPT_ESTIMATE            TABLE                  ROWS=

3 комментария

Добрый день!С какой целью таблицы размещались в InMemory? Какой был начальный запрос и план, который пытались улучшить используя I’M?

комментарий от sshumeev — 04.10.2017 @ 08:11
- Приветствую, Сергей
  в InMemory запрос помещался по рекомендации соответствующего Adviser-а, как я и писал, с целью ускорить/увеличить производительность конкретного запроса, как бы «просто» задействовав опцию
  Простой вариант не прошёл, полагаю, из-за громоздкости плана запроса (2000+ строк) — по этой же причине я не сохранил полных планов выполнения, но из текста видно, что план содержал 30 MATVIEW FULL SCAN-ов
  Собственно, этот путанный текст содержит проверенный пример неуниверсальности самого подхода «простой» InMemory оптимизации
  
  комментарий от Игорь Усольцев — 05.10.2017 @ 00:57
По согласованию с Игорем, по технологии IM, в применении к OEBS:
По результатам тестирования технологии Oracle Database In-Memory (DBIM, IM) в МТС были открыты 2 SR по выявленным самым критичным ограничениям технологии:
1. ..Отсутствие решения по вовлечению temporary tables в технологию DBIM выводит из оптимизации все тяжелые процессы….
Открыл SR #3-15540013131: Using temporary tables in DBIM e/h.
Результат: Ограничение признано и открыт ER Bug 21917857. Находится в девелопменте, ..response from the product manager: The ER has been brought to the attention of our lead architect for Database In-Memory.
2. Было выявлено ограничение технологии, не указанное в документации: оптимизатор не использует IM, если в строке запроса (query list) присутствует хотя бы одно незагруженное в IM поле (столбец) таблицы, даже если оно не входит в предикаты и агрегаты.
Открыл SR #3-15645307031: Optimizer generates non-optimal plans if query non-predicate column is not populated in In-Memory с требованием открыть ER.
Результат: Ограничение признано и открыт ER Bug 26956749 — SUPPORT ACCESS PATH WHERE INMEMORY COLUMNAR ACCESS PATH WITH TABLE ACCESS BY IND

Коллеги, если вы поддерживаете данные Enhancement Requests, открывайте SR’ы и требуйте присоединить ваш запрос к открытым ER.
Чем больше присоединившихся, тем больше внимания со стороны девелопмента, тем быстрее решат проблемы.

С уважением,
Сергей Кисиль.

комментарий от Сергей — 24.10.2017 @ 08:10

RSS feed for comments on this post.

	Сергей на 12c: In-Memory деградация и ад…
	vesselova на Hintset Object
	Игорь Усольцев на 12c: In-Memory деградация и ад…
	sshumeev на 12c: In-Memory деградация и ад…
	Игорь Усольцев на dynamic sampling при индексном…

Oracle mechanics

24.04.2015