Discussion :

[tostinni]

Bonsoir a tous,
J'ai un doute sur la maniere d'optimiser une requete utilisant une sous-requete. La table de ma sous-requete sur laquelle je veux faire le lien externe possede un index et lorsque je regarde le plan d'execution, je vois qu'a l'interieur de ma sous-requete, Oracle fait un INDEX FULL SCAN au lieu d'un INDEX RANGE SCAN.

Comment puis-je optimiser ceci ?

Voici un exemple pour reproduire mon cas :

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 TABLE LIEN (
  CLE_ORI  NUMBER,
  CLE_DEST NUMBER,
  CONSTRAINT PK_LIEN_ORI_DEST PRIMARY KEY (CLE_ORI, CLE_DEST)
);
 
CREATE TABLE CAISSE (
  CLE  NUMBER PRIMARY KEY,
  TAILLE NUMBER
);
 
SELECT /*+ORDERED INDEX(SUB.LIEN PK_LIEN_ORI_DEST) */
 C.CLE
FROM CAISSE C,
	 (
		SELECT /*+USE_NL(CAISSE LIEN)*/ LIEN.CLE_ORI, CAISSE.TAILLE
		FROM LIEN,
			 CAISSE
		WHERE LIEN.CLE_DEST = CAISSE.CLE
	) SUB
WHERE SUB.CLE_ORI(+) = C.CLE
AND C.CLE BETWEEN 123456 AND 456123
AND SUB.TAILLE IS NULL;

Evidemment je ne peux pas eviter d'avoir ma sous requete, sinon le probleme ne se poserait pas. (bien que dans l'exmple ci dessus il soit facile d'eviter la sous-requete).

Quand je regarde le plan d'execution, j'ai ceci:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Operation	Object Name	Rows	Bytes	Cost	TQ	In/Out	PStart	PStop
 
SELECT STATEMENT		1  	 	109  	 	 	 	 
  FILTER		  	 	 	 	 	 	 
    NESTED LOOPS OUTER		  	 	 	 	 	 	 
      INDEX RANGE SCAN	SYS_C0029448	1  	39  	2  	 	 	 	 
      VIEW		1  	26  	108  	 	 	 	 
        NESTED LOOPS		67  	3 K	108  	 	 	 	 
          INDEX FULL SCAN	PK_LIEN_ORI_DEST	82  	2 K	26  	 	 	 	 
          TABLE ACCESS BY INDEX ROWID	CAISSE	1  	26  	1  	 	 	 	 
            INDEX UNIQUE SCAN	SYS_C0029448	1

Donc j'ai bien un INDEX FULL SCAN sur ma vue au lieu d'un RANGE et ce malgre le "HINT" specifie au debut de la requete.

Du coup les performance s'en ressentent bcp et je me demande bien comment indiquer a Oracle d'eviter le FULL SCAN dans ma sous-requete ?

Merci d'avance pour vos reponse. 8)

[Yorglaa]

essaye de mettre cette clause

Code :

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

AND SUB.TAILLE IS NULL

DANS la sous-requête... et pas dans le requête pricipale !

plus ta sous-requête sera restrictive et moins tu aura de lignes sur lesquelles faire ta jointure externe.

[tostinni]

Merci Yorglaa pour ta suggestion, certes je peux avoir ma sous-requete la plus restrictive possible (ici le TAILLE IS NULL n'est qu'un exemple), cependant le but ici etait de voir comment attaquer l'index d'une de mes tables de ma sous-requete depuis ma requete principale

Donc oui je pourrais restreindre ma sous-requete, mais mes restrictions sont un peu plus complexes que mon exemple ce qui fait que j'ai bcp plus de tables et jointures impliquees, ce que je voulais essayer d'eviter par l'usage de l'index...

[LeoAnderson]

Tout d'abord, je ne suis pas un fan des hint car en cas de migration, c'est la galère à maitenir, reprendre avec des effets de bords imprévus...
Une requête bien écrite avec un bon schéma ne doit, à mon avis, presque jamais nécessiter de hints.

Et même si votre cas réel est plus complexe que l'exemple donné, avez-vous essayé la proposition de Yorglaa ?
C'est à dire de restreindre au MAXIMUM les lignes retournées par le sous-select.

De plus, je pense que vous devriez pouvoir remplacer le sous-select par jointure externe normal, quite à avoir 2 fois "CAISSE" dans le FROM.
Mais après, tout dépend de la volumétrie....

Question bête : Les statistiques sont collectées régulièrement et sont à jour ?

[orafrance]

Tout d'abord, je ne suis pas un fan des hint car en cas de migration, c'est la galère à maitenir, reprendre avec des effets de bords imprévus...

+1 il faut préférer les outlines autant que possible

[LaVaZza]

Tiens au passage une autre question :

Comment fait-on pour voir les plans d'exécutions qui se passent dans une bas Oracle ?

[orafrance]

http://oracle.developpez.com/guide/tuning/tkprof/#LVI

[LaVaZza]

MERCI !!

[Yorglaa]

...
De plus, je pense que vous devriez pouvoir remplacer le sous-select par jointure externe normal, quite à avoir 2 fois "CAISSE" dans le FROM.
Mais après, tout dépend de la volumétrie....

hummm ça dépend certainement des restrictions à apporter... (vu qu'on ne connait pas le cas réel)
Si le sous-select contient des restrictions sous forme de IN, NOT IN, EXISTS ou NOT EXISTS avec des sous-selects, on ne peut plus faire directement la jointure externe sur ce genre de restriction. Du coup il est normal de faire toutes ces restriction dans une vue en ligne et d'ensuite d'y appliquer la jointure externe finale

[Nick_Holmes]

Tout d'abord, je ne suis pas un fan des hint car en cas de migration, c'est la galère à maitenir, reprendre avec des effets de bords imprévus...

+1 il faut préférer les outlines autant que possible

+1.
99% du code PL/SQL peut être optimisé facilement avec ou sans l'aide d'un DBA et ne nécessite pas de hints. Seuls des cas très tordus peuvent nécessiter des hints. Après bonjour la galère pour comprendre le comportement du SQL quand il tourne sur différents environnements.

@+,
NicK.

[dyvim]

Il me semble qu'il y a un probleme dans l'écriture de tes hints... Ce qui expliquerait qu'Oracle n'en tienne pas compte.

Code :

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

1
2
3
4
5
6
SELECT /*+ORDERED INDEX(SUB.LIEN PK_LIEN_ORI_DEST) */
 C.CLE
FROM CAISSE C,
	 (
		SELECT /*+USE_NL(CAISSE LIEN)*/ LIEN.CLE_ORI, CAISSE.TAILLE
...

Et si tu rajoutais un espace entre le /*+ et ORDERED ou USE_NL ???

Ceci dit, l'optimiseur d'Oracle est très performant si tu as des statistiques fraiches et complètes et il se peut qu'il te propose une meilleure solution que celle que tu envisages...
Je te conseille de faire un trace et de vérifier que ta solution est bien la meilleure avant de l'imposer à Oracle avec des hints...

[tostinni]

Bonjour a tous,
Merci beaucoup pour tout vos conseils

Tout d'abord, les stats sont a jour et actualisee toutes les nuits.
Oracle n'a pas de pb qu'il y ait ou pas d'espace entre /* et le + des hints.

Concernant le cas reel, je ne peux pas me permettre de le sortir d'un sub select sinon j'ai un outer join sur 2 tables et evidemment ca passe pas

Par contre sincerement concernant les hints, je ne sais pas trop quoi penser.
Je suis tout a fait d'accord que c'est un peu forcer Oracle a faire son boulot d'une autre maniere et donc pas forcement tres juste de proceder ainsi. Cependant je dois vous avouer que je ne suis en rien un expert d'Oracle. Cela fait juste 4 ans que je travaille avec cette BDD et de ma humble experience sur la base de ma boite (qui est surement configuree d'une maniere differente), j'avoue que j'ai eu des resultats plutot efficaces que je n'aurai jamais pu avoir sans utiliser les HINTs. Cepedant Leo, il y a des chances que le schema ne soit pas si optimum que cela, malheureusement je suis dans l'impossibilite totale de le changer (et meme si je pouvais, je pense que je prefererai ne pas le faire)...

Ce qui m'etonnait dans mon exemple, c'est le fait de ne pas pouvoir acceder a un RANGE SCAN sur l'index de ma sous requete.

Concernant une eventuelle migration, nous en avons deja effectue une pour passer de 8 a 9 et ca c'est passe sans trop de pb. J'ai juste reviser les grosses requetes et changer un tout petit peu certains Hints, mais sincerement ca s'est fait sans probleme majeur, donc je pense que je continuerai ainsi, sauf si qqun me donne une tres bonne raison de ne pas le faire et surtout si un DBA vient jeter un oeil dans notre base pour nous dire que ca va pas du tout

C'est clair qu'il nous manque un BA en interne, car pour le moment seul un consultant vient optimiser/faire le menage de tps en tps

Une fois de plus, merci pour vos suggestions, je vais continuer a regarder tout cela avec attention.

[McM]

Oracle n'a pas de pb qu'il y ait ou pas d'espace entre /* et le + des hints.

Attention : l'explain plan d'Oracle est différent de l'explain plan de TOAD.
On s'est apperçut avec TKPROF que les "/*+"..., oracle supprimait le "+" et que donc : pas de HINT.

[tostinni]

Oracle n'a pas de pb qu'il y ait ou pas d'espace entre /* et le + des hints.

Attention : l'explain plan d'Oracle est différent de l'explain plan de TOAD.
On s'est apperçut avec TKPROF que les "/*+"..., oracle supprimait le "+" et que donc : pas de HINT.

Merci pour l'info, cependant je ne mettais jamais d'espace a cet endroit donc ca devrait aller.

Par contre qd tu dis que l'explain plan de TOAD et d'oracle est different, tu veux dire quoi exacetement ?
Je ne peux pas me servir de TOAD pour optimiser ma requete ???

[McM]

Après vérifs, on était à l'époque en 8.1.7
Maintenant on est en 9i, et après mes tests, ça marche

Voici la trace du SELECT de l'époque (debut 2004)

Code :

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

SELECT /*+RULE */BL_BLOC || '.'  || CH_CHAMP ...

TKPROF:

SELECT /*+ ULE */BL_BLOC || '.' || CH_CHAMP V_ITEM,PG_CODFAMACTIO V_ACTION
FROM PCFCHAMP
WHERE CH_LOGIN = :b1
AND BL_NOMFCT = :b2
AND BL_FORME = :b3
Misses in library cache during parse: 1
Optimizer goal: CHOOSE

Sous TOAD, ça affichait bien le mode RULE.
Notre conclusion avec le DBA (après de multiples tests) étaient :
1/ TOAD reformatait les HINTS pour mettre des espaces
2/ L'optimiseur Oracle nécessitait un espace entre le /*+ et le hint

[dyvim]

Notre conclusion avec le DBA (après de multiples tests) étaient :
1/ TOAD reformatait les HINTS pour mettre des espaces
2/ L'optimiseur Oracle nécessitait un espace entre le /*+ et le hint

Je ne me serais pas permis de donner cette info si elle n'était pas fiable...
Elle m'a été donnée lors d'une formation chez Oracle sur le SQL Tuning...
Il faut un espace entre /*+ et le hint...