Discussion :

[elyo66]

Bonjour à tous !

Voilà près de 3 ans que je n’ai rien posté (je sévissais plutôt sur le forum Delphi, hé oui, les questions débiles, c’était moi ! ) mais je n’ai jamais cessé de visiter ce forum et d’apprendre !

Etant complètement newbie en PL/SQL et ayant un besoin urgemment urgent pour le boulot, je me permets de venir chercher vos lumières sur une question d’optimisation de requête SQL :

J’ai défini un curseur comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
CURSOR MonCurseur IS
	SELECT distinct T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
	FROM T1, T2, T3, T4 --- 4 tables
	WHERE T2_ch1 like param1 || '_'
             --- jointures diverses entre les champs des tables
	and T3_ch1 = param2	
             and T4_ch2 = T3_ch2
	and T1_ch2 = T4_ch2
	and T2_ch3 = T1_ch3;

Les paramètres « param1 » et « param2 » sont des variables globales (je sais c’est pas beau !).
Le champ «T2_ch1» doit prendre la forme suivante : ‘CE01x’ où x peut prendre les valeurs 0, 1, 2 à 9, d’où le

Code :

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

like param1 || ‘_’

où param1 est un varchar2.

Mon programme et cette requête marchent très bien, mais le hic c’est que ma requête est super gourmande (temps d’exécution 800ms à 3 secondes) et elle est appelée 18 000 fois (pour chaque couple (param1,param2)) : mon programme tourne du coup pendant plus de 2 heures !

A première vue et après quelques tests, c’est la clause « where/like » qui serait coûteuse.
Qu’en pensez-vous ?

J’ai essayé d’optimiser avec « where/in » pas très concluant :

Code :

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

1
2
3
4
5
6
7
8
9
10
WHERE T2_ch1 in (param1|| '0',
			param1 || '1',
			param1 || '2', 
			param1 || '3',
			param1 || '4',
			param1 || '5',
			param1 || '6',
			param1 || '7',
			param1 || '8',
			param1 || '9')

Existe-t-il un moyen d’optimiser considérablement ma requête ?
Suis-je à côté de la plaque ? (c'est tout à fait possible aussi )

Etant novice sur ce sujet, toute aide serait la bienvenue et je vous en remercie d’avance !

Quelques infos au cas où :
Je travaille sous Toad 7.4, Oracle 9i.
- seul le champ « T3_ch1 » n’est pas indexé
Tailles approximatives des tables :
- T1 : 75 000
- T2 : 15 000
- T3 : 30 000
- T4 : 30 000
Ce qui n'est pas énorme non?

[McM]

Le distinct est pas top.
Que donne l'explain plan de ta requete et de celle-ci ?

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
	FROM T1
	WHERE EXISTS (SELECT 1
			FROM T2, T3, T4
			WHERE T2_ch1 LIKE param1 || '_'
			AND T3_ch1 = param2	
			AND T4_ch2 = T3_ch2
			AND T2_ch3 = T1_ch3
			AND T4_ch2 = T1_ch2
			)

[McM]

En reprenant les diverses jointures, on s'apperçoit que les 3 tables T2-T3-T4 sont toutes liées à T1

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
SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
FROM T1
WHERE EXISTS (SELECT 1
		FROM T2
		WHERE T2_ch1 LIKE param1 || '_'
		AND T2_ch3 = T1_ch3
		)
AND EXISTS (SELECT 1
	FROM T3
	WHERE T3_ch1 = param2	
	AND T3_ch2 = T1_ch2
	)
AND EXISTS (SELECT 1
	FROM T4
	WHERE T4_ch2 = T1_ch2
	)

[elyo66]

Merci pour tes réponses McM !
Je ne sais pas ce qu'est un "explain plan" mais je suppose que c'est une option dans Toad?
Je m'y mets dès demain matin au boulot...

[Ora92_IDS]

Bonsoir
Explain Plan donne le shema d'execution d'une requete, a vrai dire c'est une table stocke ds la Base de Donnees s'appelle Plan_table,cette table s'alimente une fois tu execute une requete.

[McM]

Sous toad, dans le sql editor :
Icone : ambulance
ou Menu Sql Editor / Explain Plan Current SQL (CTRL+E)

[elyo66]

Cool, merci pour vos réponses !
Grâce à l'explain plan, j'ai pu voir que c'était plutôt l'accès au champ non indexé qui était coûteux, et non l'utilisation du "like" : j'ai un "TABLE ACCESS FULL" sur T3_ch1.

J'ai essayé avec ta requête McM :

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
SELECT T1_ch1, T1_ch2, T1_ch3 --- 3 champs de la table T1
FROM T1
WHERE EXISTS (SELECT 1
		FROM T2
		WHERE T2_ch1 LIKE param1 || '_'
		AND T2_ch3 = T1_ch3
		)
AND EXISTS (SELECT 1
	FROM T3
	WHERE T3_ch1 = param2	
	AND T3_ch2 = T1_ch2
	)
AND EXISTS (SELECT 1
	FROM T4
	WHERE T4_ch2 = T1_ch2
	)

Et j'obtiens :

En tout cas, le fait de sectionner avec des "exists "c'est très intéressant si ma requête ne renvoie pas toujours des résultats, mais ce ne sera jamais le cas normalement.

C'est donc vraiment le champ "T3_ch1" qui pose problème : la requête doit potentiellement parcourir toute la table! Or je suis obligée de passer par ce champ là...
Si j'extrais de ma requête la partie touchant à la table T3 comme ceci :

Code :

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

1
2
3
4
5
6
7
CURSOR MonCurseur IS
	SELECT T1_ch1, T1_ch2, T1_ch3
	FROM T1, T2, T4 
	WHERE T2_ch1 like param1 || '_'
             and T4_ch2 =  resultat_RequeteT3
	and T1_ch2 = T4_ch2
	and T2_ch3 = T1_ch3;

avec RequeteT3 :

Code :

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

1
2
SELECT T3_ch2 from T3
where T3_ch1 = param2

Est-ce que ça peut optimiser mon programme ?
Voyez-vous d'autres solutions ?
Merci d'avance !

[McM]

Il y a une grosse différence entre

Code :

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

SELECT distinct FROM T1, T2, T3, T4

et

Code :

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

SELECT  FROM T1 WHERE exits (select 1 from T2) ... AND exists (select 1 from T4)

Dans le premier cas, à moins que ta clause where ne ramène qu'une seule ligne par table, c'est un produit cartésien.
Vu que tu as mis un distinct, c'est ce que je pense.
Et là, le tri nécessaire au Distinct n'est pas bon du tout.

Avec des exists, tu n'as plus ce problème.

Pour le table access full, crées un index sur T3_ch1 ou T3_ch2, ou mieux (T3_ch1, T3_ch2) ou (T3_ch2, T3_ch1), ça dépend de ta table.

[elyo66]

Ah je comprends mieux, merci McM !
J'utilise ta requête avec les "exists" du coup.

Pour la table "T3" qui est en accès full, malheureusement je n'ai rien le droit de toucher car ça impacterait trop de choses au niveau de la base. On m'a expliqué qu'en gros je n'ai aucune lattitude possible sur les tables : je ne peux en aucun cas modifier leur structure...

En tout cas je vous remercie pour votre aide ! et à moins que vous n'ayez de nouvelles idées d'ici vendredi, je clôturerai cette discussion

[McM]

C'est pas la structure de la table qu'il faut modifier, mais un index à créer !

[elyo66]

Pardon je m'exprime mal !
Ce que je veux dire c'est qu'on m'a bien stipulé que je n'avais rien le droit de modifier au niveau de la base, notamment que je ne peux pas ajouter d'index !
En tout cas merci pour ton aide ! si t'as d'autres choses qui te passent par la tête je suis preneuse...

[McM]

Yop, c'est pas la première fois que j'entends ça.

Sinon pour l'opti, faut voir au niveau complet et pas juste le curseur afin de voir si une opti ailleur n'est pas faisable pour limiter les 18000 boucles.

[elyo66]

Oui j'ai essayé d'optimiser en amont dans mon programme, mais le champ "T3_ch1" qui pose problème change à chaque itération, donc la boucle appelle bien 18 000 fois la requête avec param2 différent à chaque fois

[dyvim]

Si ton curseur est long, tu peux peut être l'accélérer avec un traitement en "BULK COLLECT". Ca peut faire la même chose qu'un curseur mais plus vite...
A condition que tu n'ai qu'une seule instruction dans ton curseur par contre.

[elyo66]

Salut Dyvim !
Aurais-tu un exemple? je vais essayer de trouver des infos sur les "bulk collect"... En tout cas, merci pour le tuyau !

[dyvim]

Un petit exemple...

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
22
23
  CREATE OR REPLACE PROCEDURE fast_proc (p_array_size IN PLS_INTEGER DEFAULT 100)
IS
 
TYPE ARRAY IS TABLE OF all_objects%ROWTYPE;
l_data ARRAY;
 
CURSOR c IS
SELECT *
FROM all_objects;
 
BEGIN
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;
 
    FORALL i IN 1..l_data.COUNT
    INSERT INTO t2 VALUES l_data(i);
 
    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END fast_proc;
/

Attention, cela ne marche que si tu joues la même instruction pour chacune des lignes du curseur...
Si c'est le cas, tu gagneras en performance par rapport à un curseur car au lieu de jouer 1000 ou 10 000 fois la même instruction, il n'en jouera qu'une plus complexe...