Optimisation requetes SQL

Version imprimable

Bonjour,
Je suis aujourd'hui confronté à des pb de temps de réponses sur certaines requetes, malgré la pose d'index, de tables en mémoire, etc... :cry: :cry:
Voici le code d'une des requetes :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT
  lower(SUPRALOCAL.REF),
  sum(decode(DO0.VAR13,'Gagnée',1,0)),
  XORGALIEN_Supra.ID_PC,
  sum(decode(DO0.ETAT,'Clôturée', 1,0)),
  sum(decode(DO0.ETAT,'En cours', 1,0))
FROM
  XORGALIEN  XORGALIEN_Supra,
  DO0,
  SO0,
  AR0,
  AR0  SUPRALOCAL
WHERE
  ( SO0.AR0_NRID=AR0.NRID  )
  AND  ( SUPRALOCAL.NRID=AR0.AR0_SUPRA_NRID  )
  AND  ( XORGALIEN_Supra.CODE_INSEE=SUPRALOCAL.CDE_TVA  )
  AND  ( SO0.NRID=DO0.SO0_NRID  )
  AND  ( SO0.TYPE='Particulier'  )
  AND  ( DO0.DAT1 >= to_date('01/01/'||TO_CHAR(sysdate,'YYYY'),'DD/MM/YYYY')  )
  AND  (
  ( lower(SUPRALOCAL.CATEGORIE)='extension'  )
  AND  ( SUPRALOCAL.nrid in (select ar0_supra_nrid from ar0
                         where ar0_supra_nrid is not null)  )
  AND  trunc(DO0.DAT1)  >=  '01-07-2002 00:00:00'
  AND  SO0.TYPE  =  'Particulier'
  )
GROUP BY
  lower(SUPRALOCAL.REF), 
  XORGALIEN_Supra.ID_PC

Cette requête va mettre jusqu'à 40MN !!!! :x
Voici pour info l'explain PLAN :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SELECT STATEMENT Optimizer Mode=CHOOSE 1  	10835
SORT GROUP BY		1  	114  	10835
NESTED LOOPS		1  	114  	10821
HASH JOIN		                1  	106 	10817
NESTED LOOPS		19  	1 K	10021
NESTED LOOPS		1 K	87 K	7497
NESTED LOOPS		1 K	73 K	4973
TABLE ACCESS FULL	DO0	1 K	37 K	2445
TABLE ACCESS BY INDEX ROWID SO0	23 M	671 M	2
INDEX UNIQUE SCAN	PK_SO0	23 M	 	1
TABLE ACCESS BY INDEX ROWID AR0	26 M	280 M        2
INDEX UNIQUE SCAN	PK_AR0	26 M	 	1
TABLE ACCESS BY INDEX ROWID AR0	26 K	855 K	2
INDEX UNIQUE SCAN	PK_AR0	26 K	 	1
VIEW	VW_NSO_1	18 K	36 K	795
SORT UNIQUE		18 K	36 K	795
INDEX FAST FULL SCAN IDX_AR0_AR0_SUPRA_NRID	396 K	774 K	124  	 	
TABLE ACCESS BY INDEX ROWID XORGALIEN	36 K	285 K	4  	 	      	             
INDEX RANGE SCAN	SEL_XORGALIEN	36 K	 	2

En résumé, la requête passe par les index (pour les grosses tables 31M de lignes), et fait des Full Scan sur de petites tables.
Comment puis je optimiser cette requête ?
merci d'avance pour vos solutions.

11/05/2004, 11h00
orafrance
est-ce que tu as un index sur DO0.DAT1 ?
Code:

1 2 SUPRALOCAL.nrid in (select ar0_supra_nrid from ar0 where ar0_supra_nrid is not null
Pourquoi tu fais ça alors que ar0 est déjà dans le FROM ?

Sinon, la requête parait bonne, le plan d'exécution est correct... t'as du volume dans les tables ?
11/05/2004, 11h16
rouardg

Bonjour,

A la place de to_date('01/01/'||TO_CHAR(sysdate,'YYYY'),'DD/MM/YYYY'), il suffit de mettre trunc (sysdate, 'YYYY').

Ensuite, à la place de trunc(DO0.DAT1) >= '01-07-2002 00:00:00' ,
il faut mettre DO0.DAT1 >= to_date ('01-07-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )

Sinon, je ne comprends pas pourquoi tu compares DO0.DAT1 2 fois (une fois au 1er juillet 2002, et une autre fois à l'année en cours). Mais peut-être que tu as une bonne raison.

Et puis, sauf erreur de ma part, le test sur SO0.TYPE = 'Particulier' est fait 2 fois (à moins que je me sois perdu dans le niveau de parenthèses).
11/05/2004, 11h21
SheikYerbouti

Citation:

lower(SUPRALOCAL.CATEGORIE)='extension'

êtes-vous obligé d'utiliser la fonction Lower() ?
11/05/2004, 11h33
orafrance

on va le démoraliser le pauvre :lol:
11/05/2004, 14h11
joel90

Citation:

Envoyé par SheikYerbouti

Citation:

lower(SUPRALOCAL.CATEGORIE)='extension'

êtes-vous obligé d'utiliser la fonction Lower() ?

Les index sont de types "lower(SUPRALOCAL.CATEGORIE)" par rapport au fait qu'il peut avoit "Extension", "extension" ou encore "EXTENSION".
11/05/2004, 14h17
SheikYerbouti

Citation:

Envoyé par joel90

Citation:

Envoyé par SheikYerbouti

Citation:

lower(SUPRALOCAL.CATEGORIE)='extension'

êtes-vous obligé d'utiliser la fonction Lower() ?

Les index sont de types "lower(SUPRALOCAL.CATEGORIE)" par rapport au fait qu'il peut avoit "Extension", "extension" ou encore "EXTENSION".

Ok, ça marche.
11/05/2004, 14h27
joel90
Citation:
Envoyé par orafrance

est-ce que tu as un index sur DO0.DAT1 ?

Code:

1 2 SUPRALOCAL.nrid in (select ar0_supra_nrid from ar0 where ar0_supra_nrid is not null

Pourquoi tu fais ça alors que ar0 est déjà dans le FROM ?

Sinon, la requête parait bonne, le plan d'exécution est correct... t'as du volume dans les tables ?
1 - il n'y a pas d'index sur DO0.DAT1

2 - Fonctionnellement, il faut que je récupére avec cette sous requête un sous ensemble de AR0 pour par la suite le comparer dans mon IN. A ce propos, j'ai modifié le IN (SELECT ar0_supra..... par un IN (SELECT DISTINCT ar0_supra..... Ou encore, j'ai pu lire ici et là dans le forum qu'il vallait mieux remplacer un IN par un EXIST (plus performant ?)
la requête deviendrait :
Code:

1 2 3 ( lower(SUPRALOCAL.CATEGORIE)='extension' ) AND exists (select 'x' from ar0 where ar0_supra_nrid is not null and SUPRALOCAL.nrid = ar0.ar0_supra_nrid)
3 - Pour la volumétrie :
XORGALIEN = 36 000 lignes
DO0 = 500 000 lignes
SO0 = 31M de lignes
AR0 = 26M de lignes
11/05/2004, 14h30
joel90

Citation:

Envoyé par rouardg

Bonjour,

A la place de to_date('01/01/'||TO_CHAR(sysdate,'YYYY'),'DD/MM/YYYY'), il suffit de mettre trunc (sysdate, 'YYYY').

Ensuite, à la place de trunc(DO0.DAT1) >= '01-07-2002 00:00:00' ,
il faut mettre DO0.DAT1 >= to_date ('01-07-2002 00:00:00', 'DD-MM-YYYY HH24:MI:SS' )

Sinon, je ne comprends pas pourquoi tu compares DO0.DAT1 2 fois (une fois au 1er juillet 2002, et une autre fois à l'année en cours). Mais peut-être que tu as une bonne raison.

Et puis, sauf erreur de ma part, le test sur SO0.TYPE = 'Particulier' est fait 2 fois (à moins que je me sois perdu dans le niveau de parenthèses).

Pour dire vrai, c'est une requête qui est récupéré d'un SQL BusinessObjects (BO). On a bien SO0.TYPE = 'Particulier' 2 fois dans la requête inutiliement.
11/05/2004, 14h30
orafrance

31 et 26M de lignes ça commence à faire pas mal... peut-être qu'un index partitionnée sur ces 2 tables arrangerait grandement les choses ;)
11/05/2004, 14h36
joel90

Citation:

Envoyé par orafrance

31 et 26M de lignes ça commence à faire pas mal... peut-être qu'un index partitionnée sur ces 2 tables arrangerait grandement les choses ;)

Partitionner les tables avec des index partitionnées serait effectivment une bonne idée, mais dans le contexte actuel, on attaque une base de production, et malheureusement pas un DataWarehouse (ou DataMart), la seule action que l'on peut faire, est d'ajouter des index (en prennant soin de ne pas perdre de perfs, pour les MAJ) ou encore d'optimiser le code SQL. Mais notre champ d'action est trés restreint.... :cry: :cry:
11/05/2004, 14h48
orafrance

Tu peux créer un index partitionné sur une table non-partitionnée, ça marche aussi bien ;)

La seule contrainte c'est que tu es limité aux partitions par RANGE
11/05/2004, 15h50
Marc Musette

y a t'il dans ce cas, un gain de perf si la table n'est pas partitionnée?
11/05/2004, 15h56
orafrance

Oui ça peut être particulièrement intéressant ;)
14/05/2004, 14h26
joel90

En ouvrant bien les yeux 8O , on fini par trouver énormément d'informations sur ce forum.
J'ai pu résoudre bon nombre de mes pb avec les indications ou les résolutions trouvées sur ce forum.
Bravo à tous ceux qui participent activement à faire vivre ce forum, et merci à ceux qui apportent leurs connaisances. 8) 8)
14/05/2004, 14h29
SheikYerbouti

Ben, n'hésitez pas à en faire la pub ! :wink:
14/05/2004, 14h41
orafrance

Citation:

Envoyé par joel90

En ouvrant bien les yeux 8O , on fini par trouver énormément d'informations sur ce forum.
J'ai pu résoudre bon nombre de mes pb avec les indications ou les résolutions trouvées sur ce forum.
Bravo à tous ceux qui participent activement à faire vivre ce forum, et merci à ceux qui apportent leurs connaisances. 8) 8)

Content qu'on puisse t'aider :)

Pourrais tu indiquer la solution que tu as retenue finalement ? ;)
14/05/2004, 18h33
Médiat

Et le gain de perf, tous les utilisateurs de SELLIGENT seront contents :wink:
15/05/2004, 21h45
joel90

Citation:

Envoyé par orafrance

Content qu'on puisse t'aider :)

Pourrais tu indiquer la solution que tu as retenue finalement ? ;)

Comme je l'expliquais, ma marche de manoeuvre est plutôt restreinte, les solutions trouvées ont été de revoir le code SQL afin d'y apporter des améliorations.
La solution idéale, étant de pouvoir dénormaliser le modéle, et d'éviter des jointures multiples sur les grosses tables, ça c'est pour les utilisateurs de SELLIGENT.
Autrement, au niveau du code :
* monter en mémoire les petites tables trés souvent utilisées (XORGALIEN, XORGADEL),
* remplacer les IN par des EXIST (essayé d'éviter les IN),
* supprimer les abérations du code (SO0.TYPE = 'Particulier', présent 2 fois, etc...).
Les temps de réponses sur certaines requêtes : divisés par 2.