[11gR2] Case When, decode et impact sur le plan d'execution

Case When, decode et impact sur le plan d'execution

Bonjour,

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

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

Code:

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:

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:

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

Quand vous exécutez votre requête originale vous réutilisez très probablement un « mauvais » plan d’exécution.
Quand vous modifiez les Case par Decode vous modifiez le texte de la requête et par voie de conséquence vous forcez un hard parsing ce qui implique l’élaboration d’un nouveau plan d’exécution.
Pour l’instant je voudrais savoir pourquoi dans le deuxième plan on voit des conversions implicites d’Oracle (présence de Internal function). Autrement dit quel est le type des colonnes implique dans l’expression
Code:

1 2 And (To_CHAR(ADS_PER.ADP_COD_PER)=TO_CHAR(DSZ_H_ADHERENTS.ADH_PER_NUM) )

les deux colonnes sont en varchar2(20).
J'ai rajouté le to_char pour empêcher l’optimiseur de faire un

Code:

|* 10 | INDEX UNIQUE SCAN | DSZ_H_ADHERENTS_PK |

Demander à mon DBA adoré de vider le pool des plans d’exécutions permettrait de contourner ce problème (nous sommes encore en recette)?

Supprimez les To_Char inutiles. Si un jour vous décidez d'influencer sur le plan d'exécution il y a des autres méthodes à employer.