Análisis de consumo de espacio REDO global, por cada sesión y por cada sentencia

El consumo de espacio de redo (redo consumption) es algo inevitable, aunque es posible minimizarlo en ciertos casos y con ciertas operaciones, no se puede cancelar por completo. El redo es necesario para asegurar que ante una caida imprevista de la base, los datos en los bloques modificados, commiteados y todavia no persistidos en disco (dirty blocks), puedan recuperarse al levantar nuevamente la base con un proceso automatico denominado "rolling forward".

Si la base esta en modo archivelog, cada redo log se copia aparte para que no se sobreescriba y asi permitir, cuando se lo requiera, por ejemplo, poder realizar backup con la base online, ir a un estado anterior de la base, recuperar una base usando el ultimo backup full y aplicando los archives, etc.

Si el consumo de redo es importante, la I/O se va a ver comprometida y podría afectar el rendimiento general de la base. Recordar que la escritura en redo es secuencial, distinta a la escritura en datafiles. Siempre alojar los redo sobre raid 1 o similares y separados de los datafiles. Además, tambien tener en cuenta que con cada commit se debe escribir en redo en forma sincronica. Esto significa serializar, y por lo tanto debe ser lo mas eficiente posible.

Para que puedan medir el consumo de redo, y en consecuencia cantidad de archives generados, en sus bases, les paso una serie de metodos y consultas que uso habitualmente. Las consultas permiten obtener lo siguiente:

• Consumo de Redo de la ultima hora
• Consumo de Redo por sesión
• Consumo de Redo por Consultas (*)

(*) El consumo por sentencia no se puede obtener en forma directa, entonces armé un procedimiento para ir guardando espacio por sqlid. Esta forma puede no ser muy precisa en ciertas ocasiones. Tiene que usarse teniendo ciertos requisitos y consideraciones. Puede ser muy util para testear el consumo de redo de una aplicación antes de la puesta en producción.

Para obtener consumo de redo global de la ultima hora (desde el ultimo snapshot de AWR) (redo consumption by DB)

Ejecutar la siguiente consulta que da el consumo de la base de datos desde el ultimo snapshot, es decir desde la ultima hora exacta. Es decir si lo ejecutamos a las 16:30, nos dará el consumo desde las 16hs para toda la base

select round((t1.value-t2.value)/1024/1024,2) "Consumo_Redo(MB)"
from
(select value from v$sysstat where name = 'redo size') t1,
(select value from dba_hist_sysstat
where stat_name = 'redo size'
and snap_id = (select max(snap_id) from dba_hist_sysstat)) t2

Para obtener consumo de redo por sesión (redo consumption by session)

Ejecutar la siguiente consulta, que da el consumo de redo por sesión. Considerar que el acumulado es desde que la sesión se abre

select se.INST_ID,
 se.SID,
 se.USERNAME,
 se.OSUSER,
 se.TERMINAL,
 se.PROGRAM,
 round(ss.value/1024/1024,2) "Redo Size(Mb)"
from gv$session se,
gv$sesstat ss,
v$statname sn
where se.SID = ss.SID
and ss.STATISTIC# = sn.STATISTIC#
and sn.NAME = 'redo size'
and username is not null
order by  "Redo Size(Mb)" desc

Para obtener consumo de redo por sentencia (redo consumption by sqlid/sentence)

Primero realizar el setup copiado a continuación (copiar el contenido en un archivo .sql y ejecutarlo desde sqlplus en el usuario elegido como repositorio) :

Rem
Rem  setup_redo_usage.sql (Redo Usage by Sentence/SQLID)
Rem
Rem  NOMBRE
Rem       setup_redo_usage.sql
Rem
Rem  DESCRIPCION
Rem       Configura la programacion de un job para recolectar         
Rem       información de uso de espacio de redo por sqlid.
Rem
Rem  NOTAS - REQUISITOS DE INSTALACION
Rem       El usuario que ejecute el script debera tener quota
Rem       suficiente sobre un tablespace auxiliar (ej:TOOLS)
Rem       para poder almacenar la informacion de monitoreo generada
Rem       cada 5".
Rem       Es necesario otorgar privilegios de SELECT sobre las
Rem       vistas dinamicas:
Rem
Rem       Conectado con sys hacer:
Rem
Rem       grant select on gv_$sesstat to ;
Rem       grant select on v_$statname to ;
Rem       grant select on gv_$session to ;
Rem       grant select on gv_$sqlarea to ;
Rem      
Rem
Rem  MODIFICADO (DD/MM/YY)
Rem
Rem
Rem      Pablo A. Rovedo  06/01/11  -- Creado     v 1.0
Rem

-- Borra la tabla TBL_REDO_USAGE si existe

drop table tmp$redo_usage
/

-- Crea la tabla de repositorio TBL_REDO_USAGE

create table tmp$redo_usage
(
USERNAME     VARCHAR2(30),
OSUSER       VARCHAR2(30),
TERMINAL     VARCHAR2(30),
PROGRAM      VARCHAR2(48),
SQL_ID       VARCHAR2(13),
SQL_FULLTEXT CLOB,
REDO_SIZE    NUMBER,
SNAP         DATE,
INST_ID      NUMBER(1)
)
pctfree 0
nologging
/


-- Crea el procedimiento que recolecta la información de Alocación de
-- espacio de redo

create or replace procedure p_get_redo_usage
is
begin
insert /*+ append */ into tmp$redo_usage
select s.USERNAME,
       s.OSUSER,
       s.terminal,
       s.PROGRAM,
       s.SQL_ID,
       sa.SQL_FULLTEXT,
       round(ss.VALUE/1024/1024) redo_size,
       sysdate,
       s.inst_id
from gv$sesstat ss,
     v$statname sn,
     gv$session s,
     gv$sqlarea sa
where s.sid = ss.sid
  and s.inst_id = ss.inst_id
  and sn.STATISTIC# = ss.STATISTIC#
  and s.sql_id = sa.SQL_ID
  and s.inst_id = sa.inst_id
  and sn.name = 'redo size'
  and s.username not in ('SYS','SYSTEM');
commit;
end;
/

-- Borra el job si ya existe

begin
dbms_scheduler.drop_job(job_name => 'J_SAVE_REDO_USAGE',
                     force => true);
end;
/

-- Crea un nuevo job

begin
dbms_scheduler.create_job(
job_name => 'J_SAVE_REDO_USAGE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin p_get_redo_usage; end;'
,start_date => sysdate
,end_date => sysdate+1/24  -- Recolecta durante 1 hora
,repeat_interval => 'FREQ=SECONDLY;BYSECOND=0,5,10,15,20,25,30,35,40,45,50,55'
,enabled => TRUE
,comments => 'Almacena Informacion de Alocación de espacio de redo');
end;
/

Luego de terminada la recolección (en el script se definió un intervalo
de monitoreo de 1 hora) se puede ejecutar la siguiente consulta para ver
los resultados:

select sql_id,sum(redo_size) "redo_size(Mb)"
from (select unique sql_id,
       redo_size-lead(redo_size) over (partition by sql_id order by snap desc) redo_size
from tmp$redo_usage)
where redo_size is not null
group by sql_id
order by "redo_size(Mb)" desc