Requête SQL très longue à s'exécuter

**dreamcat1** · 17/03/2010, 16h23

Bonjour à tous,

J'ai un problème avec une de mes requêtes SELECT SQL.

Ce SELECT met énormément de temps à ramener une trentaine de ligne lorsque je suis dans une base de développement, alors que sur la base de production cette requête met à peine 2 secondes à s'exécuter.

Le SELECT est composé de sous-requêtes et des définitions de tables du type:

Code :

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

1
2
3
 
SELECT colonne1
FROM (select colonne1 from ma_table where critere1='')

On m'a dit que les caractéristiques de la base de prod et de développement sont différentes. Du coup je me dis que peut-être la taille du buffer utilisé pour exécuter un SELECT est plus petite pour la base de dév... Mais ce n'est qu'une intuition.

Du coup, pouvez-vous me dire s'il existe un moyen d'optimiser cette requête?
J'ai pensé à une vue mais sur TOAD, quand j'affiche juste les données, ça me met aussi 10 ans à s'exécuter.

Merci d'avance pour votre contribution.

Dreamcat1

**Snipah** · 17/03/2010, 16h28

Bonjour,

Vous ne nous donnez ni la requête complète, ni la description des tables (structure et index, sur prod et sur dev), ni la volumetrie de la table, ni les plans d'execution, ni les versions d'Oracle utilisées.
C'est pas simple de vous aider du coup.

**dreamcat1** · 17/03/2010, 16h38

Effectivement ...

Les deux bases sont sous Oracle 8i.

Par contre pour les autres informations hormis la requête, je ne sais pas trop comment vous les fournir. Pouvez-vous préciser, s'il vous plaît?

Voici la requête SQL:

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
 
    select distinct
       -- infos sur la demande
      dem_abs.cd_dem_abs,dem_abs.dat_demande, gestion_cge.getrepart(dem_abs.cd_dem_abs,'Y') repart,
      --gestion_cge.ctrl_modif(dem_abs.cd_dem_abs,'','',0,0,-1,1) act_status,
      gestion_cge.ctrl_modif(dem_abs.cd_dem_abs,dem_abs.dat_deb,dem_abs.dat_fin,substr(ddat_deb,9),substr(ddat_fin,9),c.cd_collab,-1) act_status,
      to_char(dem_abs.cd_collab) cd_collab,to_char(dem_abs.cd_etat) cd_etat,lib_etat_demande.lib_etat lib_etat_demande,
      dem_abs.dat_deb,
      demi_jour_deb.lib_court lib_demi_jour_deb,
      substr(ddat_deb,9) cd_demi_jour_deb ,
      dem_abs.dat_fin,
      demi_jour_fin.lib_court lib_demi_jour_fin,
      substr(ddat_fin,9) cd_demi_jour_fin,
      nb_jours,
      to_char(dem_abs.cd_verif_rh) cd_verif_rh,
      lib_verif_rh,
      dem_abs.commentaire_rh,
      -- imputation : si non null on a une imputation paye dans au moins 1 absence
      to_char(dem_abs.mois_imputation,'dd/mm/yyyy') mois_imputation,
      to_char(dem_abs.dat_modif,'dd/mm/yyyy HH24:MI:SS') dat_modif,
      to_char(dem_abs.cd_util) cd_util,
      -- infos collab
      c.prenom_collab,c.nom_collab,etat_collab.commentaire comm_collab,  c.info_tps_partiel, c.partiel, c.email,
      -- infos approb
      to_char(etat_approb.cd_etat) cd_etat_approb,lib_etat_approb.lib_etat lib_etat_approb,
      to_char(dem_abs.cd_collab_approb) cd_collab_approb, approb.nom_collab||' '||approb.prenom_collab approb, approb.email mail_approb,
      etat_approb.commentaire comm_approb,
      -- infos client (pour afficher le nom de l'affaire req ï¿½ part)
      dem_abs.resp_client, dem_abs.accord_client, dem_abs.tel_client, to_char(dem_abs.cd_affaire) cd_affaire,
      -- infos valid
      to_char(etat_valid.cd_etat) cd_etat_valid,lib_etat_valid.lib_etat lib_etat_valid,
      to_char(dem_abs.cd_collab_valid) cd_collab_valid,valid.nom_collab||' '||valid.prenom_collab valid, valid.email mail_valid,
      etat_valid.commentaire comm_valid,
      etat_approb.dat_modif dat_approb, etat_valid.dat_modif dat_valid,
      tab_rh.nb modif_rh ,col_modif.modif_collab,
      decode(afa.ref_mission,null,to_char(afa.cd_affaire),afa.ref_mission) ref_mission,afa.nom_affaire,cli.lib_client
 
    from (
 
        select da.*, tab_max_ligne.cd_ligne_collab_max, tab_max_ligne.cd_ligne_approb_max, tab_max_ligne.cd_ligne_valid_max,
        tab_abs.nb_jours, tab_abs.ddat_deb, tab_abs.ddat_fin, tab_abs.dat_deb, tab_abs.dat_fin,tab_abs.mois_imputation
        from
          -- Sï¿½lectionner les cd_ligne adï¿½quats : SOUS-REQUETE PRINCIPALE
           (
           select da.cd_dem_abs,max(heda_c.cd_ligne) cd_ligne_collab_max,
               max(heda_a.cd_ligne) cd_ligne_approb_max,
               max(heda_v.cd_ligne) cd_ligne_valid_max
               from demande_absence da,histo_etat_dem_abs heda_c,histo_etat_dem_abs heda_a,
               histo_etat_dem_abs heda_v
               where heda_c.cd_type_util=1
               and heda_c.cd_collab=da.cd_collab
               and heda_c.cd_dem_abs=da.cd_dem_abs
               and heda_a.cd_type_util=2
               and heda_a.cd_dem_abs=da.cd_dem_abs
               and nvl(heda_a.cd_collab,0)=nvl(da.cd_collab_approb,0)
               and heda_v.cd_type_util=3
               and heda_v.cd_dem_abs=da.cd_dem_abs
               and heda_v.cd_collab=da.cd_collab_valid
               and (da.cd_collab=188 or da.cd_collab_approb=188  or da.cd_collab_valid=188)
               group by da.cd_dem_abs
            ) tab_max_ligne,
          demande_absence da,
          (select a.cd_dem_abs,a.cd_collab,sum(nb_jours) nb_jours,
                   max(dat_mois_paye) mois_imputation,min(to_char(dat_deb,'YYYYMMDD')||cd_demi_jour_deb) ddat_deb, min(dat_deb) dat_deb,  max(to_char(dat_fin,'YYYYMMDD')||cd_demi_jour_fin) ddat_fin, max(dat_fin) dat_fin
                   from absence a, demande_absence da
                   where a.cd_dem_abs=da.cd_dem_abs and (da.cd_collab=188 or da.cd_collab_approb=188 or da.cd_collab_valid=188)
                   group by a.cd_dem_abs,a.cd_collab) tab_abs
          where
            tab_max_ligne.cd_dem_abs=da.cd_dem_abs
            and tab_abs.cd_dem_abs=da.cd_dem_abs
            and tab_abs.cd_collab=da.cd_collab
            /* aj ici les critï¿½res limitatifs PHP */  and (da.cd_collab<>188 or da.cd_collab=cd_collab_approb or da.cd_collab=cd_collab_valid)  and ((da.cd_etat in (1,2,3) and cd_collab_valid=188) or (da.cd_etat=1 and cd_collab_approb=188)) 
     ) dem_abs,
 
      (select he.cd_dem_abs,decode(sum(decode(he.cd_etat,1,1,0))-1,0,'N',-1,'N','O') as modif_collab
       from histo_etat_dem_abs he,demande_absence da where he.cd_type_util=1 and he.cd_dem_abs=da.cd_dem_abs and (da.cd_collab=188 or da.cd_collab_approb=188  or da.cd_collab_valid=188)
        group by he.cd_dem_abs
       ) col_modif,
         -- la demande a-t-elle ï¿½tï¿½ modifiï¿½e en dernier par les RH ? (4= profil sicom RH)
        (select da.cd_dem_abs,count(*) nb from utilisateurs u,demande_absence da
            where da.cd_util=u.cd_collaborateur
            and da.cd_collab!=da.cd_util
            and cd_profil_utilisateur=4
            and (da.cd_collab=188 or da.cd_collab_approb=188  or da.cd_collab_valid=188)
            group by da.cd_dem_abs) tab_rh,
      histo_etat_dem_abs etat_approb,
      histo_etat_dem_abs etat_valid,
      histo_etat_dem_abs etat_collab,
      collaborateur c,
      collaborateur approb,
      collaborateur valid,
      etat_dem_abs lib_etat_approb,
      etat_dem_abs lib_etat_valid,
      etat_dem_abs lib_etat_demande,
      demi_jour_conge demi_jour_deb,
      demi_jour_conge demi_jour_fin,
      verif_rh,
      affaire afa,
      client cli
    where
    tab_rh.cd_dem_abs(+)=dem_abs.cd_dem_abs
    and dem_abs.dat_deb > '01/01/2007'
    and col_modif.cd_dem_abs(+)=dem_abs.cd_dem_abs
    and afa.cd_affaire(+)=nvl(dem_abs.cd_affaire,0)
    and afa.cd_client=cli.cd_client(+)
    and etat_approb.cd_dem_abs=dem_abs.cd_dem_abs
    and etat_approb.cd_type_util=2
    and approb.cd_collab(+)=nvl(etat_approb.cd_collab,0)
    and etat_approb.cd_ligne=dem_abs.cd_ligne_approb_max
    and etat_valid.cd_dem_abs=dem_abs.cd_dem_abs
    and etat_valid.cd_type_util=3
    and valid.cd_collab=etat_valid.cd_collab
    and etat_valid.cd_ligne=dem_abs.cd_ligne_valid_max
    and etat_collab.cd_dem_abs=dem_abs.cd_dem_abs
    and c.cd_collab=etat_collab.cd_collab
    and etat_collab.cd_type_util=1
    and etat_collab.cd_ligne=dem_abs.cd_ligne_collab_max
    and substr(dem_abs.ddat_deb,9)=demi_jour_deb.cd_demi_jour
    and substr(dem_abs.ddat_fin,9)=demi_jour_fin.cd_demi_jour
    and dem_abs.cd_verif_rh=verif_rh.cd_verif_rh
    and lib_etat_demande.cd_etat=dem_abs.cd_etat
    and lib_etat_approb.cd_etat=etat_approb.cd_etat
    and lib_etat_valid.cd_etat=etat_valid.cd_etat  order by (sysdate-dat_deb) ASC, (sysdate-dat_demande) ASC

**mnitu** · 17/03/2010, 17h11

Vous voulez optimiser cette requête ou juste comprendre pourquoi les temps d'exécution sont différents entre les deux bases ?

Commencez déjà par regarder le paramétrage de l'optimiseur sur les deux bases:
SQL>show parameter optimizer
en spécial le paramètre optimizer_mode.

**Snipah** · 17/03/2010, 17h41

Faites également la liste des tables qui entrent en jeu, et comparer leurs index entre la dev et la prod.

**orafrance** · 17/03/2010, 18h17

Vérifie aussi le calcul des stats

Sinon, la requête elle-même, entre jointure externe, tri et regroupement, j'imagine très bien comme ça peut ramer

Le plus simple pour vérifier tout ce qu'on a dit, c'est peut-être de comparer les plans d'exécution

**dreamcat1** · 18/03/2010, 11h12

Bonjour à tous,

Le plus simple pour vérifier tout ce qu'on a dit, c'est peut-être de comparer les plans d'exécution

J'ai tenté de lancer sous SQL Plus la commande explain plan for ([ma requête SQL]) mais je me heurte à un problème de privilèges insuffisants.

Code :

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

ORA-01039:insufficient privileges on underlying objects of the view

C'est galère parce que je n'ai pas les identifiants pour pouvoir changer les privilèges, si toutefois c'est ça qu'il faut faire

Voici ce que j'obtiens lorsque je lance la commande suivante sous SQL Plus ...
SQL> show parameter optimizer
ORA-00942: Table ou vue inexistante

y'a un truc que j'ai loupé ou pas?

**mongilotti** · 18/03/2010, 17h59

moi aussi ça me retourne cette erreur.

**orafrance** · 18/03/2010, 21h35

faut voir avec le DBA pour avoir accés à la table

**dreamcat1** · 19/03/2010, 15h24

Bonjour,

N'ayant toujours pas réussi à lancer l'explain plan à cause des privilèges, j'ai comparé les index avec les moyens du bord.

Pour le dév et la prod, j'ai donc listé les tables concernées par la requête et exécuté une requête qui me liste les index selon ces tables.

A l'issue de la comparaison, je constate qu'il ne manque aucun index et qu'ils sont identiques dans les deux bases.

**Snipah** · 22/03/2010, 11h44

Les stats sont-elles à jour?

Code :

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

EXEC DBMS_STATS.gather_schema_stats('NOM_SCHEMA');

Requête SQL très longue à s'exécuter

SQL Oracle

Discussions similaires

Partager

Partager