Discussion :

[Proxy]

Bonjour,

Je fais face à un problème assez inattendu.

Voici une partie de code.

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
DECLARE
    v_code      VARCHAR2 (24);
    v_count   NUMBER;
BEGIN
    v_code := '4.1214';
 
    SELECT   COUNT ( * )
      INTO   v_count
       FROM   customer cu, contract ca, contract_status cgtf
     WHERE       cu.code LIKE v_code
             AND ca.customer_id = cu.customer_id
             AND ca.co_id = cgtf.co_id
             AND ch_status IN ('a', 's');
 
    DBMS_OUTPUT.put_line (v_count);
END;

Et une copie de celui-ci, la seule différence se situe dans le fait que je n'utilise pas de variable, mais la valeur en brut dans la requète

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
DECLARE
    v_code      VARCHAR2 (24);
    v_count   NUMBER;
BEGIN
    v_code := '4.1214';
 
    SELECT   COUNT ( * )
      INTO   v_count
       FROM   customer cu, contract ca, contract_status cgtf
     WHERE       cu.code LIKE '4.1214'
             AND ca.customer_id = cu.customer_id
             AND ca.co_id = cgtf.co_id
             AND ch_status IN ('a', 's');
 
    DBMS_OUTPUT.put_line (v_count);
END;

Mon soucis est celui-ci. Pour la première requête, le temps d'exécution est de 119 sec, la seconde : 0.015 seconde.

Sachant que cette requête est appelée dans une boucle pouvant aller jusque 30.000 codes, la différence de performance est énorme.

(Notez, j'utilise un LIKE car normalement il y a '%' à la fin, mais je ne l'utilise pas pour l'exemple, le résultat est semblable et n'est donc pas en cause).

Notez aussi que le type VARCHAR2(24) utilisé pour ma variable v_code est le même que celui défini dans la table Customer pour mon cu.code.

Qu'est-ce qui peut causer cela? Quelle serait la solution pour garder autant de performance avec une variable?

Un grand merci d'avance.

Merci à vous.

[islamov2000]

ça mesemble un peu bizare;
il y a une énorme différence.
je te propose le SQL dynamique.DECLARE

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
    v_code      VARCHAR2 (24);
    v_count   NUMBER;
    la_requete varchar2(500);
BEGIN
    v_code := '4.1214';
 
la_requete:='    SELECT   COUNT ( * )'||
       ' FROM   customer cu, contract ca, contract_status cgtf '||
     ' WHERE       cu.code LIKE ''' ||v_code ||''''||
             ' AND ca.customer_id = cu.customer_id'||
             ' AND ca.co_id = cgtf.co_id'||
             ' AND ch_status IN (''a'', ''s'')'||;
 
             execute immediate la_requete into v_count;
 
    DBMS_OUTPUT.put_line (v_count);
END;

[Proxy]

Ha, super, merci.

Oui, c'est étrange cette différence pour une requête semblable... :/


Mais avec l'execute immediate, c'est parfait.

[Proxy]

Encore un petit soucis.

Comme je disais, je dois ajouter un % pour mon LIKE.

Mais j'obtiens ce message d'erreur :

Code :

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

ORA-00911: invalid character

Pour simplifier, j'obtiens également l'erreur avec ce code :

Code :

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

1
2
3
4
5
6
DECLARE
v_test VARCHAR2(500);
BEGIN
v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE 4.%';
EXECUTE IMMEDIATE v_test;
END;

Et pas avec ceci :

Code :

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

1
2
3
4
5
6
DECLARE
v_test VARCHAR2(500);
BEGIN
v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE 4.';
EXECUTE IMMEDIATE v_test;
END;

Merci.

[islamov2000]

quel est letype CUSTCODE

[al1_24]

4. est une valeur numérique. L'interpréteur Oracle peut soit le convertir en chaine pour l'utiliser avec l'opérateur LIKE, soit remplacer l'opérateur LIKE par l'opérateur = puisqu'il n'y a pas de caractère générique à prendre en compte.

4.% n'est pas compréhensible par l'interpréteur. Il faut dans ce cas déclarer une chaine et donc l'encadrer d'apostrophes : '4.%'.

[Proxy]

C'est du VARCHAR2, effectivement...

Code :

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

1
2
3
4
5
6
DECLARE
v_test VARCHAR2(500);
BEGIN
v_test := 'SELECT * FROM CUSTOMER WHERE CUSTCODE LIKE ''4.%''';
EXECUTE IMMEDIATE v_test;
END;

Hé bien, merci encore.

[islamov2000]

C'est ce que je voulais te dire.

Noter que cette procédure a aucune resultat en sortie.

Afin d'avoir un resultat en sorie tu dois ajouter un INTO comme suite

Code :

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

1
2
3
4
5
6
7
8
9
 
DECLARE
v_test VARCHAR2(500);
BEGIN
v_test := 'SELECT COL1,COL2,..,COLN FROM CUSTOMER WHERE CUSTCODE LIKE ''4.%''';
EXECUTE IMMEDIATE v_test into variable1,variable2,...,variableN;
 
 
END;

A condition que la requete retourne une et seule ligne, et le nombre de variable est de celui de champs ou colonnes projectés dans le select.


http://sheikyerbouti.developpez.com/execute_immediate/

[Proxy]

Oui oui, mais cet exemple était là juste pour la forme. ^^

[mnitu]

La requête 1 utilise une variable de liaison pour la clause LIKE pendant que la deuxième requête ne le fait pas. A partir d’Oracle 9 l’optimisation de la première requête implique la mise en route du mécanisme d’introspection de la valeur des variables de liaison (bind variable peeking) pendant l’élaboration du plan d’exécution. Par la suite le plan une fois élaboré reste le même pour toutes les exécutions consécutives et pour n’importe quelle autre valeur de la variable de liaison parce que le texte de la requête ne change plus d’une valeur a une autre.

La deuxième requête utilise des littéraux codés en dur pour passer les valeurs de la clause LIKE. A l’optimisation de la requête la valeur est connue et le plan est élaboré en conséquence. Mais si la valeur change le texte de la requête change et par conséquence un parsing hard de la requête doit être effectué. Ce type de parsing coûte cher et comme il est un facteur inhibiteur de l’accès concurrent devrait être réduit.

Il faut également comprendre qu’une même requête peut être exécutée d’une manière performante avec des plans d’exécutions différentes en fonction des valeurs fournis pour les prédicats de la requête. Ca c’est vrai seulement en présence des valeurs non-uniformément distribuées que l’optimiseur peut prendre en compte en présence des histogrammes.

Pour revenir à votre problématique : vous constatez de temps d’exécution différents en fonction de l’écriture du bloc PL/SQL. Avez-vous analysé et compris qu’elle est l’origine de cette différence ? Avez-vous une explication de ce qui se passe ? Parce que si non c’est comme allez au marabout du coin pour vous faire traiter de douleur de poitrine : il vous donne de l’aille des ses précieuse abeilles pour traiter un infarctus:

je te propose le SQL dynamique