Bonjour,

J'en appelle à vous car je suis confronté à un probléme qui me casse bien la tête !!!

ci dessous une requête sur Oracle 11g qui passait très bien il y a encore 2 jours :

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
ADS.ADS_IDI,
PP.PER_IDI,
DSZ_H_ADHERENTS.ADH_CIV,
CASE when ADS_PER.ADP_FLG_OD=0 THEN 1 ELSE 0 END,
CASE when UPPER(ADS_PER.ADP_QTE) = upper('spos') THEN 1 ELSE 0 END
from	DSZ_H_ADH_PER   ADS_PER
, DSZ_H_ADHESIONS   ADS
, DSZ_H_PERSONNES_PROT   PP
, DSZ_H_ADHERENTS   DSZ_H_ADHERENTS
, TMP_DSZ_D_STU_ADS_PER_GAR_PRM   PRM_ADS_PER_GAR
where	(1=1)       
And (ADS_PER.ADP_COD_ADH=ADS.ADS_COD)
AND (ADS_PER.ADP_COD_PER=to_char(PP.PER_NUM))
AND (To_CHAR(ADS_PER.ADP_COD_PER)=TO_CHAR(DSZ_H_ADHERENTS.ADH_PER_NUM) )
AND (ADS_PER.ADP_COD_ADH=PRM_ADS_PER_GAR.CPG_COD_ADH AND ADS_PER.ADP_COD_PER=PRM_ADS_PER_GAR.CPG_NUM_PER AND ADS_PER.ADP_NUM_CNT=PRM_ADS_PER_GAR.CPG_NUM_CNT)
Depuis aujourd’hui, la même requête ne passe plus et me donne le plan d’exécution 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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
Plan hash value: 3485142019
 
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               |  1375 |   119K|       | 11579   (1)| 00:02:19 |
|   1 |  NESTED LOOPS                |                               |       |       |       |            |          |
|   2 |   NESTED LOOPS               |                               |  1375 |   119K|       | 11579   (1)| 00:02:19 |
|*  3 |    HASH JOIN                 |                               |  1375 |   106K|       | 10859   (1)| 00:02:11 |
|*  4 |     HASH JOIN                |                               |  1467 |   100K|       |  7009   (1)| 00:01:25 |
|*  5 |      HASH JOIN               |                               |  1542 | 86352 |    14M|  5708   (1)| 00:01:09 |
|   6 |       TABLE ACCESS FULL      | DSZ_H_ADH_PER                 |   382K|    10M|       |  3034   (1)| 00:00:37 |
|   7 |       TABLE ACCESS FULL      | TMP_DSZ_D_STU_ADS_PER_GAR_PRM |   467K|    12M|       |  1063   (1)| 00:00:13 |
|   8 |      TABLE ACCESS FULL       | DSZ_H_ADHESIONS               |   202K|  2771K|       |  1299   (1)| 00:00:16 |
|   9 |     TABLE ACCESS FULL        | DSZ_H_PERSONNES_PROT          |   299K|  2628K|       |  3848   (1)| 00:00:47 |
|* 10 |    INDEX UNIQUE SCAN         | DSZ_H_ADHERENTS_PK            |     1 |       |       |     0   (0)| 00:00:01 |
|  11 |   TABLE ACCESS BY INDEX ROWID| DSZ_H_ADHERENTS               |     1 |    10 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   6 - SEL$1 / ADS_PER@SEL$1
   7 - SEL$1 / PRM_ADS_PER_GAR@SEL$1
   8 - SEL$1 / ADS@SEL$1
   9 - SEL$1 / PP@SEL$1
  10 - SEL$1 / DSZ_H_ADHERENTS@SEL$1
  11 - SEL$1 / DSZ_H_ADHERENTS@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("ADS_PER"."ADP_COD_PER"=TO_CHAR("PP"."PER_NUM"))
   4 - access("ADS_PER"."ADP_COD_ADH"="ADS"."ADS_COD")
   5 - access("ADS_PER"."ADP_COD_ADH"="PRM_ADS_PER_GAR"."CPG_COD_ADH" AND 
              "ADS_PER"."ADP_COD_PER"="PRM_ADS_PER_GAR"."CPG_NUM_PER" AND 
              "ADS_PER"."ADP_NUM_CNT"="PRM_ADS_PER_GAR"."CPG_NUM_CNT")
  10 - access("ADS_PER"."ADP_COD_PER"="DSZ_H_ADHERENTS"."ADH_PER_NUM")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=0) "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS"."ADS_IDI"[NUMBER,22], "PP"."PER_IDI"[NUMBER,22], 
       "DSZ_H_ADHERENTS"."ADH_CIV"[VARCHAR2,4]
   2 - (#keys=0) "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS"."ADS_IDI"[NUMBER,22], "PP"."PER_IDI"[NUMBER,22], 
       "DSZ_H_ADHERENTS".ROWID[ROWID,10]
   3 - (#keys=1) "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], "ADS_PER"."ADP_FLG_OD"[NUMBER,22], 
       "ADS"."ADS_IDI"[NUMBER,22], "PP"."PER_IDI"[NUMBER,22]
   4 - (#keys=1) "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS"."ADS_IDI"[NUMBER,22]
   5 - (#keys=3) "ADS_PER"."ADP_COD_ADH"[VARCHAR2,30], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS_PER"."ADP_FLG_OD"[NUMBER,22]
   6 - "ADS_PER"."ADP_COD_ADH"[VARCHAR2,30], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS_PER"."ADP_NUM_CNT"[VARCHAR2,10]
   7 - "PRM_ADS_PER_GAR"."CPG_NUM_CNT"[VARCHAR2,10], "PRM_ADS_PER_GAR"."CPG_NUM_PER"[VARCHAR2,10], 
       "PRM_ADS_PER_GAR"."CPG_COD_ADH"[VARCHAR2,30]
   8 - "ADS"."ADS_IDI"[NUMBER,22], "ADS"."ADS_COD"[VARCHAR2,30]
   9 - "PP"."PER_IDI"[NUMBER,22], "PP"."PER_NUM"[NUMBER,22]
  10 - "DSZ_H_ADHERENTS".ROWID[ROWID,10]
  11 - "DSZ_H_ADHERENTS"."ADH_CIV"[VARCHAR2,4]
Pour la faire fonctionner, je suis obligé de remplacer les case When par des decode
et je retombe sur le même plan qu'il y a 2 jours (qui s’exécute lui très bien), à savoir :
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
Plan hash value: 4071291586
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                               |  2293K|   194M|       | 13612   (1)| 00:02:44 |
|*  1 |  HASH JOIN            |                               |  2293K|   194M|       | 13612   (1)| 00:02:44 |
|*  2 |   HASH JOIN           |                               |  1375 |   106K|       | 10859   (1)| 00:02:11 |
|*  3 |    HASH JOIN          |                               |  1467 |   100K|       |  7009   (1)| 00:01:25 |
|*  4 |     HASH JOIN         |                               |  1542 | 86352 |    14M|  5708   (1)| 00:01:09 |
|   5 |      TABLE ACCESS FULL| DSZ_H_ADH_PER                 |   382K|    10M|       |  3034   (1)| 00:00:37 |
|   6 |      TABLE ACCESS FULL| TMP_DSZ_D_STU_ADS_PER_GAR_PRM |   467K|    12M|       |  1063   (1)| 00:00:13 |
|   7 |     TABLE ACCESS FULL | DSZ_H_ADHESIONS               |   202K|  2771K|       |  1299   (1)| 00:00:16 |
|   8 |    TABLE ACCESS FULL  | DSZ_H_PERSONNES_PROT          |   299K|  2628K|       |  3848   (1)| 00:00:47 |
|   9 |   TABLE ACCESS FULL   | DSZ_H_ADHERENTS               |   166K|  1628K|       |  2742   (1)| 00:00:33 |
---------------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$1
   5 - SEL$1 / ADS_PER@SEL$1
   6 - SEL$1 / PRM_ADS_PER_GAR@SEL$1
   7 - SEL$1 / ADS@SEL$1
   8 - SEL$1 / PP@SEL$1
   9 - SEL$1 / DSZ_H_ADHERENTS@SEL$1
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access(INTERNAL_FUNCTION("ADS_PER"."ADP_COD_PER")=INTERNAL_FUNCTION("DSZ_H_ADHERENTS"."ADH_PER_N
              UM"))
   2 - access("ADS_PER"."ADP_COD_PER"=TO_CHAR("PP"."PER_NUM"))
   3 - access("ADS_PER"."ADP_COD_ADH"="ADS"."ADS_COD")
   4 - access("ADS_PER"."ADP_COD_ADH"="PRM_ADS_PER_GAR"."CPG_COD_ADH" AND 
              "ADS_PER"."ADP_COD_PER"="PRM_ADS_PER_GAR"."CPG_NUM_PER" AND 
              "ADS_PER"."ADP_NUM_CNT"="PRM_ADS_PER_GAR"."CPG_NUM_CNT")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - (#keys=1) "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS"."ADS_IDI"[NUMBER,22], 
       "PP"."PER_IDI"[NUMBER,22], "DSZ_H_ADHERENTS"."ADH_CIV"[VARCHAR2,4]
   2 - (#keys=1) "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], "ADS_PER"."ADP_FLG_OD"[NUMBER,22], 
       "ADS"."ADS_IDI"[NUMBER,22], "PP"."PER_IDI"[NUMBER,22]
   3 - (#keys=1) "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS"."ADS_IDI"[NUMBER,22]
   4 - (#keys=3) "ADS_PER"."ADP_COD_ADH"[VARCHAR2,30], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS_PER"."ADP_FLG_OD"[NUMBER,22]
   5 - "ADS_PER"."ADP_COD_ADH"[VARCHAR2,30], "ADS_PER"."ADP_COD_PER"[VARCHAR2,20], 
       "ADS_PER"."ADP_FLG_OD"[NUMBER,22], "ADS_PER"."ADP_NUM_CNT"[VARCHAR2,10]
   6 - "PRM_ADS_PER_GAR"."CPG_NUM_CNT"[VARCHAR2,10], "PRM_ADS_PER_GAR"."CPG_NUM_PER"[VARCHAR2,10], 
       "PRM_ADS_PER_GAR"."CPG_COD_ADH"[VARCHAR2,30]
   7 - "ADS"."ADS_IDI"[NUMBER,22], "ADS"."ADS_COD"[VARCHAR2,30]
   8 - "PP"."PER_IDI"[NUMBER,22], "PP"."PER_NUM"[NUMBER,22]
   9 - "DSZ_H_ADHERENTS"."ADH_PER_NUM"[VARCHAR2,20], "DSZ_H_ADHERENTS"."ADH_CIV"[VARCHAR2,4]
D’où ma question, comme se fait il que l'optimiseur modifie à ce point le plan, alors que la base n'a pas du tout changer (même structure, même donnée, même stat)? Quel paramètre a pu être modifié pour obtenir une tel modification du comportement d'oracle??

merci pour vos lumieres