Oracle mechanics

17.05.2015

12c: обратимость автоматической реоптимизации

Filed under: CBO,Oracle — Игорь Усольцев @ 21:54
Tags: , ,

На продуктивной системе 12.1.0.2 с изумлением и восторгом наблюдал случай обратимости automatic re-optimization, одной из компонент 12c adaptive optimizer features:

SQL> @shared_cu12 5v18j5jkr101w
 
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 REOPT_HINTS ADAPT USE_FEEDBACK_STATS SQL_PLAN_DIRECTIVES REASON1                            SQL_PLAN_BASELINE  SQL_PATCH  OUTLINE_CATEGORY  SQL_PROFILE IS_OBSOLETE
---- -------- -------------------- -------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----------- ----- ------------------ ------------------- ---------------------------------- ----------------- ---------- ----------------- ------------ -----------
   1      214 2015-05-11/23:10:35  2015-05-12/12:50:14  12.05.2015 15:09:32   5845453723      2439019836           1159     0 Y          N          N          Y              38 Y     Y                  valid:21; used:3    Auto Reoptimization Mismatch(1)  |                                                             N
   1      166 2015-05-11/23:10:35  2015-05-12/14:14:12  12.05.2015 15:09:35   1745816312      1905979086           1159     1 Y          N          N          Y              32 Y     Y                  valid:20; used:3    Auto Reoptimization Mismatch(1)  |                                                             N
   1      176 2015-05-11/23:10:35  2015-05-12/15:02:24  12.05.2015 15:55:41      1161262      2809591419           1459     2 Y          N          Y          N               0 Y     N                  valid:21; used:3    Auto Reoptimization Mismatch(1)  |                                                             N

— дочерние курсоры удачно создавались в порядке увеличения child_number, при этом в отличие от 2-х первых последний CHILD = 2 уже не используя реоптимизацию (REOPT=V$SQL.IS_REOPTIMIZABLE = N), формируется без дополнительных хинтов — REOPT_HINTS = 0. Baseline-ов, профилей, SQL Patch-ей запрос не использовал

Несмотря на увеличение стоимости, ср.время выполнения запроса ELA_PER_EXEC значительно сократилось, т.е. отключение автоматической реоптимизации в рамках собственно процесса [ре]оптимизации достигло поставленной цели, это действительно очень сильно

12c: конкуренция при компиляции курсоров

Filed under: Диагностика системы (instance),Oracle,shared pool — Игорь Усольцев @ 20:12
Tags: ,

После обновления бд до версии 12.1.0.2 столкнулись с проблемой блокировок курсоров, сопровождавшей замедлившийся процесс разбора (parse) некоторых запросов, и материально выраженой большим кол-вом/долей ожиданий cursor: pin S wait on X и library cache lock в ASH/AWR:

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Wait   % DB Wait    
Event                                Waits Time (sec)    Avg(ms)   time Class   
------------------------------ ----------- ---------- ---------- ------ --------
cursor: pin S wait on X                941      60,9K   64747.53   50.3 Concurrency
DB CPU                                          42,5K              35.1         
library cache lock                  20,478     6681,6     326.28    5.5 Concurrency

Наблюдение в режиме реального времени позволило достаточно быстро и пОлно диагностировать и разрешить проблему, начиная с топа запросов по ожиданиям: (more…)

07.05.2015

12c: Automatic Dynamic Statistics

Filed under: Oracle — Игорь Усольцев @ 02:50
Tags: , ,

Coskan Gundogar указал примечательный документ поддержки, уточняющий границы автоматического применения в версии 12c технологии Dynamic Sampling, реализуемого через рекурсивные запросы типа SELECT /* OPT_DYN_SAMP */, — Dynamic Sampling Level Is Changed Automatically in 12C (Doc ID 2002108.1), где, в частности разъясняется, что Automatic dynamic statistics может быть использован оптимизатором не только при установке значения параметра OPTIMIZER_DYNAMIC_SAMPLING = 11, или того же через хинт DYNAMIC_SAMPLING, но и при default-ном значении OPTIMIZER_DYNAMIC_SAMPLING, не указанном явно/explicitly в pfile/spfile, к примеру

Более того, в дополнение к документации, определяющей, что значение OPTIMIZER_DYNAMIC_SAMPLING = 11 может быть автоматически использовано оптимизаторов в случаях:

  • The query will run in parallel.
  • The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).

, документ поясняет, что Auto dynamic sampling может быть запущен при использовании технологий Adatpive Query Optimization, как-то:

  • Adaptive Plans,
  • Statistics Feedback,
  • Automatic re-optimization as part of adaptive statistics,
  • SQL Plan directives,

и даже в случаях Multiple conjunctive or disjunctive filter predicates on a table, т.е. фактически на усмотрение Oracle

И единственным надёжным способом исключить всю эту динамическую котовасию возможность использования Auto dynamic sampling является установка параметра OPTIMIZER_DYNAMIC_SAMPLING=0 на уровне системы/сессии, или подсказка /*+ DYNAMIC_SAMPLING(0) */ на уровне запроса, на что, впрочем, указывает и стандартная документация

28.04.2015

12c: реоптимизация baseline-ов

Filed under: CBO,Optimizer features,Oracle,Oracle new features — Игорь Усольцев @ 13:49
Tags: ,

При тестировании Oracle 12c наблюдали с Леонидом Борчуком в своём роде замечательное поведение, адекватно отражаемое запросом shared_cu12.sql (V$SQL+V$SQL_PLAN+V$SQL_SHARED_CURSOR с добавлением адаптивных эффектов):

12.1.0.2@ SQL> @shared_cu12 ch28y57dby48a
 
EXECS LAST_LOAD_TIME      LAST_ACTIVE_TIME    ELA_PER_EXEC PLAN_HASH_VALUE OPTIMIZER_COST CHILD BIND_SENSE BIND_AWARE SHAREABLE  REOPT REOPT_HINTS ADAPT USE_FEEDBACK_STATS SQL_PLAN_DIRECTIVES OPTIMIZER_STATS  BIND_EQ_FAILURE  ROLL REASON1                              SQL_PLAN_BASELINE              SQL_PATCH OUTLINE_CATEGORY SQL_PROFILE IS_OBSOLETE
----- ------------------- ------------------- ------------ --------------- -------------- ----- ---------- ---------- ---------- ----- ----------- ----- ------------------ ------------------- ---------------- ---------------- ---- ------------------------------------ ------------------------------ --------- ---------------- ----------- -----------
    2 2015-04-23/17:37:22 23.04.2015 17:45:14    220816797      2061850922           3073     0 N          N          N          Y              19 Y     Y                  valid:13; used:3    N                N                N    Auto Reoptimization Mismatch(1)  |                                                                         N
    2 2015-04-23/18:25:09 23.04.2015 18:33:26    221786319      3290307628       19309221     1 N          N          N          Y              36       Y                  valid:13; used:3    N                N                N    Auto Reoptimization Mismatch(1)  |                                                                         N
    4 2015-04-24/12:41:54 24.04.2015 13:10:57     88755911      2061850922           3072     2 N          N          Y          Y              19 Y     Y                  valid:13; used:4    N                N                N    SQL Tune Base Object Different(3)  |                                                                       N
    1 2015-04-24/14:30:11 24.04.2015 14:30:14      3347403       506569056           3104     3 N          N          Y          N               0       N                  valid:9; used:2     N                N                N                                         SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    1 2015-04-24/14:31:09 24.04.2015 14:31:34     15259875       506569056           3104     4 N          N          N          Y              43       Y                  valid:9; used:2     N                N                N    Auto Reoptimization Mismatch(1)  |   SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    1 2015-04-24/14:42:43 24.04.2015 14:43:10     13913257       506569056           3104     6 N          N          N          Y              44       Y                  valid:9; used:2     N                N                N    Auto Reoptimization Mismatch(1)  |   SQL_PLAN_8aa3jkjfw98tf56386d53                                        N
    4 2015-04-24/16:50:05 24.04.2015 16:52:30      3762610       506569056           3105     5 N          N          Y          N               0       N                  valid:9; used:2     N                N                N                                         SQL_PLAN_8aa3jkjfw98tf56386d53                                        N

— запрос ch28y57dby48a, вызывавший опасения в плане скорости выполнения (ELA_PER_EXEC в первых 3-х строках вывода) после фиксации хорошего плана выполнения PLAN_HASH_VALUE = 506569056:

SQL> @spm_check4sql_id ch28y57dby48a
 
SPM_TYPE          SQL_HANDLE            PLAN_NAME                      ORIGIN      VERSION    LAST_MODIFIED       LAST_EXECUTED       ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE
----------------- --------------------- ------------------------------ ----------- ---------- ------------------- ------------------- ------- -------- ----- ---------- ---------
SQL Plan Baseline SQL_852871945dc4a32e  SQL_PLAN_8aa3jkjfw98tf56386d53 MANUAL-LOAD 12.1.0.2.0 24.04.2015 14:30:12 24.04.2015 14:30:12 YES     YES      NO    YES        YES

— одновременно с мирным использованием зафиксированного baseline-ом плана, формально продолжал применять SQL Plan Directives (SQL_PLAN_DIRECTIVES) и использовать реоптимизацию (столбцы REOPT и REOPT_HINTS) как следствие деятельности Statistics Feedback (USE_FEEDBACK_STATS), т.о. производя ненужную работу и генерируя избыточные дочерние курсоры (more…)

24.04.2015

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

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

Вслед за опцией 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 только на уровне конкретной таблицы плана запроса в виде: (more…)

13.04.2015

PL/SQL, RESULT_CACHE и sysdate в запросах

Filed under: Oracle,SQL Tuning — Игорь Усольцев @ 01:20
Tags: ,

Жил-был запрос, потреблявший бОльшую часть DB Time:

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:    307361 02-Apr-15 18:30:14       371       2.0
  End Snap:    307362 02-Apr-15 19:00:16       343       2.1
   Elapsed:               30.04 (mins)
   DB Time:            1,395.37 (mins)

...

SQL ordered by Elapsed Time

        Elapsed                  Elapsed Time                                   
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id    
---------------- -------------- ------------- ------ ------ ------ -------------
        50,849.2        122,466          0.42   60.7   56.1     .5 d3gt02wdxt8k3 -- с большим опережением
SELECT /*+ RESULT_CACHE*/ NVL(BUDGET, 0) FROM U0.V_CLIENT_DISCOUNTS D RIGHT JOIN
 DUAL ON CLIENT_ID = :B2 AND DT = TRUNC(:B1 )

         7,787.2          4,241          1.84    9.3   28.4     .0 14a9q0pdpf0jv -- от ближайшего конкурента

, указанный в тексте /*+ RESULT_CACHE*/ не работал, поскольку в глубинах вложенных обзоров запроса содержалось упоминание sysdate в виде TRUNC(SYSDATE), что не способствует использованию SQL Result Cache по определению, но и логика в этом есть не всегда. В частности, при разного рода округлениях sysdate, да и без округлений — например, для запроов с достаточно высоким RPS, как в этом случае возможность использования Result Cache может быть отнюдь не лишней

План запроса адекватно отражает источник высокой стоимости и невысокой скорости выполнения (~ 400 мс на выполнение): (more…)

25.03.2015

12c: ASH.SQL_PLAN_HASH_VALUE и адаптивные эффекты

Filed under: Active Session History,Oracle — Игорь Усольцев @ 00:15
Tags: ,

В Active Session History (ASH) версии 11g содержалось относительно небольшое кол-во записей с PLAN_HASH_VALUE = 0 о запросах в стадии выполнения (IN_SQL_EXECUTION = ‘Y’):

11.2@ SQL> select decode(sql_plan_hash_value, 0, '0', '>0') as sql_plan_hash_value,
  2         case when sql_plan_line_id is null then 'NULL' else '>= 0' end as SQL_PLAN_LINE_ID,
  3        count(*)
  4   from v$active_session_history
  5  where IN_PARSE         = 'N'
  6    and IN_HARD_PARSE    = 'N'
  7    and IN_SQL_EXECUTION = 'Y'
  8  group by decode(sql_plan_hash_value, 0, '0', '>0'),
  9           case when sql_plan_line_id is null then 'NULL' else '>= 0' end
 10  order by count(*)
 11  /
 
SQL_PLAN_HASH_VALUE SQL_PLAN_LINE_ID   COUNT(*)
------------------- ---------------- ----------
>0                  NULL                   1120
0                   NULL                   1903 -- немного, SQL_PLAN_LINE_ID is NULL - "служебные записи"
>0                  >= 0                 141186

, и, в основном, это ожидаемые куски PL/SQL кода или какие-то специальные случаи типа ожиданий по db link, etc…

В Oracle 12c кол-во таких записей может внезапно/периодически увеличиться:

12.1@ SQL> select decode(sql_plan_hash_value, 0, '0', '>0') as sql_plan_hash_value,
  2         count(*)
  3    from v$active_session_history
  4   where IN_PARSE         = 'N'
  5     and IN_HARD_PARSE    = 'N'
  6     and IN_SQL_EXECUTION = 'Y'
  7   group by decode(sql_plan_hash_value, 0, '0', '>0')
  8   order by count(*)
  9  /
 
SQL_PLAN_HASH_VALUE   COUNT(*)
------------------- ----------
0                        33215
>0                      163247

(more…)

12.03.2015

12c: ошибки

Filed under: error,Oracle — Игорь Усольцев @ 23:30
Tags: ,

, всплывающие при тестировании:

1) Подробно разобранная у Jonathan Lewis. Subquery with OR проблема и отражённая в Bug 18650065 : WRONG RESULTS ON QUERY WITH SUBQUERY USING OR EXISTS проблема при тестировании ОЁБС (aka Oracle Apps) на версии бд 12.1.0.* заиграла новыми красками

Дело в том, что проблемная конструкция используется в VPD политиках стандартных модулей OEBS при использовании Multi-Org Access Control (MOAC), что легко видеть из запроса:

select distinct object_owner, object_name, policy
  from v$vpd_policy
 where predicate like 'EXISTS (SELECT 1
                        FROM mo_glob_org_access_tmp oa
                       WHERE oa.organization_id = security_id_int_1) OR security_id_int_1 IS NULL%'

А непонятность, собственно, состоит в том, что в рекомендациях оёбс по миграции на 12c ни этот баг, ни доступные workaround-ы никак не отмечены, несмотря на попытки Леонида Борчука донести это простое соображение до команды поддержки (more…)

07.03.2015

12c: управление адаптивными фичами

Filed under: CBO,Oracle — Игорь Усольцев @ 23:03
Tags: ,

По умолчанию, ответственный параметр OPTIMIZER_ADAPTIVE_FEATURES enables or disables all of the adaptive optimizer features, including adaptive plan (adaptive join methods and bitmap plans), automatic re-optimization, SQL plan directives, and adaptive distribution methods, установлен в TRUE и перечисленные функции оптимизатора 12c могут работать

Если же отключить параметр на уровне любого простого запроса:

12.1.0.2.SCOTT@/ORCL1201 SQL> select /*+ opt_param('optimizer_adaptive_features' 'false')*/ sysdate from dual;

1 row selected.

, секция Outline плана выполнения показывает параметы управления адаптивными компонентами по отдельности: (more…)

02.03.2015

Стабильность SQL Plan Baseline при обновлениях

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

Всё указывало на запрос 9s7ppf88qzx2w: и экспресс-анализ по пользователю/модулю доморощенным скриптом ASH_SQL_WAIT_TREE.SQL:

SQL> @ash_sql_wait_tree "client_id  = 'APPS_USER' and module = 'e:AR:frm:XXXXX'" 0
 
LVL BLOCKING_TREE  EVENT                     WAITS_COUNT EXECS_COUNT AVG_WA SQL_ID
--- -------------- ------------------------- ----------- ----------- ------ -------------
  1 (USER)         On CPU / runqueue                 691          10      0 9s7ppf88qzx2w
  1 (USER)         db file sequential read            83           7     53 9s7ppf88qzx2w
  1 (USER)         gc current block 2-way             29           3      3 9s7ppf88qzx2w
  1 (USER)         gc cr grant 2-way                  12           3      4 9s7ppf88qzx2w
  1 (USER)         db file sequential read             9           9    295 63sr2px4kd9da
...

, и данные SQL трейса: (more…)

Следующая страница →

Тема: Rubric. Создайте бесплатный сайт или блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 153 other followers