4Dados Consultoria & Suporte

Oracle Partner Network

Monitorando a utilização da tablespace de UNDO

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:

ORA-01555 Snapshot Too Old

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.

TABLESPACE_NAME       SIZE_ALLOCATED_MB SIZE_USED_MB PCT_SIZE_USED_MB
--------------------- ----------------- ------------ ----------------
UNDOTS1                           30720      5502,25            17,91
UNDOTS2                           30720     9242,375            30,09
2 rows selected

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:

  SELECT bsu.inst_id,
         bsu.sid,
         bsu.serial#,
         bsu.username,
         bsu.program,
         esu.block_changes - bsu.block_changes block_changes
    FROM begin_set_undo bsu,
         (  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 6 DESC,
                   s.inst_id,
                   s.sid,
                   s.serial#,
                   s.username,
                   s.program) esu
   WHERE     bsu.inst_id = esu.inst_id
         AND bsu.sid = esu.sid
         AND bsu.serial# = esu.serial#
ORDER BY block_changes DESC;

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.

  SELECT btu.inst_id,
         btu.sid,
         btu.serial#,
         btu.program,
         etu.used_ublk - btu.used_ublk used_ublk
    FROM begin_transaction_undo btu,
         (  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) etu
   WHERE etu.inst_id = btu.inst_id AND etu.taddr = btu.taddr
ORDER BY used_ublk DESC;

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