Optimisation requête SQL (millions d'enregistrements)

[escuel]

Bonjour,

si je me permets de poster ici, c'est que je suis vraiment dans la .
J'essaye d'optimiser des requêtes qui portent sur des millions de lignes, depuis quelques temps, mais c'est encore beaucoup trop lent.
Alors j’espère que vous pourrez me donner quelques pistes.


voilà un exemple de requête et son plan d’exécution :

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
 
SELECT   CHAMP1,count(*) cpt   
FROM TABLE1   
WHERE (CHAMP1='XXXXX'  OR CHAMP1='YYYYY')  
AND TABLE1.CHAMP2 IN (SELECT CHAMP2 FROM TABLE3 WHERE table3ID=1) 
AND TABLE1.CHAMP3 IN ( SELECT CHAMP3  FROM TABLE4 WHERE table4ID=1)         	
AND  CHAMP5   IN 
	(                    
	SELECT    CHAMP5                    
	FROM TABLE2                     
	WHERE  CHAMP6 =                                  
			(                                  
			SELECT max(CHAMP6)                                   
			FROM TABLE2 m2                                  
			WHERE TABLE2.CHAMP5=CHAMP5                               
			)                    
	AND TABLE2.CHAMP1 = 'ZZZZZZ'                        
	GROUP BY CHAMP5                       
	)     
GROUP BY CHAMP1;
 
 
SELECT plan_table_output FROM TABLE(dbms_xplan.display());
 
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |     1 |    36 |       | 20596   (3)| 00:04:08 |
|   1 |  HASH GROUP BY                  |                     |     1 |    36 |       | 20596   (3)| 00:04:08 |
|   2 |   NESTED LOOPS                  |                     |     1 |    36 |       | 20595   (3)| 00:04:08 |
|   3 |    NESTED LOOPS                 |                     |     1 |    31 |       | 20595   (3)| 00:04:08 |
|   4 |     NESTED LOOPS                |                     |     2 |    50 |       | 20595   (3)| 00:04:08 |
|   5 |      VIEW                       | VW_NSO_2            |     2 |    10 |       | 20591   (3)| 00:04:08 |
|   6 |       HASH GROUP BY             |                     |     2 |    10 |       | 20591   (3)| 00:04:08 |
|   7 |        VIEW                     | VM_NWVW_3           |     2 |    10 |       | 20591   (3)| 00:04:08 |
|*  8 |         FILTER                  |                     |       |       |       |            |          |
|   9 |          HASH GROUP BY          |                     |     2 |    84 |       | 20591   (3)| 00:04:08 |
|* 10 |           HASH JOIN             |                     |  4090K|   163M|    14M| 20305   (2)| 00:04:04 |
|* 11 |            TABLE ACCESS FULL    | TABLE2              |   350K|    10M|       | 12948   (1)| 00:02:36 |
|  12 |            INDEX FAST FULL SCAN | IDX_MAX_CHAMP6      |  3548K|    37M|       |  2726   (2)| 00:00:33 |
|* 13 |      TABLE ACCESS BY INDEX ROWID| TABLE1              |     1 |    20 |       |     2   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN         | IDX8                |     1 |       |       |     1   (0)| 00:00:01 |
|* 15 |     INDEX UNIQUE SCAN           | IDX20               |     1 |     6 |       |     0   (0)| 00:00:01 |
|* 16 |    INDEX UNIQUE SCAN            | IDX23               |     1 |     5 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   8 - filter("CHAMP6"=MAX("CHAMP6"))
  10 - access("TABLE2"."CHAMP5"="CHAMP5")
  11 - filter("TABLE2"."CHAMP1"='ZZZZZZ')
  13 - filter("TABLE1"."CHAMP2" IS NOT NULL AND ("CHAMP1"='XXXXX' OR "CHAMP1"='YYYYY'))
  14 - access("CHAMP5"="CHAMP5")
  15 - access("table3ID"=1 AND "TABLE1"."CHAMP2"="CHAMP2")
  16 - access("table4ID"=1 AND "TABLE1"."CHAMP3"="CHAMP3")

Peut être que c'est au niveau des requetes, des indexes, ou bien d'oracle lui même...
Je ne sais plus trop quoi faire pour optimiser cette requête.
Merci pour l'aide que vous pourrez m'apporter.

[mnitu]

Il me semble que votre problème viens de la sous-requête

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
AND  CHAMP5   IN 
  (                    
  SELECT    CHAMP5                    
  FROM TABLE2                     
  WHERE  CHAMP6 =                                  
      (                                  
      SELECT max(CHAMP6)                                   
      FROM TABLE2 m2                                  
      WHERE TABLE2.CHAMP5=CHAMP5                               
      )                    
  AND TABLE2.CHAMP1 = 'ZZZZZZ'                        
  GROUP BY CHAMP5                       
  )

Modifier la requête pour faire d'abord la jointure entre les table 1 et 2 en utilisant une fonction analytique row_number() pour trier décroisant par champ6 de type top N et retenez que les plus récentes row_number = 1 avec les filtres sur les autres tables.

[Mohamed.Houri]

Le temps total de votre requête est de 00:04:08 alors que les deux premières opérations à elles seules prennent (via le HASH JOIN) 00 :04 :04

Code :

1
2
3
4
 
HASH JOIN             |                     |  4090K|   163M|    14M| 20305   (2)| 00:04:04 |
|* 11 |            TABLE ACCESS FULL    | TABLE2              |   350K|    10M|       | 12948   (1)| 00:02:36 |
|  12 |            INDEX FAST FULL SCAN | IDX_MAX_CHAMP6      |  3548K|    37M|       |  2726   (2)| 00:00:33 |

Ceci vous dit déjà sur quoi concentrer vos éfforts

Code :

1
2
 
|* 11 |            TABLE ACCESS FULL    | TABLE2              |   350K|    10M|       | 12948   (1)| 00:02:36 |

Avec toujours l'importante information du ''predicate" correspondant

Code :

1
2
 
11 - filter("TABLE2"."CHAMP1"='ZZZZZZ')

[escuel]

Bonjour,

merci pour vos réponses.

Pour mnitu :

Avec toute ma bonne volonté, j'ai essayé de tourner la requête dans tous les sens en y incorporant row_number() over (ORDER BY CHAMP6 DESC) r where r=1 mais je n'arrive pas du tout à la faire fonctionner... Si une âme charitable pouvait me donner la voie...


pour Mohamed.Houri :

il y a déjà un index sur "TABLE2"."CHAMP1"


Merci encore de m'aider.

[mnitu]

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
SELECT t.champ1, count(*) cpt
  FROM (
        SELECT table1.champ1, row_number() over (partition BY table2.champ5 ORDER BY table2.champ6 DESC) rn
          FROM table1
               JOIN
               table2
            ON (table1.champ5 = table2.champ5)
         WHERE (table1.champ1='XXXXX'  OR table1.champ1='YYYYY')
           AND TABLE1.CHAMP2 IN (SELECT CHAMP2 FROM TABLE3 WHERE table3ID=1) 
           AND TABLE1.CHAMP3 IN ( SELECT CHAMP3  FROM TABLE4 WHERE table4ID=1)           
           AND TABLE2.CHAMP1 = 'ZZZZZZ'   
       ) t    
 WHERE t.rn = 1
 GROUP BY t.champ1
/

[ojo77]

Pouvez vous faire l'explain plan avec cette réécriture qui évite les sous requête et les remplace par des vues dynamiques

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT TABLE1.CHAMP1
     , count(*) cpt   
FROM TABLE1 INNER JOIN
     ( SELECT CHAMP2 FROM TABLE3 WHERE table3ID=1) T3  
	 ON ( TABLE1.CHAMP2 = T3.CHAMP2 ) INNER JOIN
     ( SELECT CHAMP3 FROM TABLE4 WHERE table4ID=1) T4
	 ON ( TABLE1.CHAMP3 = T4.CHAMP3 ) INNER JOIN
     ( SELECT DISTINCT T2A.CHAMP5
	   FROM TABLE2 T2A INNER JOIN 
            ( SELECT max(CHAMP6)  MC6 FROM TABLE2 m2 ) T2 
            ON 	( T2A.CHAMP6 = T2.MC6 AND T2A.CHAMP5 = T2.CHAMP5 )
	   WHERE TABLE2.CHAMP1 = 'ZZZZZZ') T2B ON TABLE1.CHAMP5 = T2B.CHAMP5
WHERE TABLE1.CHAMP1 IN ('XXXXX', 'YYYYY')  
GROUP BY TABLE1.CHAMP1;

[Mohamed.Houri]

En plus de ce qui vous a été conseillé pour la réecriture de la requête, je me pose la question suivante: combien d'enregistrement votre requête produit-elle? 1 enregistrement!!!

Code :

1
2
3
4
5
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |     1 |    36 |       |

Si c'est le cas alors sachez que vos deux premières opérations à savoir 11 et 12 génèrent un volume de données énorme (350K enregistrements) par rapport au résultat final

Code :

1
2
3
 
* 11 |            TABLE ACCESS FULL    | TABLE2              |   350K|    10M|       | 12948   (1)| 00:02:36 |
|  12 |            INDEX FAST FULL SCAN | IDX_MAX_CHAMP6      |  3548K|    37M|       |  2726

Ce qui veut dire que vous commencez par sélectionner 350.000 lignes pour n'en laisser à la fin qu'une seule!!!!

Ceci va à l'encontre de la bonne performance :"Start Small and Keep Small". Commencer par le plus petit volume de données possible et essayer de le rester. En d'autres mots, éliminez les données dès les premières opérations. D'où la proposition de Marius de réecrire la requête.

Enfin, si vous sélectionnez plus d'un enregistrement alors utilisez plutôt la génération d'un explain plan qui contient les estimations faites par le CBO (E-Rows et A-Rows) afin de pouvoir localiser où les discordances commencent et peut-être comprendre pourquoi l'index déjà présent sur la table2 n'est pas utilisé.