sábado, 20 de diciembre de 2008

Creación de PK sobre tablas grandes

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.

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.


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:

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.