¿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.
