Requete qui debouche sur "unable to extend temp segment

Bonjour à tous,

J'avais une requete qui fonctionnait bien jusqu'a ce que je charge beaucoup de donnée. Maintenant elle débouche sur "unable to extend temp segment by 128 in tablespace TEMP_1". Le DBA a agrandi le tablespace TEMP_1 mais cela plante toujours. Peut-etre que ma requete est "mauvaise". La voici (simplifiée) :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

select T.TB0000_IDAFFAIRE, TB0013_IDEMPLOYE from CM, CB, T, EXE, FONCIND WHERE FONCIND.IDINDICATEUR='Chef' and T.IDAFFAIRE=CM.IDAFFAIRE and CB.IDMARQUE=CM.IDMARQUE and EXE.IDEMPLOYE=T.IDEMPLOYE and EXE.IDFONCTION=FONCIND.IDFONCTION

---

C'est basique et les jointures semblent bonnes non ?

N'ayant pas le schéma relationnel, je ne peux pas être affirmatif, mais cela me semble curieux de vous baser sur 5 tables pour ramener les colonnes d'une seule table, et en plus, je pense qu'il manque des jointures ...

Oui, elles semblent toutes la (les jointures) mais comme tes noms de tables sont tout sauf parlants et qu'on ne connait pas le schema relationnel de ta base, il est difficile d'etre affirmatif :(. Ce serai bon a coup sur si :
IDAffaire est clef primaire de T
IDMarque clef primaire de CB
IDEmploye clef primaire de EXE
IDFonction clef primaire de FONCIND
Si ce n'est pas le cas, envoie une description plus precise de ta tables (avec les colonnes utilisées et surtout les clef) pour qu'on puisse t'aider....

Le plan d'exécution serait plus utile.
Il est clair que cette requête est gourmande en terme de tris.

Citation:

---

Envoyé par guda

La voici (simplifiée) :

---

Tu ne veux pas poster la vraie requête, ca cache peut être qqchose qui ne ressort pas sur la requête simplifiée ?


Laly.

Hello,

unable to extend temp seg arrive lorsque le tablespace temporaire est saturé et ne peux pas s'étendre.

il ne peux pas s'etendre, soit parcequ'il n'a pas les paramètres nécessaires, soit parcequ'il n'y a plus d'espace sur le disque.

un remède comme un autre:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

voir la liste des tablespaces temporaire: select name from v$tempfile;   1) CREATE TEMPORARY TABLESPACE LocalManagedTemp TEMPFILE '/home/oracle/oradata/DBSTAT/datafile/LMTemp2.dbf' SIZE 1000M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;   2) ALTER DATABASE DEFAULT TEMPORARY TABLESPACE LocalManagedTemp;   3) DROP TABLESPACE TEMP;   5) CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/oradata/DBSTAT/datafile/LMTemp.dbf' SIZE 20000M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;   6) ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;   7) DROP TABLESPACE LocalManagedTemp;

---

ceci est mon pense bête.

une technique plus simple et de simplement ajouter un nouveau tablespace temporaire, mais je n'est pas la requête en tête, quelq'un pourra certainement y répondre.

j'ajoute que si tu as toujours cette réponse, c'est que vraiment ton tablespace et trop petit! agrandit le encore et encore...

pour info, j'ai eu ce cas en créant un index sur une table de plus de 120 millions d'enregistrements et j'ai du mettre en place un tablespace de 20 giga!

Citation:

---

j'ajoute que si tu as toujours cette réponse, c'est que vraiment ton tablespace et trop petit! agrandit le encore et encore...

pour info, j'ai eu ce cas en créant un index sur une table de plus de 120 millions d'enregistrements et j'ai du mettre en place un tablespace de 20 giga!

---

Bien sûr, dans le cas de la création d'un index, on a guère le choix si on veut que le traitement aboutisse : il faut donner l'espace, coûte que coûte.

par contre, dans le cas d'une requête, avant d'aggrandir l'espace, il faut cependant s'assurer que la requête ne peux pas être optimisée ! ;-)

effectivement! une requête optimale et toujours mieux que de devoir utiliser un monstre pour la faire tournée...

mais à défaut, agrandir un tablespace peux solutionner le problème de notre ami.

il faut parfois savoir utiliser des solutions à la N.T. (Néandertale Technologie) que de tourner en rond, de résoudre le problème même si c'est temporaire et plus tard, avec les idées plus claires, y revenir.

cela dit, il ext claire que la jointure telle que présentée pourrait (je pense) être avantageusement remplacée par des "join" pure et dure en prenant soins de déclarer les plus petites tables d'abord, cela sera plus claire (il me semble) et permettra une réflexion sur le volume de chaque table et surtout des index pour les jointures.

mais bon, ne me jeté pas de pierre, je débute aussi avec oracle...

J'ai tout essayé : Index et utilisation d'INNER JOIN (ralenti la requete)

Nous n'avons pas les drois de modification des tablespaces mais ils ont été augmenté à 600Mo.

Pour ce qu'il est de l'optimisation, faut-il bien déclarer en premier la table la plus conséquente ? Puis dans quel ordre mettre les jointures?

Y a pas un moyen d'éviter de créer des tables temporaires ?

Tu ne veux pas poster ta "vraie" requête ? :evil:

Au niveau de la requête simplifiée, je ne vois rien qui entraine un tri (order by, group by, distinct ...) donc je pense que c'est dû à une jointure par hash, ou qqchose dans ce sens.

Tu peux poster le plan d'exécution de la requête et si c'est ce que je pense mettre des hints pour choisir un autre plan d'exécution qui risque d'être sub-optimal cependant mais qui permettrait à la requête de se terminer.


Laly.

Ma vraie requete est celle-ci :

Code:

---

1 2 3 4 5 6 7 8 9

select Distinct AFF.TB0000_IDAFFAIRE, T.TB0013_IDEMPLOYE   from TB0000_BIR3MAFFAIRE AFF,TB0004_BIR3MCONTRATMARQUE CM, TB0005_BIR3MCONTRATBASE CB, TB0010_BIR3MTRAVAILLER T, TB0011_BIR3MEXERCER EXE, TB0017_BIR3MFONCTIONIND FONCIND   WHERE   AFF.TB0000_IDAFFAIRE=CM.TB0000_IDAFFAIRE and CB.TB0004_IDMARQUE=CM.TB0004_IDMARQUE and T.TB0000_IDAFFAIRE=AFF.TB0000_IDAFFAIRE and EXE.TB0013_IDEMPLOYE=T.TB0013_IDEMPLOYE and EXE.TB0014_IDFONCTION=FONCIND.TB0014_IDFONCTION and   CM.TB0003_IDFILIALE = 'ESPAGP' and CM.TB0004_PCTId='72400001' and CM.TB0004_DATEFIN=to_date('31/12/9999','DD/MM/YYYY') and CM.TB0004_NIVEAUCONTRAT in ('1','2') and CB.TB0005_DOMAINECONTRAT='3' and CB.TB0005_DATEFIN=to_date('31/12/9999','DD/MM/YYYY') and T.TB0010_DATEFIN=to_date('31/12/9999','DD/MM/YYYY') and EXE.TB0011_DATEFIN=to_date('31/12/9999','DD/MM/YYYY') and EXE.TB0011_INDPRINCIPAL=1 and FONCIND.TB0040_IDINDICATEUR='NB_PROD_MECA_02'

---

DISTINCT exécute obligatoirement un tri.

Et que donne l'explain plan

L'explain plan donne ceci :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

OPERATION OPTIONS OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE SEARCH_COLUMNS ID PARENT_ID POSITION SELECT STATEMENT (null) (null) (null) (null) (null) 0 (null) (null) SORT UNIQUE (null) (null) (null) (null) 1 0 1 MERGE JOIN (null) (null) (null) (null) (null) 2 1 1 SORT JOIN (null) (null) (null) (null) 3 2 1 NESTED LOOPS (null) (null) (null) (null) (null) 4 3 1 MERGE JOIN (null) (null) (null) (null) (null) 5 4 1 SORT JOIN (null) (null) (null) (null) 6 5 1 NESTED LOOPS (null) (null) (null) (null) (null) 7 6 1 NESTED LOOPS (null) (null) (null) (null) (null) 8 7 1 NESTED LOOPS (null) (null) (null) (null) (null) 9 8 1 TABLE ACCESS FULL TB0005_BIR3MCONTRATBASE 3 (null) (null) 10 9 1 TABLE ACCESS BY INDEX ROWID TB0004_BIR3MCONTRATMARQUE 2 (null) (null) 11 9 2 INDEX UNIQUE SCAN ID_PK_TB0004_I00 (null) UNIQUE 1 12 11 1 INDEX UNIQUE SCAN ID_PK_TB0000_I00 (null) UNIQUE 1 13 8 2 INDEX RANGE SCAN ID_PK_TB0017_I00 (null) UNIQUE 1 14 7 2 SORT JOIN (null) (null) (null) (null) 15 5 2 TABLE ACCESS FULL TB0011_BIR3MEXERCER 5 (null) (null) 16 15 1 INDEX UNIQUE SCAN ID_PK_TB0013_I00 (null) UNIQUE 1 17 4 2 SORT JOIN (null) (null) (null) (null) 18 2 2 TABLE ACCESS FULL TB0010_BIR3MTRAVAILLER 4 (null) (null) 19 18 1

---

comme dit plus haut, le distinct demande un tri et un tri cela ce passe dans le tablespace temporaire!

en dehors du changement de cette requête, il faudra porter le tablespace temporaire à bien plus que les quelques centaines de méga alloués actuellement!

à défaut d'augmenter le tablespace temporaire, il faudra saucissonner la requête...

moi je vais deux full scan de tables et surtout un merge
A mon avis c'est la que l'on peut améliorer

ce qui m'inquiete c'est que la meme requete sans Distinct plante pour les meme raisons.
Explain plan donne alors :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

OPERATION OPTIONS OBJECT_NAME OBJECT_INSTANCE OBJECT_TYPE SEARCH_COLUMNS ID PARENT_ID POSITION SELECT STATEMENT (null) (null) (null) (null) (null) 0 (null) (null) MERGE JOIN (null) (null) (null) (null) (null) 1 0 1 SORT JOIN (null) (null) (null) (null) 2 1 1 NESTED LOOPS (null) (null) (null) (null) (null) 3 2 1 MERGE JOIN (null) (null) (null) (null) (null) 4 3 1 SORT JOIN (null) (null) (null) (null) 5 4 1 NESTED LOOPS (null) (null) (null) (null) (null) 6 5 1 NESTED LOOPS (null) (null) (null) (null) (null) 7 6 1 NESTED LOOPS (null) (null) (null) (null) (null) 8 7 1 TABLE ACCESS FULL TB0005_BIR3MCONTRATBASE 3 (null) (null) 9 8 1 TABLE ACCESS BY INDEX ROWID TB0004_BIR3MCONTRATMARQUE 2 (null) (null) 10 8 2 INDEX UNIQUE SCAN ID_PK_TB0004_I00 (null) UNIQUE 1 11 10 1 INDEX UNIQUE SCAN ID_PK_TB0000_I00 (null) UNIQUE 1 12 7 2 INDEX RANGE SCAN ID_PK_TB0017_I00 (null) UNIQUE 1 13 6 2 SORT JOIN (null) (null) (null) (null) 14 4 2 TABLE ACCESS FULL TB0011_BIR3MEXERCER 5 (null) (null) 15 14 1 INDEX UNIQUE SCAN ID_PK_TB0013_I00 (null) UNIQUE 1 16 3 2 SORT JOIN (null) (null) (null) (null) 17 1 2 TABLE ACCESS FULL TB0010_BIR3MTRAVAILLER 4 (null) (null) 18 17 1

---

Déjà je vais revoir tous mes index mais est-ce que cela sera suffisant !!!

C'est bien ce que je dit :
Il y a deux merge join .et la table TB0005_BIR3MCONTRATBASE , TB0011_BIR3MEXERCER et TB0010_BIR3MTRAVAILLER sont en full accées .

Quelle est la volumétrie de ces tables

TB0005_BIR3MCONTRATBASE= 3 150 lignes
TB0011_BIR3MEXERCER = 29 000 lignes
TB0010_BIR3MTRAVAILLER = 15 000 lignes.

Je refais les index de suite

Voir également si les statistiques tables et les indexs sont bien calculés