Cómo corregir las tablas fragmentadas de una base de datos Oracle 11g

¿Qué es la fragmentación de datos?

Si a una tabla únicamente se somete a inserciones, no habrá ninguna fragmentación. La fragmentación viene solo cuando hacemos borrados/actualizaciones a la tabla.
El espacio que se libera durante las operaciones “no-inserts” no son usadas inmediatamente (algunas veces jamás se usaran). Esto deja huecos en la tabla que resultan en fragmentación.

la fragmentación de las tablas es diferente a la fragmentación de archivos, cuando se realizan muchas operaciones DML (Data manipulation language) en una tabla, la tabla se fragmenta, debido a que las DML no liberan el espacio de la tabla debajo de la High Water Mark ( HWM=  es la última parte de una tabla que le indica al cursor que hasta allí llego la tabla y contiene la cantidad de registros)

Cada vez que la información crece, elimina el bloque donde está el HWM y recreando otro HWM cuando termina de incrementar el espacio, estos bloques donde estaban los HWM’s no ocupan casi nada de espacio, pero después de muchísimo uso y mucho tiempo ya pueden ser significativos en el tiempo de lectura.

¿Cuáles son las razones de reorganizar la tabla?

·         bajo tiempo de respuesta (de esa tabla en especial)

·         Alto número de campos encadenados (o migrados)

·         La tabla ha crecido muchos bloques y el espacio antiguo no ha sido reutilizado

Nota: Los consultas basados en índices no se beneficiaran mucho por la reorganización comparado con las consultas que hacen un “full table scan”

 ¿Cómo encontrar la fragmentación de las tablas?

En los esquemas de Oracle en donde se encuentran una gran diferencia entre el tamaño actual (se encuentra en la vista user_segments) y el tamaño esperado de user_tables (Num_rows*avg_row_length (in bytes)). Toda esta diferencia se debe a la fragmentación de la tabla o la columna de stats no está actualizada en dba_tables.

Pasos para revisar y remover la fragmentación de las tablas:
Obtener las estadísticas de las tablas:

Para tener la diferencia exacta del tamaño actual (dba_segments) y el tamaño de las stats (dba_tables). La diferencia entre estos valores reportara la verdadera fragmentación al DBA. Así que para tener actualizadas las stats en dba_tables. Revisa el valor de LAST_ANALYZED de la tabla en dba_tables. Si este valor es reciente, puedes brincarte este paso, además yo sugeriría obtener las estadísticas de las tablas para actualizar este valor.

exec dbms_stats.gather_table_stats(‘&schema_name’,’&table_name’);

Revisa el tamaño de la tabla:

De nuevo revisa el tamaño de la tabla y fíjate si se redujo. Recuerda que el dato esta en bytes, lo transformas a kb dividiéndolo en 1024 y a megabytes otra vez dividiendo en 1024

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

 Revisa la fragmentación de la tabla:

El siguiente query te mostrara el tamaño total de la fragmentación esperada y cuanto porcentaje del tamaño se puede reclamar después de remover la fragmentación, el administrador de la base de datos te tiene que proveer el table_name y el schema_name, eso te lo pedirá este query.

set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||’kb’ “Tamanio fragmentado”, round((num_rows*avg_row_len/1024),2)||’kb’ “Tamanio Actual”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “espacio reclamable % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/

Esta consulta obtiene datos de dba_tables, así que la precisión del resultado depende de los stats del dba_table. Si tú encuentras espacio reclamable mayor al 20%, entonces podemos esperar que exista fragmentación es en esta tabla, suponiendo que el dba encuentra el 50% reclamable de la consulta anterior, puede proceder a remover la fragmentación.

¿cómo reiniciar los HWM /remover fragmentación?

Tenemos cuatro opciones diferentes para reorganizar las tablas fragmentadas

• Alter table move (mover la tabla a otro tablespace o dentro del mismo tablespace) y reconstruir índices.
• Exportar e importar la tabla. (Muy difícil de implementar en un ambiente productivo)
• Comando Shrink (de Oracle 10g para arriba) (el comando shrink solo es aplicable a las tablas que tengan activado “auto segment space management)
• Alter table move (mover la tabla a otro tablespace o dentro del mismo tablespace) y reconstruir índices.

Recolecta el estatus de todos los índices de la tabla:
Recolectaremos todos los estatus de los índices en un solo lugar.

select index_name,status from dba_indexes where table_name like ‘&table_name’;

Mueve la tabla al mismo tablespace o a uno diferente:

En este paso moveremos la tabla fragmentada a otro espacio (o al mismo) para recuperar el espacio fragmentado, encuentra el tamaño actual de tu table de los segmentos de dba_segments y revisa si continua con el mismo espacio libre.

alter table <table_name> move; <—mueve la tabla al mismo tablespace

O

alter table <table_name> enable row movement;
alter table <table_name> move tablespace <nuevo_tablespace>;
Luego tienes que regresarlo al tablespace antiguo usando el siguiente comando:
alter table table_name move tablespace tablespace_viejo;

Ahora reconstruye los índices

Necesitamos reconstruir todos los índices de la tabla porque con el comando move todos los índices viejos se vuelven inservibles

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME

——– ——————————
UNUSABLE INDEX_NAME ——-> El valor que te diga aquí te dice si el índice sirve o no sirve

SQL> alter index <INDEX_NAME> rebuild online; ——-> Usa este comando para cada índice
Index altered.

SQL> select status,index_name from dba_indexes where table_name = ‘&table_name’;

STATUS INDEX_NAME
——– ——————————
VALID INDEX_NAME ——-> después de ejecutar el comando anterior, aquí te debe de decir VALID

Obtener estadísticas de la tabla
——————
SQL> exec dbms_stats.gather_table_stats(‘&owner_name’,’&table_name’);
PL/SQL procedure successfully completed.

Revisa el tamaño de la tabla:
—————–
De nuevo revisa el tamaño de la tabla y verifica si ya bajo de tamaño

select table_name,bytes/(1024*1024*1024) from dba_table where table_name=’&table_name’;

Revisa la fragmentación de la tabla:
——————————–
set pages 50000 lines 32767
select owner,table_name,round((blocks*8),2)||’kb’ “Fragmented size”, round((num_rows*avg_row_len/1024),2)||’kb’ “Actual size”, round((blocks*8),2)-round((num_rows*avg_row_len/1024),2)||’kb’,
((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 “reclaimable space % ” from dba_tables where table_name =’&table_Name’ AND OWNER LIKE ‘&schema_name’
/

 Usa el comando shrink (para Oracle 10g)

Comando shrink:

Entre las nuevas características de 10g para reorganizar (shrink) las tablas “casi” en línea, puede ser usada junto al ASS (automatic segment space management)

Este comando solo es aplicable para tablas que el tablespace tenga ASS

Antes de usar este comando, tú debes de activar el “row movement”
SQL> alter table <table_name> enable row movement;
Table altered.

Existen dos maneras de usar este comando.

1. Reorganiza los campos y resetea el HWM
Parte 1: reorganiza (todas las DML pueden ocurrir mientras haces esto)

 SQL> alter table <table_name> shrink space compact;
Table altered.

 Parte 2: resetea el HWM (mientras haces esto no debes de ejecutar DMS. pero es muy rápido, casi imperceptible)

SQL> alter table <table_name> shrink space;
Table altered.

2. Directamente resetea el HWM:

SQL> alter table <table_name> shrink space; (con este comando se resetea y se reorganiza a la vez)
Table altered.