Oracle : détecter et résorber la fragmentation des tables et tablespaces
Requêtes pour mesurer la fragmentation des tablespaces, tables et datafiles Oracle, et méthodes pour récupérer l'espace (shrink, move, index rebuild, UNDO).
Cet article rassemble des requêtes pour détecter la fragmentation de l'espace dans une base Oracle (tablespaces, tables, datafiles) et les méthodes usuelles pour récupérer l'espace perdu.
Concepts
Le high water mark (HWM) est la limite entre les blocs déjà utilisés et les blocs jamais utilisés d'un segment. Après de nombreuses suppressions, une table peut contenir beaucoup de blocs vides sous le HWM : les full scans les lisent toujours et le datafile ne peut pas être réduit.
Le HWM se calcule ainsi (la procédure DBMS_SPACE.UNUSED_SPACE le fournit aussi) :
High Watermark = Total blocks - Unused blocks - 1
select blocks from dba_segments where owner = upper('HR') and segment_name = upper('JOBS');
analyze table hr.jobs estimate statistics;
select empty_blocks from dba_tables where owner = upper('HR') and table_name = upper('JOBS');
Identifier les tablespaces fragmentés
Indice de fragmentation par tablespace
Nombre de chunks libres, plus grand chunk libre et indice de fragmentation (plus l'indice est élevé, moins le tablespace est fragmenté) :
SELECT
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
FROM
sys.dba_free_space
GROUP BY
tablespace_name
ORDER BY 2 desc, 1;
Contenu d'un tablespace
select distinct segment_type from dba_segments where tablespace_name = 'MY_TABLESPACE';
select distinct owner, segment_name, partition_name, segment_type, segment_subtype, tablespace_name
from dba_segments
where tablespace_name = 'MY_TABLESPACE';
Datafiles d'un tablespace
column FILE_NAME format a80
column BLOCKS format 999999999
column TABLESPACE_NAME format a30
SELECT
FILE_NAME, BLOCKS, TABLESPACE_NAME
FROM
DBA_DATA_FILES
WHERE
TABLESPACE_NAME = 'MY_TABLESPACE';
Carte des extents d'un tablespace
Espace libre et extents d'un tablespace, triés par fichier et par bloc (variable de substitution SQL*Plus &ts) :
create view TABLESPACE_MAP_VIEW as
SELECT 'free space' owner, ' ' OBJECT, dfs.file_id, dfs.block_id, dfs.blocks,
dfs.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME
FROM dba_free_space dfs, DBA_DATA_FILES ddf
WHERE dfs.tablespace_name = UPPER ('&ts.') and ddf.file_id = dfs.file_id
UNION
SELECT SUBSTR (dext.owner, 1, 20), SUBSTR (dext.segment_name, 1, 32), dext.file_id, dext.block_id,
dext.blocks, dext.blocks*8192/1024/1024 size_In_MB, ddf.FILE_NAME
FROM dba_extents dext, DBA_DATA_FILES ddf
WHERE dext.tablespace_name = UPPER ('&ts.') and dext.file_id = ddf.file_id
ORDER BY 3, 4;
Identifier les tables fragmentées
Les requêtes ci-dessous comparent l'espace alloué à une table (blocks) à l'espace que devraient occuper ses lignes (num_rows * avg_row_len). Elles reposent sur les statistiques de l'optimiseur : vérifiez qu'elles sont récentes. Elles supposent aussi une taille de bloc (8 Ko pour blocks*8, 16 Ko pour blocks*16) : adaptez le facteur à votre db_block_size.
Tables fragmentées de toute la base
Tables dont l'écart entre taille allouée et taille réelle dépasse 100 Mo :
set lines 170
set pages 10000
col owner format a30
col table_name format a30
col TOTAL_SIZE format 99999999999
col ACTUAL_SIZE format 999999999999
col FRAGMENTED_SPACE format 999999999999
select owner, table_name, blocks, num_rows, avg_row_len,
round(((blocks*8/1024)),0) "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),0) "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),0) "FRAGMENTED_SPACE"
from dba_tables
where owner not in ('SYS','SYSTEM','PERFSTAT')
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100
order by 8 desc;
Top 25 des tables fragmentées d'un schéma
select * from (
select owner, table_name, round((blocks*8),2) "size (kb)",
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (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 owner in ('&SCHEMA_NAME')
and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 5 desc )
where rownum < 25;
Pourcentage de fragmentation d'une table
select table_name, avg_row_len,
round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from all_tables
where table_name = '&TABLE_NAME';
Taille physique et taille des données
Taille physique occupée par la table dans les datafiles :
select table_name, round((blocks*8)/1024,2)||'MB' "size"
from dba_tables
where table_name = 'MY_TABLE' and owner = 'MY_SCHEMA';
Taille estimée des données réellement présentes :
select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size"
from dba_tables
where table_name = 'MY_TABLE' and owner = 'MY_SCHEMA';
Gain estimé avec DBMS_SPACE
Une méthode fondée sur DBMS_SPACE estime le pourcentage d'espace qu'une réorganisation permettrait de récupérer. Elle nécessite une fonction d'aide (table_saving_function) et les scripts create_table_cost / table_saving décrits dans l'article cité en références. La sélection des tables candidates est :
select a.owner, a.table_name, table_saving_function(a.owner, a.table_name) as percentage_gain
from dba_tables a
where a.owner = 'MY_SCHEMA'
and a.status = 'VALID' -- In valid state
and a.iot_type is null -- IOT tables not supported by dbms_space
--and external = 'no' -- starting from 12cR2
and not exists (select 'x' from dba_external_tables b
where b.owner = a.owner and b.table_name = a.table_name)
and temporary = 'N' -- Temporary segment not supported
and a.last_analyzed is not null -- Recently analyzed
order by 3 desc;
Récupérer l'espace
Solutions courantes, de la moins à la plus intrusive :
- Reconstruire les index.
- Réduire la table (shrink) ou la déplacer (vers un autre tablespace ou le même).
- Exporter puis réimporter les données (Data Pump,
expdp/impdp). COALESCEdes extents libres (à tester avant usage).
Dans les tablespaces gérés localement, Oracle peut aussi défragmenter tables et index en ligne.
Réduire une table (shrink)
SHRINK SPACE nécessite que le row movement soit activé et compacte les données en ligne. Collectez les statistiques avant et après pour mesurer le résultat :
select table_name, last_analyzed, stale_stats from dba_tab_statistics where table_name = 'MY_TABLE';
EXEC dbms_stats.gather_table_stats('MY_SCHEMA','MY_TABLE');
ALTER TABLE MY_SCHEMA.MY_TABLE enable ROW movement;
ALTER TABLE MY_SCHEMA.MY_TABLE shrink SPACE;
EXEC dbms_stats.gather_table_stats('MY_SCHEMA','MY_TABLE');
Avec les scripts d'aide mentionnés plus haut, la même séquence est encadrée par une mesure avant et après :
@create_table_cost MY_SCHEMA MY_TABLE
@table_saving MY_SCHEMA MY_TABLE
-- enable row movement, shrink space, gather statistics (voir ci-dessus)
@table_saving MY_SCHEMA MY_TABLE
Déplacer une table et reconstruire ses index
Un MOVE réécrit la table mais rend ses index UNUSABLE : reconstruisez-les ensuite et collectez les statistiques.
alter table MY_SCHEMA.MY_TABLE move;
select index_name from dba_indexes where table_name = 'MY_TABLE';
alter index MY_INDEX_1 rebuild online;
alter index MY_INDEX_2 rebuild online;
EXEC dbms_stats.gather_table_stats(ownname => 'MY_SCHEMA', tabname => 'MY_TABLE', method_opt => 'for all indexed columns size skewonly', granularity => 'ALL', degree => 8, cascade => true, estimate_percent => 15);
select sum(bytes)/1024/1024/1024 from dba_segments where segment_name = 'MY_TABLE';
Réduire un datafile
Si le datafile ne contient qu'un seul objet, un shrink en ligne peut suffire à compacter les données et à libérer les blocs vides, ce qui permet de redimensionner le datafile. Le shrink ne fait que compacter les données : il ne garantit pas que les blocs libres se trouvent en fin de fichier. S'il reste des blocs utilisés en fin de fichier, le datafile ne peut pas être réduit et il faut déplacer les objets ou employer une autre méthode.
Extents par tablespace et type de segment, et segments d'index :
select tablespace_name, count(*) as "# of extents", SEGMENT_TYPE,
sum(BYTES) as "Total of Byte", sum(BLOCKS) as "Total of block"
from dba_extents
group by tablespace_name, SEGMENT_TYPE
order by tablespace_name;
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'INDEX'
--AND OWNER = 'HR'
ORDER BY SEGMENT_NAME;
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/DB11G/reclaim01.dbf' AUTOEXTEND OFF;
Recréer le tablespace UNDO
Un tablespace UNDO surdimensionné ne peut pas toujours être réduit. Basculez sur un tablespace UNDO temporaire, supprimez l'ancien, recréez-le à la taille voulue, rebasculez, puis supprimez le temporaire :
create undo tablespace UNDOTBS2 datafile '/path/undotbs2.dbf' size 500m;
alter system set undo_tablespace = undotbs2 scope=both;
drop tablespace UNDOTBS1 including contents and datafiles;
create undo tablespace UNDOTBS1 datafile '/path/undotbs1.dbf' size 500m autoextend on next 100m maxsize 10000m;
alter system set undo_tablespace = undotbs1 scope=both;
drop tablespace UNDOTBS2 including contents and datafiles;
Remarques
- Tous les noms de schémas, de tables et de chemins ci-dessus sont des valeurs génériques.
- Les estimations fondées sur les statistiques ne valent que ce que valent les statistiques : collectez-les avant de mesurer.
- Exécutez
MOVE,SHRINKet les reconstructions d'index hors des heures de pointe, après un test sur une copie hors production. Supprimer un tablespace UNDO avecincluding contents and datafilesest irréversible.