Tamanho UNDO

[lmendes.cps]

Amigos,

Tenho ambiente com a seguinte configuração:

Oracle RAC Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit. SO: AIX

O cliente está questionando o tamanho da UNDO, que atualmente está em 106 Gb. Levantei o tamanho pela querie:

Selecionar tudo

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;  

Resultado:

Selecionar tudo

TABLESPACE_NAME    SIZE_ALLOCATED_MB     SIZE_USED_MB     PCT_SIZE_USED_MB
UNDOTBS1           71680                 59490,8125       82,99
UNDOTBS2           70335                 46978,1875       66,79

Fiz o calculo sugerido pela Oracle para determinar o tamanho da UNDO, diminui o tempo de retensão, obtive o valor de 30 Gb.
Segue a querie:

Selecionar tudo

select ((ur * (ups * dbs)) + (dbs * 24)/1024)/1024 as total 
  from (select value as ur 
          from v$parameter 
         where name = 'undo_retention'), 
       (select (sum(undoblks)/sum(((end_time - begin_time)*86400))) as ups 
          from v$undostat), 
       (select block_size as dbs 
          from dba_tablespaces 
         where tablespace_name= (select value from v$parameter where name = 'undo_tablespace'));

Com a alteração do tempo de retenção, o tamanho da UNDO não diminuiu para o tamanho esperado está em 95 Gb aproximadamente.
A aplicação, está usando aproximadamente 9 Gb deste total:

Selecionar tudo

select s.sid, s.username, s.PROGRAM, (v.rssize)/1024/1024 uso_mb
  from gv$rollstat v,  
       dba_rollback_segs d, 
       gv$transaction t, 
       gv$session s
where v.usn             = d.segment_id
       and t.ses_addr    = s.saddr
       and t.xidusn      = d.segment_id; 

Pesquisei, sobre esta diferença dos 86 Gb que estaria "sobrando" e cheguei a querie:

Selecionar tudo

select v.usn, (v.rssize)/1024/1024 uso_mb, d.owner, d.tablespace_name, d.status, d.segment_name, s.segment_type, s.owner
  from gv$rollstat v,  
       dba_rollback_segs d, 
       dba_segments s
where v.usn               = d.segment_id
       and d.segment_name = s.segment_name;

Resultado:

Selecionar tudo

USN  USO_MB      OWNER   TABLESPACE_NAME  STATUS  SEGMENT_NAME  SEGMENT_TYPE   OWNER
73   1220,17188  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU73$    TYPE2 UNDO     SYS
72   820,109375  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU72$    TYPE2 UNDO     SYS
71   956,109375  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU71$    TYPE2 UNDO     SYS
70   760,421875  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU70$    TYPE2 UNDO     SYS
69   865,109375  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU69$    TYPE2 UNDO     SYS
68   997,109375  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU68$    TYPE2 UNDO     SYS
67   867,109375  PUBLIC  UNDOTBS2         ONLINE  _SYSSMU67$    TYPE2 UNDO     SYS
...

Onde conclui que os segmentos de tipo 2 (TYPE2 UNDO - Gerados automaticamente pelo Oracle) são os que estão "consumindo" esta diferença.

Gostaria da ajuda dos amigos para saber se estou correto na analise, e se tem como diminuir o espaço utilizado pelos segmentos TYPE2.

Muito obrigado!

[fbifabio]

@lmendes.cps ,

"TYPE2 UNDO" é o tipo de segmento atual utilizado pelo tablespace de UNDO.

Quando você diminui o UNDO_RETENTION isso não necessariamente limpa os segmentos de UNDO que estavam sendo utilizados. A única forma atual zerar tudo é criar um novo tablespace de undo e configurar o Bd para utilizar o novo tablespace. você só conseguirá apagar o anterior se reiniciar o Bd ou após os segmentos serem liberados automaticamente pelo Oracle (infelizmente nunca consegui dessa forma).

[]s