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:
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:
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
.JPG)