Oracle 9.2 : Tuning SQL

Version imprimable

Bonjour à tous !

J'ai une requête un peu gourmande, voyez vous un moyen de l'optimiser ?
Merci.

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
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
 
SELECT  pol.numero_police,                                                     
mou.numero_mvt,                                                    
det.id_agent_mvt,                                                  
mou.mouvement_agent,                                               
mou.date_de_reglement,                                             
mou.date_incident,                                                 
mou.date_debut_periode,                                            
mou.date_fin_periode,                                              
mou.statut_mouvement,                                              
mou.mouvement_mods_modidate,                                       
mou.id_mouvement,                                                  
det.montant_detail_mvt,                                            
par1.col_n2,                                                       
par2.col_a2,                                                       
eve.fractionnement,                                                
mou.id_type_mvt,                                                   
det.id_nature,                                                     
cat.code_categ,                                                     
mou.mou_id_mouvement,                                              
reg.montant_reglement,                                              
mou.montant_total_mouv,                                            
mou.retenues_sociales,                                             
reg.id_mode_paie,                                                   
mou.prelevement_liberatoire,                                       
eve.date_d_effet_evt,                                              
reg.id_reglement,                                                   
mou.id_mon_mvt,                                                    
par4.col_n4,
pol.id_de_police,
mou.mouvement_mods_creadate,
pol.id_pays,
pol.id_agent_gestion,
eve.id_evenement,
eve.evenement_mods_creadate                                         
FROM  gb_evenements eve,                                                                 
gb_mouvements mou,                                                                 
gb_police pol,                                                                     
gb_detail_mvt det,                                                                 
gb_parametre par1,                                                 
gb_categorie cat,                                                                  
gb_parametre par2,                                                 
gb_parametre par3,                                  
gb_parametre par4,                                  
gb_reglement reg,
gb_regltgdpa gdpa                                                                   
WHERE  mou.id_mouvement = reg.id_mouvement (+)
AND	 reg.id_reglement = gdpa.id_reglement (+) 
AND  mou.id_mon_mvt = par1.id_param 
AND  mou.id_evenement = eve.id_evenement 
AND  pol.id_de_police = eve.id_de_police 
AND  det.id_mouvement = mou.id_mouvement 
AND  eve.id_categorie = cat.id_categorie 
AND  cat.id_famille = par2.id_param  
AND  par2.cle_etr1 = par3.id_param 
AND  par3.cle_etr1 = par4.id_param 
AND  (((mou.date_de_reglement > '28/02/2006') 
AND  (mou.date_de_reglement < '01/04/2006'))  
OR  ((mou.date_incident > '28/02/2006')  
AND  (mou.date_incident < '01/04/2006'))  
OR  ((mou.statut_mouvement = 68) 
AND  (mou.mouvement_mods_modidate > '28/02/2006') 
AND  (mou.mouvement_mods_modidate < '01/04/2006') 
AND  (mou.date_de_reglement  IS NOT NULL))
OR	 ((gdpa.dt_annulation > '28/02/2006')
AND	  (gdpa.dt_annulation < '01/04/2006')
AND   (gdpa.annulation = 1)))      
ORDER BY  pol.numero_police ASC,                                                 
mou.id_mouvement ASC,                                              
det.id_agent_mvt ASC,                                              
reg.id_reglement ASC;

08/08/2006, 13h06
bouyao

Bonjour,

Pour l'optimiser, il faudrait encore plus de renseignement :
Plan d'execution
Les indexes utilisés
Nombres de lignes des tables

etc
08/08/2006, 13h14
davy.g

Vous auriez une requête pour extraire correctement les données de PLAN_TABLE ?
08/08/2006, 13h18
bouyao

essayez de voir le tutoriel de Jaouad : http://oracle.developpez.com/guide/tuning/tkprof/

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
30
31
32
33
34
35
36
37
38
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
 
----------------------------------------------------------------------------------------
| Id  | Operation                     |  Name          | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |  2533K|   572M|       |   706K|
|   1 |  SORT ORDER BY                |                |  2533K|   572M|  1237M|   706K|
|*  2 |   HASH JOIN                   |                |  2533K|   572M|    67M| 34268 |
|   3 |    TABLE ACCESS FULL          | GB_DETAIL_MVT  |  2533K|    38M|       |   996 |
|*  4 |    HASH JOIN                  |                |   802K|   169M|       | 21493 |
|   5 |     TABLE ACCESS FULL         | GB_PARAMETRE   |  4786 | 47860 |       |     7 |
|*  6 |     FILTER                    |                |       |       |       |       |
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|*  7 |      HASH JOIN OUTER          |                |       |       |       |       |
|*  8 |       HASH JOIN OUTER         |                |   802K|   153M|   151M| 12516 |
|*  9 |        HASH JOIN              |                |   802K|   142M|    30M|  6239 |
|* 10 |         HASH JOIN             |                |   273K|    27M|  1848K|  2048 |
|  11 |          TABLE ACCESS FULL    | GB_POLICE      | 47189 |  1290K|       |   182 |
|* 12 |          HASH JOIN            |                |   273K|    20M|       |   791 |
|* 13 |           HASH JOIN           |                |   135 |  5940 |       |    26 |
|* 14 |            HASH JOIN          |                |   135 |  4590 |       |    18 |
|* 15 |             HASH JOIN         |                |   135 |  3375 |       |    10 |
|  16 |              TABLE ACCESS FULL| GB_CATEGORIE   |   135 |  1755 |       |     2 |
|* 17 |              TABLE ACCESS FULL| GB_PARAMETRE   |  3669 | 44028 |       |     7 |
 
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
|* 18 |             TABLE ACCESS FULL | GB_PARAMETRE   |  3669 | 33021 |       |     7 |
|  19 |            TABLE ACCESS FULL  | GB_PARAMETRE   |  4786 | 47860 |       |     7 |
|  20 |           TABLE ACCESS FULL   | GB_EVENEMENTS  |   273K|  9095K|       |   759 |
|  21 |         TABLE ACCESS FULL     | GB_MOUVEMENTS  |   802K|    61M|       |  1099 |
|  22 |        TABLE ACCESS FULL      | GB_REGLEMENT   |   756K|    10M|       |   235 |
|  23 |       TABLE ACCESS FULL       | GB_REGLTGDPA   |   625K|  6105K|       |  2286 |
----------------------------------------------------------------------------------------

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
30
31
32
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
   2 - access("DET"."ID_MOUVEMENT"="MOU"."ID_MOUVEMENT")
   4 - access("MOU"."ID_MON_MVT"="PAR1"."ID_PARAM")
   6 - filter("MOU"."DATE_DE_REGLEMENT">TO_DATE('2006-02-28 00:00:00', 'yyyy-mm-dd hh
              24:mi:ss') AND "MOU"."DATE_DE_REGLEMENT"<TO_DATE('2006-04-01 00:00:00',
               'yyyy-mm-dd hh24:mi:ss') OR "MOU"."DATE_INCIDENT">TO_DATE('2006-02-28
              00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "MOU"."DATE_INCIDENT"<TO_DATE('
              2006-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR "MOU"."STATUT_MOUVEME
              NT"=68 AND "MOU"."MOUVEMENT_MODS_MODIDATE">TO_DATE('2006-02-28 00:00:00
              ', 'yyyy-mm-dd hh24:mi:ss') AND "MOU"."MOUVEMENT_MODS_MODIDATE"<TO_DATE
              ('2006-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "MOU"."DATE_DE_REG
              LEMENT" IS NOT NULL OR "GDPA"."DT_ANNULATION">TO_DATE('2006-02-28 00:00
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
              :00', 'yyyy-mm-dd hh24:mi:ss') AND "GDPA"."DT_ANNULATION"<TO_DATE('2006
              -04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "GDPA"."ANNULATION"=1)
   7 - access("REG"."ID_REGLEMENT"="GDPA"."ID_REGLEMENT"(+))
   8 - access("MOU"."ID_MOUVEMENT"="REG"."ID_MOUVEMENT"(+))
   9 - access("MOU"."ID_EVENEMENT"="EVE"."ID_EVENEMENT")
  10 - access("POL"."ID_DE_POLICE"="EVE"."ID_DE_POLICE")
  12 - access("EVE"."ID_CATEGORIE"="CAT"."ID_CATEGORIE")
  13 - access("PAR3"."CLE_ETR1"="PAR4"."ID_PARAM")
  14 - access("PAR2"."CLE_ETR1"="PAR3"."ID_PARAM")
  15 - access("CAT"."ID_FAMILLE"="PAR2"."ID_PARAM")
  17 - filter("PAR2"."CLE_ETR1" IS NOT NULL)
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
  18 - filter("PAR3"."CLE_ETR1" IS NOT NULL)

Tu peux nous donner pour chaque table :
1/ La clé primaire (quelles colonnes dans l'ordre)
2/ La taille (Nb lignes et taille physique)
3/ Les différents index (juste les colonnes)

PS : Simplifie aussi le code (les () et utilise les between)
Les date : Ce sont des paramètres ou c'est en dur pour tes tests, sinon, il faut mettre un to_date, ça évite les conversions

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
WHERE pol.id_de_police 	= eve.id_de_police 
AND  mou.id_evenement 	= eve.id_evenement 
AND  mou.id_mouvement 	= det.id_mouvement
AND  mou.id_mouvement   = reg.id_mouvement (+)
AND	 reg.id_reglement 	= gdpa.id_reglement (+) 
AND  mou.id_mon_mvt 	= par1.id_param 
AND  cat.id_categorie 	= eve.id_categorie
AND  cat.id_famille 	= par2.id_param
AND  par2.cle_etr1 		= par3.id_param
AND  par3.cle_etr1 		= par4.id_param 
AND  (	
		mou.date_de_reglement BETWEEN TO_DATE('28/02/2006', 'DD/MM/YYYY') AND TO_DATE('01/04/2006', 'DD/MM/YYYY')
	OR  mou.date_incident BETWEEN TO_DATE('28/02/2006', 'DD/MM/YYYY') AND TO_DATE('01/04/2006', 'DD/MM/YYYY')
	OR  (	mou.statut_mouvement = 68
		AND mou.mouvement_mods_modidate BETWEEN TO_DATE('28/02/2006', 'DD/MM/YYYY') AND TO_DATE('01/04/2006', 'DD/MM/YYYY')
		AND mou.date_de_reglement IS NOT NULL
		)
	OR	(	gdpa.dt_annulation BETWEEN TO_DATE('28/02/2006', 'DD/MM/YYYY') AND TO_DATE('01/04/2006', 'DD/MM/YYYY')
		AND gdpa.annulation = 1
		)
	)      
ORDER BY  pol.numero_police, mou.id_mouvement, det.id_agent_mvt, reg.id_reglement;

08/08/2006, 13h52
davy.g

Les tables ne sont pas énormes.
La + grosse doit contenir 300000 lignes.

Mis à part jouer sur les indexs, y a t il autres choses à faire?
08/08/2006, 13h54
davy.g

Les valeurs des dates, c'est pour le test !

Et les BETWEEN, c'est mieux que > ou < ?
09/08/2006, 23h26
remi4444

petite remarque fonctionnelle beetween est l'équivalent de (>= et <=) et non (< et >) question performance, je pense que c'est exactement pareil.

Pour commencer l'optimisation, il faut que tu identifie quelle est la clause qui va être la plus discriminante. (clause where tres filtrante, table avec tres peu de donnée etc...) Si c'est une clause where, il va faloir veiller à ce qu'il y ait des index sur les champs concernés. Ensuite tu va remonter jointure apres jointure tes tables, à chaque fois il faut qu'il y ait un index sur la colonne de jointure (c'est pour ça que souvent on crée un index sur chaque colonne de clef etrangère).

Sur ta requête, je pense que le point perturbant pour l'optimiseur se situe dans la baterie de "or" qui mélange des clause qui n'ont rien à voir (mou.date_incident , mou.date_incident etc...) En tout tu as 4 membres dans cette clause. Je te conseille en travail intermédiaire d'optimiser de manière séparée 4 requêtes avec chacune un seul des membre de ton "or". Tu va isoler ainsi le problème.

Sinon, de maniere plus "bourine" tu peux tenter le pari de faire un index sur chancune des colonnes concernée à savoir:
- mou.date_de_reglement
- mou.date_incident
- couple (mou.statut_mouvement,mou.mouvement_mods_modidate)
- gdpa.dt_annulation

Tu vérifiera aussi que toutes tes colonnes de jointure son indexées, (celles qui appartiennent à de grosses tables surtout)

Ensuite, il ne faut pas oublier de repasser les stats sur ton schéma (remarque, j'epère que tu as commencé par ça d'ailleur avant d'aller plus loin!)
11/08/2006, 02h23
hadlak
salut à tous si vous remarquer la colonne cost(coût) calculé par oracle au niveau du PLAN_TABLE vous remarquerez que le plus coûteux est les jointure donc essai de créer les index relatifs aux colonnes de jointures
de quel type sont les colonnes :
- mou.date_de_reglement
- mou.date_incident
- mou.statut_mouvement )
- mou.mouvement_mods_modidate'
- gdpa.dt_annulation
11/08/2006, 08h42
Oraman

Bonjour,
juste pour l'info cette requête renvoi combien d'enregistrements ?
est ce que les stat sur les tables sont à jour ?