optimisation requete SQL

**lepierot** · 23/08/2007, 08h52

Voici une requete sur laquelle toutes les tables sont ridiculement petites sauf la table paie2 76 millions de lignes et la table salaries 250000 lignes.

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
39
40
41
42
43
44
45
46
47
SELECT   
  AGENDA.ANN || lpad(AGENDA.MOI,2,0),
  SERVICES.COD_SOCIETE,
  SERVICES.COD_ETAB,
  SERVICES.COD_SERV,
  SERVICES.LIB_SERV,
  SALARIES.CDMATN,
  SALARIES.NOMSAN,
  sum(decode(PAIE2.PAYE.CDRUB9,'330090',PAIE2.PAYE.MTSR29,'330110', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330010',PAIE2.PAYE.MTSR29,'330030', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330070',PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330122',PAIE2.PAYE.MTSR29,'330124', - PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'241510',PAIE2.PAYE.MTSR29,'242010',PAIE2.PAYE.MTSR29,'241580', - PAIE2.PAYE.MTSR29,'242140',-PAIE2.PAYE.MTSR29,0)),
  sum(decode(PAIE2.PAYE.CDRUB9,'330090',PAIE2.PAYE.MTSR29,'330110', - PAIE2.PAYE.MTSR29,'330010',PAIE2.PAYE.MTSR29,'330030', - PAIE2.PAYE.MTSR29,'330070',-PAIE2.PAYE.MTSR29,'330122',PAIE2.PAYE.MTSR29,'330124', - PAIE2.PAYE.MTSR29,'241510',PAIE2.PAYE.MTSR29,'242010',PAIE2.PAYE.MTSR29,'241580', - PAIE2.PAYE.MTSR29,'242140',-PAIE2.PAYE.MTSR29,0))
FROM
  SERVICES,
  AGENDA,
  PAIE.CENTRE_COUT,
  SALARIES,
  PAIE2.PAYE
WHERE
  ( PAIE.CENTRE_COUT.COD_SOCIETE=SERVICES.COD_SOCIETE and PAIE.CENTRE_COUT.MCMCU=SERVICES.COD_CC  )
  AND  ( SERVICES.COD_DEP=SALARIES.CDDEPN and SERVICES.COD_ETAB=SALARIES.CDETSN and SERVICES.COD_SERV=SALARIES.CDSECN and SERVICES.COD_SOCIETE=SALARIES.CDSOCN and SERVICES.COD_CC=SALARIES.CTANAN  )
  AND  ( PAIE2.PAYE.CDMAT9=SALARIES.CDMATN AND                                                        PAIE2.PAYE.CDSOC9=SALARIES.CDSOCN  AND 
PAIE2.PAYE.CDETS9=SALARIES.CDETSN  AND                                                        PAIE2.PAYE.CDDEP9=SALARIES.CDDEPN  AND                                                       PAIE2.PAYE.CDSEC9=SALARIES.CDSECN  AND                                        to_number(to_char(PAIE2.PAYE.AADPP9)||lpad(to_char(PAIE2.PAYE.MMDPP9),2,0)||lpad(to_char(PAIE2.PAYE.JJDPP9),2,0)) = SALARIES.DTDPPN  AND to_number(to_char(PAIE2.PAYE.AADPP9)||lpad(to_char(PAIE2.PAYE.MMDPP9),2,0)) = SALARIES.PERATN  )
  AND  ( SERVICES.COD_DEP=PAIE2.PAYE.CDDEP9 and SERVICES.COD_ETAB=PAIE2.PAYE.CDETS9 and SERVICES.COD_SERV=PAIE2.PAYE.CDSEC9 and SERVICES.COD_SOCIETE=PAIE2.PAYE.CDSOC9  )
  AND  ( AGENDA.ANN=PAIE2.PAYE.AAPPA9 and AGENDA.CCCO=PAIE2.PAYE.CDSOC9 and AGENDA.MOI=PAIE2.PAYE.MMPPA9  )
  AND  ( PAIE.CENTRE_COUT.USR_BO='buonaguidi'  )
  AND  (SALARIES.CDSOCN IN ('HBS','SBM','MIR','STM','SMS') )
  AND  (
  AGENDA.ANN || lpad(AGENDA.MOI,2,0)  =  '200607'
  AND  SERVICES.COD_SOCIETE  IN  'STM'
  AND  ( (SALARIES.cdsocn, SALARIES.cdmatn, SALARIES.rrn) in (SELECT c.CDSOCN,c.CDMATN,max(c.rrn)
FROM
  SALARIES C
WHERE   C.PERATN  =  '200607'
  group by c.cdsocn, c.cdmatn)
  )
  )
GROUP BY
  AGENDA.ANN || lpad(AGENDA.MOI,2,0), 
  SERVICES.COD_SOCIETE, 
  SERVICES.COD_ETAB, 
  SERVICES.COD_SERV, 
  SERVICES.LIB_SERV, 
  SALARIES.CDMATN, 
  SALARIES.NOMSAN;

cette requete rame, elle dur 40 minutes, comment l'optimiser ?

Merci

**orafrance** · 23/08/2007, 09h07

nous ne sommes pas devin

merci de lire et respecter les règles du forum notamment dans la précision d'éléments fondamentaux pour pouvoir t'aider : version, indexes, plan d'exécution et éventuellement événement d'attente (v$session_wait ou tkprof)

**laurentschneider** · 23/08/2007, 09h10

pour éliminer le double table scan de salaries

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
 
SELECT   
  SALARIES.CDMATN,
  SALARIES.NOMSAN
FROM
  SALARIES
WHERE
  (SALARIES.cdsocn, SALARIES.cdmatn, SALARIES.rrn) in 
  (
    SELECT c.CDSOCN,c.CDMATN,max(c.rrn) 
    FROM SALARIES C 
    WHERE  C.PERATN  =  '200607' 
    group by c.cdsocn, c.cdmatn
  )
GROUP BY
  SALARIES.CDMATN, 
  SALARIES.NOMSAN;

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT   
  CDMATN,
  max(NOMSAN) keep (dense_rank last order by RRN)
FROM
  SALARIES
WHERE
  PERATN  =  '200607' 
GROUP BY
  CDMATN;

PS: ce n'est pas une solution, les requêtes ne sont pas 100% équivalentes, c'est juste une piste !

**orafrance** · 23/08/2007, 09h14

déjà, si c'est possible, supprime :

Code :

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

1
2
3
4
5
6
7
8
AND (salaries.cdsocn, salaries.cdmatn, salaries.rrn) IN (
                                        SELECT   c.cdsocn, c.cdmatn,
                                                 MAX (c.rrn) 
                                            FROM salaries c
                                           WHERE c.peratn = '200607'
                                        GROUP BY c.cdsocn, c.cdmatn)
              )
     AND salaries.cdsocn IN ('HBS', 'SBM', 'MIR', 'STM', 'SMS')

et remplace salaries par :

Code :

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

1
2
3
4
5
6
 SELECT   c.cdsocn, c.cdmatn,cdetsn,cddepn,cdsecn
                                                 MAX (c.rrn) rrn
                                            FROM salaries c
                                           WHERE c.peratn = '200607'
                                               AND c.cdsocn IN ('HBS', 'SBM', 'MIR', 'STM', 'SMS')
                                        GROUP BY c.cdsocn, c.cdmatn,cdetsn,cddepn,cdsecn

dans la clause FROM

Edit : si fonctionnellement, le GROUP BY te géne, utilise la fonction analytique comme indiqué par Laurent

**orafrance** · 23/08/2007, 09h18

sans le plan d'exécution c'est pas évident mais ces jointures :

Code :

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

1
2
3
4
     AND services.cod_dep = paie2.paye.cddep9
     AND services.cod_etab = paie2.paye.cdets9
     AND services.cod_serv = paie2.paye.cdsec9
     AND services.cod_societe = paie2.paye.cdsoc9

posent peut-être problème en faisant un NL services-paie2.paye au lieu de salaire-paie2.paye. Comme fonctionnellement ça ne sert à rien (les jointures services-salaire et salaire-paie2.paye étant présentes) tu peux tenter de les supprimer

**laurentschneider** · 23/08/2007, 09h20

Code :

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

max(...) keep (dense_rank last order by ...)

est fonction d'aggrégation pas une fonction analytique.une fonction analytique, c'est quelque chose comme

Code :

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

max(...) over ()

ou

Code :

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

dense_rank() over (order by ...)

**orafrance** · 23/08/2007, 09h24

ha... pour moi une fonction d'aggrégation c'est uniquement :

Code :

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

1
2
3
 
SELECT MAX ...
GROUP BY

me tromperais-je donc ?

Edit : Apparemment : http://download.oracle.com/docs/cd/B...s.htm#i1006709

MAX est une fonction analytique lorsqu'elle est utilisé avec un fenêtrage et l'expression que tu proposes est une expression d'aggrégation... c'est bien ça ?

Windowing Aggregate Functions

analytic_function([ arguments ])
OVER (analytic_clause)

**laurentschneider** · 23/08/2007, 09h40

FIRST/LAST, ce sont des fonctions d'aggrégations qui requièrent la clause KEEP

le plus haut salaire du département (aggrégat)

Code :

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

1
2
3
4
5
6
7
8
 
SQL> select deptno, max(sal) from emp group by deptno
 
    DEPTNO   MAX(SAL)
---------- ----------
        30       2850
        20       3000
        10       5000

l'employé et son salaire comparé au plus haut salaire du département (analytique)

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
SQL> select deptno, ename,sal,max(sal) over (partition by deptno) from emp
 
    DEPTNO ENAME             SAL MAX(SAL)OVER(PARTITIONBYDEPTNO)
---------- ---------- ---------- -------------------------------
        10 CLARK            2450                            5000
        10 KING             5000                            5000
        10 MILLER           1300                            5000
        20 JONES            2975                            3000
        20 FORD             3000                            3000
        20 ADAMS            1100                            3000
        20 SMITH             800                            3000
        20 SCOTT            3000                            3000
        30 WARD             1250                            2850
        30 TURNER           1500                            2850
        30 ALLEN            1600                            2850
        30 JAMES             950                            2850
        30 BLAKE            2850                            2850
        30 MARTIN           1250                            2850

l'employé le mieux payé et son salaire par département (aggrégat)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select deptno, max(ename) keep (dense_rank last order by sal) from emp
select deptno, max(ename) keep (dense_rank last order by sal) from emp
       *
Error at line 1
ORA-00937: not a single-group group function
 
SQL> select deptno, max(ename) keep (dense_rank last order by sal) from emp group by deptno
 
    DEPTNO MAX(ENAME)
---------- ----------
        10 KING      
        20 SCOTT     
        30 BLAKE

l'erreur ORA-937 montre bien qu'il s'agit d'un aggrégat et non d'une fonction analytique

PS: la clause analytique est toujours précédée de OVER()

**orafrance** · 23/08/2007, 09h49

magnifique démonstration, c'est très clair

**laurentschneider** · 23/08/2007, 10h39

pour ajouter un peu de piment, voici keep et over réunis

chaque employé (nom et salaire) comparé à l'employé le mieux payé du département (nom et salaire)

Code :

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

1
2
3
4
5
6
7
8
 
select deptno, 
  ename, 
  sal, 
  max(ename) keep (
    dense_rank last order by sal) over (partition by deptno) maxsal_ename,
  max(sal) over (partition by deptno) maxsal_sal
from emp;

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
 
    DEPTNO ENAME             SAL MAXSAL_ENA MAXSAL_SAL
---------- ---------- ---------- ---------- ----------
        10 CLARK            2450 KING             5000
        10 KING             5000 KING             5000
        10 MILLER           1300 KING             5000
        20 JONES            2975 SCOTT            3000
        20 FORD             3000 SCOTT            3000
        20 ADAMS            1100 SCOTT            3000
        20 SMITH             800 SCOTT            3000
        20 SCOTT            3000 SCOTT            3000
        30 WARD             1250 BLAKE            2850
        30 TURNER           1500 BLAKE            2850
        30 ALLEN            1600 BLAKE            2850
        30 JAMES             950 BLAKE            2850
        30 BLAKE            2850 BLAKE            2850
        30 MARTIN           1250 BLAKE            2850

**orafrance** · 23/08/2007, 11h21

c'est trop beau

avec tout ça, la discussion initiale n'avance pas

**lepierot** · 23/08/2007, 15h22

merci pour vos reponses.
J'essaierais demain si j'ai le temps de vous fournir un plan d'execution.
j'ai oublié de vous dire que cette requete etait quasi immediate en 8.1.7 c'est depuis que j'ai migré en 10.2.0.3 que ca deconne.

j'ai alors fait un alter session set optimizer_features_enable='8.1.7' et al ca refonctionne correctement.

**lepierot** · 23/08/2007, 15h26

Plan d'exécution
------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 181 |14 |
| 1 | HASH GROUP BY | | 1 | 181 |14 |
| 2 | HASH JOIN SEMI | | 1 | 181 |13 |
| 3 | TABLE ACCESS BY INDEX ROWID | PAYE | 1 | 38 |3 |
| 4 | NESTED LOOPS | | 1 | 158 |10 |
| 5 | NESTED LOOPS | | 1 | 120 |7 |
| 6 | HASH JOIN | | 1 | 66 |5 |
| 7 | MERGE JOIN CARTESIAN | | 110 | 2640 |2 |
| 8 | INDEX RANGE SCAN | PK_AGENDA | 1 | 8 |1 |
| 9 | BUFFER SORT | | 114 | 1824 |1 |
| 10 | INDEX RANGE SCAN | FK_SOC | 114 | 1824 |1 |
| 11 | TABLE ACCESS BY INDEX ROWID| SERVICES | 23 | 966 |2 |
| 12 | INDEX RANGE SCAN | SERVICES_OUTL01 | 23 | |1 |
| 13 | TABLE ACCESS BY INDEX ROWID | SALARIES | 1 | 54 |2 |
| 14 | INDEX RANGE SCAN | FK_SALSERV | 1 | |1 |
| 15 | INDEX RANGE SCAN | FK_TYE_PART | 1 | |2 |
| 16 | VIEW | VW_NSO_1 | 6 | 138 |3 |
| 17 | HASH GROUP BY | | 6 | 120 |3 |
| 18 | TABLE ACCESS BY INDEX ROWID | SALARIES | 181 | 3620 |2 |
| 19 | INDEX RANGE SCAN | SALARIE_OUTL01 | 3 | |1 |
---------------------------------------------------------------

**orafrance** · 23/08/2007, 15h34

MERGE JOIN CARTESIAN

Dis donc, tu pourrais pas le formater un peu mieux ? Une impression écran de TOAD par exemple ce serait pas mal

Et elle est passé où la 2° table paye... t'es sûr que c'est le bon plan ?

**lepierot** · 24/08/2007, 11h11

En fait cette requete s'executait parfaitement en 8I. C'est avec la 10G (10.2.0.3) que ca a commencer a deconner. Pour l'instant j'ai résolu mon probleme en forcant l'optimieur 8.1.7 par la commande :

alter session set optimizer_features_enable='8.1.7'

j'ai mis ca dans un trigger de logon et ca marche.

D'autre part, j'ai supprimer les statistiques sur la grosse table Paie et je les ai laissée sur toutes les autres.

Ensuite j'ai aussi supprimer presque toutes les fonctions.

Voila et merci à tous.

cette discussion m'aura permis de comprendre que je dois absolument suivre une formation d'optimisation SQL.

**orafrance** · 24/08/2007, 11h28

une optimisation bien dégueu sans aucun doute

**salim11** · 13/09/2007, 13h25

Envoyé par laurentschneider

pour ajouter un peu de piment, voici keep et over réunis

chaque employé (nom et salaire) comparé à l'employé le mieux payé du département (nom et salaire)

Code :

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

1
2
3
4
5
6
7
8
 
select deptno, 
  ename, 
  sal, 
  max(ename) keep (
    dense_rank last order by sal) over (partition by deptno) maxsal_ename,
  max(sal) over (partition by deptno) maxsal_sal
from emp;

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
    DEPTNO ENAME             SAL MAXSAL_ENA MAXSAL_SAL
---------- ---------- ---------- ---------- ----------
        10 CLARK            2450 KING             5000
        10 KING             5000 KING             5000
        10 MILLER           1300 KING             5000
        20 JONES            2975 SCOTT            3000
        20 FORD             3000 SCOTT            3000
        20 ADAMS            1100 SCOTT            3000
        20 SMITH             800 SCOTT            3000
        20 SCOTT            3000 SCOTT            3000
        30 WARD             1250 BLAKE            2850
        30 TURNER           1500 BLAKE            2850
        30 ALLEN            1600 BLAKE            2850
        30 JAMES             950 BLAKE            2850
        30 BLAKE            2850 BLAKE            2850
        30 MARTIN           1250 BLAKE            2850

J'ai une petite remarque : l'utilisation de max(ename) keep (
dense_rank peut nous induire en erreurs à cause des doublons; pour le département 20 il y a scott et ford qui ont le meilleur salaire mais le max(ename) keep (
dense_rank last order by sal) over (partition by deptno) donne seulement SCOTT, donc en cas d'égalité il prend le max(ename) ???????

**laurentschneider** · 13/09/2007, 14h46

Envoyé par salim11

donc en cas d'égalité il prend le max(ename) ?

oui

**salim11** · 13/09/2007, 15h01

Salut,

La conclusion :
c'est mieux alors d'utiliser la fonction analytique dense_rank () over().

**laurentschneider** · 13/09/2007, 15h41

Envoyé par salim11

Salut,

La conclusion :
c'est mieux alors d'utiliser la fonction analytique dense_rank () over().

Bof, ce n'est ni mieux ni moins bien, c'est juste différent. Si tu ne veux qu'une seule ligne alors KEEP est beaucoup plus performant. Si tu veux retourner tous les employés avec le même salaire, tu peux employer DENSE_RANK, mais attention, tu devras utiliser une sous-requête en plus (where r=1)

optimisation requete SQL

SQL Oracle

Discussions similaires

Partager

Partager