tablespace TEMP enorme 33 Go et plein a 100%

**tibal** · 08/10/2006, 19h11

Bonjour,
J'ai une base en production avec un tablespace TEMP de type temporary gere localement enorme : 33 Go et plein a 100%. L'instance fait 55 Go. Cela me parait anormale. Lorsque je cherche a connaitre les utilisateurs qui utilisent ce tablespace, il y en a peu et ils n'utilisent geeralement qu'environ 15000 blocs. Quelqu'un a t'il deja rencontre ce probleme.
Merci d'avance.

**edi@csb.nc** · 09/10/2006, 04h48

Est-ce que tu parles du fichier TEMP01.dbf ?
Parce que si c'est le cas, je peux peut-être t'aider puisque nous rencontrons assez souvent ce problème au boulot. Ce qu'on fait, c'est qu'on supprime puis recrée simplement ce fichier de la manière suivante:
- se connecter sous SQL*Plus en tant que "sys";
- exécuter : ALTER DATABASE TEMPFILE '[chemin du fichier]\TEMP01.dbf' DROP INCLUDING DATAFILES;
- exécuter : ALTER TABLESPACE TEMP ADD TEMPFILE '[chemin du fichier]\TEM01.dbf' SIZE 2000M REUSE AUTOEXTEND ON;

Bon, ce n'est pas top puisque le problème fini par revenir quelques mois après. De plus, parfois ces 2 requêtes SQL ne sont pas suffisantes et nous sommes contraints de réinstaller la base entièrement...

Si quelqu'un a une autre idée je suis preneuse...

**LeoAnderson** · 09/10/2006, 08h31

vous au moins, vous ne vous embêtez pas !!!!

1. Savoir qui et pourquoi consomme du temp
2. vérifier s'il n'y a pas des distinct utilsés à tort et à travers
3. Si besoin, créer plusieurs temp pour identifier + finement les consommateurs

**Arturius** · 09/10/2006, 08h55

Le tablespace temp est utilisé pour stocké les tri qui ne peuvent pas être complètement résaliser un mémoire, les données des tables temporay.

L'espace du tablespace temp est marqué comme utilisé dans DBA Studio si à un moment il a été utilisé. Cela veux dire que l'instruction qui a utilisé le tablespace temp n'est plus forcement activite même si le tablespace est marqué comm occupé.

Le problème est qu'il y a probable un requête qui consomme de manière abusive de l'espace temporaire.

**pifor** · 09/10/2006, 09h03

La vue V$SORT_USAGE peut permettre de retrouver les requêtes qui utilisent l'espace temporaire. voir http://www.developpez.net/forums/sho...v%24sort_usage

**tibal** · 11/10/2006, 11h45

Bonjour et merci de vos réponses.
Lorsque je lance la requete donnee par Aline, je trouve soit aucun utilisateur, soit un utilisateur ayant pour la champ SPACE la valeur 29360128. Je suppose qu'il s'agit de blocks (8Ko surr le TBS). Mais ce qui est currieux, c'est que même lorsque je ne trouve aucun utilisateur avec la requete, je vois le TBS à 100% sur la console d'Entreprise Manager dans la section STOCKAGE -> ESPACES DISQUE LOGIQUE.
Quelqu'un peu t'il m'expliquer la raison.

Merci d'avance.

**bouyao** · 11/10/2006, 11h52

Ce que je pense moi c'est que tu kill beaucoup les sessions

**LeoAnderson** · 11/10/2006, 11h52

Les blocks temp ne sont jamais désalloués même s'ils ne sont plus utilisés.
Il n'y a qu'un stop/start de la base pour désallouer le temp.

**Jaouad** · 11/10/2006, 12h03

il existe d'autres solution qui consiste à modifier les paramétres de storage , mais je ne sais pas si cela marche avec les Tempfiles ..

**aline** · 11/10/2006, 12h34

Envoyé par tibal

Bonjour et merci de vos réponses.
Lorsque je lance la requete donnee par Aline, je trouve soit aucun utilisateur, soit un utilisateur ayant pour la champ SPACE la valeur 29360128. Je suppose qu'il s'agit de blocks (8Ko surr le TBS). Mais ce qui est currieux, c'est que même lorsque je ne trouve aucun utilisateur avec la requete, je vois le TBS à 100% sur la console d'Entreprise Manager dans la section STOCKAGE -> ESPACES DISQUE LOGIQUE.
Quelqu'un peu t'il m'expliquer la raison.

Merci d'avance.

Bon alors, on reprend tout au début.
Ce que ma requête t'indique, n'a rien à voir avec la taille d'un bloc, on ne s'en préoccupe pas ici, mais au nombre d'extents aloué (et donc à l'espace utilisé).
Pour savoir comment fonctionne ton tablespace:

Code :

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

1
2
3
4
 
select tablespace_name, initial_extent,next_extent, allocation_type
from DBA_Tablespaces 
where tablespace_name='TEMP'

J'immagine que tu as des valeurs par défaut, dans ce cas, tu as une allocation uniforme de blocs de 1M0 (1048576).

Dans la requête que tu utilises, tu utilises donc 28Mo (ou 28 extents dans la plupart des cas).

Le tablespace pour les tris doit être autoextensible jusqu'à 33 Go.

donc tu dois trouver la session qui fait des tris de 33 Go et la tunner.
Maintenant, en attendant, la seule solution est de créer un deuxième tablespace pour les tris et de dropper le premier (quand personne ne l'utilise évidément!).

**LeoAnderson** · 11/10/2006, 12h41

Envoyé par aline

donc tu dois trouver la session qui fait des tris de 33 Go et la tunner.
Maintenant, en attendant, la seule solution est de créer un deuxième tablespace pour les tris et de dropper le premier (quand personne ne l'utilise évidément!).

ou les 2 sessions concurrentes qui font 16.5 Go de tris
ou les 4 sessions concurrentes qui font 8.25 Go de tris
mais de toute façon, avec de tels volumes de tris, il faudrait être aveugle pour ne pas les voir !

Sinon, pour isoler les schémas, créez 3~4 tablespaces TEMP et affectez-les à certains users afin de savoir déjà quels sont les comptes qui bouffent autant de temp (et si vous devez le dropper/recreer, l'impact sera moindre puisque seulement le 1/4 des utilisateurs sera impacté)

[edit]
une éventualité non négligeable est qu'un jour, un utilisateur a loupé sa requête et à necessité 33 Go mais depuis, il a corrigé le pbe et il n'y a plus besoin que de 28 Mo de temp !

[/edit]

**orafrance** · 11/10/2006, 14h44

Ou la table triée à des NEXT très gros et ça surconsomme le TEMP pour rien

**bouyao** · 11/10/2006, 15h24

Et le version Oracle ? ca nous aident a expliquer ton problème.

Il y a des tablespace temporaire gérés dans le dictionnaire et d'autres non.

des fois le temps de démarrage de la base est très long ( ca peut être 1h ou plus) car le process SMON essaye de vider la tablespace temporaire.

ce temps est caluclé suivant la somme des extents et la taille du db_buffer.

Il y a une astuce pour accelerer le demarrage de la base, il suffit de diminuer la taille de db_buffer dans le init.ora et de redemarrer la base.

Par contre dans les nouvelles versions ce n'est pas pareil.

Comme ta tablespace temporaire n'a pas été vidé, ca veut dire que le smon ne fait pas son travail chaque 2 heure. Dans certains versions ca vient d'un bug.
dans les autres c'est souvent le fait d'interrompre les sessions qui font des tris, ce qui donne beaucoup de travail a SMON.

des fois ca vient des verrous (enqueue). Car un enqueue TS (temporary segment) est acquis à chaque fois le SMON trouve un segmentà supprimer.

Pour identifier si SMON est entrains de nettoyer, il faut vérifier le nombre de segments temporaire.

Code :

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

1
2
 
select count(*) from db_extents where segment_type = 'TEMPORARY';

si ce nombre s'accroit constamment, signifie que ce processes est entrain de mourir avant de nettoyer le segment temporaire.

SMON crée un curseur pour trouver les segments temporaires à nettoyer, la requête est :

Code :

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

1
2
 
select file#, block#, ts# from seg# where type#=3;

A chaque candidat, une verification est effectué pour voir si le deteneur du segement temporaire est en vie. Sinon, l'enqueue TS est acqui et le segment est supprimé.

il y'a une technique pour reveiller le smon. (c'es a vous de chercher).

**LeoAnderson** · 11/10/2006, 15h36

Comme ça ne s'invente pas et que ça ne doit pas être super documenté...
A faire sous SQL*Plus

Code :

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

1
2
3
4
5
6
7
8
9
    SELECT pid FROM v$process
    WHERE addr = 
    (
        SELECT paddr FROM v$bgprocess
        WHERE name = 'SMON'
    );
 
 
    ORADEBUG WAKEUP 6

mais quand j'en ai eu besoin, smon dormait trop profondément ou quoi, il n'a jamais daigné se réveiller ! :S

**orafrance** · 11/10/2006, 15h40

6 c'est le PID résultant de la requête ?

j'ai jamais eu à réveiller le SMON moi... il doit être suffisamment occupé pour pas s'endormir

**LeoAnderson** · 11/10/2006, 15h45

Envoyé par Fred_D

6 c'est le PID résultant de la requête ?

oui !

En général : 6 => 8i, 7 => 9i et 8 => 10g

**aline** · 11/10/2006, 16h01

excellent!

J'adore de plus en plus ce forum

**orafrance** · 11/10/2006, 16h04

c'est sûr que c'est plus sympa qu'auféminin.com

Merci Leo

**tibal** · 13/10/2006, 16h01

Bonjour,
Je n'ai pas trouvé l'utilisateur qui a fait grossir le tablespace temporaire (Oracle 9.2.0.7). Je sais que de gros traitement tournent qui doivent générer de gros tris. Ils doivent être à l'origine de la grande taille du TBS. Nous allons donc le supprimer puis le récreer.
Merci de votre aide.

**Wurlitzer** · 23/10/2006, 11h22

Une petite question en passant ou plutot une confirmation.

Je suis en Oracle 9.2 quand je fais un backup (hot en l'ocurence mais je pense qu'en cold ca doit etre la meme chose).

J'ai besoin de sauvegarder uniquement les datafiles ? Les temp files je ne suis pas obligé de les sauvegarder car je pourrais les recréer au moment de la restauration.

En attendant que je fasse un test pour me rassurer. Pouvez vous me confirmer si c'est bien ca ?

Merci,

tablespace TEMP enorme 33 Go et plein a 100%

Administration Oracle

Discussions similaires

Partager

Partager