Como corregir las tablas fragmentadas de Oracle

abril 4, 2014
admin

Como corregir las tablas fragmentadas de Oracle

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

 

¿Qué es la fragmentación de datos?
Si a una tabla únicamente se somete a inserts, no habrá ninguna fragmentación. La fragmentación viene solo cuando hacemos deletes/updates 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.

Como corregir las tablas fragmentadas de Oracle

Para tener esto más claro, necesitaremos saber cómo Oracle maneja los espacios para las tablas.
Los campos de las tablas no se almacenan de forma contigua, o si los campos están divididos en más de un bloque, el performance disminuye debido a que esos campos requieres bloques de acceso adicionales.

 

Nota: la fragmentación de las tablas son diferentes 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 HWM (High Water Mark)

 

HWM= High water mark, 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?

A) bajo tiempo de respuesta (de esa tabla en especial)
B) Alto número de campos encadenados (o migrados)
C) La tabla ha crecido muchos bloques y el espacio antiguo no ha sido reutilizado
Nota: Los querys basados en índices no se beneficiaran mucho por la reorganización comparado con los querys 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:
—————————————————————————————————————-

1. 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’);

 

—————————————————————————————————————–

2. 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’;

 

—————————————————————————————————————–

 

3. 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’
/

 

Nota: Este query 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 esn esta tabla, suponiendo que el dba encuentra el 50% reclamable del query anterior, puede proceder a remover la fragmentación.

 

—————————————————————————————————————–

 

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

Tenemos cuatro opciones diferentes para reorganizar las tablas fragmentadas

 

  1. Alter table move (mover la tabla a otro tablespace o dentro del mismo tablespace) y reconstruir índices.
  2. Exportar e importar la tabla. (Muy difícil de implementar en un ambiente productivo)
  3. Comando Shrink (de Oracle 10g para arriba) (el comando shrink solo es aplicable a las tablas que tengan activado “auto segment space management)
  4. Aquí solo mostrare las opciones 1 a la 3, para tener la tabla accesible.
  5. 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’
/

 

Opción 3: 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.

 

Ventajas del método convencional son:

  • a diferencia del “alter table move …” los índices no se vuelven un usables, después del comando shrink los índices se recrean solos
  • es una operación ONLINE, no necesitas parar la operación para ejecutar esto
  • no requiere espacio extra para ejecutar esto

4 Comments. Leave new

Francisco Herrera
abril 5, 2016 2:23 pm

muy buen aporte, pero tengo una duda.

que pasa o que se debe que al revisar la fragmentacion de la tabla salga un resultado negativo??

En varias tablas que revise con este query el resultado era de -2.8%

Saludos.

Responder

no me ha ocurrido, pero debe de estar relacionado al tipo de campos que tenga la tabla, yo adivinaría que esa tabla tiene campos tipos blob o cblob, de ser asi entonces marca error debido al largo del campo que no viene considerado en el calculo en el query

Responder
Francisco Herrera
abril 5, 2016 4:54 pm

De hecho ninguna tabla de las que valide que sale con un resultado negativo tiene campo BLOB, CLOB, LONG o LOB

Responder

Estimados, no se si estoy mal pero el calculo del % tiene un -10 al final, a mi me parece que esta demás.

Saludos
SNN

Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *