Optimisation requête SQL

dahu29 · 24/08/2011, 17h41

Bonjour,

Je travaille sur des bases ORACLE 10i.
Dans mon script d'extraction, j'ai rajouté cette 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
 
	SELECT DECODE(CLI.code_couverture,'BIEN',1,'TIER',2,' '),
		RPAD(NVL(SFAC.no_contrat_prestation,' '),10,' '),
		TO_CHAR(NVL(SFAC.mt_ht,0), '09999999999999999S'),
		TO_CHAR(NVL(SFAC.mt_ttc,0), '09999999999999999S'), 
		RPAD(NVL(SIMP.reference_externe,' '),20,' ')
	FROM (
		SELECT no_contrat_prestation, mt_ht, mt_ttc, tiers_prestataire, id_simpres, id_affaire, id_element,
			RANK() over (PARTITION BY id_element, id_clipres ORDER BY id_sfac_clipres DESC) rang	
		FROM SFAC_CLIPRES
		  ) sfac
	INNER JOIN CLIPRES cli ON (CLI.no_contrat_prestation = SFAC.no_contrat_prestation)
	INNER JOIN SIMPRES simp ON (SIMP.id_simpres = SFAC.id_simpres AND SIMP.code_contrat_prestation = '5')
	WHERE SFAC.rang = 1
		AND SFAC.id_element = idelement 
		AND SFAC.id_affaire = idaffaire
		AND ROWNUM <= 10
	ORDER BY SFAC.id_element, SFAC.id_simpres
	;

L'exécution me fait passer mes temps de traitements de 3minutes à 26 minutes.

Les indexs des tables sont :
SFAC_CLIPRES => ID_SFAC_CLIPRES
CLIPRES => ID_CLIPRES, NO_CONTRAT_PRESTATION
SIMPRES => ID_SIMPRES

Je ne peux effectuer aucune action sur la structure des tables.

Voici ce que j'ai récupéré en exécutant un explain plan de la requête sous sql/developer :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
OPERATION        OPTIONS          OBJECT_NAME  OBJECT_TYPE    OPTIMIZER   ID    COST    CPU_COST    IO COST ACCESS_PREDICATES                           FILTER_PREDICATES                                                                                                   TIME	PROJECTION
SELECT STATEMENT                                              ALL_ROWS    0   	2930 	245513949 	2915                                         																														36	
SORT             ORDER BY                                                 1   	2930 	245513949 	2915                                         																														36	(#keys=2) SFAC.ID_ELEMENT[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], DECODE(CLI.CODE_COUVERTURE,'BIEN',1,'TIER',2,TO_NUMBER(' '))[22], RPAD(NVL(SFAC.NO_CONTRAT_PRESTATION,' '),10,' ')[10], TO_CHAR(NVL(SFAC.MT_HT,0),'09999999999999999S')[18], TO_CHAR(NVL(SFAC.MT_TTC,0),'09999999999999999S')[18], RPAD(NVL(SIMP.REFERENCE_EXTERNE,' '),20,' ')[20]
COUNT            STOPKEY                                                  2   	     	          	                                                   	ROWNUM<=10																												SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4], SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
NESTED LOOPS                                                              3   	2929 	229561053 	2915                                         																														36	(#keys=0) SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4], SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
NESTED LOOPS                                                              4   	2928 	229556670 	2914                                         																														36	(#keys=0) SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], CLI.CODE_COUVERTURE[VARCHAR2,4]
VIEW                                                                      5   	2927 	229552779 	2913                                             	SFAC.RANG=1																											36	SFAC.NO_CONTRAT_PRESTATION[VARCHAR2,10], SFAC.MT_HT[NUMBER,22], SFAC.MT_TTC[NUMBER,22], SFAC.ID_SIMPRES[NUMBER,22], SFAC.ID_AFFAIRE[NUMBER,22], SFAC.ID_ELEMENT[NUMBER,22], SFAC.ID_CLIPRES[NUMBER,22], SFAC.RANG[NUMBER,22]
WINDOW           SORT PUSHED RANK                                         6   	2927 	229552779 	2913                                             	RANK() OVER ( PARTITION BY ID_AFFAIRE,ID_ELEMENT,ID_CLIPRES ORDER BY INTERNAL_FUNCTION(ID_SFAC_CLIPRES) DESC )<=1	36	(#keys=4) ID_AFFAIRE[NUMBER,22], ID_ELEMENT[NUMBER,22], ID_CLIPRES[NUMBER,22], INTERNAL_FUNCTION(ID_SFAC_CLIPRES)[22], NO_CONTRAT_PRESTATION[VARCHAR2,10], MT_HT[NUMBER,22], ID_SIMPRES[NUMBER,22], MT_TTC[NUMBER,22], RANK() OVER ( PARTITION BY ID_AFFAIRE,ID_ELEMENT,ID_CLIPRES ORDER BY INTERNAL_FUNCTION(ID_SFAC_CLIPRES) DESC )[22]
TABLE ACCESS     FULL             SFAC_CLIPRES TABLE          ANALYZED    7   	2926 	213599884 	2913                                            	ID_AFFAIRE=1718201																									36	ID_SFAC_CLIPRES[NUMBER,22], ID_AFFAIRE[NUMBER,22], ID_ELEMENT[NUMBER,22], ID_CLIPRES[NUMBER,22], NO_CONTRAT_PRESTATION[VARCHAR2,10], MT_HT[NUMBER,22], MT_TTC[NUMBER,22], ID_SIMPRES[NUMBER,22]
TABLE ACCESS     BY INDEX ROWID   CLIPRES      TABLE          ANALYZED    8   	1    	88        	1                                            																														1	CLI.CODE_COUVERTURE[VARCHAR2,4]
INDEX            UNIQUE SCAN      UI_CLIPRES_1 INDEX (UNIQUE) ANALYZED    9   	1    	11        	1        CLI.ID_CLIPRES=SFAC.ID_CLIPRES  																																1	CLI.ROWID[ROWID,10]
TABLE ACCESS     BY INDEX ROWID   SIMPRES      TABLE          ANALYZED    10  	1    	100       	1                                               	SIMP.CODE_CONTRAT_PRESTATION='5'																					1	SIMP.REFERENCE_EXTERNE[VARCHAR2,30]
INDEX            UNIQUE SCAN      UI_SIMPRES_1 INDEX (UNIQUE) ANALYZED    11  	1    	19        	1        SIMP.ID_SIMPRES=SFAC.ID_SIMPRES 																																1	SIMP.ROWID[ROWID,10]

Merci pour votre aide

skuatamad · 24/08/2011, 19h31

Filtrer SFAC_CLIPRES dans la sous-requête avant d'évaluer RANK peut aider même si oracle semble déjà avoir poussé les filtres.
Mais de toute façon la requête est fausse car il faut trier avant de filtrer avec ROWNUM.

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
SELECT *
  FROM (
SELECT DECODE(CLI.code_couverture,'BIEN',1,'TIER',2,' '),
       RPAD(NVL(SFAC.no_contrat_prestation,' '),10,' '),
       TO_CHAR(NVL(SFAC.mt_ht,0), '09999999999999999S'),
       TO_CHAR(NVL(SFAC.mt_ttc,0), '09999999999999999S'), 
       RPAD(NVL(SIMP.reference_externe,' '),20,' ')
  FROM (SELECT no_contrat_prestation, mt_ht, mt_ttc, tiers_prestataire, id_simpres, id_affaire, id_element,
               RANK() over (PARTITION BY id_clipres ORDER BY id_sfac_clipres DESC) rang	
          FROM SFAC_CLIPRES
         WHERE id_element = idelement 
           AND id_affaire = idaffaire
        ) sfac
  JOIN CLIPRES cli  ON CLI.no_contrat_prestation = SFAC.no_contrat_prestation
  JOIN SIMPRES simp ON SIMP.id_simpres = SFAC.id_simpres
 WHERE SFAC.rang = 1
   AND SIMP.code_contrat_prestation = '5'
 ORDER BY SFAC.id_element, SFAC.id_simpres
       )
 WHERE ROWNUM <= 10

Par contre le plan associé est peu lisible, peux tu en refournir un depuis sqlplus avec un linesize suffisant pour que l'affichage soit correct.

Mohamed.Houri · 25/08/2011, 10h31

Bonjour,

Une étape importante lors de l'optimisation d'une requête est la génération de son explain plan accompagné toujours de la partie "predicate''. Et, pour que cet explain plan soit lisible et exploitable, il faut que l'indentation entre les différentes opérations exécutées par Oracle (Cost Based Optimizer) soit fidèlement reproduite. Donc, si vous voulez une aide générez alors l'explain plan de votre requête en suivant les étapes de l'exemple 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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
 
mhouri.world > spool c:\get_explain.txt
mhouri.world > SET linesize 120
mhouri.world > SET pagesize 500
mhouri.world > SELECT /*+ gather_plan_statistics */
  2                  ename,
  3                  job,
  4                  sal
  5            FROM emp
  6            WHERE empno = 7369;
 
ENAME      JOB              SAL                                                                                         
---------- --------- ----------                                                                                         
smith      clerk            815                                                                                         
 
mhouri.world >SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                       
----------------------------------------------------------------------------------------------------------
SQL_ID  1a7d3qsrnzyj0, child number 0                                                                                   
-------------------------------------                                                                                   
SELECT /*+ gather_plan_statistics */                 ename,                 job,                                        
sal               FROM emp               WHERE empno = 7369                                                             
 
Plan hash value: 2741608761                                                                                             
 
---------------------------------------------------------------------------------------------------------               
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |               
---------------------------------------------------------------------------------------------------------               
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:00.03 |       2 |      2 |               
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |      1 |      1 |      1 |00:00:00.02 |       1 |      1 |               
---------------------------------------------------------------------------------------------------------               
 
Predicate Information (IDENTIFIED BY operation id):                                                                     
---------------------------------------------------                                                                     
 
   2 - access("EMPNO"=7369)                                                                                             
 
 
19 rows selected.
 
mhouri.world >spool off;

dahu29 · 25/08/2011, 17h36

Merci pour ta réponse.
Je ne savais qu'il fallait faire une restriction sur le Rownum obligatoirement après un ORDER BY.
J'ai exécuté ta requête, j'obtiens un temps de 45min (j'ai augmenté le nombre de données à extraire).

Lorsque j'exécuté cette requête, le temps est de 35 minutes.

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
 
	SELECT * FROM (
		SELECT DECODE(CLI.code_couverture,'BIEN',1,'TIER',2,' '),
			RPAD(NVL(SFAC.no_contrat_prestation,' '),10,' '),
			TO_CHAR(NVL(SFAC.mt_ht,0)*100, '09999999999999999S'),
			TO_CHAR(NVL(SFAC.mt_ttc,0)*100, '09999999999999999S'), 
			RPAD(NVL(SIMP.reference_externe,' '),20,' ')
		FROM (
			SELECT no_contrat_prestation, mt_ht, mt_ttc, tiers_prestataire, id_simpres, id_affaire, id_element,
				RANK() over (PARTITION BY id_affaire, id_element, id_clipres ORDER BY id_sfac_clipres DESC) rang	
			FROM SFAC_CLIPRES
			  ) sfac
		INNER JOIN CLIPRES cli ON (CLI.no_contrat_prestation = SFAC.no_contrat_prestation AND SFAC.rang = 1)
		INNER JOIN SIMPRES simp ON (SIMP.id_simpres = SFAC.id_simpres AND SIMP.code_contrat_prestation = '5' AND SFAC.rang = 1)
		WHERE SFAC.id_affaire = idaffaire
		ORDER BY SFAC.id_affaire, SFAC.id_element, SFAC.id_simpres
	) WHERE ROWNUM <= 10
	;

J'ai rajouté les restrictions de rang dans chaque jointure, cela à l'air de beaucoup améliorer les temps.

Déjà le temps a bien amélioré, on est passé de 48 à 35minutes.
Je suis désolé, je n'ai pas accès à SQL+, seulement à SQLDevelopper

mnitu · 26/08/2011, 10h54

Citation:

Envoyé par dahu29

...J'ai rajouté les restrictions de rang dans chaque jointure, cela à l'air de beaucoup améliorer les temps.

Déjà le temps a bien amélioré, on est passé de 48 à 35minutes.
...

35 minutes pour 10 occurences

Mohamed.Houri · 26/08/2011, 12h39

L'index sur la table sfac_clipres semble n'être d'aucune utilité. Essayer plutôt de créer les indexes suivants :

Code :

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

1
2
3
 
CREATE INDEX ind_sfac_usr1 ON sfac_clipres (id_element, id_affaire);
CREATE INDEX ind_sfac_usr2 ON sfac_clipres (no_contrat_prestation, id_simpres) ;

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email