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;