Você já parou para pensar como o banco de dados Oracle consiste as informações que estão sendo modificadas pelas transanções?

Pois bem isso é feito através da tablespace de UNDO.

As informações da tablespace de Undo são utilizadas pelo banco para:  

• Desfazer transações quando é emitido um comando de ROLLBACK
• Recuperar o banco de dados
• Realizar leituras consistentes
• Verificar os dados utilizando Oracle Flashback Query
• Recuperar corrupções lógicas(Oracle Flashback)

A tablespace de UNDO, assim como outras possui um limite de tamanho, e é preciso estar atento a erros como por exemplo:

Daí a necessidade de monitorar a área utilizada, e principalmente, o crescimento súbito dessa utilização.

A consulta abaixo utiliza a visão DBA_UNDO_EXTENTS para obter informações a respeito dos extents de undo, e dessa forma calcular a área utilizada.

   SELECT size_allocated.tablespace_name,
          size_allocated.size_allocated_mb,
          size_used.size_used_mb,
          ROUND ( size_used.size_used_mb /
          size_allocated.size_allocated_mb *
          100,  2 ) pct_size_used_mb
     FROM ( SELECT due.tablespace_name,
                   SUM (due.BYTES) / 1024 / 1024 AS size_used_mb
              FROM dba_undo_extents due
          GROUP BY due.tablespace_name) size_used,
            ( SELECT dt.tablespace_name,
                     SUM (ddf.BYTES) / 1024 / 1024 size_allocated_mb
                FROM dba_tablespaces dt, dba_data_files ddf
               WHERE dt.tablespace_name = ddf.tablespace_name
                 AND dt.CONTENTS = 'UNDO'
            GROUP BY dt.tablespace_name) size_allocated
    WHERE size_allocated.tablespace_name =
          size_used.tablespace_name(+)
 ORDER BY tablespace_name 

Como resultado você terá os valores percentuais de ocupação nessas tablespaces.

No exemplo acima, são exibidas duas tablespaces de undo por se tratar de um ambiente com Oracle RAC com dois nós, o que exige que, cada instância possua sua própria UNDO.

Agora que sabemos o percentual utilizado, devemos descobrir quais sessões estão gerando essa ocupação, pode ser que um usuário esteja executando um procedimento que venha a ocupar desnecessariamente essa área e erros ORA-01555 venham a ocorrer.

Existem duas formas de encontrar as sessões que estão gerando a ocupação, e são feitas através de consultas nas visões gv$session, gv$sess_io e gv$transaction.

Em ambas as formas, é preciso utilizar o que conhecemos por delta, que em resumo, por se tratar de visões dinâmicas.

Para o primeiro exemplo precisamos fazer uma coleta das informações, e realizarmos comparações entre os valores atuais e o realizado na primeira coleta.

Faremos a primeira coleta, utilizando um CTAS conforme abaixo:

DROP TABLE BEGIN_SET_UNDO;

CREATE TABLE BEGIN_SET_UNDO AS
        SELECT s.inst_id, s.sid, s.serial#,
               s.username, s.program, i.block_changes
          FROM gv$session s, gv$sess_io i
         WHERE s.inst_id = i.inst_id AND s.sid = i.sid
      ORDER BY i.block_changes DESC, s.inst_id, s.sid,
               s.serial#, s.username, s.program;

Vamos aguardar alguns minutos, e executar a consulta abaixo, fazendo uma relação entre o snapshot que realizamos com o CTAS e o status atual:

Como resultado, teremos as sessões que estão transacionando mais no ambiente.

Grandes diferenças na coluna block_changes, denotam a geração de redo por parte da sessão.

A outra forma de encontrar essa informação é através de consulta na gv$transaction.

De forma semelhante a anterior, é preciso realizar o “delta” entre as consultas, por isso, mais uma vez utilizaremos um CTAS:

DROP TABLE begin_transaction_undo;

CREATE TABLE begin_transaction_undo AS
   SELECT s.inst_id, s.sid, s.serial#, s.username, s.program,
          s.taddr, t.used_ublk, t.used_urec
     FROM gv$session s, gv$transaction t
    WHERE s.inst_id = t.inst_id AND s.taddr = t.addr
 ORDER BY 6 DESC, 7 DESC, 1, 2, 3, 4, 5;

Após tirarmos um snapshot, utilizando novamente a view, vamos fazer a comparação.

Após tirarmos um snapshot, utilizando novamente a view, vamos fazer a comparação.

 INST_ID       SID    SERIAL# PROGRAM     USED_UBLK
-------- --------- ---------- ----------- ---------
       2      2124       7210 SOFTECH.EXE       116
       1      1950      13512 SOFTECH.EXE        14
       2      2047      39537 SOFTECH.EXE         4
       1      1955       4160 SOFTECH.EXE         2
       1      1963       5733 DANGO.EXE           1
       1      1832       4631 SOFTECH.EXE         1
       2      1863       4811 DANGO.EXE           1

Com essas informações, será possível a você identificar as sessões que estão levando a ocupação das suas tablespaces de UNDO ao limite.

Esperamos ter ajudado e até o próximo artigo.

   SELECT size_allocated.tablespace_name,
          size_allocated.size_allocated_mb,
          size_used.size_used_mb,
          ROUND ( size_used.size_used_mb /
          size_allocated.size_allocated_mb *
          100,  2 ) pct_size_used_mb
     FROM ( SELECT due.tablespace_name,
                   SUM (due.BYTES) / 1024 / 1024 AS size_used_mb
              FROM dba_undo_extents due
          GROUP BY due.tablespace_name) size_used,
            ( SELECT dt.tablespace_name,
                     SUM (ddf.BYTES) / 1024 / 1024 size_allocated_mb
                FROM dba_tablespaces dt, dba_data_files ddf
               WHERE dt.tablespace_name = ddf.tablespace_name
                 AND dt.CONTENTS = 'UNDO'
            GROUP BY dt.tablespace_name) size_allocated
    WHERE size_allocated.tablespace_name =
          size_used.tablespace_name(+)
 ORDER BY tablespace_name