Oracle mechanics

30.11.2016

12c: Log File Sync, параметр _DB_MTTR_ADVICE и опасности увеличения кол-ва LGWR-workers

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

После switchover-а на аналогичное железо в разы выросло время ожидания log file sync:

12.1.0.2@ SQL> @ash_wait_tree "event = 'log file sync'" 0 "where inst_id = 1"
 
LVL BLOCKING_TREE         EVENT                         WAIT_CLASS WAITS_COUNT SESS_COUNT AVG_WA  EST_WAITS EST_AVG_LATENCY_MS
--- --------------------- ----------------------------- ---------- ----------- ---------- ------ ---------- ------------------
  1 (FOREGROUND)          log file sync                 Commit           37104       2019    395    1992400                 19 -- ну не 19 мс, конечно, но точно > 10 мс - недопустимо долго
  1 (J...)                log file sync                 Commit              37         35      0          0               1000
  2   (LGWR)              LGWR any worker group         Other            31107          1    518     134101                231 -- осн.блокер для log file sync
  2   (LGWR)              target log write size         Other             4053          1      7    1898397                  2
  2   (LGWR)              LGWR all worker groups        Other             1137          1    187      59184                 19
  2   (LGWR)              On CPU / runqueue                                 54          1      0          0               1000
  2   (LGWR)              enq: CF - contention          Other               19          1    316         60                317
  2   (LGWR)              control file sequential read  System I/O          12          1      1      10607                  1
  2   (LGWR)              control file parallel write   System I/O           7          1      1       6261                  1
  3     (LG..)            LGWR wait for redo copy       Other            25103          1    606      73038                340 -- осн.блокер для LGWR any worker group *
  3     (LG..)            log file parallel write       System I/O        6290          1    209     200335                 31
  3     (LG..)            LGWR worker group ordering    Other              406          1    223       2500                162
  4       (FOREGROUND)    On CPU / runqueue                              21073        354      0          0               1000
  4       (J...)          On CPU / runqueue                               3908         21      0          0               1000
  4       (LG..)          log file parallel write       System I/O         230          1    228       1014                227
  4       (LG..)          LGWR wait for redo copy       Other              176          1    198       1391                127
  4       (Q...)          On CPU / runqueue                                122          1      0          0               1000
  5         (FOREGROUND)  On CPU / runqueue                                176          4      0          0               1000

— при этом в качестве важного промежуточного блокера в вышеприведённой цепочке по частоте и продолжительности неожиданно проявилось LGWR wait for redo copy (*), что было отчётливо заметно в цепочке ожиданий, начиная с LGWR any worker group: (more…)

12.12.2014

Гарантированный DOP и параметр PARALLEL_MIN_PERCENT

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

Встречаются запросы с завидным потреблением временного табличного пр-ва для HASH JOIN-ов разного рода и других сортировочно-группировочных операций, при недостатке показывающие:

ORA-01652: невозможно увеличить временный сегмент до 64 в разделе TEMP

Одним из методов сокращения требуемых объёмов temp является использование параллельного выполнения благодаря которому, попутно с улучшением общей скорости выполнения запроса, можно съэкономить временное пр-во, используя бОльший объём PGA большИм кол-вом px-процессов

В этом случае бывает необходимо обеспечить определённый Degree Of Parallelism (DOP) для выполнения конкретного запроса, т.е. ограничить DOP «снизу», для чего теоретически идеально подходит механизм parallel statement queuing, однако установка параметра _PARALLEL_STATEMENT_QUEUING = TRUE не всегда срабатывает, например, при выполнении DBMS_SCHEDULER-ного задания (job) запросы без ожидания resmgr:pq queued, начинают выполняться непараллельно, невзирая на отсутствие свободных PX

Как оказалось, в заданиях DBMS_SCHEDULER отлично срабатывает другой параметр PARALLEL_MIN_PERCENT, со своими особенностями, но срабатывает и в PL/SQL блоке DBMS_SCHEDULER, и на уровне обычной пользовательской сессии

Итак, на сервере со следующими ресурсами: (more…)

03.06.2013

Параметр _very_large_object_threshold

Filed under: Oracle,Oracle new features,parameters — Игорь Усольцев @ 23:20
Tags: ,

English version

Начиная с Oracle 11.2 на выбор способа чтения блоков индекса между serial direct path read и буферизованным чтением (через db buffer cache SGA) при операции INDEX FAST FULL SCAN (IFFS) влияет параметр:

SQL> @param_ _very_large_object_threshold

NAME                         VALUE IS_DEF   DSC
---------------------------- ----- -------- -----------------------------------------------------
_very_large_object_threshold 500   TRUE     upper threshold level of object size for direct reads

Правильное значение этого параметра: процент от размера буферного кэша (точнее, параметра _db_block_buffers) при превышении которого индекс считается «большим» и в процессе IFFS будет использоваться direct path read, если же размер индекса меньше — IFFS будет выполняться стандартными чтениями блоков через буферный кэш SGA — в точности как показал Саян Малакшинов в своём блоге Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats), где кроме этого параметра описывает хинт INDEX_STATS(“OWNER”.”TABLE_NAME”, “INDEX_NAME”, scale, blocks=X), частовстречаемый / применяемый в SQL Profiles

В процессе тестирования на разных платформах, о котором мы договорились с Саяном, замечено также влияние значения параметра _very_large_object_threshold на управление direct path read с использованием event 10949 при полном сканировании таблиц (more…)

03.03.2013

Index Join vs Index Bitmap и использование db file parallel read при доступе к блокам таблицы

Filed under: heuristics,hints,Oracle,wait events — Игорь Усольцев @ 21:26
Tags: , ,

Сравнение методов соединения индексов Index Join и Index Bitmap, релизуемых, например, подсказками /*+ INDEX_JOIN */ и /*+ INDEX_COMBINE */ , показывает, что оптимизатор отдаёт предпочтение Index Join, даже в случае проигрыша по стоимости

Далее приведены простые тесты индексных методов, а также попутно полученный тесткейс использования операции db file parallel read на шаге TABLE ACCESS BY INDEX ROWID плана выполнения (more…)

02.03.2013

11.2: ожидание cursor: pin S wait on X при избыточной генерации курсоров параллельного выполнения

В почти идеальном топе ожиданий AWR версии 11.2.0.3 попалось странное :

Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
DB CPU                                           16,642          77.7
direct path read                  7,870,194       3,309      0   15.4 User I/O
log file sync                       154,270         292      2    1.4 Commit
cursor: pin S wait on X                 218         269   1235    1.3 Concurrenc -- ***
direct path read temp               157,474         204      1    1.0 User I/O

— нечастое ожидание cursor: pin S wait on X с огромной длительностью ~ 1.2 сек занимает больше процента DB time

Согласно классическим описаниям Troubleshooting ‘cursor: pin S wait on X’ waits. [ID 1349387.1]«cursor: pin S wait on X» Reference Note основными причинами ожидания являются:

  • частые или длительные hard parse
  • либо множественные копии курсора в shared pool — high version count

(more…)

18.12.2012

Использование временного пространства при параллельном выполнении

Filed under: AWR,commonplace,Oracle,PX,temp segment — Игорь Усольцев @ 00:39
Tags: , ,

Обратил на себя внимание меняющийся объём используемого временного табличного пространства при выполнении ежедневного задания по неатомарному обновлению матвью, по данным периодически заполняемой таблицы TEMP_TEMP_SEG_USAGE:

SQL> select trunc(date_time) AS DATE_TIME,
  2         max(SUM_TEMP_USAGE_MB) as MAX_SUM_TEMP_USAGE_MB
  3    from (select date_time,
  4                 round(sum(blocks) * t.block_size / 1024 / 1024) as SUM_TEMP_USAGE_MB
  5            from SYSTEM.TEMP_TEMP_SEG_USAGE ttu, dba_tablespaces t
  6           where sql_id in ('7q1grk5zza7f0')
  7             and ttu.TABLESPACE = t.tablespace_name
  8             and date_time > sysdate - 8
  9           group by date_time, t.block_size)
 10   group by trunc(date_time)
 11   order by trunc(date_time) desc
 12  /

 

DATE_TIME   MAX_SUM_TEMP_USAGE_MB
----------- ---------------------
13.12.2012                   3279
12.12.2012                  28813 -- ???
11.12.2012                   3095
10.12.2012                   3276
09.12.2012                   3277
08.12.2012                   3276
07.12.2012                   3095
06.12.2012                  26766 -- ???

— ничего необычного, конечно же, в этом нет: статистика выполнения запроса показывает прямую зависимость потребляемого объёма TEMP от количества использованных при выполнении PX процессов: (more…)

24.11.2012

Особенности расчёта cardinality в материализованных подзапросах при обновлении 11.1 -> 11.2

Filed under: CBO,heuristics,Oracle,parameters,SQL — Игорь Усольцев @ 11:10
Tags: , , , ,

После обновления 11.1.0.7 -> 11.2.0.3 БОЛЬШОЙ ЗАПРОС перешёл к бесконечным временам выполнения, причиной чему стала неточность определения cardinality некого подзапроса, вынесенного в секцию WITH основного запроса, и автоматически материализованного Oracle ввиду неоднократности использования

План проблемного подзапроса, выполняемого отдельно, без необходимости TEMP TABLE TRANSFORMATION показывает не вполне точные, но разумные оценки: (more…)

Таймауты параллельных ожиданий, или почему по умолчанию объекты бд создаются noparallel

Filed under: Oracle,parameters,PX,statistics,wait events — Игорь Усольцев @ 00:43
Tags: , , , ,

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

11.2.0.3.@ SQL> select /*+ noparallel */ *
  2    from view_history
  3   where order_id = 4181494
  4     and start_dt >= sysdate - 20
  5     and sysdate - 20 < end_dt
  6  /

no rows selected

Elapsed: 00:00:00.12

(more…)

21.09.2012

Влияние параметра optimizer_index_cost_adj на оптимизатор версии 11.1

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

Традиционно начинаю с неэффективно работающего запроса:

11.1.0.7.@ SQL> Select
Distinct ("EXP")
  From "SOME_PARTITIONED_TABLE"
 Where "PATH__LVL" < 10
   And ("PATH" In (Chr(9) || 'R' || Chr(9),
                   Chr(9) || 'R' || Chr(9) || 'v11' || Chr(9)))
/

------------------------------------------------------------+-----------------------------------+---------------+
| Id  | Operation              | Name                       | Rows  | Bytes | Cost  | Time      | Pstart| Pstop |
------------------------------------------------------------+-----------------------------------+---------------+
| 0   | SELECT STATEMENT       |                            |       |       |  6723 |           |       |       |
| 1   |  RESULT CACHE          | 9qyjpzzpk6tyu0r3wpj8un8pbd |       |       |       |           |       |       |
| 2   |   HASH UNIQUE          |                            |    21 |  1155 |  6723 |  00:02:35 |       |       |
| 3   |    PARTITION RANGE ALL |                            |    59 |  3245 |  6722 |  00:02:35 | 1     | 5     |
| 4   |     INDEX FULL SCAN    | MULTIFIELDS_INDX           |    59 |  3245 |  6722 |  00:02:35 | 1     | 5     |
------------------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
4 - access("PATH__LVL"<10)
4 - filter((INTERNAL_FUNCTION("PATH") AND "PATH__LVL"<10))

Строка RESULT CACHE в плане значения не имеет и определяется установленным в системе параметром:

SQL> show parameter result_cache_mode

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
result_cache_mode                    string      FORCE

Показательно, хотя и не очень важно в этом случае, что тот же запрос с одним условием выполняется по тому же «плохому» плану: (more…)

04.06.2012

Как форсировать разбор SQL при каждом выполнении: новый параметр _disable_cursor_sharing в 11.2.0.3

Filed under: bind variables,CBO,Oracle,parameters,Plan Management,SQL — Игорь Усольцев @ 01:52
Tags: , , , , , ,

Иногда (нечасто) возникает необходимость при каждом выполнении запроса со связанными переменными выполнять разбор этого запроса оптимизатором (hard parse) с целью генерации отдельного плана для каждого набора связанных переменных. Такая необходимость может возникать, например, в случае нечастого выполнения тяжёлых отчётов, для которых Oracle вполне в состоянии подобрать быстрый план, если бы в запросе не использовались связанные переменные и традиционный механизм повторного использования курсоров cursor sharing

Вариантами решения проблемы могли бы быть (Dion Cho: Making SQL always hard parsed): (more…)

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

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