Oracle: Мониторинг использования табличного пространства UNDO

Источник: all-oracle

Рекомендовано для:
  • Oracle Database 9i R1
  • Oracle Database 9i R2
  • Oracle Database 10g R1
  • Oracle Database 10g R2
  • Oracle Database 11g R1
  • Oracle Database 11g R2
 

Традиционно, данные для отката транзакции хранятся в сегментах отката, до момента фиксации или отмены транзакции, путем выполнения COMMIT или ROLLBACK. Автоматическое управление UNDO, позволяет DBA указать, как долго хранить информацию после завершения транзакции, таким образом позволяя избежать ошибки: snapshot too old, при выполнении длительных запросов.

Это достигается путем установки параметра UNDO_RETENTION. По умолчанию, его значение равно 900 секунд (15 минут), и изменяя этот параметр вы можете гарантированно увеличить, или уменьшить сроки хранения информации для отката.

По большому счету, вы можете переложить на Oracle управление сегментами отката, вместо того, чтобы заниматься этим самому. Для включения автоматического управления, необходимо указать всего лишь один параметр UNDO_MANAGEMENT=AUTO. Если же требуется вернуть ручное управление, то значение параметра выставляется равным MANUAL.

В сегментах отката есть три типа экстентов:

  1. Unexpired - Данные, чей возраст не превысил период хранения, определяемый параметром UNDO_RETENTION
  2. Expired - Данные, чей возраст превысил период хранения, определяемый параметром UNDO_RETENTION. Эти экстенты больше не нужны для согласованного чтения.
  3. Active - Данные, являющиеся частью активной транзакции. Параметр UNDO_RETENTION не применим к таким экстентам, потому что никто не может сказать заранее, как быстро завершится транзакция, и, например, через 900 секунд вы не сможете выполнить откат.

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

  1. Новый экстент в табличном пространстве UNDO выделяется по мере возникновения потребности. Если достигается конец текущего экстента, и следующий экстент содержит данные с истекшим сроком жизни, то новые данные (генерируемые текущей транзакцией) будут записаны в этот экстент, содержащий старые данные.
  2. Если это не удается, из-за отсутствия доступных свободных экстентов, и отключена возможность автоматического расширения файла данных (AUTOEXTEND), то Oracle будет пытаться использовать экстенты с истекшим сроком давности из другого сегмента UNDO.
  3. Если попытка использовать экстенты другого сегмента не удается, то Oracle пытается использовать повторно экстенты, имеющие статус UNEXPIRED, в текущем сегменте отката.
  4. Если такая попытка терпит неудачу, то Oracle пытается использовать UNEXPIRED экстенты в других сегментах.
  5. Если все предыдущие попытки не увенчались успехом, то Oracle сообщит о недостатке свободного пространства, например: ORA-30036: unable to extend segment in Undo tablespace

Далее представлены несколько запросов, которые помогут отслеживать использование UNDO:
Статус UNDO:

SELECT   TABLESPACE_NAME,
         STATUS, 
         SUM (BLOCKS) * 8192 / 1024 / 1024 / 1024 GB
FROM     DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS;

TABLESPACE_NAME                STATUS            GB
------------------------------ --------- ----------
UNDOTBS                        EXPIRED   2.65649414
UNDOTBS                        UNEXPIRED 5.47167969
UNDOTBS                        ACTIVE       .015625

3 rows selected.

Блоков UNDO в секунду:

SELECT MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM V$UNDOSTAT;

UNDO_BLOCK_PER_SEC
------------------
        150.876667
  
1 row selected.

Оптимальное значение UNDO RETENTION при текущей активности:

SELECT D.UNDO_SIZE/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(E.VALUE,1,25) "UNDO RETENTION [Sec]",
       ROUND((D.UNDO_SIZE / (TO_NUMBER(F.VALUE) *
       G.UNDO_BLOCK_PER_SEC))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
       SELECT SUM(A.BYTES) UNDO_SIZE
          FROM V$DATAFILE A,
               V$TABLESPACE B,
               DBA_TABLESPACES C
         WHERE C.CONTENTS = 'UNDO'
           AND C.STATUS = 'ONLINE'
           AND B.NAME = C.TABLESPACE_NAME
           AND A.TS# = B.TS#
       ) D,
       V$PARAMETER E,
       V$PARAMETER F,
       (
       SELECT MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*3600*24))
              UNDO_BLOCK_PER_SEC
         FROM V$UNDOSTAT
       ) G
WHERE  E.NAME = 'undo_retention'
       AND F.NAME = 'db_block_size';

    
ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]      OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
                    8000 900                                               6787
1 row selected.

Подсчет необходимого размера табличного пространства UNDO при текущей нагрузке:

SELECT D.UNDO_SIZE / (1024 * 1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR (E.VALUE, 1, 25) "UNDO RETENTION [Sec]",
         (TO_NUMBER (E.VALUE) * TO_NUMBER (F.VALUE) * G.UNDO_BLOCK_PER_SEC)
       / (1024 * 1024)
          "NEEDED UNDO SIZE [MByte]"
FROM (SELECT SUM (A.BYTES) UNDO_SIZE
          FROM V$DATAFILE A, V$TABLESPACE B, DBA_TABLESPACES C
         WHERE     C.CONTENTS = 'UNDO'
               AND C.STATUS = 'ONLINE'
               AND B.NAME = C.TABLESPACE_NAME
               AND A.TS# = B.TS#) D,
       V$PARAMETER E,
       V$PARAMETER F,
       (SELECT MAX (UNDOBLKS / ( (END_TIME - BEGIN_TIME) * 3600 * 24))
                  UNDO_BLOCK_PER_SEC
          FROM V$UNDOSTAT) G
WHERE E.NAME = 'undo_retention' AND F.NAME = 'db_block_size';

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]      NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
                    8000 900                                     1060.85156
1 row selected.

Примеры состояния экстентов

Представленный ниже запрос позволяет увидеть текущее состояние экстентов и их статус. На основании результатов можно сделать определенные выводы и скорректировать, при необходимости, параметры базы данных, касающиеся UNDO:

SELECT STATUS,
  ROUND(SUM_BYTES / (1024*1024), 0) AS MB,
  ROUND((SUM_BYTES / UNDO_SIZE) * 100, 0) AS PERC
FROM
(
  SELECT STATUS, SUM(BYTES) SUM_BYTES
  FROM DBA_UNDO_EXTENTS
  GROUP BY STATUS
),
(
  SELECT SUM(A.BYTES) UNDO_SIZE
  FROM DBA_TABLESPACES C
    JOIN V$TABLESPACE B ON B.NAME = C.TABLESPACE_NAME
    JOIN V$DATAFILE A ON A.TS# = B.TS#
  WHERE C.CONTENTS = 'UNDO'
    AND C.STATUS = 'ONLINE'
);

Запрос суммирует три типа экстентов и показывает разбиение по ним содержимого табличного пространства UNDO. Свободные сегменты не учитываются.

Нормальная ситуация

STATUS            MB       PERC
--------- ---------- ----------
ACTIVE            10          4
EXPIRED          110         43
UNEXPIRED         25         10

Это пример нормальной ситуации. Система использует экстенты со статусом ACTIVE, некоторые экстенты, со статусом UNEXPIRED, используются для согласованного чтения и экстенты со статусом EXPIRED, могут быть использованы повторно.

Отсуствие свободных/EXPIRED экстентов

STATUS            MB       PERC
--------- ---------- ----------
ACTIVE           230         90
EXPIRED            0          0
UNEXPIRED         26         10

Если система находится под нагрузкой, и экстентов со статусом EXPIRED нет, или их количество близко к нулю, а общее количество активных (ACTIVE) и не устаревших (UNEXPIRED) экстентов близится к 100%, и табличное пространство UNDO не может быть расширено, то Oracle начинает заимствовать экстенты со статусом UNEXPIRED, для текущих транзакций. Это может привести к ошибке ORA-01555, потому что не удовлетворяется требование UNDO_RETENTION.

Отсуствие свободного пространства в табличном пространстве UNDO

STATUS            MB       PERC
--------- ---------- ----------
ACTIVE           255        100
EXPIRED            0          0
UNEXPIRED          1          0

Если система находится под нагрузкой, и количество активных экстентов близко к 100%, а общее количество EXPIRED и UNEXPIRED экстентов близко к нулю, возможности расширить табличное пространство UNDO нет, и Oracle не может заимствовать экстенты, вы можете получить сообщение об ошибке: ORA-30036.

Большой период удержания или маленький размер UNDO

STATUS            MB       PERC
--------- ---------- ----------
ACTIVE             2          1
EXPIRED            0          0
UNEXPIRED        254         99

В этом случае, все UNDO экстенты используются для обеспечения периода удержания (UNDO_RETENTION). Это может быть связано со слишком большим значением времени удержания, или табличное пространство UNDO слишком маленькое. В этом случае DBA должен принять решение, как быть.

Размер UNDO

Хранение данных для отката операций требует определенного места на дисковой системе, и определяется исходя из активности использования базы данных. В самом простом варианте, для расчета, можно применить формулу RETENTION * RATE = SPACE. Плюс добавятся накладные расходы, но в целом общую картину можно получить.

Как видно из уравнения, размер табличного пространства или время удержания не могут быть фиксированными. Время удержания не может быть фиксированным, потому что зависит от активности использования базы данных.

Начиная с Oracle 10g, использование становится более эффективным, если одна и та же запись обновляется более одного раза в пределах транзакции. В этом случае экстенты со статусом ACTIVE используются повторно.

Фиксированный размер

Если отменить автоматическое расширение (для файла данных установить AUTOEXTEND=NO), то Oracle автоматически настроит время удержания (UNDO RETENTION), чтобы вписаться в размер табличного пространства. Параметр UNDO_RETENTION будет использоваться по минимуму, но может быть автоматически настроен на большее значение, при наличии свободного пространства.

Проверить настроенное значение для UNDO_RETENTION можно выполнив запрос к представлению V$UNDOSTAT, колонка TUNED_UNDORETENTION.

В Oracle 9i, нет автоматической настройки, но есть возможность изменения времени удержания.

Для определения фиксированного размера табличного пространства, можно воспользоваться советом Undo Advisor.

STATUS            MB       PERC
--------- ---------- ----------
ACTIVE             2          1
EXPIRED            0          0
UNEXPIRED        254         99

Поскольку Oracle может продлить время удержания, то создается большее количество экстентов со статусом UNEXPIRED. В этом случае возможно заполнение табличного пространства. Если табличное пространство заполнено, проверьте TUNED_UNDORETENTION против UNDO_RETENTION, если настроенное значение больше, то заполненность на 99% не означает наличие проблемы.

Посмотрим на следующий запрос, он рассчитывает UNDO со следующими допущениями: ACTIVE - берется то, что требуется, EXPIRED - пустые и UNEXPIRED расчитывается как деление UNDO_RETENTION на TUNED_UNDORETENTION:

BREAK ON REPORT
COMPUTE SUM OF MB ON REPORT
COMPUTE SUM OF PERC ON REPORT
COMPUTE SUM OF FULL ON REPORT
SELECT status,
       ROUND (sum_bytes / (1024 * 1024), 0) AS MB,
       ROUND ( (sum_bytes / undo_size) * 100, 0) AS PERC,
       DECODE (
          status,
          'UNEXPIRED', ROUND ( (SUM_BYTES / UNDO_SIZE * FACTOR) * 100, 0),
          'EXPIRED', 0,
          ROUND ( (SUM_BYTES / UNDO_SIZE) * 100, 0))
          FULL
FROM   (  SELECT STATUS, SUM (BYTES) SUM_BYTES
            FROM DBA_UNDO_EXTENTS
        GROUP BY STATUS),
       (SELECT SUM (A.BYTES) UNDO_SIZE
          FROM DBA_TABLESPACES C
               JOIN V$TABLESPACE B
                  ON B.NAME = C.TABLESPACE_NAME
               JOIN V$DATAFILE A
                  ON A.TS# = B.TS#
WHERE C.CONTENTS = 'UNDO' AND C.STATUS = 'ONLINE'),
       (SELECT TUNED_UNDORETENTION,
               U.VALUE,
               U.VALUE / TUNED_UNDORETENTION FACTOR
          FROM V$UNDOSTAT US
               JOIN (SELECT MAX (END_TIME) END_TIME FROM V$UNDOSTAT) USM
                  ON USM.END_TIME = US.END_TIME
               JOIN (SELECT NAME, VALUE FROM V$PARAMETER) U
                  ON U.NAME = 'UNDO_RETENTION');

Полученный при выполнении запроса результат покажет, что UNDO_RETENTION = 900 и TUNED_UNDORETENTION равен примерно 1800 секунд:

STATUS            MB       PERC       FULL
--------- ---------- ---------- ----------
ACTIVE             2          1          1
EXPIRED            0          0          0
UNEXPIRED        254         99         50
          ---------- ---------- ----------
sum              256        100         51

UNEXPIRED экстенты на самом деле не проблема, потому что автоматически настроенное время удержания (TUNED_UNDORETENTION) в два раза больше чем желаемое, установленное пользователем (UNDO_RETENTION).

Начиная с Oracle 10g Release 2 введено максимальное время удержания. Наиболее длинный период, что встречался в моей практике - 72 часа. При этом пользователи не испытвали никаких проблем при работе с приложениями. Автоматическую настройку можно отключить, используя скрытый параметр _undo_autotune, и установив его значение равным false: _undo_autotune=false. Использовать его без разрешения Oracle не желательно. Дополнительно можно посмотреть документ My Oracle Support: ID 413732.1 : Full UNDO Tablespace In 10gR2.

Фиксированный размер и автоматическая настройка UNDO_RETENTION

Когда табличное пространство UNDO настроено на автоматическое расширение файлов данных, Oracle устанавливает время удержания равное времени, необходимого для самого длительного по выполнению запросу. Это может привести к появлению очень большого табличного пространства. Особенно это актуально при наличии плохо написанных, или не настроенных запросов - кандидаты на тюнинг.

Усечение (SHRINK) табличного пространства UNDO

Уменьшить табличное пространство UNDO нельзя, можно только увеличить. Если же необходимо уменьшить размер, топ ридется создать новое табличное пространство, обозначить его как используемое по умолчанию, выставив параметр UNDO_TABLESPACE, и удалить старое. Более подробно об этом рассказано в статье "Oracle: Изменение табличного пространства UNDO".

Установка RETENTION GUARANTEE

Следует помнить, что при создании UNDO с опцией RETENTION GUARANTEE, экстенты со статусом UNEXPIRED не будут заимствоваться для других транзакций. Устанавливать эту опцию можно, если планируется использовать целостное чтение или если планируется использовать FLASHBACK.

Но прежде чем выставлять эту опцию, стоит учесть, что вероятность получения ORA-30036 возрастает. И возможно, вам придется выбирать между ORA-30036 и ORA-01555.

Установка параметра UNDO_RETENTION на основании самого длинного запроса

Распространенной практикой является установка параметра UNDO_RETENTION равным времени выполнения самого длинного запроса, что бы избежать ошибки ORA-01555. Для получения информации о самом длинном запросе, за последние 7 дней, выполните запрос:

SELECT MAX(MAXQUERYLEN) FROM V$UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
            2204
1 row selected.

Так же можно попробовать выполнить запросы к представлениям V$SESSION_LONGOPS и V$TRANSACTION.


Страница сайта http://test.interface.ru
Оригинал находится по адресу http://test.interface.ru/home.asp?artId=29897