Discussion :

[guda]

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 :

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

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 ?

[LeoAnderson]

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 ...

[fred777888999]

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....

[SheikYerbouti]

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

[lalystar]

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.

[jean-jacques varvenne]

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 :

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
 
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!

[LeoAnderson]

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 ! ;-)

[jean-jacques varvenne]

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...

[guda]

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 ?

[lalystar]

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

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.

[guda]

Ma vraie requete est celle-ci :

Code :

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

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'

[SheikYerbouti]

DISTINCT exécute obligatoirement un tri.

[Jaouad]

Et que donne l'explain plan

[guda]

L'explain plan donne ceci :

Code :

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
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

[jean-jacques varvenne]

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...

[Jaouad]

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

[guda]

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

Code :

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
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 !!!

[Jaouad]

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

[guda]

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

Je refais les index de suite

[Jaouad]

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