Oracle DB: UNDO

Ссылки по теме

• Кто использует UNDO tablespace?
• How to monitor Undo Tablespace Usage and the Free Space in Oracle Database
• Frequent commits and ORA-01555
• ORA-01555 Snapshot Too Old

-- кто занимает UNDO
SELECT DISTINCT s.sid, s.serial#, s.schemaname, s.osuser, s.program,
t.start_time, t.used_ublk,
r.name rollback_segment_name,
st.sql_text, st_prev.sql_text prev_sql_text,
s.event
FROM v$session s, v$transaction t, v$rollname r, v$sql st, v$sql st_prev
WHERE
t.addr = s.taddr AND
t.USED_UBLK > 0 AND
t.xidusn = r.usn AND
st.SQL_ID(+) = s.SQL_ID AND
st_prev.SQL_ID(+) = s.SQL_ID
ORDER BY t.USED_UBLK DESC;

-- Занятые сегменты UNDO, их транзакции и сессии
select e.segment_name, round(e.blocks*8192/1024/1024) as sz_mb,
       s.sid, s.serial#, t.status, t.start_time,
       s.username, s.schemaname, s.osuser, s.program, s.module, s.action, s.event,
       round(t.used_ublk*8192/1024/1024) as used_sz_mb,
       sq.sql_text
from (
  select segment_name , sum(blocks) as blocks
  from dba_undo_extents
  where
--    status IN ('ACTIVE','UNEXPIRED')
    status IN ('ACTIVE')
  group by segment_name) e
left join v$rollname r on name = e.segment_name
left join v$transaction t on r.usn = t.xidusn
left join v$session s on t.addr = s.taddr
left join v$sql sq on s.sql_id = sq.sql_id; 

-- текущие транзакции
select s.sid,s.serial#,s.username,s.schemaname,s.osuser,s.process,s.machine,t.status,t.start_date,s.program,s.module,s.action,
       t.used_ublk,t.used_urec,t.log_io,t.phy_io,t.cr_get,t.cr_change
from v$transaction t, v$session s where t.ADDR=s.TADDR;

-- список подключенных табличных пространств UNDO
select * from dba_tablespaces
where contents = 'UNDO' and status = 'ONLINE';

-- проверка текущего размера UNDO
select round(sum(a.bytes)/1024/1024/1024,1) as undo_sz_gb
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#;

-- проверка свободного места в UNDO
select round(sum(bytes)/1024/1024/1024,1) as free_sz_gb from dba_free_space
where tablespace_name in (select tablespace_name from dba_tablespaces where contents = 'UNDO' and status = 'ONLINE');

-- проверка доступного места в UNDO
select tablespace_name, round(sum(blocks)*8192/1024/1024/1024,1) as reusable_sz_gb from dba_undo_extents where status='EXPIRED' group by tablespace_name;

-- проверка занятого места в UNDO
select tablespace_name , round(sum(blocks)*8192/1024/1024/1024,1) as used_sz_gb from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by  tablespace_name;

-- табличные пространства UNDO и их файлы
select a.tablespace_name, a.retention, c.name, round(c.bytes/1024/1024/1024,1) as SZ_GB 
from dba_tablespaces a
left join v$tablespace b on a.tablespace_name = b.name
left join v$datafile c on c.ts# = b.ts#  
where a.contents = 'UNDO' and a.status = 'ONLINE';

Выделение и освобождение места

Ролбэк сегменты в UNDO создают почти все запросы, в том числе SELECT для консистентного выполнения.

В UNDO выделенные экстенты получают статус

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

Рекомендуется задавать максимально большой период в параметре undo_retention, т.к. старые версии блоков данных часто используются при выполнении команды SELECT, а также функциями флешбэка.

Как и в других табличных пространствах, в UNDO свободным (Free) считается место выделенное файлам данных, но не выделенное экстентам данных. В UNDO выделенные экстенты никогда не освобождаются, но повторно используются, когда получают статус EXPIRED. Поэтому при оценке доступного места, необходимо учитывать не только свободное место, но и экстенты в статусе EXPIRED.

Управление UNDO

UNDO управляется тремя параметрами:

• UNDO_MANAGEMENT = AUTO | MANUAL, значение по умолчанию AUTO (начиная с версии 11)
  • AUTO - Oracle автоматически создает, включает, выключает, удаляет ROLLBACK сегменты
  • MANUAL - управление ROLLBACK сегментами выполняется полностью в ручном режиме
• UNDO_TABLESPACE = '<имя_табличного_пространства>', указывает автоматическому процессу в каком табличном пространстве необходимо создавать ROLLBACK сегменты. Если имеется другое табличное пространство UNDO в статусе ONLINE, то автоматический процесс будет в нем создавать ROLLBACK сегменты только в случае переполнения первого.
• UNDO_RETENTION = <количество_секунд>, значение по умолчанию 900 (15 мин), интервал в течении которого блоки данных считаются не устаревшими UNEXPIRED. Если установлено значение 0, то неактивные блоки сразу будут считаться устаревшими EXPIRED. При выделении новых блоков, всегда используются самые старые, поэтому даже в состоянии EXPIRED блоки будут хранится настолько долго насколько этого позволяет размер табличного пространства.

Гарантия сохранения неустаревших блоков (RETENTION GUARANTEE)

По умолчанию, табличное пространство UNDO не гарантирует сохранение неустаревших UNEXPIRED блоков в течение заданного интервала UNDO_RETENTION. В случае, когда транзакция изменяющая данные не может выделить место в UNDO, она может использовать UNEXPIRED блоки необходимые операции SELECT, что приводит к сбою SELECT и известной ошибке ORA-1555 "snapshot too old".

Если на табличном пространстве UNDO определена опция RETENTION GUARANTEE, то неустаревшие UNEXPIRED блоки не могут быть освобождены пока не истечет период UNDO_RETENTION.

Опцию RETENTION GUARANTEE можно задавать или отменять как при создании табличного пространства UNDO, так и после.

-- включение гарантии сохранения не устаревших блоков
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

-- выключение гарантии сохранения не устаревших блоков
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;