Fabien Celaia

Introduction

Est-ce aussi trivial que cela de demander à son DBA si la base est pleine ou non ?

Dans mon organisation, nous gérons des centaines d’applications hétérogènes dans de nombreuses bases. Nous travaillons donc par affinité de schéma (en attendant qu'Oracle redevienne raisonnable et passe son modèle multitenant dans sa version de base).

Nous avons donc quelques couches

• un schéma est rattaché à un tablespace
• un tablespace peut avoir plusieurs datafiles
• ces datafiles sont rattachés à un disk group ASM
• un disk group ASM peut comprendre plusieurs disques

Si vous persistez à ne pas utiliser ASM, revoyez mon script en conséquence...

La réponse apportée à la question "mon application est pleine à quel pourcentage ?" n'est donc pas si triviale

• le tablespace de l'application est rempli dans une certaine mesure
• est-il extensible ? si oui, jusqu'à où ? Il faut donc descendre au niveau du Disk Group pour déterminer sa taille maximale

Démarche

La vue V$ASM nous remonte l'information sur la disponibilité de l'espace au niveau Disk Group.

Les vues dba_data_files et dba_temp_files font le lien entre le disk_group et les tablespaces

Dans un premier temps, on se focalise sur les datafiles pour déterminer les fichiers utilisés et leur taux d'occupation, remonté au niveau du tablespace, y.c. les temporaires

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select d.tablespace_name,
        d.autoextensible, 
        substr(d.file_name,1,instr(d.file_name,'/')-1) DG_NAME,
        sum(d.bytes)/1024/1024  TAILLE_MB,
        sum(d.maxbytes)/1024000  MAXTAILLE_MB
from dba_data_files d
group by d.tablespace_name, d.autoextensible, substr(d.file_name,1,instr(d.file_name,'/')-1)
union 
select t.tablespace_name,
        t.autoextensible, 
        substr(t.file_name,1,instr(t.file_name,'/')-1) ,
        sum(t.bytes)/1024/1024  ,
        sum(t.maxbytes)/1024000  
from dba_temp_files t
group by t.tablespace_name, t.autoextensible, substr(t.file_name,1,instr(t.file_name,'/')-1)

On obtient donc les colonnes suivantes :

1. TABLESPACE_NAME : le nom du tablespace analysé
2. AUTOEXTENSIBLE : s'il peut s'étendre automatiquement ou non
3. DG_NAME : le disk group ASM dans lequel il peut s'étendre
4. TAILLE_MB : sa taille actuelle (ce qu'il occupe, pas ce qu'il contient)
5. MAXTAILLE_MB : la taille que ses divers datafiles peuvent prendre au maximum

On détermine ensuite la taille que les objets de chaques tablespaces prennent réellement, ceci en attaquant la vue dba_segments

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select tablespace_name, sum(bytes)/1024000  UTILISE_MB 
from dba_segments 
group by tablespace_name

On obtient donc les colonnes suivantes :

1. TABLESPACE_NAME : le nom du tablespace analysé
2. UTILISE_MB: la taille occupée réellement par les objets du tablespace

Attention : les objets temporaires n'apparaissent pas. cela va nous inciter par la suite à travailler avec des jointures externes

Reste à faire le lien avec les disques ASM. Là, inutile de descendre trop bas. L'information utile (à savoir l'espace encore disponible) et accessible directement via la vue V$ASM_DISKGROUP. Prendre juste garde que le nom du diskgroup dans a vue v$asm ne comprend pas le '+' initial, contrairement au chemin donné dans dba_data_files.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select name, total_mb, free_mb, total_mb-free_mb busy_mb
from v$asm_diskgroup

On obtient les colonnes suivantes :

1. NAME : le nom du diskgroupanalysé
2. TOTAL_MB: sa taille maximale
3. FREE_MB : sa taille encore disponible pour les extensions de nos datafiles
4. BUSY_MB : la taille déjà occupée

Le final

Voilà : nous avons extrait toute l'informaiton utile. Reste à nous créer un indicateur correpsondant à nos besoins

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
select df.tablespace_name,
       TAILLE_MB, 
       case df.autoextensible WHEN 'NO' then TAILLE_MB else ASM.FREE_MB end MAX_MB, 
       UTILISE_MB,
       100/TAILLE_MB*UTILISE_MB UTILISE_PCT, 
       100/(case df.autoextensible WHEN 'NO' then TAILLE_MB else ASM.FREE_MB end)*UTILISE_MB UTILISE_POTENTIEL_PCT
from 
(select d.tablespace_name,
        d.autoextensible, 
        substr(d.file_name,1,instr(d.file_name,'/')-1) DG_NAME,
        sum(d.bytes)/1024/1024  TAILLE_MB,
        sum(d.maxbytes)/1024000  MAXTAILLE_MB
from dba_data_files d
group by d.tablespace_name, d.autoextensible, substr(d.file_name,1,instr(d.file_name,'/')-1)
union 
select t.tablespace_name,
        t.autoextensible, 
        substr(t.file_name,1,instr(t.file_name,'/')-1) ,
        sum(t.bytes)/1024/1024  ,
        sum(t.maxbytes)/1024000  
from dba_temp_files t
group by t.tablespace_name, t.autoextensible, substr(t.file_name,1,instr(t.file_name,'/')-1)
) df left join 
(select tablespace_name, sum(bytes)/1024000  UTILISE_MB 
from dba_segments 
group by tablespace_name) s on s.tablespace_name=df.tablespace_name
inner join v$asm_diskgroup asm on df.DG_NAME='+'||asm.name
order by 1

Ce qui nous donne les colonnes suivantes

1. TABLESPACE_NAME : le nom de chaque tablespace
2. TAILLE_MB : la taille q'on lui a attribué pour l'instant
3. MAX_MB : la taille qu'il pourrait encore aller chercher (dans le pot commun du disk group)
4. UTILISE_MB : la taille actuelle de tous ses objets
5. UTILISE_PCT : le % d'utilisation de ce qu'il a actuellement, avant extension
6. UTILISE_POTENTIEL_PCT : le % utilisé par rapport à sa taille maximale envisageable

C'est cette dernière colonne qui détermine si oui ou non, il y a risque de saturation.

Quelques imprécisions restent encore à corriger

1. L'occupation des UNDO tablespaces
2. L'occupation des tablespaces temporaires
3. la gestion de l'espace de la FRA

La démarche me semble à peu près correcte, mais je suis prêt à en débattre si une critique constructive est remontée...
Toute aide est la bienvenue...