Discussion :

[Matworms]

Bonjour,

On m'a demandé de voir comment optimiser une requête SQL (pour le boulot), mais je ne suis pas vraiment expert et j'ai l'impression que je vais y passer mes journées... Pour le contexte, c'est une requête qui attaque une base de production (ou plus exactement une copie de la base de production) et je ne suis pas DBA donc aucun droit particulier. Ce qui signifie donc qu'il s'agit d'optimiser la requête et uniquement la requête.

Voilà la requête en question :

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
 
select distinct H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
from ERESHX H
where H.DANEG = (select
                 distinct DATRA
                 from DATRAI
                 where COMAR = 'EUREX');

Note :
SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX' renvoie une seule ligne (une date).

Elle n'est pas de moi cette requête, pour les observateurs, elle donne une indication sur l'entreprise pour laquelle je travaille (je suis en stage) et le type de données traitées...

Au niveau des temps de traitement, la requête que j'ai copiée plus haut a un temps d'exécution beaucoup trop long (près d'une heure), alors que si je remplace le second SELECT DISTINCT par une date "en dur" (to_date('16/06/2008', 'DD/MM/YYYY') par exemple), la requête s'exécute en 5 minutes environ.

En ce qui concerne la volumétrie des tables, la voici :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> select count(*) from ERESHX;
 
  COUNT(*)
----------
   9315190
 
SQL> select count(*) from DATRAI;
 
  COUNT(*)
----------
        31

Et si ça a une importance, la base est sous Oracle 9i.

Merci pour votre aide

[philcero]

As-tu un de tes indexs sur la table ERESHX qui commence par la champs DANEG ?

Autre axe, Envoyer le DISTINCT un cran plus haut.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT
    DANEG,COMAR,COINF,CNACT,LNACT,NUCON,CSENS,CSOPT,QTCCP,CMECH,CAECH,DAECA,CODEV,MTSNA,MTNEG,COMAR
FROM
(
SELECT    DANEG,COMAR,COINF,CNACT,LNACT,NUCON,
    decode(CSENS, 'A', 'B', 'S'),
    CSOPT,QTCCP,CMECH,CAECH,DAECA,CODEV,MTSNA,MTNEG,
    decode(COMAR, 'EUREX', decode(COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
    FROM ERESHX H
    WHERE DANEG = (SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX')
);

[Matworms]

Merci pour ta réponse philcero.

Je viens tout juste d'apprendre que ERESHX n'est pas une table, mais une vue résultant d'une requête de 250 lignes environ... (je l'ai sous les yeux)

As-tu un de tes indexs sur la table ERESHX qui commence par la champs DANEG ?

Compte tenu de ma découverte récente, je dirais que c'est un peu plus compliqué que ça, et que faire la liste des indexes m'a l'air d'être une tâche peu aisée (si j'ai bien vu la vue fait intervenir des jointures entre 12 tables).

Autre axe, Envoyer le DISTINCT un cran plus haut.

Je suis en train de tester le code que tu m'as donné, mais j'ai dû faire quelques adaptations (mettre les decode() dans la partie haute) :

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
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
 
SELECT DISTINCT
  DANEG,
  COMAR,
  COINF,
  CNACT,
  LNACT,
  NUCON,
  decode(CSENS, 'A', 'B', 'S'),
  CSOPT,
  QTCCP,
  CMECH,
  CAECH,
  DAECA,
  CODEV,
  MTSNA,
  MTNEG,
  decode(COMAR, 'EUREX', decode(COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM
(
  SELECT DANEG,
         COMAR,
         COINF,
         CNACT,
         LNACT,
         NUCON,
         CSENS,
         CSOPT,
         QTCCP,
         CMECH,
         CAECH,
         DAECA,
         CODEV,
         MTSNA,
         MTNEG
  FROM ERESHX
  WHERE DANEG = (SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX')
);

La requête tourne en ce moment... depuis 5 minutes environ, wait and see. Je reviendrai pour donner les résultats obtenus.

[vlah79]

Bonjour,

As-tu essayé le code suivant :

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
 
SELECT DISTINCT H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM ERESHX H
,    DATRAI D
WHERE H.DANEG = D.DATRA
AND     D.COMAR = 'EUREX'

Tu éviteras de faire appel x fois à ta sous requête.
Et le plan d'exécution devrait en être amélioré.


pistes alternatives :
- Vérifier la volumétrie de ces tables, forcément trés grosse, pour justifier l'appel à un expert.
- Utilisation d'une commande bulk, pour traiter la requête en plusieurs lots -> diminution de la mémoire utilisée pour l'opération - ce qui évite des effets de saturation) -> demander conseil à une personne expérimentée.

[Matworms]

Merci vlah79.

J'avais envisagé quelque chose dans le genre, je suis en train de tester ta requête, j'ai stoppé l'autre qui tournait depuis plus de 40 minutes. Cela dit, il faut que je vérifie que le nombre de lignes renvoyées est correct, je fais un retour dès que c'est terminé.

Je vais étudier d'autres pistes aussi, mais j'ai plutôt l'impression qu'on m'a donné ce travail plus pour m'occuper l'esprit (et les doigts) que parce que c'est d'une nécessité absolue. Mais comme je suis quelqu'un de sérieux, je cherche une solution acceptable...

[vlah79]

Si tu disposes de TOAD ou de SQL DEVELOPER, essaye de récupérer le plan d'execution de ta requête.

Cela aide toujours pour voir ou la requete perd du temps.

[kervoaz]

Bonjour,

si avec une date en dur ca fonctionne bien tu peux faire:

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
with u as (SELECT
                 DISTINCT DATRA
                 FROM DATRAI
                 WHERE COMAR = 'EUREX')
SELECT DISTINCT H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM ERESHX H, u
WHERE H.DANEG = u.DATRA;

ou

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
with u as (SELECT
                  DATRA
                 FROM DATRAI
                 WHERE COMAR = 'EUREX' WHERE ROWNUM = 1)
SELECT DISTINCT H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
FROM ERESHX H, u
WHERE H.DANEG = u.DATRA;

essaie d'enlever les decode pour voir si l'appel à cette fonction est couteux

cdt

[mnitu]

Je ne connais pas mais je dois optimiser : ça déjà c’est un défi. Mais si en plus on ajoute le n’importe quoi reçu sous forme des conseils c’est mission impossible !
Donc perte pas ton temps, fait autre chose.

[kervoaz]

Un tres bon conseil ca Mnitu ?
Si tu connais pas, tu fais pas et cela même si des gens sont prêt à t'aider.

Désolé je ne partage pas du tout cette opinion....sinon on peut fermer tous les forums.

[Matworms]

mnitu a dû oublier qu'un jour il (elle ?) ne savait pas non plus. (en plus de sa conjugaison)

Désolé kervoaz, mais visiblement WITH ... AS ne simule pas une date passée "en dur". Etant donné que ça fait plus de 30 minutes que la requête tourne, il semble bien que l'expression soit réévaluée, "u" ne serait alors qu'un alias appelé au besoin.

[mnitu]

mnitu a dû oublier qu'un jour il (elle ?) ne savait pas non plus. (en plus de sa conjugaison)

Désolé kervoaz, mais visiblement WITH ... AS ne simule pas une date passée "en dur". Etant donné que ça fait plus de 30 minutes que la requête tourne, il semble bien que l'expression soit réévaluée, "u" ne serait alors qu'un alias appelé au besoin.

Ce n’était pas une moquerie, c’étai sérieux. Les conseils que t’a reçu sont n’importe quoi (bulk, decode, etc.).
Bref, j’ai un conseil : essaie de comprendre.
Voilà

PS. Heureusement la conjugaison en français n'a rien à voir avec Oracle. Mais peut importe, j'y travail.

[Matworms]

C'est un peu plus clair comme ça...

Pour ce qui est du decode, il fait partie de la requête qu'on m'a donnée, ce n'est rien de plus qu'une façon de renommer certains champs (je n'ai pas de contrôle là-dessus). Par contre pour les commandes bulk, ça m'a un peu surpris aussi.

Cela dit, ce n'est pas aussi simple que ça peut le paraître, comme je l'ai dit, ERESHX est une vue faisant intervenir 18 jointures sur 12 tables, pour un résultat à 9 315 190 lignes. La requête de "remplissage" de la vue fait environ 250 lignes, je ne vais donc pas la coller ici (sauf si c'est vraiment nécessaire)...

Il s'agit d'essayer de réécrire la requête pour qu'elle s'exécute en un temps convenable, le périmètre d'action se limite donc à la requête elle-même.

EDIT: Merci pour le conseil mnitu, mais tu pourrais aussi faire monter ton compteur en donnant le même à tous ceux qui postent sur le forum.

[McM]

SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX' renvoie une seule ligne (une date).

Tu fais ce select avant, et tu mets la date dans une variable.

[kervoaz]

Enlever les decode, c'etait juste pour évaluer leur "poids". La fonction decode va être appelée pour chaque ligne ramenée et l'explain plan ne fait pas apparaitre ce cout supplementaire.
Concernant le with, son utilité est justement de forcer l'exécution de cette requête en premier.
As tu essayé avec le rownum? dans certains cas, ca peut également pousser l'exécution de la requête à être évalué en premier.

N'y aurait il pas une conversion implicite entre DANEG et DATRA(une date et un varchar2)?
Ca donne quoi si tu fais qqchose comme
WITH u AS (SELECT
sysdate
FROM dual)

[Matworms]

Merci à tous, j'ai une solution.

J'ai suivi le conseil de McM (je me souviens avoir pensé à ça quand j'ai vu la requête pour la première fois, mais je suis assez fatigué et j'ai oublié entre temps)...

Voilà la nouvelle requête :

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
 
column VAR new_value _VAR
 
select (select distinct DATRA from DATRAI where COMAR = 'EUREX') VAR from DUAL;
 
select distinct H.DANEG,
                H.COMAR,
                H.COINF,
                H.CNACT,
                H.LNACT,
                H.NUCON,
                decode(H.CSENS, 'A', 'B', 'S'),
                H.CSOPT,
                H.QTCCP,
                H.CMECH,
                H.CAECH,
                H.DAECA,
                H.CODEV,
                H.MTSNA,
                H.MTNEG,
                decode(H.COMAR, 'EUREX', decode(H.COINF, 'HSBC EUR', 'HL1235', 'HSBCEURC', 'CL1234'), 'AEX', 'HL1236', 'ADEX', 'HL1238', 'HL1234')
from ERESHX H
where H.DANEG = to_date('&_VAR', 'DD-MON-YY');

Ca tient plus de l'astuce que de l'optimisation, mais ça a le mérite de fonctionner aussi rapidement qu'avec une date "en dur".

Il y a peut-être une façon plus élégante de stocker le résultat du premier SELECT dans une variable ? J'ai pris une méthode que j'avais déjà utilisée pour un autre script.

[McM]

Tu es sur d'avoir une seule date pour EUREX ?

Code :

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

SELECT (SELECT DISTINCT DATRA FROM DATRAI WHERE COMAR = 'EUREX') VAR FROM DUAL;

Dans ce cas, évite le distinct

Code :

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

SELECT DATRA VAR FROM DATRAI WHERE COMAR = 'EUREX' AND ROWNUM = 1

[McM]

Cela dit, ce n'est pas aussi simple que ça peut le paraître, comme je l'ai dit, ERESHX est une vue faisant intervenir 18 jointures sur 12 tables, pour un résultat à 9 315 190 lignes. La requête de "remplissage" de la vue fait environ 250 lignes, je ne vais donc pas la coller ici (sauf si c'est vraiment nécessaire)...

Voila pourquoi je déteste les VUES.

En fait la restriction de date sur ta vue ERESHX n'est pas juste une jointure, mais 18 fois les jointures dans les sous-requetes de ta vue

Si tu veux optimiser la vue, ça doit être possible aussi

[Matworms]

Tu es sur d'avoir une seule date pour EUREX ?

Oui, tu as raison, j'ai modifié ma requête.

En fait la restriction de date sur ta vue ERESHX n'est pas juste une jointure, mais 18 fois les jointures dans les sous-requetes de ta vue

Si tu veux optimiser la vue, ça doit être possible aussi

Exact, mais pour le moment ce n'est pas mon travail.