12c: EBS R12 система с отключёнными адаптивными фичами / OPTIMIZER_ADAPTIVE_FEATURES = FALSE

В докуметации Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1) определённо(*) рекомендуется отключить адаптивные фичи:

#########
#
# Optimizer Parameters
#
# Release 12 uses cost based optimization. The following optimizer
# parameters must be set as shown, and should not be changed.
# It is recommended to disable the adaptive optimizer features: adaptive plans,
# automatic re-optimization, and SQL plan directives.
#
#########

optimizer_adaptive_features = FALSE #MP -- Mandatory parameters --*

— как указано, чтобы использовать «чистый» CBO, т.о. блокируя избыточную генерацию планов и общую нестабильность (см., например, 12c: адаптивная оптимизация и CBO), что и было давно выполнено на уровне системы:

12.1.0.2@ SQL> @param optimizer_adaptive_features
 
NAME                         VALUE  IS_DEF   IS_MOD     DSC
---------------------------- ------ -------- ---------- --------------------------
optimizer_adaptive_features  FALSE  FALSE    FALSE      controls adaptive features

, отключив Adaptive Plans, Automatic Re-Optimization / Statistics Feedback, and SQL plan directives как следствие

Однако, так просто условно-избыточная генерация планов выполнения не прекратилась:

Hintset Object

The plan_baseline hintset is just the list of hints SPM tried to use to reproduce the plan

В ТОПе Library Cache периодически появлялся непривычный объект:

SQL> select *
  2    from (select hash_value,
  3                 substr(name, 1, 100) as object_name,
  4                 namespace,
  5                 type,
  6                 kept,
  7                 count(*),
  8                 min(timestamp),
  9                 sum(locked_total),
 10                 sum(pinned_total),
 11                 sum(loads),
 12                 sum(executions),
 13                 sum(sharable_mem)
 14            from v$db_object_cache
 15           group by hash_value,
 16                    substr(name, 1, 100),
 17                    namespace,
 18                    type,
 19                    kept
 20           order by sum(sharable_mem) desc)
 21   where rownum <= 3
 22  /
 
HASH_VALUE OBJECT_NAME          NAMESPACE      TYPE           KEPT COUNT(*) MIN(TIMESTAMP)      SUM(LOCKED_TOTAL) SUM(PINNED_TOTAL) SUM(LOADS) SUM(EXECUTIONS) SUM(SHARABLE_MEM)
---------- -------------------- -------------- -------------- ---- -------- ------------------- ----------------- ----------------- ---------- --------------- -----------------
2209848120 6004199150836888961  HINTSET OBJECT HINTSET OBJECT NO          1                                 14872             14872          4               0         625277440
 838046317 select               SQL AREA       CURSOR         NO        459 2016-11-02/10:54:33            442985            288445      79518          180659         155325176
 269081421 select               SQL AREA       CURSOR         NO        627 2016-10-31/00:07:03            273128            218432     143717           91650         154551720
...

необычайно крупного размера SHARABLE_MEM > 600MB с цифровым наименованием, под коим числилось 2 объекта:

Презентация Oracle 12c ADS

, представленная на Oracle Database Community Day 2016 (добавил комментарии на сером фоне):

Небольшое дополнение и иллюстрация:

SQL Patch для решения стандартных проблем версии 12c

Как бы решая проблему High Version Count находим пациента:

SQL> select *
  2    from (select inst_id,
  3                 sql_id,
  4                 count(*),
  5                 sum(count(*)) over(partition by sql_id) as child_count
  6            from gv$sql
  7           group by inst_id, sql_id
  8           order by 4 desc, 1)
  9   where child_count > 200
 10  /

INST_ID SQL_ID          COUNT(*) CHILD_COUNT
------- ------------- ---------- -----------
...
      1 a40b55bnbjm5t        179         362
      2 a40b55bnbjm5t        183         362
...

Суммарный запрос по причинам нерасшаривания курсора указывает на Auto Reoptimization Mismatch(1) + Bind mismatch(25):

12c: простое управление планом с использованием SQL Plan Directives (SPD)

После обновления на версию 12.1.0.2 обнаружился деградировавший запрос:

12.1.0.2.@ SQL> @shared_cu12_noxml 4nut8pr74vpff
 
EXECS USERS_OPENING LAST_LOAD_TIME       LAST_ACTIVE_TIME    ELA_PER_EXEC CURSOR_STATUS PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  USE_FEEDBACK_STATS REOPT REOPT_HINTS ADAPT SPD_Valid SPD_Used  DS_LEVEL REASON#1
----- ------------- -------------------- ------------------- ------------ ------------- --------------- -------------- ----- ---------- ---------- ---------- ------------------ ----- ----------- ----- --------- --------- -------- ----------------------
  374             2 2015-10-28/01:30:14  28.10.2015 15:38:55    349992290 VALID              2500238505           2253     1 Y          N          Y          N                  N                       0         1         2        Optimizer mismatch(12)

— со ср.временем ~ 350 сек. при SLA/таймауте в 300

Из запроса заметно, что вероятной причиной деградации может быть использованная в процессе директива (SPD_Used), которую удалось запеленговать командой EXPLAIN PLAN:

CBQT при создании/обновлении матвью в 12.1.0.1

Тема предыдущего поста про BEGIN_OUTLINE_DATA возникла во время проверки действительно нового и, не побоюсь этого слова, революционного фикса:

SQL> @fix 9852856
 
  BUGNO VALUE SQL_FEATURE          DESCRIPTION                 OPTIMIZER_FEATURE_ENABLE
------- ----- -------------------- --------------------------- ------------------------
9852856     1 QKSFM_CBO_3904125    Enable CBQT for MV refresh  12.1.0.1

, указанного Леонидом Борчуком при обсуждении неоднозначных результатов оптимизации запросов создания/полного обновления материализованных представлений в версиях Oracle 11.2

Это полезное улучшение могло было бы решить множество проблем, но радость была преждевременной

12c: о пользе Temporary Undo и реализации out-of-place обновления матвью в 11g

1) в предыдущем посте забыл упомянуть про Temporary Undo — крайне полезную возможность переключать запись undo для временных таблиц из системного undo табличного пр-ва во временное, что позволяет:

• снизить общий требуемый размер undo
• как следствие — уменьшить генерируемый объём redo
• допускает DML на временных таблицах физического стенбая, где Temporary Undo включен по умолчанию

Вспомнил, т.к. попался хороший практический пример на версии 11.2.0.3 при тестах стандартного приложения OEBS, где использование этой возможности могло бы в значительной мере исключить проблемы, возникающие на на уровне системы

Итак, первоначально проблема представлялась в виде относительно большого потока генерируемых redo:

Oracle 12c

Нововведения 12c, на первый взгляд показавшиеся важными и интересными:

Контейнерная архитектура бд (Multitenant Architecture)

проявляется сразу же после установки и старта сервиса на ноутбуке (Windows 7 x86_64), при соединении к дескриптору, автоматически прописываемому в tnsnames.ora: