Surveillance de l’utilisation temporaire d’Oracle
Il semble que l’espace de table temporaire d’Oracle reste un mystère pour certains, peut-être parce qu’il n’est pas géré de la même manière qu’un espace de table conventionnel. Les extensions sont allouées et réutilisées afin que la gestion de l’espace ne ressemble en rien à celle d’un espace de table traditionnel. En raison de cela, les erreurs d’espace sont gérées différemment. Voyons comment gérer un espace de table temporaire et quels outils sont à la disposition du DBA.
À partir de la version 8.1.5 Oracle a fourni un » vrai » espace de table temporaire où les extensions sont allouées et affectées aux sessions selon les besoins. Les extensions ne sont jamais supprimées pendant l’exécution de la base de données, de sorte qu’un espace de table temporaire correctement utilisé aura toujours 0 espace « libre ». En ce sens, « libre » signifie non alloué, et tout dans l’espace de table temporaire sera alloué, mais cela ne signifie pas qu’il est utilisé. C’est pourquoi la gestion conventionnelle de l’espace n’a pas sa place dans l’espace de table temporaire.
Alors, comment l’espace de table temporaire est-il géré? C’est fait à travers plusieurs vues V$:
V$TEMPFILE V$TEMPSTAT V$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL V$TEMP_SPACE_HEADER V$TEMPSEG_USAGE (Oracle 9i et les versions ultérieures) V$SORT_USAGE (Oracle 8.1.7, 8.1.6 et 8.1.5)
V$TEMP_EXTENT_MAP rapports de toutes les alloué étendues dans l’espace de stockage temporaire:
SQL> desc v$temp_extent_map Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_ID NUMBER BLOCK_ID NUMBER BYTES NUMBER BLOCKS NUMBER OWNER NUMBER RELATIVE_FNO NUMBER
Les plus utiles points de vue de gérer les tablespaces temporaires sont V$TEMP_EXTENT_POOL et V$TEMPSEG_USAGE/V$SORT_USAGE. VTEMPTEMP_EXTENT_POOL répertorie non seulement les extensions allouées, mais également toutes les extensions utilisées dans un espace de table temporaire par tempfile:
SQL> desc v$temp_extent_pool Name Null? Type ----------------------------------------- -------- ---------------------------- TABLESPACE_NAME NOT NULL VARCHAR2(30) FILE_ID NUMBER EXTENTS_CACHED NUMBER EXTENTS_USED NUMBER BLOCKS_CACHED NUMBER BLOCKS_USED NUMBER BYTES_CACHED NUMBER BYTES_USED NUMBER RELATIVE_FNO NUMBER
VTEMPTEMPSEG_USAGE (ou, si vous préférez, V VSORT_USAGE) affiche l’utilisation du segment temporaire, par utilisateur, pour tous les espaces de table:
SQL> desc v$tempseg_usage Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(30) USER VARCHAR2(30) SESSION_ADDR RAW(8) SESSION_NUM NUMBER SQLADDR RAW(8) SQLHASH NUMBER SQL_ID VARCHAR2(13) TABLESPACE VARCHAR2(31) CONTENTS VARCHAR2(9) SEGTYPE VARCHAR2(9) SEGFILE# NUMBER SEGBLK# NUMBER EXTENTS NUMBER BLOCKS NUMBER SEGRFNO# NUMBER
La surveillance temporaire de l’utilisation de l’espace de table est une tâche simple consistant à interroger VTEMPTEMPSEG_USAGE (VVSORT_USAGE, si cela est préféré) au fil du temps:
select segtype, extents, blocksfrom v$tempseg_usagewhere tablespace = 'TEMP';
N’oubliez pas qu’une telle surveillance ne produira aucune donnée exploitable; à moins que la base de données ne signale des erreurs d’allocation d’espace temporaire, le DBA n’a vraiment rien à voir.
Si vous souhaitez savoir combien d’extensions sont allouées et réellement utilisées par fichier de données, la requête suivante produira un rapport assez utile:
select tablespace_name, file_id, extents_cached extents_allocated, extents_used, bytes_cached/1024/1024 mb_allocated, bytes_used/1024/1024 mb_usedfrom v$temp_extent_pool/
Encore une fois, aucune mesure ne doit être prise sur la base d’un tel rapport; c’est simplement à des fins d’information.
Qui utilise votre espace temporaire, quelles requêtes exécutent-ils et quelle quantité de cet espace est consommée par chacun ? C’est aussi une tâche assez facile à accomplir:
select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocksfrom v$tempseg_usage u, v$sql swhere s.sql_id = u.sql_id;
La vue VTEMPTEMP_SPACE_HEADER fournit une vue moins granulaire de l’espace consommé et disponible; il répertorie l’espace alloué et non alloué dans les fichiers temporaires, par fichier. Gardez à l’esprit qu’alloué ne signifie pas utilisé; il y aura probablement de grandes différences entre ce que VTEMPTEMP_SPACE_HEADER rapporte et ce que VTEMPTEMPSEG_USAGE fournit.
Je vais me citer (d’ailleurs sur le web) en ce qui concerne le dimensionnement approprié des tablespaces temporaires: « Alors, de combien d’espace avez-vous besoin dans votre tablespace temporaire? Cela dépend de l’activité de votre système, du nombre de sessions actives simultanées, de la taille des transactions et de l’espace disque dont vous disposez. Ce n’est pas une honte d’augmenter la taille de votre espace de table TEMPORAIRE au fil du temps à mesure que les modèles d’utilisation, le nombre d’utilisateurs et les volumes de données changent. Oracle vous informera également que l’espace de table temporaire doit être augmenté en émettant des erreurs ORA-01652 (impossible d’étendre le segment temporaire de 128 dans la TEMPÉRATURE de l’espace de table, par exemple). Le nombre signalé dans une erreur ORA-01652 est en blocs, pas en octets, vous devrez donc le convertir à l’aide de la valeur db_block_size pour savoir combien d’octets l’espace de table temporaire avait besoin pour terminer la transaction générant l’erreur. Il n’y a pas de « règle empirique » pour dimensionner un espace de table temporaire car de telles règles créent généralement des situations où le seul outil devient un marteau et chaque tâche finit comme un clou, et le plus souvent, vous frappez ce pouce avec le seul « outil » qui vous a été donné. »
DBA_TEMP_FILES vous indiquera la taille de l’espace de table temporaire:
SQL> select tablespace_name, sum(bytes)/1024/1024 MB 2 from dba_temp_files 3 group by tablespace_name 4 /TABLESPACE_NAME MB------------------------------ ----------TEMP 1024SQL>
Il peut également signaler quels fichiers sont associés à votre espace de table temporaire:
SQL> select tablespace_name, file_name, bytes 2 from dba_temp_files 3 order by tablespace_name, file_name 4 /TABLESPACE_NAME FILE_NAME BYTES--------------- ------------------------------------------------------- ----------TEMP /u2/orawiz/parlopnett/temp01.dbf 1073741824SQL>
Si vous souhaitez ou avez besoin de réduire la taille de votre espace de table TEMPORAIRE, vous devez fermer la base de données, l’ouvrir en mode restreint, redimensionner le fichier temporaire (ou les fichiers temporaires) plus petit, puis arrêter et démarrer la base de données. L’ajout d’espace n’est pas aussi impliqué, car un simple « modifier le redimensionnement du fichier temporaire de la base de données; » augmente l’espace.
Que se passe-t-il si l’espace de table temporaire n’existe pas ou n’est pas disponible ? Oracle affichera ceci:
SQL> select * from mybigtable order by mycoolkey;select * from mybigtable order by mycoolkey*ERROR at line 1:ORA-01129: user's default or temporary tablespace does not exist SQL>
Si vous voyez ce message d’erreur, vérifiez si l’espace de table temporaire existe et est en ligne. Corrigez tous les problèmes que vous trouvez (créez l’espace de table manquant ou remettez les fichiers temporaires en ligne).
Si l’espace de table existe mais qu’aucun fichier temporaire ne lui est attribué (ce qui peut se produire après une restauration/récupération de base de données à partir d’une sauvegarde), Oracle signale:
SQL> select * from mybigtable order by mycoolkey;select * from mybigtable order by mycoolkey*ERROR at line 1:ORA-25153: Temporary Tablespace is Empty SQL>
Ainsi, la tâche consiste simplement à émettre ‘alter temporary tablespace… ajouter un fichier temporaire…’ commandes pour que l’espace de table ne soit plus vide.
La gestion de l’espace de table temporaire n’est pas sorcier, mais elle diffère (parfois considérablement) des procédures de gestion de l’espace de table « normales » utilisées pour les fichiers de données ordinaires. Savoir qu’un espace de table temporaire « complet » n’est pas un problème (rappelez-vous que les segments sont alloués puis réutilisés) peut rendre la tâche un peu plus facile car Oracle vous le dira (avec des erreurs ORA-01652) si vous n’avez plus d’espace. Ce qui signifie que le DBA peut passer du temps sur des tâches utiles, comme les réinitialisations de mot de passe.
Voir tous les articles de David Fitzjarrell