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.