[9i] tuning : comportement bizarre

**Vld44** · 07/05/2010, 12h56

Salut

Je viens à vous pour vous faire part d'un comportement étrange de l'optimizer d'oracle, je souhaite comprendre pourquoi ses décisions changent pour 2 requêtes (explain plan) alors que visiblement il n'y a aucun changement logique entre les 2.

Voici les requêtes, je vous les mets en entier car je ne sais pas trop si les simplifier serait une bonne chose pour le fond du problème.
La seule différence entre les 2 est mise en caractères gras :

REQUETE 1:

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
            SELECT
                ampaclav clav,
                CASE 
                    WHEN ampaesta||AMPAMARC||AMPATIPO||SUBSTR(AMPACTAC,1,4) = '0138889' THEN ' '
                    ELSE SUBSTR(TO_CHAR(ampactac),1,4) || '/' || SUBSTR(TO_CHAR(ampactac),5,4) || SUBSTR(TO_CHAR(ampactac),10)
                END NumCuenta,  
                a.RELACLA2 NumTarjeta, 
                SUBSTR(a.RELACLA2,3,4) || ' ' || SUBSTR(a.RELACLA2,7,4) || ' XXXX ' || SUBSTR(a.RELACLA2,15,4) Tarjeta,
                DECODE(ampaesta||AMPAMARC||AMPATIPO||SUBSTR(AMPACTAC,1,4),'0138889','CORPORATE',SUBSTR(pdudata,41,LENGTH(pdudata)-42)) Tipo,
                CASE 
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459992' THEN 'A'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459993' THEN 'A'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459994' THEN 'D'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '603977' THEN 'C'
                END Modo
            FROM golf.PDU,
                 golf.rela a, 
                 golf.ampa, 
                 golf.pers
            WHERE 
                PDUTBLA = 'MPBINES' 
            AND SUBSTR(a.RELACLA2,3,6) = pduclav
            AND ampaclav = SUBSTR(a.RELACLA2,3) 
            AND persnume = RELACLA1
            AND 
                (
                a.RELAMAE1 = 'PERS' 
            AND a.RELAMAE2 = 'AMPA' 
            AND    
                   (    a.RELACLA1 = LPAD(ParCliente,18, '0') 
                    AND perssexo < 5) 
                 OR       
                   (
                        RELACLA1 = persnume  
                    AND ampatita = 'B'    
                    AND SUBSTR(ampaclav,1,13) IN 
                       (
                        SELECT SUBSTR(ampaclav,1,13) FROM golf.ampa 
                        WHERE ampaclav IN 
                             (
                              SELECT SUBSTR(RELACLA2,3) FROM golf.rela, golf.pers
                              WHERE RELACLA1 = LPAD(ParCliente,18,'0')
                              AND persnume = RELACLA1 
                              AND RELAMAE1 = 'PERS' 
                              AND RELAMAE2 = 'AMPA' 
                              AND TO_NUMBER(RELACLA1) = persnume 
                              AND perssexo > 5 
                              AND ampatita = 'P'
                             )
                       )
                   )
                )
            ORDER BY ampactac

REQUETE 2:

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
            SELECT
                ampaclav clav,
                CASE 
                    WHEN ampaesta||AMPAMARC||AMPATIPO||SUBSTR(AMPACTAC,1,4) = '0138889' THEN ' '
                    ELSE SUBSTR(TO_CHAR(ampactac),1,4) || '/' || SUBSTR(TO_CHAR(ampactac),5,4) || SUBSTR(TO_CHAR(ampactac),10)
                END NumCuenta,  
                a.RELACLA2 NumTarjeta, 
                SUBSTR(a.RELACLA2,3,4) || ' ' || SUBSTR(a.RELACLA2,7,4) || ' XXXX ' || SUBSTR(a.RELACLA2,15,4) Tarjeta,
                DECODE(ampaesta||AMPAMARC||AMPATIPO||SUBSTR(AMPACTAC,1,4),'0138889','CORPORATE',SUBSTR(pdudata,41,LENGTH(pdudata)-42)) Tipo,
                CASE 
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459992' THEN 'A'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459993' THEN 'A'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '459994' THEN 'D'
                    WHEN SUBSTR(a.RELACLA2,3,6) = '603977' THEN 'C'
                END Modo
            FROM golf.PDU,
                 golf.rela a, 
                 golf.ampa, 
                 golf.pers
            WHERE 
                PDUTBLA = 'MPBINES' 
            AND SUBSTR(a.RELACLA2,3,6) = pduclav
            AND ampaclav = SUBSTR(a.RELACLA2,3) 
            AND persnume = RELACLA1
            AND a.RELAMAE1 = 'PERS'
            AND a.RELAMAE2 = 'AMPA'
            AND
                (    
                   (    a.RELACLA1 = LPAD(ParCliente,18, '0') 
                    AND perssexo < 5) 
                 OR       
                   (
                        RELACLA1 = persnume  
                    AND ampatita = 'B'    
                    AND SUBSTR(ampaclav,1,13) IN 
                       (
                        SELECT SUBSTR(ampaclav,1,13) FROM golf.ampa 
                        WHERE ampaclav IN 
                             (
                              SELECT SUBSTR(RELACLA2,3) FROM golf.rela, golf.pers
                              WHERE RELACLA1 = LPAD(ParCliente,18,'0')
                              AND persnume = RELACLA1 
                              AND RELAMAE1 = 'PERS' 
                              AND RELAMAE2 = 'AMPA' 
                              AND TO_NUMBER(RELACLA1) = persnume 
                              AND perssexo > 5 
                              AND ampatita = 'P'
                             )
                       )
                   )
                )
            ORDER BY ampactac

Le changement, comme vous pouvez le constater, est très simple. On a un séquent de type

(A and (B AND (C OR D)))
qu'on transforme en
(A and B AND (C OR D))

Cependant, les explains plan sont différent, dans un cas il utilise un FULL FAST INDEX SCAN d'un index non unique, dans l'autre il utilise un RANGE INDEX SCAN d'un index non unique non plus. Ce sont les 2 seules différences entre les explains plan.

Je ne comprends pas, je n'ai fait que sortir d'une parenthèse une expression basique ...

**mnitu** · 07/05/2010, 13h49

Comment fait-vous pour comparer les plans ? Ma question veut en faire dire : est vous certain que la seule chose qui change est seulement le texte de la requête et que tous les autres paramètres de la session sont identiques ?

**Vld44** · 09/05/2010, 18h58

j'utilise l'outil pl/sql developper

j'ouvre une seule instance, une seule connexion donc

2 explain plan windows, avec les 2 requêtes précédemment citées

F5 sur l'une, F5 sur l'autre

j'exporte les résultats obtenus et je les colle sous excel pour pouvoir comparer ligne à ligne, ce qui m'a mené aux observations vues plus haut et seulement celles-là

**pachot** · 09/05/2010, 22h05

Bonjour,
Il y aurait 2 chose interresantes à tester:
1- comparer les filter/access predicates des 2 plans d'exécution
2- forcer à avoir les mêmes plans avec un hint ( index_ffs() ou index() ) et comparer les couts/cardinalités estimées
Cordialement,
Franck.

**Ahmed AANGOUR** · 11/05/2010, 17h46

ne serait-ce pas un pb de "condition precedence" ?

http://download.oracle.com/docs/cd/B...1.htm#i1034834

http://ezinearticles.com/?Oracle-SQL...nce&id=1597846

**mnitu** · 12/05/2010, 09h12

Envoyé par Ahmed AANGOUR

ne serait-ce pas un pb de "condition precedence" ?

http://download.oracle.com/docs/cd/B...1.htm#i1034834

http://ezinearticles.com/?Oracle-SQL...nce&id=1597846

Franchement je ne vois pas le lien avec le problème en question.

**Ahmed AANGOUR** · 12/05/2010, 10h03

la question que je me posais est: est-ce que le fait de rajouter une parenthèse dans la clause where ne modifie pas l'ordre des clauses évaluées par le CBO ce qui impacterait les cardinalités estimées et donc le plan.

Il faudrait comparer les filter/access predicates pour les 2 plans

[9i] tuning : comportement bizarre

PL/SQL Oracle

Discussions similaires

Partager

Partager