Oracle mechanics

16.09.2013

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

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

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

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

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

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:     57606 03-Sep-13 16:00:08       486      30.6
  End Snap:     57607 03-Sep-13 17:00:15       474      28.7
   Elapsed:               60.11 (mins)
   DB Time:            1,814.62 (mins)                       -- симптом
...
Load Profile              Per Second    Per Transaction   Per Exec   Per Call
~~~~~~~~~~~~         ---------------    --------------- ---------- ----------
      DB Time(s):               30.2                0.7       0.02       0.07
       DB CPU(s):                7.9                0.2       0.00       0.02
       Redo size:       13,228,727.3          301,707.8                       -- причина
...
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time    Wait Class
------------------------------ ------------ ----------- ------ ------ -------------
db file sequential read           1,477,911      38,301     26   35.2 User I/O
DB CPU                                           28,498          26.2
gc buffer busy acquire              156,738       7,623     49    7.0 Cluster       -- симптом в RAC-конфигурации
log buffer space                     20,299       6,200    305    5.7 Configuration -- большой Avg wait, как следствие
log file sync                        15,557       5,837    375    5.4 Commit        -- --//--
...
~~~~~~~~         Load Average
               Begin       End     %User   %System      %WIO     %Idle
           --------- --------- --------- --------- --------- ---------
                9.76      5.38      50.8       3.7      23.5      44.0 -- высокий %WIO, как следствие
...
Global Cache and Enqueue Services - Workload Characteristics
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...
            Avg global cache cr block flush time (ms):    114.9
...
       Avg global cache current block flush time (ms):    200.6

- высокие времена GC block flush time также находятся в прямой зависимости от скорости генерации redo и нагрузки на LGWR:

SQL> @ash_wait_tree_hist "event = 'gcs log flush sync' and instance_number = 2" 57607 57607

LVL INST_ID BLOCKING_TREE EVENT                        WAITS_COUNT SESS_COUNT AVG_WA
--- ------- ------------- ---------------------------- ----------- ---------- ------
  1       2 (LMS0)        gcs log flush sync                   253          1      6
  1       2 (LMS1)        gcs log flush sync                   239          1      6
  2       2   (LGWR)      log file parallel write              417          1    217 -- LGWR не успевает писать и блокирует поцессы LMS*
  2       2   (LGWR)      On CPU / runqueue                     34          1      0
  2       2   (LGWR)      enq: CF - contention                  25          1    653
  2       2   (LGWR)      control file sequential read          11          1     93
  2       2   (LGWR)      LGWR wait for redo copy                5          1      5
...

Тот же LGWR, естественно, замедляет / блокирует пользовательские ожидания log file sync и log buffer space:

SQL> @ash_wait_tree_hist "event = 'log file sync' and instance_number = 2" 57607 57607

LVL INST_ID BLOCKING_TREE EVENT                        WAITS_COUNT SESS_COUNT AVG_WA
--- ------- ------------- ---------------------------- ----------- ---------- ------
  1       2 FOREGROUND    log file sync                        580        221    476
  2       2   (LGWR)      log file parallel write              487          1    284 -- основной блокер
  2       2   (LGWR)      enq: CF - contention                  33          1    584
  2       2   (LGWR)      On CPU / runqueue                     28          1      0
  2       2   (LGWR)      control file sequential read          24          1     99
  2       2   (LGWR)      LGWR wait for redo copy                4          1      5
...

SQL> @ash_wait_tree_hist "event = 'log buffer space' and instance_number = 2" 57607 57607

LVL INST_ID BLOCKING_TREE EVENT                        WAITS_COUNT SESS_COUNT AVG_WA
--- ------- ------------- ---------------------------- ----------- ---------- ------
  1       2 FOREGROUND    log buffer space                     644        106    734
  1       2 (Q001)        log buffer space                       3          1    660
  1       2 (SMON)        log buffer space                       1          1   3202
  2       2   (LGWR)      log file parallel write              501          1    835 -- основной блокер
  2       2   (LGWR)      enq: CF - contention                  88          1    886
  2       2   (LGWR)      control file sequential read          54          1     57
  2       2   (LGWR)      On CPU / runqueue                      5          1      0
  3       2     (ARC1)    control file sequential read          52          1     47 -- ARC-иверы блокируют LGWR, ожидающего на CF - contention
  3       2     (ARC6)    control file sequential read          20          1     71
  3       2     (ARC5)    control file parallel write           16          1    689

При проверке DML запросов, генерировавших поток redo с помощью простых скриптов:

SQL> @redogen_sql_hist "03-Sep-13 16:00" "03-Sep-13 17:00" 10

INST_ID WHEN              COMMAND  EXEC_DELTA ROWS_PROC_DELTA BUFFER_GETS_DELTA BUFFER_RANGE SQL_ID        SQL_TEXT
------- ----------------- -------- ---------- --------------- ----------------- ------------ ------------- ------------------------------------------------------
      2 2013_09_03 16:00  INSERT          130         6670395          45776858            4 b5tq6435p6j2h INSERT INTO XLA_DISTRIBUTION_LINKS
      2 2013_09_03 16:00  INSERT        16124         6464279         101892999            2 1urr1xwyc7m5w INSERT INTO XLA_AE_LINES_GT
      2 2013_09_03 16:00  UPDATE          127         5987775           6225992           19 b74pv9hjp67u7 UPDATE XLA_AE_LINES_GT
      2 2013_09_03 16:00  INSERT         8050         5925454          16466353           13 90mumbpp4s8ay INSERT INTO XLA_TRANSACTION_ACCTS_GT
      2 2013_09_03 16:00  UPSERT          123         4792261         116474802            1 0vjcg5zp30z2x MERGE /*+ use_hash(xal) */ INTO XLA_AE_LINES_GT
      2 2013_09_03 16:00  UPDATE          104         3277070          28030395            6 cuzx7rp43k50g UPDATE /*+ dynamic_sampling(1) */ XLA_AE_LINES_GT TEMP
      2 2013_09_03 16:00  UPDATE          104         3277070          25583332            7 fpjcgvudxsran UPDATE /*+ dynamic_sampling(1) */ XLA_AE_LINES_GT TEMP
      2 2013_09_03 16:00  DELETE          121         2706989          50393411            3 dccfw9vf6th9a DELETE FROM XLA_AE_LINES_GT
      1 2013_09_03 16:00  INSERT         5546         2225119          34956329            5 1urr1xwyc7m5w INSERT INTO XLA_AE_LINES_GT
      1 2013_09_03 16:00  INSERT         2771         2026051           5470975           20 90mumbpp4s8ay INSERT INTO XLA_TRANSACTION_ACCTS_GT

- можно заметить, что основная часть «тяжёлых» DML посвящена стандартным GTT таблицам XLA_AE_LINES_GT и XLA_TRANSACTION_ACCTS_GT. При этом столбец BUFFER_RANGE отражает место запроса по кол-ву логических чтений (buffer gets) — и тут временные таблицы также находятся на почётных местах

Большой вклад GTT DML подтверждает и статистика изменённых блоков по объектам:

SQL> @redogen_obj_hist "03-Sep-13 16:00" "03-Sep-13 17:00" 4

WHEN                 OBJECT_NAME                    DB_BLOCK_CHANGES PERCENT
-------------------- ------------------------------ ---------------- -------
13-09-03 16:00       XLA_AE_LINES_GT                        61764352  41.95 -- GTT
13-09-03 16:00       XLA_AE_LINES_GT_N4                     21056128  14.30 -- GTT index
13-09-03 16:00       XLA_EVENTS                             16318752  11.08
13-09-03 16:00       XLA_AE_LINES_GT_U1                     11748976   7.98 -- GTT index

- топ-4 самых многоизменяемых сегментов показывает, что на XLA_AE_LINES_GT (+ индексы) приходится более 60% изменённых системой блоков. Т.о., учитывая, что для GTT redo не генерируются напрямую, а только для записываемых при DML undo блоков — не менее 40% сгенерированных за проблемный период redo относятся к GTT и простое включение TEMP_UNDO_ENABLED=TRUE заметно снизит [в будующем] нагрузку в этом месте

2) и чтобы не забылось, — ссылка на пост Дж.Льюиса MV Refresh, где маэстро описывает экономичный (в плане redo/undo) «ручной» (never refresh) метод обновления матвью через EXCHANGE PARTITION [INCLUDING INDEXES] WITHOUT VALIDATION с отдельно приготовленной (direct path DML/CTAS) таблицей

А при выполнении дополнительного ALTER MATERIALIZED VIEW … CONSIDER FRESH — матвью может использоваться при значениях QUERY_REWRITE_INTEGRITY и STALE_TOLERATED, и TRUSTED (там же в комментах)

Метод может быть крайне полезен для версии 11g, а в 12c для тех целей уже есть готовая фича — Out-of-Place MV Refresh

About these ads

Комментарии (2) »

  1. Great Post. Glad to learn from you.
    Please post ash_wait_tree_hist.sql to your script section as well.

    комментарий от Oleg — 26.09.2013 @ 00:09 | Ответить


RSS-лента комментариев к этой записи. TrackBack URI

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

Тема: Rubric. Блог на WordPress.com.

Отслеживать

Get every new post delivered to your Inbox.

Join 114 other followers

%d bloggers like this: