Discussion :

[Narold]

Bonjour j'aurais besoin d'un petit coup de pouce ^^
je suis sur une base oracle 9i
j'executé des requete bien tordue 16 jointures à peu pres

biensur les perf pour ce genre de requete sont mauvaises
en regardant bien le code je me suis apperçu que la requete etait en 2 bloques symétrique sur le principe une table enorme (80 millions de lignes) joint a des tables plus petites ( de 200 à 4000 lignes)

l'une des deux gros table (celle de 80 millions de lignes ) porte sur au moins 15 ans de prod. hors seul l'année en cours m'est util. j'ai du souhaité faire une VUE materialisée sur l'année avec les memes index ( normalement les deux ont la meme structure ; j'ai fais un create as select...puis ajoute des index)

ma VUE ne fait plus que 200 milles lignes !! jusque là tout va bien


sauf que ma requete ramme 3 fois plus en utilisant la vue ....

avec la version normal g un nested loop et tout le reste des jointure passe en hash join

avec la version utilisant la vue j'ai 5 ou 6 nested loop qui apparaissent..

en Bref : il va plus vite à parcourir 11 milions lignes que 200 milles!!

quelqu'un aurait une idée ??

[Loyd1974]

déjà, de ne pas poster 2 fois le même message

Bon, blague à part, sans avoir une idée de la requête, un petit plan d'exécution, cela va relever un peu de la divination pour t'aider.

A la limite, j'essayerais de voir ce que donne la requête sur la vue matérialisée avec le plan d'exécution de ta grosse table (en clair d'utiliser le hint HJ)

[Narold]

Désolé pour le message en double..
voici l'explain plan de ma fonction (en piece jointe..)

Voici comment ce presente les jointures
rsbmtp mtp, rsbcfa cfa, rsbpol pol, rsbcai cai, rsbbef bef, rsbtrt trt,
rsbstr str, rsbfco fco_cfa, rsbfco fco_str, rsbcbo cbo, rsbcsd csd, rsbter ter, rsbcie cie, rsbdev dev, rsbtba tba, rsbncl ncl,
mtp.cfaseqx = cfa.cfaseqx (+)

and mtp.cbocodc = cbo.cbocodc
and mtp.ciecodc = cbo.ciecodc
and mtp.csdcodc = csd.csdcodc (+)
and mtp.ciecodc = csd.ciecodc (+)
and mtp.strseqx = str.strseqx (+)
and mtp.ciecodc = cie.ciecodc
and mtp.tercodc = ter.tercodc
and mtp.ncenatx = ncl.ncenatx
and mtp.devcodc = dev.devcodc
and mtp.tbaseqx = tba.tbaseqx

[Loyd1974]

C'est pas que je veux jouer les rabat-joie, mais je pense que la requête peut être utile aussi, ne serait-ce que pour voir comment est construit la clause where pour savoir s'il est utile d'avoir 2 requêtes dans un union all ou si on peut le jouer en une passe (vue que tu fais des fulls quasiment de partout, je pense qu'il peut avoir une chance que cela soit jouable)

Je vois aussi que tu as des tables qui apparaissent plusieurs fois et qui sont aussi lue en FULL (en survol, je vois déjà RSBFCO, RSBTRT (une fois en direct, une fois au sein d'une vue apparemment)). C'est pareil, est-il utile de lire 2 fois la table et n'est-il pas possible de résoudre ton besoin avec une seule passe. Vu que tu fais un full sur une table, donc la ligne que tu vas chercher sur le deuxième appel de ta table, tu l'as déjà lu avec la première.

Il faut peut être chercher à utiliser les possibilités du DECODE, du CASE ou même des fonctions analytiques

Une "erreur" qui est faite souvent c'est de coder comme cela :

Code :

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

1
2
3
4
5
6
 
SELECT A.Master_Key, SUM(B.Total), Sum(C.Total)
FROM Master A, Detail B, Detail C
WHERE A.Master_Key = B.Master_Key
AND A.Master_Key = C.Another_Master_Key
Group by A.Master_Key

Tu vas lire la table DETAIL 2 fois, si les index sont bons et que les volumes sont faibles, ca peut aller, mais si tu fais un full sur DETAIL, alors il vaut mieux coder comme cela

Code :

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

1
2
3
4
5
6
7
 
SELECT A.Master_Key, 
sum(case when A.Master_Key = B.Master_Key THEN A.TOTAL ELSE 0 END),
sum(case when A.Master_Key = B.Another_Master_Key Then A.TOTAL ELSE 0 END)
FROM Master A, Detail B
WHERE A.Master_Key in (B.Master_Key, B.Another_Master_Key
Group by A.Master_Key

La table détail est lu uniquement une fois

[Narold]

Bonne Info ,merci , j'aurais jamais pensé à tourner une requete comme ca...
je vais voir ce que ca peut donnée
en attendant voici la requete ...
ps :
les tables lourdes sont la mtp et mts ( mais bon surtout la mtp).

[Narold]

Sympa t'as structure !!
t'aurais pas un equivalent pour les jointures ouverte ??

[McM]

Quelques idées d'opti :
1/ LTRIM(RTRIM()) à remplacer par TRIM()
2/ IN (SELECT DISTINCT : distinct inutile.
3/ AND TO_CHAR(tba.tbafind,'mm') = 05 : Eviter les conversion mettre = '05'
4 Voir si remplacer marche

Code :

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

1
2
3
4
5
6
SELECt ...DECODE(str.strseqx,NULL,NULL,DECODE(R1.MAX_EXE,2007,'EN_COURS','RUN_OFF')) AS ETAT_CONTRAT,
...   
FROM ... (SELECT rsbtrt.trtnumx, MAX(rsbtrt.trtrefn) AS MAX_EXE 
  FROM rsbtrt WHERE rsbtrt.supcodc = 'N'
  GROUP BY rsbtrt.trtnumx) R1
... WHERE ...  AND trt.trtnumx = R1.trtnumx (+)

Par

Code :

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

1
2
3
4
5
DECODE(str.strseqx,NULL,NULL,
	DECODE( (SELECT MAX(trtrefn) FROM rsbtrt r1 
		WHERE r1.supcodc = 'N' AND r1.trtnumx = trt.trtnumx),
		2007, 'EN_COURS','RUN_OFF')
	) AS ETAT_CONTRAT,

5/ Essaye d'optimiser chaque requete de l'UNION séparément

[Narold]

J'ai fais le test et effectivement sa passe , je gagne en perf
J'avance , j'avance !!! (merci )

[Loyd1974]

dans ton code, je vois plein de lignes comme çà dans la clause where :

Code :

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

1
2
('' IS NULL AND << condition sur une colonne de la table >>
or '' is not null and << condition sur une autre colonne de la table>>

je présume que ce qu'il y'a entre tes quotes c'est une variable, alors il faut faire attention avec ce genre d'écriture car c'est comme ça qu'on se prive d'un index.
Imaginons la table A avec un index sur la colonne Col1, et je lui passe un paramètre ParmA
Si j'écris comme ça:

Code :

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

1
2
3
4
 
SELECT *
FROM A 
WHERE (ParmA is Null or (ParmA is not null and Col1 = ParmA)

L'index ne sera pas utilisé (du moins en 10g, au dessus, je connais pas encore)
Là, on peut faire du SQL dynamique par exemple et ne rajouter la condition sur Col1 que si ParmA n'est pas null. De cette façon, quand tu renseigne le paramètre, au moins tu passes par l'index. Alors que précédemment c'était FULL dans tous les cas.

Alors tout va dépendre du nombre de paramètres que tu as, si tu en as très peu, tu peux imaginer de faire différentes versions de ton select, sinon, d'envisager le SQL Dynamique mais seulement si les champs qui sont utilisés avec tes paramètres ont des index, sinon, oublies tout ce que j'ai dit

[Narold]

voila la requete d'origine .. ce sera plus clair

quand tu parle de Sql dynamique tu pense à du plsql ou à une implémentation dynamique de la requete via soft type VB... ou autre ?
(plsql .. j'y aurai peut droit mais c'est pas sur , implémentation c'est mort..) donc si tu vois autre chose je suis preneur

[Narold]

j'ai 7 parametres et effectivement un para est sur ncl

Ps : avec cette structure tu passe pas pour les index meme si c'est renseigné ?? oyo

[lallio]

Bonjour,

Si tu es en version enterprise, tu as envisgaé le partionnement physique de ta table ? Ca devrait également améliorer les perfs

[Narold]

partitionnement physique ??? (voie à étudier ) je connais pas

[Loyd1974]

Je parlais de SQL dynamique par du PL/SQL

Dans ton code original, la partie que tu peux optimiser comme cela est celle-ci (qui est commune à tes 2 selects):

Code :

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

1
2
3
4
 
  and (('&&5' is null and mts.ciecodc in (select distinct ciecodc from rsbcie where ltrim(rtrim(to_char(sap_ciecodc)))='&&4')) or ('&&5' is not null and mts.ciecodc='&&5'))
  and (('&&6' is null and mts.acrcodc in (select acrcodc from rsbacr)) or ('&&6' is not null and mts.acrcodc='&&6'))
  and (('&&7' is null and mts.ncenatx in (15,20,26,30,31,66,87,92,93,113,184,185)) or ('&&7' is not null and ltrim(rtrim(to_char(mts.ncenatx)))='&&7'))

Si une des colonnes suivantes possède un index, il faut envisager de passer par du SQL dynamique :

• CIECODC : testé avec le paramètre 5
• ACRCODC : testé avec le paramètre 6
• NCENATX : testé avec le paramètre 7

A propos de la dernière colonne, tu fais :

Code :

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

1
2
 
ltrim(rtrim(to_char(mts.ncenatx)))='&&7'

ce qui n'est pas bon si un index est sur cette colonne car tu appliques une fonction dessus.
Pour schématiser, toujours ma table A, ma colonne C et mon index I sur ma colonne C, le 1er cas n'utilisera pas l'index, le second oui:

Code :

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

1
2
3
4
5
6
7
8
9
 
-- index inutilisable à cause de la fonction
SELECT C
FROM A
WHERE TO_NUMBER(C) = ParamNumerique
-- index utilisable car pas de fonction sur la colonne
SELECT C
FROM A
WHERE C = TO_CHAR(ParamNumerique)

En règle générale, si dans une clause where, une conversion doit être faite, il faut toujours la faire au niveau du paramètre, ou si il s'agit de 2 colonnes de 2 tables, le faire sur la colonne non indexée

Pour en revenir à ton problème, je te conseille de tester le plan d'exécution de ta requête en l'état, puis d'essayer en remplacant les 3 lignes en question par :

Code :

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

1
2
3
4
 
and mts.ciecodc='&&5'
and mts.acrcodc='&&6'
and mts.ncenatx='&&7'

[Narold]

j'ai regardé ici par rapport aux index c'est pas vraiment concluant ... mais c'est toujours bon à savoir

si tu as d'autres conseille je suis preneur !!