Вслед за опцией 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=
Добрый день!С какой целью таблицы размещались в 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