Para analizar las distintas alternativas que tenemos de crear una primary key vamos a usar una tabla particionada de ejemplo con 8M de registros (ver en apendice detalle de la tabla). El equipo de prueba es un SunFire 890 con 16Gb de RAM y 8 procesadores. Para independizar los casos y limpiar el buffer cache se hace un flush del buffer_cache entre cada caso.
Caso 1: Creación de pk en forma directa
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo Total: 69.672s
Esta operacion crea implicitamente un indice unique global de soporte a la pk. Cuando se elimina la pk se elimina implicitamente el indice de soporte.
Caso 2: Pre-Creación de indice non-unique global
create index pk_t_part on t_part(c3,c1,c5,c2);
Tiempo: 56.984s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 76.5s
Tiempo Total: 133.484s
Al eliminar no se elimina el indice ya que fue creado en forma independiente.
Caso 3: Pre-Creación de indice non-unique local
create index pk_t_part on t_part(c3,c1,c5,c2) local;
Tiempo: 58.797s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 79.516s
Tiempo Total: 138.313s
Al eliminar no se elimina el indice ya que fue creado en forma independiente.
Caso 4: Pre-Creación de indice unique global
create unique index pk_t_part on t_part(c3,c1,c5,c2)
Tiempo: 54.156s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 16.938s
Tiempo Total: 71.094s
Es necesario eliminar explicitamente el indice cdo se elimina la pk ya que no se elimina automaticamente.
Caso 5: Pre-Creación de indice unique local
create unique index pk_t_part on t_part(c3,c1,c5,c2) local
Tiempo: 60.436s
alter table t_part
add constraint pk_t_part
Tiempo: 12.093s
Tiempo Total: 72.529s
Caso 6: Creación de pk con especificación de creación de índice
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2))
Tiempo Total: 93.688s
Al eliminar la pk se elimina implicitamente el indice.
Caso 7: Pre-Creacion de indice global no-unique en paralelo y nologging
create index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local
Tiempo: 15.016s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 101.456s
Tiempo Total: 116.472s
Caso 8: Pre-Creacion de indice local no-unique en paralelo y nologging
create index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local
Tiempo: 11.687s.
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 99.5s
Tiempo Total: 105.687s
Caso 9:Pre-Creacion de indice unique global en paralelo y nologging
create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8)
Tiempo: 14.281s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 12.187s
Tiempo Total: 26.468s
Caso 10:Pre-Creacion de indice unique local en paralelo y nologging
create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local
Tiempo: 10.328s
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
Tiempo: 16.375s
Tiempo Total: 26.703s
Caso 11: Creación de pk con creación de índice global en paralelo y nologging
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8))
Tiempo Total: 103.906s
Caso 12: Creación de pk con creación de índice local en paralelo y nologging
alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local)
Tiempo Total: 97.391s
Conclusión
Casos Tiempo Total
1 Creación de pk en forma directa 69.672s
2 Creación de indice non-unique global 133.484s
3 Pre-Creación de indice non-unique local 138.313s
4 Pre-Creación de indice unique global 71.094s
5 Pre-Creación de indice unique local 72.529s
6 Creación de pk con especificacion de creación de indice 93.688s
7 Pre-Creacion de indice global no-unique en paralelo y nologging 116.472s
8 Pre-Creacion de indice local no-unique en paralelo y nologging 105.687s
9 Pre-Creacion de indice unique global en paralelo y nologging 26.468s
10 Pre-Creacion de indice unique local en paralelo y nologging 26.703s
11 Creación de pk con creación de índice global en paralelo y nologging 103.906s
12 Creación de pk con creación de índice local en paralelo y nologging 97.391s
Tabla 1: Análisis de tiempos por caso
De los casos evaluados podemos concluir:
- El tiempo que insume la creación de un índice unique comparado con la creación de un índice no-unique es similar.
- El tiempo que insume la creación de un índice global comparado con la creación de un índice local es similar.
- Los casos en los que se pre-crea un índice unique con paralelismo y nologging (casos 9 y 10) son los mas eficientes.
- En los casos 11 y 12 pareciera no tener efecto el paralelismo ni el uso de índice unique dentro de la sentencia de creación de la pk, ya que los tiempos no son buenos.
sábado, 20 de diciembre de 2008
Creación de PK sobre tablas grandes
lunes, 8 de diciembre de 2008
Estimación de espacio de Tablas e Indices
Como comenté en la nota Dimensionamiento de Esquema de Datos., la estimación de espacio es una tarea muy requerida pero a veces un tanto complicada de obtenerse con precisión. Cuanto mas datos tengamos respecto a la distribución de los datos, tipo de tablespace utilizado y parámetros de storage de segmento requeridos (ej: pctfree) mejor será nuestra estimación. Cuando se estima se pueden tomar dos enfoques: a) el enfoque pesimista que consta de pensar que cada fila ocupará el máximo permitido (el máximo posible para cada tipo de datos de las columnas) ó b) un enfoque mas realista que consiste en generar una cantidad apropiada de datos reales o pseudo-reales y tomar el largo promedio de la fila. Con el primer enfoque es común encontrarse con tamaños estimados demasiado grandes, que a veces asustan y son complicados de justificar, sobre todo cuando se realiza el requerimiento de disco al sector encargado de administrar el storage corporativo. La segunda alternativa es la que me parece mas real y se basa en algo simple, se toma el largo promedio de la filas (ese dato se obtiene de la recolección estadística) y se multiplica por la cantidad de filas totales que se estiman a un cierto tiempo.
La idea de esta nota es mostrarles con ejemplos como estimar usando el paquete DBMS_SPACE, que agrega, entre otros, dos nuevos procedimientos para estimar espacio de tablas e indices.
Como siempre hago intentaré mostrar su funcionamiento mediante un ejemplo.
Voy a crear una tabla T de 1M de registros aleatorios, con campos C1,C2,C3 y C4 con tipos de datos number,varchar2 y date.
Luego de crear la tabla y recolectar las estadísticas, me da un promedio de largo de fila de 28 bytes. También dejé el valor default de pctfree (10%) y el tablespace DATA es un tablespace de tipo LOCAL con tipo de alocacion SYSTEM y ASSM (esto es obligatorio para que funcionen las estimaciones). A continuación armé un bloque PL/SQL anónimo para obtener el espacio que necesitará la tabla recién creada si tuviera que almacenar 100M de filas. Con la cantidad de filas actuales (1M) la tabla aloca 38Mb.
Vemos que el tamaño alocado estimado para 100M es de 3392Mb.
Ahora voy a probar el procedimiento para estimar espacio de índices. Este procedimiento a diferencia del procedimiento para estimar tamaño de tablas solo requiere la DDL de creación del índice.
Hay que tener cuidado con este procedimiento porque si la tabla a indexar no tiene estadísticas o bien tiene pero no son actuales no fallará y dará un valor irreal. La estimación de índices solo necesita la DDL, no se le pasa otra información y por tanto lo que estima es la cantidad de espacio que ocupará el índice para la tabla actual, pero si quisiera estimar el tamaño del índice para
la tabla de 100M filas estimada arriba?, tendría que crear la tabla de mas de 3Gb?. Eso podría ser un inconveniente, ya que se necesitará tiempo y espacio disponible. De todas formas hay una solución, se puede "mentir" en la estadísticas y setearlas al valor que querramos.
Usando el procedimiento set_table_stats del paquete de dbms_stats definimos nosotros los valores.
Observamos que ahora se estimó un espacio para el índice T_IDX1 de 2176Mb, lo cual suena mas real para un indice de una tabla de 100M de filas.
Como vimos, en 10g se pueden utilizar procedimientos nativos para generar estimaciones de tablas e indices. Tambien se pueden obtener proyecciones de crecimiento de la tablas. Todo esto facilita las tareas y permiten anticipar el espacio requerido por la base de datos.
La idea de esta nota es mostrarles con ejemplos como estimar usando el paquete DBMS_SPACE, que agrega, entre otros, dos nuevos procedimientos para estimar espacio de tablas e indices.
Como siempre hago intentaré mostrar su funcionamiento mediante un ejemplo.
Voy a crear una tabla T de 1M de registros aleatorios, con campos C1,C2,C3 y C4 con tipos de datos number,varchar2 y date.
rop@DESA10G> create table t as
2 select rownum c1,
3 dbms_random.string('a',trunc(dbms_random.value(1,20))) c2,
4 trunc(dbms_random.value(1,100000)) c3,
5 dbms_random.value(-100,100)+sysdate c4
6 from dual
7 connect by rownum <= 1000000 8 / Tabla creada.
Luego de crear la tabla y recolectar las estadísticas, me da un promedio de largo de fila de 28 bytes. También dejé el valor default de pctfree (10%) y el tablespace DATA es un tablespace de tipo LOCAL con tipo de alocacion SYSTEM y ASSM (esto es obligatorio para que funcionen las estimaciones). A continuación armé un bloque PL/SQL anónimo para obtener el espacio que necesitará la tabla recién creada si tuviera que almacenar 100M de filas. Con la cantidad de filas actuales (1M) la tabla aloca 38Mb.
rop@DESA10G> declare
2 l_used_bytes int;
3 l_alloc_bytes int;
4 begin
5 dbms_space.create_table_cost(tablespace_name => 'DATA',
6 avg_row_size => 28,
7 row_count => 100000000,
8 pct_free => 10,
9 used_bytes => l_used_bytes,
10 alloc_bytes => l_alloc_bytes);
11 dbms_output.put_line('Espacio ocupado '||round(l_used_bytes/1024/1024,2));
12 dbms_output.put_line('Espacio alocado '||round(l_alloc_bytes/1024/1024,2));
13 end;
14 /
Espacio ocupado 3338.68
Espacio alocado 3392
Procedimiento PL/SQL terminado correctamente.
rop@DESA10G>
Vemos que el tamaño alocado estimado para 100M es de 3392Mb.
Ahora voy a probar el procedimiento para estimar espacio de índices. Este procedimiento a diferencia del procedimiento para estimar tamaño de tablas solo requiere la DDL de creación del índice.
rop@DESA10G> declare
2 l_used_bytes int;
3 l_alloc_bytes int;
4 begin
5 dbms_space.create_index_cost(ddl => 'create index t_idx1 on t(c1,c2,c3)',
6 used_bytes => l_used_bytes,
7 alloc_bytes => l_alloc_bytes);
8 dbms_output.put_line('Espacio ocupado 'round(l_used_bytes/1024/1024,2));
9 dbms_output.put_line('Espacio alocado 'round(l_alloc_bytes/1024/1024,2));
10 end;
11 /
Espacio ocupado 20.03
Espacio alocado 34
Hay que tener cuidado con este procedimiento porque si la tabla a indexar no tiene estadísticas o bien tiene pero no son actuales no fallará y dará un valor irreal. La estimación de índices solo necesita la DDL, no se le pasa otra información y por tanto lo que estima es la cantidad de espacio que ocupará el índice para la tabla actual, pero si quisiera estimar el tamaño del índice para
la tabla de 100M filas estimada arriba?, tendría que crear la tabla de mas de 3Gb?. Eso podría ser un inconveniente, ya que se necesitará tiempo y espacio disponible. De todas formas hay una solución, se puede "mentir" en la estadísticas y setearlas al valor que querramos.
Usando el procedimiento set_table_stats del paquete de dbms_stats definimos nosotros los valores.
rop@DESA10G> begin
2 dbms_stats.set_table_stats(ownname => user,
3 tabname => 'T',
4 numrows => 100000000,
5 avgrlen => 28);
6 end;
7 /
Procedimiento PL/SQL terminado correctamente.
rop@DESA10G> declare
2 l_used_bytes int;
3 l_alloc_bytes int;
4 begin
5 dbms_space.create_index_cost(ddl => 'create index t_idx1 on t(c1,c3)',
6 used_bytes => l_used_bytes,
7 alloc_bytes => l_alloc_bytes);
8 dbms_output.put_line('Espacio ocupado 'round(l_used_bytes/1024/1024,2));
9 dbms_output.put_line('Espacio alocado 'round(l_alloc_bytes/1024/1024,2));
10 end;
11 /
Espacio ocupado 953.67
Espacio alocado 2176
Procedimiento PL/SQL terminado correctamente.
Observamos que ahora se estimó un espacio para el índice T_IDX1 de 2176Mb, lo cual suena mas real para un indice de una tabla de 100M de filas.
Como vimos, en 10g se pueden utilizar procedimientos nativos para generar estimaciones de tablas e indices. Tambien se pueden obtener proyecciones de crecimiento de la tablas. Todo esto facilita las tareas y permiten anticipar el espacio requerido por la base de datos.
jueves, 4 de diciembre de 2008
Como reasumir procesos que cancelan por falta de espacio (Resumable Space Management)
A partir de Oracle 9i se introduce el mecanismo para suspender y luego reasumir procesos que se quedan sin espacio disponible en el tablespace o alcanzan limitaciones de quota. Este feature permite al operador o dba ejecutar tareas correctivas y así evitar la generación de errores por falta de espacio.
Luego de que la condición de error es corregida la operación suspendida se reasume automáticamente Como funciona la resumisión de espacio alocado
Una sentencia esta habilitada para reasumir si la sesión desde donde fue ejecutada cumple alguna de las siguientes condiciones:
• El parámetro de inicio RESUMABLE_TIMEOUT es distinto a 0.
• Se habilita la sesión para resumir mediante: ALTER SESSION ENABLE RESUMABLE.
Una sentencia con resumisión habilitada es suspendida cuando ocurre una de las siguientes condiciones:
• Falta de espacio.
• Cantidad máxima de extents alcanzada.
• Quota de espacio excedida.
Cuando una sentencia es suspendida se generan las siguientes acciones:
• Se reporta el error en Alert log.
• El sistema genera una alerta de sesión reasumible suspendida.
• Si existe algún trigger registrado que se dispare ante el evento de sistema “AFTER SUSPEND” se ejecuta.
La suspensión de la sentencia resulta en la suspensión de la transacción por lo tanto los recursos transaccionales se mantendrán “tomados” hasta que se reasuma.
Cuando se resuelve la condición de error (por ejemplo, por intervención del usuario o por que otra sentencia haya liberado espacio) la sesión suspendida reasume
automáticamente y se limpia la alerta de sesión resumible suspendida.
Una sentencia suspendida puede ser forzada a terminar mediante la ejecución de DBMS_RESUMABLE.ABORT().
Cada sentencia reasumible tiene asociado un time-out (el default es de 2 horas) que una vez superado retoma la excepción suspendida y retorna el error al usuario.
Una sentencia pude ser suspendida y reasumida multiples veces durante su ejecución.
Las siguientes operaciones pueden ser reasumidas:
Consultas: Las sentencias SELECT que requieran de espacio temporal para ordenar o agrupar.
DML: Las operaciones de INSERT, UPDATE o DELETE ejecutadas desde cualquier interface (OCI, SQLJ, PL/SQL).
Utilidades de Carga y Descarga de datos: Las utilidades como exp/imp, expdp/impdp y sql loader pueden ser parametrizadas por consola para reasumir.
DDL: Las siguientes sentencias son candidatas a reasumir:
CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
Existen 3 tipos de errores que pueden ser corregidos usando resumisión:
Falta de espacio disponible
La operación no puede alocar un nuevo extent para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice en un tablespace. Los siguientes errores son ejemplos del tipo de error por falta de espacio:
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...
Máxima cantidad de extents
El número de extents maximo es alcanzado para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice. Ejemplos de errores que entran en esta categoría son:
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...
Cuota de espacio excedida
El usuario excedió el espacio disponible en un tablespace dado. El siguiente error es arrojado en dicho caso:
ORA-1536 space quote exceeded for tablespace …
Para reasumir una operación es necesario que la sesión se encuentre en modo de resumisión. La habilitación de dicho modo se puede realizar a nivel general configurando adecuadamente el parámetro de entorno RESUMABLE_TIMEOUT o a nivel sesión usando las cláusula ALTER SESSION. Dado que este tipo de sesiones
bloquean los objetos involucrados cuando entra en modo suspendido, es requisito que el usuario tenga el privilegio de sistema RESUMABLE.
Seteando el parámetro RESUMABLE_TIMEOUT a nivel global o de instancia todas las sesiones ejecutaran sentencias en modo reasumible. El valor default del parámetro es 0 lo cual implica que ninguna sesión permite reasumir. Por ejemplo:
ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600
Implica que ante un evento de error como los citados anteriormente, la sesión quedará suspendida por un tiempo máximo de 1 hora
Utilización de ALTER SESSION para habilitar resumisión
Un usuario puede ejecutar:
Para Habilitar la resumisión:
ALTER SESSION ENABLE RESUMABLE
Para Deshabilitar la resumisión:
ALTER SESSION DISABLE RESUMABLE
También se puede definir el intervalo de time-out (7200 segundos si no se especifica)
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600
También se puede nombrar a la sentencia para identificarla mas fácilmente:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800 NAME 'insert into table';
Las siguientes vistas pueden ser usadas para monitorear el status de las sentencias con resumisión activada:
USER_RESUMABLE: Esta vista contiene detalle de todas las sentencias en ejecución o suspendidas en sesiones con resumisión habilitada de un determinado usuario.
DBA_RESUMABLE: Idem anterior pero para las sesiones de todos los usuarios
V$SESSION_WAIT: Cuando la sentencia queda suspendida la sesión invocante se pone en estado de wait y se puede ver un nueva fila en la vista con el siguiente evento “statement suspended, wait error to be cleared".
A fin de mostrar el uso de este feature se va a realizar la siguiente simulación:
Se crea un tablespace de 5Mb:
Luego para una tabla T con un solo campo CHAR(100) insertamos filas hasta que se produce el error:
Luego desde otra sesión se agrega espacio:
y una vez agregado el espacio adicional la otra sesión continua insertando las filas que faltaban:
Con este mecanismo se evita que los errores por falta de espacio ocasionen tener que reprocesar todo nuevamente. Cuando una sesión se queda sin espacio suficiente se mantiene suspendida hasta que se agrega mas espacio y luego en forma automática continúa su ejecución hasta que finaliza.
Como contrapartida ese mecanismo debe ser utilizado con cierto cuidado ya que las sesiones suspendidas dejaran transacciones sin confirmar y por lo tanto bloquearan a los objetos involucrados.
Para mas información: Managing Resumable Space Allocation.
Luego de que la condición de error es corregida la operación suspendida se reasume automáticamente Como funciona la resumisión de espacio alocado
Una sentencia esta habilitada para reasumir si la sesión desde donde fue ejecutada cumple alguna de las siguientes condiciones:
• El parámetro de inicio RESUMABLE_TIMEOUT es distinto a 0.
• Se habilita la sesión para resumir mediante: ALTER SESSION ENABLE RESUMABLE.
Una sentencia con resumisión habilitada es suspendida cuando ocurre una de las siguientes condiciones:
• Falta de espacio.
• Cantidad máxima de extents alcanzada.
• Quota de espacio excedida.
Cuando una sentencia es suspendida se generan las siguientes acciones:
• Se reporta el error en Alert log.
• El sistema genera una alerta de sesión reasumible suspendida.
• Si existe algún trigger registrado que se dispare ante el evento de sistema “AFTER SUSPEND” se ejecuta.
La suspensión de la sentencia resulta en la suspensión de la transacción por lo tanto los recursos transaccionales se mantendrán “tomados” hasta que se reasuma.
Cuando se resuelve la condición de error (por ejemplo, por intervención del usuario o por que otra sentencia haya liberado espacio) la sesión suspendida reasume
automáticamente y se limpia la alerta de sesión resumible suspendida.
Una sentencia suspendida puede ser forzada a terminar mediante la ejecución de DBMS_RESUMABLE.ABORT().
Cada sentencia reasumible tiene asociado un time-out (el default es de 2 horas) que una vez superado retoma la excepción suspendida y retorna el error al usuario.
Una sentencia pude ser suspendida y reasumida multiples veces durante su ejecución.
Las siguientes operaciones pueden ser reasumidas:
Consultas: Las sentencias SELECT que requieran de espacio temporal para ordenar o agrupar.
DML: Las operaciones de INSERT, UPDATE o DELETE ejecutadas desde cualquier interface (OCI, SQLJ, PL/SQL).
Utilidades de Carga y Descarga de datos: Las utilidades como exp/imp, expdp/impdp y sql loader pueden ser parametrizadas por consola para reasumir.
DDL: Las siguientes sentencias son candidatas a reasumir:
CREATE TABLE ... AS SELECT
CREATE INDEX
ALTER INDEX ... REBUILD
ALTER TABLE ... MOVE PARTITION
ALTER TABLE ... SPLIT PARTITION
ALTER INDEX ... REBUILD PARTITION
ALTER INDEX ... SPLIT PARTITION
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
Existen 3 tipos de errores que pueden ser corregidos usando resumisión:
Falta de espacio disponible
La operación no puede alocar un nuevo extent para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice en un tablespace. Los siguientes errores son ejemplos del tipo de error por falta de espacio:
ORA-1653 unable to extend table ... in tablespace ...
ORA-1654 unable to extend index ... in tablespace ...
Máxima cantidad de extents
El número de extents maximo es alcanzado para una tabla/índice/undo/temporal/cluster/LOB/partición de una tabla/partición de un índice. Ejemplos de errores que entran en esta categoría son:
ORA-1631 max # extents ... reached in table ...
ORA-1654 max # extents ... reached in index ...
Cuota de espacio excedida
El usuario excedió el espacio disponible en un tablespace dado. El siguiente error es arrojado en dicho caso:
ORA-1536 space quote exceeded for tablespace …
Para reasumir una operación es necesario que la sesión se encuentre en modo de resumisión. La habilitación de dicho modo se puede realizar a nivel general configurando adecuadamente el parámetro de entorno RESUMABLE_TIMEOUT o a nivel sesión usando las cláusula ALTER SESSION. Dado que este tipo de sesiones
bloquean los objetos involucrados cuando entra en modo suspendido, es requisito que el usuario tenga el privilegio de sistema RESUMABLE.
Seteando el parámetro RESUMABLE_TIMEOUT a nivel global o de instancia todas las sesiones ejecutaran sentencias en modo reasumible. El valor default del parámetro es 0 lo cual implica que ninguna sesión permite reasumir. Por ejemplo:
ALTER SYSTEM SET RESUMABLE_TIMEOUT=3600
Implica que ante un evento de error como los citados anteriormente, la sesión quedará suspendida por un tiempo máximo de 1 hora
Utilización de ALTER SESSION para habilitar resumisión
Un usuario puede ejecutar:
Para Habilitar la resumisión:
ALTER SESSION ENABLE RESUMABLE
Para Deshabilitar la resumisión:
ALTER SESSION DISABLE RESUMABLE
También se puede definir el intervalo de time-out (7200 segundos si no se especifica)
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600
También se puede nombrar a la sentencia para identificarla mas fácilmente:
ALTER SESSION ENABLE RESUMABLE TIMEOUT 1800 NAME 'insert into table';
Las siguientes vistas pueden ser usadas para monitorear el status de las sentencias con resumisión activada:
USER_RESUMABLE: Esta vista contiene detalle de todas las sentencias en ejecución o suspendidas en sesiones con resumisión habilitada de un determinado usuario.
DBA_RESUMABLE: Idem anterior pero para las sesiones de todos los usuarios
V$SESSION_WAIT: Cuando la sentencia queda suspendida la sesión invocante se pone en estado de wait y se puede ver un nueva fila en la vista con el siguiente evento “statement suspended, wait error to be cleared".
A fin de mostrar el uso de este feature se va a realizar la siguiente simulación:
Se crea un tablespace de 5Mb:
rop@TEST10G> create tablespace tbs datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' size 5m
Tablespace creado.
Luego para una tabla T con un solo campo CHAR(100) insertamos filas hasta que se produce el error:
rop@TEST10G> create table t (x char(100)) tablespace tbs;
Tabla creada.
rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; insert into t select 'a' from dual connect by rownum <= 100000 * ERROR en línea 1: ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS Ahora habilitamos la misma sesión para que suspenda y no genere error: rop@TEST10G> alter session enable resumable;
Sesión modificada.
rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; La sesión queda suspendida. Si realizamos la consulta en USER_RESUMABLE se ve lo siguiente: USER_ID 77 SESSION_ID 65 INSTANCE_ID 1 COORD_INSTANCE_ID COORD_SESSION_ID STATUS SUSPENDED TIMEOUT 7200 START_TIME 06/06/08 15:59:32 SUSPEND_TIME 06/06/08 15:59:32 RESUME_TIME NAME User ROP(77), Session 65, Instance 1 SQL_TEXT insert into t select 'a' from dual connect by rownum <= 100000 ERROR_NUMBER 1653 ERROR_PARAMETER1 ROP ERROR_PARAMETER2 T ERROR_PARAMETER3 128 ERROR_PARAMETER4 TBS ERROR_PARAMETER5 ERROR_MSG ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS ORA-01653: no se ha podido ampliar la tabla ROP.T con 128 en el tablespace TBS
Luego desde otra sesión se agrega espacio:
rop@TEST10G> alter database datafile '/disco02/oracle10g/oradata/TEST10G/tbs01.dbf' resize 200m;
y una vez agregado el espacio adicional la otra sesión continua insertando las filas que faltaban:
rop@TEST10G> insert into t select 'a' from dual connect by rownum <= 100000; 100000 filas creadas.
Con este mecanismo se evita que los errores por falta de espacio ocasionen tener que reprocesar todo nuevamente. Cuando una sesión se queda sin espacio suficiente se mantiene suspendida hasta que se agrega mas espacio y luego en forma automática continúa su ejecución hasta que finaliza.
Como contrapartida ese mecanismo debe ser utilizado con cierto cuidado ya que las sesiones suspendidas dejaran transacciones sin confirmar y por lo tanto bloquearan a los objetos involucrados.
Para mas información: Managing Resumable Space Allocation.
Suscribirse a:
Entradas (Atom)
.JPG)