Oracle: Моніторинг використання табличного простору UNDO








Рекомендовано для:

  • 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.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*