UNDO_MANAGEMENT: Erros, Conceitos e Análise de situação

UNDO_MANAGEMENT

Blz, pessoal ? Para quem está enfrentando erros de UNDO esgotado/indisponível (tipicamente, erro “ORA-30036: unable to extend segment by … in undo tablespace ‘….'”, ou erro de dados não encontrados para leitura consistente (tipicamente erro “ORA-01555: snapshot too old: rollback segment number .. with name “….” too small”) vou passar alguns scripts úteis para Análise de situação, e além disso o mais Importante, que é o básico dos Conceitos envolvidos pra vc poder INTERPRETAR o output deles…

Primeiro, o UNDO (antigamente era chamado de ÁREA DE ROLLBACK, mas vamos na nomenclatura corrente) nada mais é do que uma CÓPIA dos dados do jeito que estavam antes de uma transação os alterar – isso é porque um database ORACLE TEM QUE ATENDER à um dos FUNDAMENTOS da teoria de RDBMS, que é : dados que estão sendo alterados ABSOLUTAMENTE NUNCA PODEM SER LIDOS por outras sessões , então cfrme uma transação altera um dado, a versão ANTERIOR desse dado vai pra área de UNDO, e são ESSES dados que as sessões outras vão enxergar/vão ler, ENQUANTO a transação não for encerrada via COMMIT ou ROLLBACK, isso é Connceito Fundamental… Assim, SE vc está tendo algum erro de área de UNDO esgotando ou coisa do tipo, é praticamente CERTO que vc TEM SIM TRANSAÇÕES ABERTAS alterando dados…

Segundo, a questão de SEGMENTOS e EXTENTs : por questão de performance, se um objeto qualquer (tabela, índice, partição, não importa) precisa alocar espaço em disco, NÃO É ALOCADO apenas e tão somente os poucos bytes que os dados ocupariam – como essa alocação envolve formatar blocos e fazer I/O, o SGBD já vai no disco e já aloca um monte de blocos de uma vez só , de forma que as futuras necessidades de espaço já usam espaço desse monte de blocos, monte esse que se chama EXTENT… O conjunto de extents forma um objeto lógico chamado SEGMENT…

No caso específico do UNDO, vc até pode optar por criação e gerenciamento manual dos UNDO SEGMENTs mas o recomendado / best practice é usar o gerenciamento Automático (parâmetro UNDO_MANAGEMENT como AUTO) : sendo AUTO, aí o SGBD ORACLE vai criando os UNDO SEGMENTs sozinho (com um nome automático tipo _SYSSMUxxx$ , onde xxx é um número que não se repete, um ID) para cada Transação (a ídéia ao fazer cada transação usar o seu UNDO SEGMENT é tentar obter algum tipo de “balanceamento”, de usar diferentes segmentos de cada vez) , e cfrme vai sendo preciso, os extents vão sendo criados dentro de cada segment….

STATUS e gerenciamento de espaço de UNDO : primeiro, assim que a tablespace é criada, basicamente TODO o espaço dela nunca foi usado, ele tá lá registrado na lista de espaços livres (que consultamos na DBA_FREE_SPACE).. Em relação ao espaço livre e usado , embora dentro de um UNDO SEGMENT nós tenhamos dados REAIS (repito, por definição o que vc tem lá dentro é a ultima versão, “quente”, dos dados como estavam ANTES da transação os alterar) , esses dados só precisam ser mantidos ENQUANTO a transação não é encerrada – assim sendo, da mesma forma que acontece nos extents dos segmentos temporários, os extents de undo assim que deixam de ser ATIVOS, não precisam mais ser mantidos… O ponto é : como eles podem ser grandes, quando os dados ficam desnecessários/não ativos, ao invés de gastar recursos Apagando/des-formatando os blocos dos extents e os devolver para o espaço livre, o SGBD ORACLE simplesmente marca esses extents como REUSÁVEIS, e as próximas utilizações vão gravar dados por cima dessses dados que estavam lá antes e agora são desnecessários…Em resumo : a DBA_FREE_SPACE mostra o espaço da tablespace (seja de UNDO seja de dados) que NUNCA foi usado, e para vc saber o que REALMENTE se tem disponível, é preciso SOMAR o conteúdo da DBA_FREE_SPACE com as views administrativas que mostram os dados que são REUSÁVEIS, no caso de UNDo seriam a view DBA_UNDO_EXTENTS e a view V$TRANSACTION.

Scripts e demonstração de uso

Espaço livre na tablespace de UNDO (que, repetindo, mostra o espaço que NUNCA foi alocado) :

select sum(bytes) as BYTES from dba_free_space where tablespace_name='UNDOTBS1'; -- usar o nome da SUA tablespace de UNDO

              BYTES
-------------------
         77.463.552

Não estando esgotada a área de UNDO (ie, ainda existem blocos re-usáveis no UNDO), crio duas transações diferentes, em sessões diferentes :

SYSTEM#1@xepdb1::CNTNR=XEPDB1> update HELP set INFO='xxxx';

Em outra sessão :

SYSTEM#2@xepdb1::CNTNR=XEPDB1> create table BIG_TABLE tablespace USERS as (select * from DBA_OBJECTS);
SYSTEM#2@xepdb1::CNTNR=XEPDB1> update BIG_TABLE set OBJECT_NAME='XXXXXXXXXXX';

Consulto free space, veja que NÃO diminuiu :

select sum(bytes) as BYTES from dba_free_space where tablespace_name='UNDOTBS1'; -- usar o nome da SUA tablespace de UNDO

              BYTES
-------------------
         77.463.552

Script que mostra o consumo Atual de UNDO nas transações :

select DISTINCT 
          r.name                              rbs
 ,        s.username                          username
 ,        s.sid                               sid
 ,        s.serial#                           "serial#"
 ,        substr( t.start_time, 10, 5)        "Start"
 ,        t.used_ublk * p.value / (1024*1024) "Undo Mb"
 ,        t.used_ublk                         "Undo blocks"
 ,        t.STATUS
 ,        to_date(start_time, 'MM/DD/RR HH24:MI:SS') as START_DATE
 ,        t.FLAG
 ,        t.SPACE
 ,        t.RECURSIVE
 ,        t.NOUNDO
 ,        t.PTX
 ,        t.NAME
 ,        t.LOG_IO
 ,        t.PHY_IO
 ,        t.CR_GET
 ,        t.CR_CHANGE
 ,        t.START_SCN
 ,        t.DEPENDENT_SCN
 ,        q1.sql_text                         "Current SQL"
 from     v$transaction                       t
 ,        v$rollname                          r
 ,        v$session                           s
 ,        v$parameter                         p
 ,        v$sql                               q1
 where  t.addr                               = s.taddr
   and  t.xidusn                             = r.usn
   and  s.sql_address                        = q1.address(+)
   and  s.sql_hash_value + DECODE
                          ( SIGN
                            ( s.sql_hash_value
                            )
                          , -1, POWER( 2, 32)
                          ,0
                          )                  = q1.hash_value(+)
   and p.name                                = 'db_block_size'
 order by 7;

RBS                 USERNAME SID serial# Start    Undo Mb Undo blocks STATUS START_DATE                FLAG SPA REC NOU PTX NAME                LOG_IO     PHY_IO     CR_GET  CR_CHANGE  START_SCN DEPENDENT_SCN Current SQL                   
------------------- -------- --- ------- ----- ---------- ----------- ------ ------------------- ---------- --- --- --- --- --------------- ---------- ---------- ---------- ---------- ---------- ------------- ------------------------------
_SYSSMU6_378927244$ SYSTEM   386   51908 12:55   ,1171875          15 ACTIVE 04/07/2022 12:55:26   67112451 NO  NO  NO  NO                        2961          0         17          0    2832894             0 
_SYSSMU4_659303195$ SYSTEM    13   59760 12:13 13,546875         1734 ACTIVE 04/07/2022 12:13:27       3587 NO  NO  NO  NO                      475403       1260       3215          0    2831139             0 

Encerro uma das Transações :

SYSTEM#1@xepdb1::CNTNR=XEPDB1> rollback;

Re-executando o script, vejo que a transação já não mais está registrada na V$TRANSACTION :

RBS                   USERNAME SID SERIAL# Start    Undo Mb Undo blocks STATUS START_DATE                 FLAG SPA REC NOU PTX NAME                LOG_IO     PHY_IO     CR_GET  CR_CHANGE  START_SCN DEPENDENT_SCN Current SQL                 
--------------------- -------- --- ------- ----- ---------- ----------- ------ -------------------  ---------- --- --- --- --- --------------- ---------- ---------- ---------- ---------- ---------- ------------- ---------------------------
_SYSSMU4_1624059398$  SYSTEM    13   59760 12:13  13,546875        1734 ACTIVE 04/07/2022 12:13:27        3587 NO  NO  NO  NO                      475403       1260       3215          0    2831139             0

Veja que os extents da transação comitada passarem a não ser mais Ativos :

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

STATUS     TABLESPACE_NAME                              SUM(BYTES)   COUNT(*)
---------- ------------------------------ ------------------------ ----------
EXPIRED    UNDOTBS1                                      5,636,096         71
UNEXPIRED  UNDOTBS1                                      5,898,240         15
ACTIVE     UNDOTBS1                                     15,925,248         18

Resumidamente, sobre o STATUS: Os extents ACTIVE contém versão anterior dos dados sendo alterados por transação Ativa – uma vez que a transação deixa se ser Ativa, o parâmetro de banco UNDO_RETENTION estabelece um período de tempo que o extent fica como UNEXPIRED, passado esse tempo o extent de undo é marcado como EXPIRED.

O algoritmo de alocação (bem resumidamente, coisas como datafiles Auto-extensíveis e RETENTION GUARANTEE podem alterar) é : quando uma Transação precisa de mais undo primeiro o SGBD tenta reusar extents com STATUS=EXPIRED, se todos eles já foram consumidos ele tenta criar novo extent formatando espaço free, se isso não for possível aí o SGBD tenta usar espaço dos segments UNEXPIRED, que ainda não completaram o tempo de retenção…

OBS: Como informação complementar, para listarmos os SEGMENTOS DE UNDO, pode se usar :

select * from dba_rollback_segs;

Notar que, como dito antes, ao encerrar a Transação e ~liberar~ logicamente os dados, o espaço NÃO volta para a DBA_FREE_SPACE :

SYSTEM#2@xepdb1::CNTNR=XEPDB1> commit;

=Veja a situação de free espace e de undo extents :

select sum(bytes) as BYTES from dba_free_space where tablespace_name='UNDOTBS1'; -- usar o nome da SUA tablespace de UNDO

              BYTES
-------------------
         77.463.552

SELECT DISTINCT STATUS,TABLESPACE_NAME, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS, TABLESPACE_NAME;

STATUS    TABLESPACE_NAME                              SUM(BYTES)   COUNT(*)
--------- ------------------------------ ------------------------ ----------
UNEXPIRED UNDOTBS1                                     20.512.768         28
EXPIRED   UNDOTBS1                                      5.832.704         59

OU SEJA , os 15 MB que a transação Ativa estava consumindo voltaram para a situação de UNEXPIRED, ie, dados que já não pertencem à nenhuma transação Ativa mas ainda não passou o tempo determinado pelo parâmetro UNDO_RETENTION desde que os dados foram Liberados…

OBS: A idéia da Oracle ao implementar o TEMPO DE RETENÇÃO de dados de um undo extent é tornar mais improvável que dados consistentes que alguma query longa precise ler sejam menos prováveis de serem sobrescritos – como dito acima, no algoritmo o Oracle sempre vai tentar consumir o espaço de undo dos segmentos EXPIRED primeiro, se a tua maior query leva, digamos, meia hora para rodar completamente e vc indicar 90 minutos pra UNDO_RETENTION, muito provavelmente esses dados VÃO estar em extents UNEXPIRED, que só são consumindos se TODOS OS EXPIRED já foram usados…

Script para mostrar dados de cada segmento de UNDO e soma dos extents :

select
  r.tablespace_name,
  r.status as STATUS_UNDO_SEGMENT, /* ONLINE, OFFLINE */
  u.status as STATUS_UNDO_EXTENT, /* UNEXPIRED, EXPIRED, ACTIVE */
  sum(u.bytes)/1024/1024 MB
from dba_rollback_segs r, dba_undo_extents u
where r.segment_name=u.segment_name
group by r.tablespace_name,r.status,u.status
order by r.tablespace_name,r.status,u.status;

Script geral para verificar disponibilidade de UNDO, constrastando espaço total dos datafiles de undo contra o espaço consumido:

WITH free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f
                   group by tablespace_name 
                ) ,
           a as ( select tablespace_name , sum(case when status = 'EXPIRED'                then blocks end)*8/1048576 reusable_space_gb , 
                                           sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb
                    from dba_undo_extents
                   where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED')
                   group by tablespace_name 
                ) ,
     undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb
                    from dba_tablespaces ts join dba_data_files df using (tablespace_name)
                   where ts.contents = 'UNDO' and ts.status = 'ONLINE' 
                )
SELECT tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , 
       free_gb + reusable_space_gb + allocated_gb total
  FROM undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;
  
TABLESPACE_NAME                 USER_SZ_GB     FREE_GB REUSABLE_SPACE_GB ALLOCATED_GB       TOTAL
------------------------------ ----------- ----------- ----------------- ------------ -----------
UNDOTBS1                       ,0966796875 ,0719604492      ,00439453125   ,020324707 ,0966796875
  
------------------------------------------------------------------------------------------------------

Referências

• Documentação Oracle
• Troubleshooting ORA-01555 – Snapshot Too Old: Rollback Segment Number “String” With Name “String” Too Small’ (Doc ID 1580790.1)
• ‘How To Check the Usage of Active Undo Segments in AUM’ (Doc ID 1337335.1)
• IF: How to monitor Undo Tablespace Usage and the Free Space’ (Doc ID 1951400.1)
• Artigo em https://blog.toadworld.com/how-does-oracle-reuse-expired-and-unexpired-undo-extents

Observações gerais

1. estes scripts usaram as views administrativas V$xxx : se for preciso repetir as consultas num database em RAC cluster, referenciar as views GV$xxx cfrme preciso, e adaptar os scripts para incluir INST_ID
2. para o troubleshoot de uma situaçao de UNDO esgotado, a sugestão é executar os scripts acima indicados diversas vezes e salvar/manter os resultados, permitindo comparação entre as diversas medidas : porém, se o database usa UNDO automático gerenciado pelo próprio SGBD Oracle (recomendado) a view V$UNDOSTAT vai coletar a cada 10 minutos informação de consumo de UNDO, número de transações ativas, qtdade de blocos EXPIRED/UNEXPIRED/ACTIVE (ie, blocos pertencentes a undo extents expirados, não expirados e ativos, Bem como (se houver AWR ativo no database) será mantido Histórico desses dados : vide nota de Suporte Oracle ‘IF: Understanding V$UNDOSTAT and DBA_HIST_UNDOSTAT Views’ (Doc ID 1951693.1)