Рекомендовано для:
|
|
|
Традиционно, данные для отката транзакции хранятся в сегментах отката, до момента фиксации или отмены транзакции, путем выполнения COMMIT или ROLLBACK. Автоматическое управление UNDO, позволяет DBA указать, как долго хранить информацию после завершения транзакции, таким образом позволяя избежать ошибки: snapshot too old, при выполнении длительных запросов. Это достигается путем установки параметра UNDO_RETENTION. По умолчанию, его значение равно 900 секунд (15 минут), и изменяя этот параметр вы можете гарантированно увеличить, или уменьшить сроки хранения информации для отката. По большому счету, вы можете переложить на Oracle управление сегментами отката, вместо того, чтобы заниматься этим самому. Для включения автоматического управления, необходимо указать всего лишь один параметр UNDO_MANAGEMENT=AUTO. Если же требуется вернуть ручное управление, то значение параметра выставляется равным MANUAL. В сегментах отката есть три типа экстентов:
Последовательность использования экстентов выглядит следующим образом:
Далее представлены несколько запросов, которые помогут отслеживать использование 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 3 rows selected. Блоков UNDO в секунду: SELECT MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*3600*24)) "UNDO_BLOCK_PER_SEC" FROM V$UNDOSTAT; UNDO_BLOCK_PER_SEC Оптимальное значение 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';
Подсчет необходимого размера табличного пространства 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] Примеры состояния экстентовПредставленный ниже запрос позволяет увидеть текущее состояние экстентов и их статус. На основании результатов можно сделать определенные выводы и скорректировать, при необходимости, параметры базы данных, касающиеся 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, некоторые экстенты, со статусом UNEXPIRED, используются для согласованного чтения и экстенты со статусом EXPIRED, могут быть использованы повторно. Отсуствие свободных/EXPIRED экстентовSTATUS MB PERC Если система находится под нагрузкой, и экстентов со статусом EXPIRED нет, или их количество близко к нулю, а общее количество активных (ACTIVE) и не устаревших (UNEXPIRED) экстентов близится к 100%, и табличное пространство UNDO не может быть расширено, то Oracle начинает заимствовать экстенты со статусом UNEXPIRED, для текущих транзакций. Это может привести к ошибке ORA-01555, потому что не удовлетворяется требование UNDO_RETENTION. Отсуствие свободного пространства в табличном пространстве UNDOSTATUS MB PERC Если система находится под нагрузкой, и количество активных экстентов близко к 100%, а общее количество EXPIRED и UNEXPIRED экстентов близко к нулю, возможности расширить табличное пространство UNDO нет, и Oracle не может заимствовать экстенты, вы можете получить сообщение об ошибке: ORA-30036. Большой период удержания или маленький размер UNDOSTATUS MB PERC В этом случае, все 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 Поскольку 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 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) Так же можно попробовать выполнить запросы к представлениям V$SESSION_LONGOPS и V$TRANSACTION. | |