Oracle mechanics

26.07.2015

Одиночное выполнение SQL запроса с несколькими SQL_EXEC_ID и различными планами

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

В процессе совместного с Евгением Калининым тестирования с целью стандартизации/оптимизации времени выполнения запроса посчастливилось наблюдать диво дивное нечасто встречающееся событие: выполнение одного запроса очевидно затягивалось по причине периодической инвалидации используемого курсора, следом генеровался новый курсор (с новым планом) и начинал выполняться с начала, при этом со стороны запускающего приложения выполнение запроса не прерывалось, просто затянулось на 1,5 часа вместо обычных 20 минут:

12.1.0.2@ SQL> select nvl(qc_session_id, session_id) sid,
  2         nvl(qc_session_serial#, session_serial#) serial#,
  3         sql_exec_id,
  4         sql_child_number                    as CHILD,
  5         sql_plan_hash_value                 as PHV,
  6         sql_full_plan_hash_value            as FPHV,
  7         min(sample_time),
  8         max(sample_time),
  9         count(*)                            as ASH_ROWS,
 10         count(distinct session_id) - 1      as PX_COUNT,
 11         max(sample_time) - min(sample_time) as DURATION
 12    from v$active_session_history
 13   where module = 'sqlplus.exe'
 14     and sql_id = '5s0w4cubz5yyt'
 15     and sql_child_number >= 0
 16   group by nvl(qc_session_id, session_id),
 17            nvl(qc_session_serial#, session_serial#),
 18            sql_exec_id,
 19            sql_child_number,
 20            sql_plan_hash_value,
 21            sql_full_plan_hash_value
 22   order by max(sample_time)
 23  /
 
 SID SERIAL# SQL_EXEC_ID CHILD        PHV       FPHV MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)       ASH_ROWS PX DURATION
 --- ------- ----------- ----- ---------- ---------- ---------------------- ---------------------- -------- -- -------------------
  43    2250    16777216     0 1177121823 1348556722 21.07.15 14:56:58,291  21.07.15 15:15:56,736      3802  8 +000000000 00:18:58
  43    2250                 1  598640444 1944960939 21.07.15 15:15:57,736  21.07.15 15:15:58,736         2  1 +000000000 00:00:01
  43    2250                 1 2027645636 2114082327 21.07.15 15:15:59,736  21.07.15 15:16:13,736        15  0 +000000000 00:00:14
  43    2250                 1 2864496871 3345652639 21.07.15 15:16:14,736  21.07.15 15:16:27,736        14  0 +000000000 00:00:13
  43    2250                 0 1852732831 4122082463 21.07.15 15:16:28,736  21.07.15 15:16:28,736         1  0 +000000000 00:00:00
--43    2250                 1          0          0 21.07.15 15:54:16,636  21.07.15 15:54:30,646        15  0 +000000000 00:00:14
--43    2250                 3 3013780789 4105931067 21.07.15 15:55:48,694  21.07.15 15:56:00,694        13  0 +000000000 00:00:12
--43    2250                 2  667538297 1913179438 21.07.15 15:56:28,694  21.07.15 15:58:23,740         4  2 +000000000 00:01:55
--43    2250                 2 3013780789 4105931067 21.07.15 15:55:18,674  21.07.15 15:59:47,777       112  5 +000000000 00:04:29
--43    2250                 1  667538297 1913179438 21.07.15 15:27:52,024  21.07.15 16:00:50,807        16  5 +000000000 00:32:58
--43    2250                 1 3013780789 4105931067 21.07.15 15:27:53,024  21.07.15 16:01:17,807       332  6 +000000000 00:33:24
--43    2250                 0          0 4122082463 21.07.15 16:11:06,080  21.07.15 16:11:06,080         4  3 +000000000 00:00:00
  43    2250    16777217     0 1852732831 4122082463 21.07.15 15:16:29,736  21.07.15 16:11:06,080      7198  8 +000000000 00:54:36
  43    2250    16777218     0 2628977128  173302026 21.07.15 16:11:07,080  21.07.15 16:16:07,196       630  8 +000000000 00:05:00

(more…)

25.07.2015

12c: как надёжно отключить Automatic Dynamic Statistics на уровне запроса?

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

В предыдущей заметке 12c: Automatic Dynamic Statistics я сослался на документ поддержки Dynamic Sampling Level Is Changed Automatically in 12C (Doc ID 2002108.1), рекомендующий в качестве метода отключения ADS
использовать подсказку на следующем примере:

Disabling Dynamic Statistics

ADS can be disabled but setting optimizer_dynamic_sampling to 0 either with a parameter or using a hint.

Disable for all tables:
select /*+ dynamic_sampling(0) */ …

Пробуя отключить Automatic Dynamic Sampling в одном из тестовых запросов, обратил внимание, что метод с /*+ DYNAMIC_SAMPLING(0) */ нельзя признать надёжным: (more…)

15.07.2015

12c: Сравнение планов выполнения и выявление соответствий в паре PLAN_HASH_VALUE >-< FULL_PLAN_HASH_VALUE

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

Для одного из запросов внезапно сработал Statistics Feedback, что в V$SQL отразилось следующим образом:

EXEC LOAD_TIME   OPENING LAST_ACTIVE_TIME ELA_PER_EXEC PLAN_HASH_VALUE    COST CHILD BIND_SENSITIVE BIND_AWARE SHARABLE FEEDBACK_STATISTIC    ROWS PARSE_CALLS
---- ----------- ------- ---------------- ------------ --------------- ------- ----- -------------- ---------- -------- ------------------ ------- -----------
   1 29.06 12:07       0      29.06 12:45   1912450710      2108937779  396162     0 N              N          N        Y                  1106472           1
   1 29.06 15:17       1      30.06 15:07  85765923098      3773907172 5597267     1 N              N          Y        N                        0           1

— т.е. 29.06 в 12:45 запрос выполнялся с нормальным планом 2108937779, а при следующем выполнении в 30.06 в 15:07 под действием FEEDBACK_STATISTIC=Y с новым планом 3773907172 наблюдается затянувшееся выполнение, что в принципе не является чем-то необычным, любопытно что в AWR таблице DBA_HIST_SQLSTAT оба выполнения записаны с одним планом 2108937779, что, мягко говоря, не способствует правильной диагностике (первая строка SNAP_ID=87604 соотв.1-му, последующие — 2-му выполнению):

12.1.0.2.@ SQL> @dba_hist_sqlstat "sql_id = '38dyq2ab12nju' and snap_id >= 87605"
 
INST BEGIN_SNAP_ID BEGIN_SNAP_TIME    EXECS ROWS_PROCESSED SQL_ID              PLAN       COST PARSE_PER_EXEC ELA_PER_EXEC CPU_PER_EXEC GETS_PER_EXEC DISK_READS_PER_EXEC READ_MB_PER_EXEC READS_PER_EXEC WRITES_MB_PER_EXEC WRITES_PER_EXEC DIRECT_WRITES_PER_EXEC ROWS_PER_EXEC FETCHES_PER_EXEC IOWAITS_PER_EXEC CLWAITS_PER_EXEC_US
---- ------------- --------------- -------- -------------- ------------- ---------- ---------- -------------- ------------ ------------ ------------- ------------------- ---------------- -------------- ------------------ --------------- ---------------------- ------------- ---------------- ---------------- -------------------
   2         87604 29.06 12:30            0              0 38dyq2ab12nju 2108937779     396162              0    553193496    117012000      10507935             1083047             8461         954997                910            7763                 116445       1106472           158068        462927031             3887793
   2         87609 29.06 15:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87610 29.06 15:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
...
   2         87655 30.06 14:00            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0
   2         87656 30.06 14:30            0              0 38dyq2ab12nju 2108937779     396162              0            0            0             0                   0                0              0                  0               0                      0             0                0                0                   0

(more…)

14.07.2015

Deduplicate LOB

Filed under: Блокировки,Oracle — Игорь Усольцев @ 01:17
Tags: ,

Разочаровал способ реализации в Oracle killer-фичи версии 11g — Deduplicate LOB через обычный странный UNIQUE индекс, который значительно ограничивает возможности транзакционного использования, в частности, конкурентного заполнения таблицы дублирующими LOB-ами

SQL> CREATE TABLE deduplicate_clob (clob_data  CLOB)
  2  LOB(clob_data) STORE AS SECUREFILE dedup_lob( DEDUPLICATE ENABLE STORAGE IN ROW )
  3  tablespace USERS
  4  /
 
Table created

SQL> -- индекс вместе с LOB-сегментом создаются сразу по созданию таблицы:
SQL> select * from dba_indexes where table_name = 'DEDUPLICATE_CLOB';
 
OWNER  INDEX_NAME                INDEX_TYPE  TABLE_NAME        UNIQUENESS COMPRESSION   LOGGING STATUS  GENERATED VISIBILITY SEGMENT_CREATED INDEXING
------ ------------------------- ----------- ----------------- ---------- ------------- ------- ------- --------- ---------- --------------- --------
SCOTT  SYS_IL0000105054C00001$$  LOB         DEDUPLICATE_CLOB  UNIQUE     DISABLED      YES     VALID   Y         VISIBLE    YES             FULL

SQL> -- , однако, для случая ENABLE STORAGE IN ROW конкурентная вставка коротких LOB-дублей допускается:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data'); -- в 1-й сессии без COMMIT-а
 
1 row inserted

SQL> -- и во 2-й сессии:
SQL> insert into deduplicate_clob (clob_data) values ('test CLOB data');
 
1 row inserted

SQL> -- при этом обе сессии вполне успешно получают две уникальные блокировки (неожиданно на разные ресурсы):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  355 TX       327699       6328          6          0         46          0          0
  371 TX        65547       5571          6          0         52          0          0

SQL> -- - т.е. уникальность индекса не задействована, и, как следствие, в вышеописанном случае STORAGE IN ROW дедубликация в смысле экономии места практически не функционирует (как и продекларированная уникальность LOB-индекса)
SQL>
SQL> -- Если же при создании определить свойства LOB как DEDUPLICATE DISABLE STORAGE IN ROW, или же, не пересоздавая таблицу, попытаться одновременно в 2-х сессиях вставить в LOB длинные значения:
SQL> insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000));
 
1 row inserted

SQL> -- получаем рудименты классического уникального индекса (проявляющиеся только при заполнении LOB-сегмента):
SQL> select * from v$lock where type = 'TX';
 
  SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
----- ---- ---------- ---------- ---------- ---------- ---------- ---------- ----------
  371 TX       458758       6029          6          0         16          1          0
  355 TX       458758       6029          0          4          5          0          0

SQL> -- с предсказуемым ожиданием на этом самом индексе:
SQL> @lock_tree
 
BLOCKING_TREE     USERNAME  EVENT                          REQ_OBJECT                           SECS_IN_WAIT BLOCK_SESSTAT SQL_TEXT                                                                       P1TEXT
----------------- --------- ------------------------------ ------------------------------------ ------------ ------------- ------------------------------------------------------------------------------ -------------------
INST#1 SID#371    SCOTT     SQL*Net message from client    LOB SCOTT.DEDUP_LOB                           604 NO HOLDER                                                                                    driver id driver id
  INST#1 SID#355  SCOTT     enq: TX - row lock contention  INDEX SCOTT.SYS_IL0000105054C00001$$          593 VALID         insert into deduplicate_clob (clob_data) values (rpad('test CLOB data',5000))  name|mode TX 4

SQL> -- - но в этом случае дедубликация действительно работает!

21.06.2015

Dynamic sampling (level=AUTO) в параллельных запросах с фиксированным планом выполнения

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

При выполнении параллельного запроса наблюдалась неприятная картина ожиданий:

12.1.0.2.@ SQL> @ash_sqlmon2 8d49sjc17xwuc
 
LAST_PLSQL  SQL_ID        PLAN_HASH_VALUE   ID PLAN_OPERATION                                                                   PX ASH_ROWS WAIT_PROFILE
----------- ------------- --------------- ---- -------------------------------------------------------------------------------- -- -------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Soft Parse  8d49sjc17xwuc       162625646    0 sql_plan_hash_value > 0; sql_exec_id is null                                     39    26689 library cache lock(19424); cursor: pin S wait on X(7223); kksfbc child completion(29); ON CPU(9); db file sequential read(3); db file scattered read(1)
Main Query  8d49sjc17xwuc       162625646    0   SELECT STATEMENT                                                                           
                                             1     PX COORDINATOR                                                                           
...
                                            38                                                           TABLE ACCESS FULL      19      339 db file scattered read(194); ON CPU(80); db file sequential read(64); latch: gcs resource hash(1)
                                            39                                                     TABLE ACCESS BY INDEX ROWID  19       96 ON CPU(90); db file sequential read(6)
                                            40                                                       INDEX RANGE SCAN           19      158 ON CPU(129); db file sequential read(29)
                                            41                                                   INDEX RANGE SCAN               19      834 db file sequential read(737); ON CPU(97)
                                            42                                                 TABLE ACCESS BY INDEX ROWID      19     5278 db file sequential read(4076); gc cr grant 2-way(597); ON CPU(378); gcs drm freeze in enter server mode(219); gc current block 2-way(5); latch: gcs resource hash(1); gc cr request(1); read by other session(1)
...
SQL Summary                             0    0 ASH fixed 1 execs from 41 sessions                                                     34181  ash rows were fixed from 29.05.2015 12:13:09 to 29.05.2015 12:30:48

— где наряду с «нормальными» ожиданиями по ходу выполнения (ID 38-42, например), преобладают типичные для стадии Soft Parse (sql_plan_hash_value > 0 AND sql_exec_id is null) ожидания library cache lock и cursor: pin S wait on X, вероятно, связанные с параллельным выполнением судя по кол-ву ожидающих PX-процессов (столбец PX)

Что можно проверить во-время (выполнения запроса) запущенным скриптом: (more…)

19.06.2015

Короткие ожидания enq: TX — row lock contention в запросах FOR UPDATE SKIP LOCKED

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

Пример использования блокировок на уровне строки наблюдали с Дмитрием Балабановым для запросов этого типа:

11.2.0.3.@ SQL> @ash_sql_wait_tree "event = 'enq: TX - row lock contention' and sql_id in (select sql_id from gv$sqlarea where upper(sql_fulltext) like '%FOR%UPDATE%SKIP%LOCKED%' and command_type = 3)"


LVL  INST_ID BLOCKING_TREE  EVENT                          WAITS_COUNT  EXECS_COUNT  AVG_WAIT_TIME_MS DATA_OBJECT               BLOCK_SID       SQL_ID        SQL_PLAN_LINE_ID SQL_PLAN_OPERATION SQL_TEXT
--- -------- -------------- ----------------------------- ------------ ------------ ----------------- ------------------------- --------------- ------------- ---------------- ------------------ ----------------------------------------
  1        2 (USER)         enq: TX - row lock contention         1766         1764                 0 MY.AQ_TABLE.              GLOBAL i#       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention         1163         1162                 0 MY.AQ_TABLE.              GLOBAL i#       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        2 (USER)         enq: TX - row lock contention          304          304                 0 MY.U_TAABLE.U_PARTITION   GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention          288          288                 0 MY.U_TAABLE.U_PARTITION   GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        2 (USER)         enq: TX - row lock contention           29           29                 0 MY.AQ_TABLE.              UNKNOWN i#      5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention           11           11                 0 MY.AQ_TABLE.              UNKNOWN i#      5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention            7            7                 0 MY.U_TAABLE.OEBS          GLOBAL i#       fkrrfw2jmg1jw                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention            7            7                 0 MY.U_TAABLE.U_PARTITION2  GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        2 (USER)         enq: TX - row lock contention            3            3                 0 MY.AQ_TABLE.              VALID i#1       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        2 (USER)         enq: TX - row lock contention            3            3                 0 MY.U_TAABLE.U_PARTITION2  GLOBAL i#       8x5xmcz928ydq                1 FOR UPDATE         SELECT                                   -- *
  1        1 (USER)         enq: TX - row lock contention            2            2                 0 MY.AQ_TABLE.              VALID i#2       5cbp4s5jq04xg                1 FOR UPDATE         select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */
  1        1 (USER)         enq: TX - row lock contention            1            1                 0 MY.U_LOCKS_TABLE.         GLOBAL i#       5c4mptv666r2x                1 FOR UPDATE         SELECT ID FROM U_LOCKS_TABLE WHERE NAME  -- *
  2        1   (USER)       On CPU / runqueue                        1            1                 0 ..                        NOT IN WAIT i#  5cbp4s5jq04xg                3 INDEX RANGE SCAN   select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */ -- **
  2        1   (USER)       On CPU / runqueue                        1            1                 0 MY.AQ$_AQ_TABLE_I.        NOT IN WAIT i#  5cbp4s5jq04xg                3 INDEX RANGE SCAN   select  /*+ INDEX(TAB AQ$_AQ_TABLE_I) */ -- **

— т.е. to skip any rows that are found to be already locked by another transaction, в ASH фиксируются формальные блокировки с 0-й длительностью AVG_WAIT_TIME_MS, вычисляемой как AVG(TIME_WAITED) / 1000

Среди запросов с использованием SKIP LOCKED, вролне естественно, много AQ-запросов, есть пользовательские запросы (*) и даже отмечена пара AQ-блокеров (**)

04.06.2015

12c: директивы плана выполнения — SQL Plan Directives

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

SQL Plan Directives (SPD) определяются как дополнительная информация/указания/инструкции, генерируемые и фиксируемые в бд в процессе выполнения запроса в тех случаях, когда оптимизатор на основе имеющейся статистики строит неточный в части оценки кол-ва возвращаемых строк (cardinality misestimate) план выполнения

В дальнейшем эти SPD инструкции, привязанные к объектам бд (таблицам, группам таблиц, столбцам и группам столбцов), используются:

  • в процессе Automatic Reoptimization (т.е. на основе статистики предыдущего выполнения стимулируя создание нового плана / дочернего курсора / hard parse запроса установкой флага V$SQL.IS_REOPTIMIZABLE=’Y’) для генерации более точного плана выполнения через форсированное выполнение Automatis Dynamic Statistics / Sampling (ADS) — запросов типа SELECT /* DS_SVC */ /*+ dynamic_sampling(0) …*/
  • для создания на основании этих SPD записей Extended Statistics по группам столбцов в процессе очередного ручного или автоматического сбора статистики с использованием процедур пакета DBMS_STATS

И поскольку и Automatis Dynamic Statistics — запросы, и автоматически создаваемая Extended Statistics легко могут стать причинами определённых неудобств после миграции на 12c, SPD директивы заслуживают внимания, например, в плане управления и контроля использования

1) Вкл/Выкл SPD (more…)

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) */ на уровне запроса, на что, впрочем, указывает и стандартная документация

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

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

Отслеживать

Настройте получение новых записей по электронной почте.

Присоединиться к ещё 164 подписчикам