Introducción

Cuando diseña una base de datos y durante su existencia (tareas de

mantenimiento), es necesario realizar una estimación del tamaño que tendrá la
base de datos cuando esté llena. Esta estimación puede ayudarle a determinar la
configuración de hardware que necesitará para realizar lo siguiente:

 Conseguir el rendimiento que necesitan las aplicaciones.

 Asegurar la cantidad física adecuada de espacio en disco necesario para almacenar los datos
y los índices.

Asimismo, la estimación del tamaño de la base de datos puede ayudarle a

determinar si el diseño de su base de datos necesita reajustes. Por ejemplo, puede
determinar que el tamaño estimado de la base de datos es demasiado grande para
una implementación en su organización, y que se necesita un mayor grado de
normalización. Por el contrario, el tamaño estimado puede ser inferior al
esperado, con lo que podrá reducir la normalización de la base de datos para
mejorar el rendimiento de las consultas.

Estimación de crecimiento de
tablas

En general para realizar una estimación del tamaño de una base de datos, esta debe
efectuarse del tamaño de cada tabla por separado, luego sumar los valores
obtenidos. El tamaño de una tabla depende de si tiene índices y, si los tiene, del
tipo de índices, así como columnas y sus tipos de datos.

Calcular la estimación del crecimiento de las tablas, es una tarea poco exacta
cuando se trata de una base de datos nueva, pues no se tienen estadísticas reales
del crecimiento y los cálculos deben realizarse de forma manual o simulando datos
en las tablas para que se generen estadísticas de los mismos.

Estimación manual
Para obtener la estimación de espacio necesario para almacenar la base datos si esta es nueva, se han
definido los siguientes tamaños:

1. Tamaño de los tipos de datos.

2. Tamaño de las tablas, sin considerar sus índices.
3. Tamaño total de las tablas.
4. Tamaño de los tipos de datos, a continuación se presentan los tipos de datos válidos para
Oracle y sus tamaños.

Tipo Dato Descripción

El tamaño
máximo es de
4000 bytes o
caracteres, y la
mínima es de 1
VARCHAR2(size [BYTE | CHAR]) byte o un
carácter. Se
debe especificar
el tamaño de
para
VARCHAR2.
El tamaño
máximo está
determinado por
la definición del
juego de
caracteres
NVARCHAR2(size) nacional, con un
límite máximo
de 4000 bytes.
Se debe
especificar el
size de
NVARCHAR2.
Requiere de 1 a
NUMBER [ (p [, s]) ] 22 bytes.
Requiere de 1 a
FLOAT [(p)] 22 bytes.
De longitud
LONG variable de
hasta 2
 gigabytes, o
231 -1 bytes.
El tamaño es de
DATE 7 bytes fijos.
Este tipo
BINARY_FLOAT de datos requier
e 4 bytes fijos.
Este tipo
de datos se
BINARY_DOUBLE requieren de 8
bytes fijos.
El tamaño es
de 7 o 11
bytes,
TIMESTAMP [(fractional_seconds_precision)]
dependiendo
de la
precisión.
El tamaño se fija
TIMESTAMP [(fractional_seconds_precision)]WITH TIME ZONE en 13 bytes.
El tamaño es de
7 o 11 bytes,
TIMESTAMP [(fractional_seconds)] WITHLOCAL TIME ZONE dependiendo de
la precisión.
El tamaño se fija
INTERVAL YEAR [(year_precision)] TO MONTH en 5 bytes.
INTERVAL DAY [(day_precision)] TO SECOND[(fractional_secon El tamaño se fija
ds)] en 11 bytes.
El tamaño
máximo es de
2000 bytes. Se
RAW(size) debe especificar
el tamaño de un
valor RAW.
De longitud
LONG RAW variable hasta 2
gigabytes.
El tamaño
opcional es el
tamaño de una
columna de tipo
UROWID [(size)] UROWID
de Oracle. El
tamaño máximo
y por defecto es
de 4000 bytes.
El tamaño
máximo es de
2000 bytes o
CHAR [(size [BYTE | CHAR])] caracteres, el
tamaño
predeterminado
 y mínimo es de
1 byte.
El tamaño
máximo está
determinado por
la definición del
juego de
caracteres
NCHAR[(size)] nacional, con un
límite máximo
de 2000 bytes.
El tamaño
predeterminado
y mínimo es de
un carácter.
El tamaño
máximo es (4
gigabytes - 1) *
CLOB (tamaño del
bloque de
la base de
datos).
El tamaño
máximo es (4
gigabytes - 1) *
NCLOB (tamaño del
bloque de
la base de
datos).
El tamaño
máximo es (4
gigabytes - 1) *
BLOB (tamaño del
bloque de
la base de
datos).
El tamaño
BFILE máximo es de 4
gigabytes.

1. Tamaño de las tablas, sin considerar sus índices.

Calcular el tamaño de las tablas

Para calcular de forma manual el tamaño de las tablas, debe seguir los siguientes pasos:

1. Número de filas de la tabla = númFilas

2. Se realizan los siguientes cálculos:

 Número de columnas = númCols

  Suma de todas las columnas de longitud fija = datosTñoFijo.
 Número de columnas de longitud variable = númColsVariables
 Tamaño máximo de todas las columnas de longitud variable = tñoMáxVar

1. Si existen columnas de longitud fija en la tabla (pej: VARCHAR(10) ), se calcula:

Mapa de bits nulo (mapaBitsNulo) = 2+((númCols + 7)/ 8)

Nota: Sólo se utiliza la parte entera de la expresión anterior.

2. Tamaño total de las columnas de longitud variable (datosTñoVar) = 2 + (númColsVariables

x 2) + tñoMáxVar

Si no hay columnas de longitud variable, datosTñoVar = 0.

3. Tamaño total de la fila (tñoFila) = datosTñoFijo + datosTñoVar + mapaBitsNulo +4

4. Se calcula el número de filas por página (8096 bytes disponibles por página):

N° de filas por página (filasPorPág) = ( 8096 ) / (tñoFila + 2)

El número de filas por página se redondea a la fila anterior.

 5. Si la tabla tiene un índice agrupado, se calcula:

Número de filas libres por página (filasLibresPorPág) = 8096 x ((100 - factorRelleno) / 100)
/ tñoFila

El número de filas por página se redondea a la fila anterior.

6. Se calcula el número de páginas necesarias para almacenar todas las filas:

Número de páginas (númPágs) = númFilas / (filasPorPág - filasLibresPorPág)

El número de páginas estimado se redondea a la siguiente página.

7. Cálculo de la cantidad de espacio necesario para almacenar los datos en una tabla (8192 bytes
por página):

Tamaño de la tabla (bytes) = 8192 x númPágs.

8. Tamaño total de las tablas.

Para calcular en tamaño total de las tablas, primero se deben calcular el tamaño de sus índices
(agrupados y no agrupados).

Calcular el tamaño de los índices

Para calcular de forma manual el tamaño de los índices agrupados(clustered), debe seguir los
siguientes pasos:

1. Se realizan los siguientes cálculos:

 Número de columnas de la clave del índice = númColsClaveAgr

 Suma de los bytes de todas las columnas de clave de longitud fija = TñoFijoClaveArg
 Número de columnas de longitud variable de la clave del índice =
númColsVarClaveAgr
 Tamaño máximo de todas las columnas de clave de longitud variable =
tñoVarMáxClaveAgr

1. Si hay columnas de longitud fija en el índice agrupado:

Mapa de bits nulo del índice (mapaBitsNuloÍndAgr) = 2 + (( númColsClaveAgr + 7) / 8 ).

Se utilizar la parte entera de la expresión anterior.

2. Si hay columnas de longitud variable en el índice:

Tamaño total de las columnas de longitud variable (tñoVarClaveAgr) = 2 +

(númColsVarClaveAgr x 2) + tñoVarMáxClaveAgr

Si no hay columnas de longitud variable, tñoVarClaveAgr=0.

 3. Tamaño total de la fila del índice (tñoFilaÍndAgr) = tñoFijoClaveAgr + tñoVarClaveAgr +
mapaBitsNuloÍndAgr + 9.
4. Número de filas de índice por página (filasÍndAgrPorPág) = ( 8096 ) / (tñoFilaÍndAgr + 2).
5. Número de páginas (nivel 0) (númPágsNivelAgr0) = (espacioDatosUtilizado / 8192) /
filasÍndAgrPorPág
6. Número de páginas (nivel 1) (númPágsNivelAgr1) = númPágsNivelAgr0 / filasÍndAgrPorPág.
7. Se repite el cálculo, a partir del nivel anterior n por filasÍndAgrPorPág hasta que el número
de páginas para un nivel n dado (númPágsNivelAgrn) sea igual a uno.
8. Luego se calcula:

Número total de páginas (númPágsÍndAgr) = númPágsNivelAgr0 + númPágsNivelAgr1 + …

+ númPágsNivelAgrn.

1. Se calcula el tamaño del índice agrupado (8192 bytes por página):

Tamaño del índice agrupado (bytes) = 8192 x númPágsÍndAgr.

Para calcular de forma manual el tamaño de los índices agrupados(non clustered), debe seguir los
siguientes pasos:

1. Se realizan los siguientes cálculos:

 Número de columnas de la clave del índice = númColsClave

 Suma de los bytes de todas las columnas de la clave de longitud fija = tñoFijoClave
 Número de columnas de longitud variable de la clave del índice = númColsVarClave
 Tamaño máximo de todas las columnas de clave de longitud variable = tñoVarMáxClave
 1. Si hay columnas de longitud fija en el índice, entonces:

Mapa de bits nulo del índice (mapaBitsNuloÍnd) = 2 + (( númColsClave+ 7) / 8).

Se utiliza la parte entera del resultado anterior.

1. Si hay columnas de longitud variable en el índice:

Tamaño total de las columnas de longitud variable (tñoVarClave) = 2 + (númColsVarClave x 2) +

tñoVarMáxClave
Si no hay columnas de longitud variable, tñoVarClave =0.

1. Tamaño total de la fila del índice no de hoja (tñoFilaÍndNH) = tñoFijoClave + tñoVarClave +

mapaBitsNuloÍnd + 9.
2. Número de filas de índice no de hoja por página (filasÍndNHPorPág) = ( 8096 ) /
(tñoFilaÍndNH + 2)

Se considera la parte entera del resultado anterior.

1. Tamaño total de la fila del índice de hoja (tñoFilaÍnd) = tñoFilaÍndAgr + tñoFijoClave +

tñoVarClave + mapaBitsNuloÍnd + 1.
2. Número de filas de índice de nivel de hoja por página (filasÍndPorPág) = ( 8096 ) /
(tñoFilaÍnd + 2)
3. Número de filas de índice libres por página (filasÍndLibresPorPág) = 8096 x ((100 -
factorRelleno) / 100) / tñoFilaÍnd.

Se utiliza Factor de relleno = 100, por tanto esta fórmula no se considera.

1. Número de páginas (nivel 0) (númPágsNivel0) = númFilas / (filasÍndPorPág -

filasÍndLibresPorPág)

Número de páginas (nivel 1) (númPágsNivel1) = númPágsNivel0 / filasÍndNHPorPág

1. Se repite el cálculo hasta que el número de páginas para un nivel n dado (númPágsNiveln)
sea igual a uno.
2. Luego se calcula:

Número total de páginas (númPágsÍnd) = númPágsNivel0 + númPágsNivel1 … +

númPágsNiveln
 3. Se calcula el tamaño del índice no agrupado:

Tamaño del índice no agrupado (bytes) = 8192 x númPágsÍnd.

Estimación con estadísticas

Si se está estimando el crecimiento de las tablas sobre una base de datos ya

existente a la cual se le está realizando mantenimiento, la tarea es mucho más
simple, en Oracle existen querys que nos pudieran ser de utilidad e inclusive desde
la versión de Oracle 10g, se incorporaron un par de store procedures en el paquete
DBMS_SPACE , los cuales serán de utilidad.

Query para obtener espacio a reservar

Lo que tenemos que hacer es sumar el espacio de las columnas, a ese resultado
multiplicarlo por la cantidad de filas que se estima para la tabla y luego agregarle
un porcentaje de overhead del motor de un 5% y luego un riesgo al crecimiento que
puede ir entre un 5% o 15%.
El query que nos dará la información es el siguiente, para ello debemos indicar la
cantidad de registros estimados que tendrá la tabla, si se trata de un catálogo, esto
es muy simple determinar, pero si se trata de una tabla operativa, esto deberá
determinarse por juicio de experto.
select (sum(data_lenght)*&cantidad_de_registros* 1.2*1.05)/1024/1024
\"Espacio a reservar en Mb\"
from dba_tab_columns
where owner = &owner
and table_name = &table_name;

Store procedure para obtener espacio a reservar

Como lo mencionábamos, para obtener el espacio a reservar se puede utilizar un

store procedure llamado DBMS_SPACE.CREATE_TABLE_COST, disponible en
Oracle desde la versión 10g, este nos permite estimar mediante una \"simulación\",
el espacio que ocupara una tabla, pues se definen los campos que conforman una
tabla y en base a ello nos arroja el costo en espacio, previo a la creación físicamente
de la tabla.
Con el SP CREATE_TABLE_COST_COLUMNS, se inicializa una tabla temporal
con la cual definiremos el costo, a esta se le indica con el SP
CREATE_TABLE_COST_COLINFO, la cantidad necesaria de campos que tendrá
la tabla, el tipo de dato y longitud, por último, se ejecuta el SP
CREATE_TABLE_COST, el cual se encarga de realizar la estimación, entre otros
parámetros se le proporciona el número de registros con los cuales deseamos se
calcule el costo.
A continuación un script útil para esta tarea:
Si se va a crear una tabla con las siguientes características
CREATE TABLE CATALOGO_CANAL (
ID_CANAL CHAR(3) NOT NULL,
DESCRIPCION VARCHAR2(256) NOT NULL,
CLAVE_TIPO_IVA CHAR(3) NOT NULL,
SISTEMA_ORIGEN VARCHAR2(20) NULL,
STATUS CHAR(4) NULL,
MONTO NUMBER(5,2),
PRIMARY KEY(ID_CANAL)
);
El siguiente script seria valido para calcular el costo previo a su creación y así
definir el tablespace que se le asignara.
set serveroutput on

DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('CHAR',3),
sys.create_table_cost_colinfo('VARCHAR2',256),
sys.create_table_cost_colinfo('CHAR',3),
sys.create_table_cost_colinfo('VARCHAR2',20),
sys.create_table_cost_colinfo('CHAR',4),
sys.create_table_cost_colinfo('NUMBER',2));
DBMS_SPACE.CREATE_TABLE_COST('SYSTEM',cl,100,0 ,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used MB: ' || round(ub/1024/1024,2));
DBMS_OUTPUT.PUT_LINE('Alloc MB: ' || round(ab/1024/1024,2));
END;
/
Donde 100, es el número de filas con el cual se desea realizar el cálculo, este valor
como se comentó anteriormente se debe estimar en función del tipo de tabla y una
estimación a juicio de experto del número de registros que se espera que almacene.

Estimación de costo de índices con SP

Es posible calcular el espacio que ocuparan los índices que deseamos crear, para
ello existe el SP CREATE_INDEX_COST, basta con que pasemos por parámetro el
script de creación del índices al índice y nos regresara el espacio que debemos
considerar.

declare
l_used_bytes int;
l_alloc_bytes int;
begin
dbms_space.create_index_cost(ddl => 'create index t_idx1 on t(c1,c2,c3)',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes);
dbms_output.put_line('Espacio ocupado ' || round(l_used_bytes/1024/1024,2));
dbms_output.put_line('Espacio alocado ' || round(l_alloc_bytes/1024/1024,2));
end;
/
Donde, en create index t_idx1 on t(c1,c2,c3), se indica el script del índice que
deseamos crear.

Fórmula para calcular el tamaño total de la

tabla
Una vez obtenido el tamaño de la tabla e índices, se utiliza la siguiente fórmula para determinar el
tamaño total de la tabla.
Tamaño total de la tabla (bytes) = espacioDatosUtilizado + (Tamaño del índice agrupado + Tamaño del

índice no agrupado.) o Tamaño del índice si se obtuvo con el SP create_index_cost

Nota: La sumatoria del tamaño total de todas las tablas de una base de datos,
determinaran por lo consiguiente el tamaño que ocupara la base de datos.
Tamaño de la base de datos = ∑ Tamaño total de la tabla (bytes)

Calcular el espacio para UNDO Tablespace

Cada base de datos Oracle debe tener un método para el mantenimiento de la

información que se utiliza para deshacer los cambios en la base de datos. Dicha
información consta del registro de las acciones de las transacciones, sobre todo
antes de que sea ejecutado el COMMIT en la transacción. Estos registros se
denominan colectivamente como UNDO.

UNDO Tablespace se utilizan para:

 Revertir las transacciones cuando se ejecuta una instrucción ROLLBACK

 Recuperar la base de datos
 Proporcionar coherencia de lectura
 Analizar los datos a partir de un punto anterior en el tiempo mediante el uso de Flashback
Query
 Recuperarse de corrupciones lógicas utilizando funciones Flashback

Cuando una instrucción ROLLBACK es ejecutada, UNDO Tablespace se utiliza para

deshacer los cambios realizados a la base de datos de la transacción no confirmada
(SIN COMMIT). Durante la recuperación de base de datos, UNDO Tablespace se
utiliza para deshacer los cambios no se aplican desde el registro para rehacer los
archivos de datos.UNDO Tablespace proporciona consistencia en la lectura de
información, manteniendo una imagen de los datos antes de que los usuarios
accedan a la liga y al mismo tiempo otro usuario está realizando cambios.
En versiones anteriores de Oracle, se utilizaban segmentos de rollback para
almacenar los datos a deshacer. A partir de Oracle9i se introdujo la gestión
automática de esta actividad, lo que simplifica la gestión al deshacer el espacio
mediante la eliminación de las complejidades asociadas con la gestión de segmento
de rollback. Oracle recomienda encarecidamente que utilice UNDO tablespace para
eliminar la gestión de segmentos de rollback.
Obviamente el tablespace es un elemento más que ocupa espacio en disco y
también debemos estimar su espacio pues es un factor a considerar en el cálculo y
estimación del tamaño y crecimiento de una base de datos, para estimar un UNDO
Tablespace, se tiene la siguiente formula:
UndoSpace = UR * UPS + overhead
Dónde:

 UndoSpace es el número de bloques "deshacer"

 UR: es UNDO_RETENTION en segundos
UPS: son los bloques undo por cada segundo (50)
 overhead : es la una pequeña sobrecarga de metadatos (tablas de transacciones, mapas de
bits, etc.)

Por ejemplo para 5 horas de retención, con bloques de 8k y generando 50 bloques

por segundo, la formula seria:
Undo Space (Tamaño de undo en bytes) = 5 (horas de rentention) *
3600 (Segundos) * 50 (bloques por segundo) * 8192 (Tamaño de bloque
en bytes)

El siguiente script nos será útil para calcular el UNDO Tablespace, &hs
corresponde a la cantidad de horas de retención de la información, este valor debe
establecerse según nuestras necesidades.
set linesize 200
col retention_segs format a15
col management format a10
col TABLESPACE_NAME format a30
select tablespace_name,
(select value from v$parameter where name = 'undo_management') management,
(select value from v$parameter where name = 'undo_retention') retention_segs,
(select sum(bytes)/1024/1024 from
dba_data_files where tablespace_name = a.tablespace_name) Tamano_Actual,
(&hs*3600*(select avg(UNDOBLKS)/10/60*1.5 UPS
from V$UNDOSTAT)*block_size)/1024/1024 Tamano_req_MB_AVG,
(&&hs*3600*(select max(UNDOBLKS)/10/60*1.5 UPS
from V$UNDOSTAT)*block_size)/1024/1024 Tamano_req_MB_MAX
from dba_tablespaces a
where contents = 'UNDO';

Donde Tamano_req_MB_MAX, es el tamaño en MB máximo que necesitamos

reservar para el UNDO Tablespace

Querys útiles para obtener

información general y de
volumetría en una base de datos
Oracle

DATOS GENERALES DE LAS BASES DE DATOS

set pagesize 10000

select a.instance_name as \"INSTANCIA\", a.host_name as \"SERVIDOR\",
b.platform_name as \"SISTEMA OPERATIVO\", a.status as \"ESTATUS\",
a.archiver as \"MODO DE ARCHIVO\",
to_char(b.created,'DD-MM-RRRR HH24:MI:SS') as \"FECHA DE CREACION\"
from v$instance a, v$database b
where a.instance_name=b.name

PARAMETROS DE CONFIGURACION DE LA
BASE DE DATOS
select name, type, value from v$parameter;

VERSION DE LA BASE DE DATOS

set pagesize 0
select * from v$version;

VOLUMETRIA FISICA

set pagesize 10000

select sum(bytes)/1024/1024 as \"Tamaño (MB)\" from dba_data_files;

VOLUMETRIA LOGICA

select sum(bytes)/1024/1024 as \"Tamaño (MB)\" from dba_segments;

RESUMEN DE ESPACIO UTILIZADO POR

ESQUEMA

select owner as \"ESQUEMA\", segment_type as \"TIPO DE OBJETO\",

sum(bytes)/1024/1024 as \"TAMAÑO (MB)\"
from dba_segments
group by owner, segment_type
order by owner;

TABLESPACES
select tablespace_name as \"TABLESPACE\", block_size as \"BLOQUE DE
DATOS\", status as \"ESTATUS\", logging as \"LOGGING\",
extent_management as \"EXTENCION\", allocation_type as \"TIPO DE
ASIGNACION\", segment_space_management as \"SEGMENTO\",
retention as \"RETENCION\"
from dba_tablespaces;

RESUMEN POR TABLESPACES

select decode(grouping(tablespace_name),0,null,1,'TOTAL (MB) =') as \"1\",

tablespace_name as \"TABLESPACE\", segment_type as \"TIPO DE OBJETO\",
sum(bytes)/1024/1024 as \"TAMAÑO (MB)\"
from dba_segments
group by rollup(tablespace_name, segment_type)
order by tablespace_name;

VERIFICACION ESTADISTICA DE TABLAS

select decode(to_char(last_analyzed,'DD-MM-RRRR'),null,'CON FALTA DE

ESTADISTICAS',to_char(last_analyzed,'DD-MM-RRRR')) as \"ANALISIS\",
count(table_name) as \"TOTAL DE TABLAS\"
from dba_tables
group by to_char(last_analyzed,'DD-MM-RRRR')
order by to_char(last_analyzed,'DD-MM-RRRR');

VERIFICACION DE ESTADISTICAS EN
INDICES
select decode(to_char(last_analyzed,'DD-MM-RRRR'),null,'CON FALTA DE
ESTADISTICAS',to_char(last_analyzed,'DD-MM-RRRR')) as \"ANALISIS\",
count(index_name) as \"TOTAL DE INDICES\"
from dba_indexes
group by to_char(last_analyzed,'DD-MM-RRRR')
order by to_char(last_analyzed,'DD-MM-RRRR');

VERIFICACION DEL ESTADO DE LOS INDICES

select a.status as \"ESTATUS\", count(a.index_name) as \"TOTAL\",

sum(b.bytes)/1024/1024 as \"TAMAÑO (MB)\"
from dba_indexes a, dba_segments b
where a.index_name=b.segment_name
group by a.status;