IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Problème de performance avec une variable


Sujet :

PL/SQL Oracle

  1. #1
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Novembre 2011
    Messages
    101
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : Novembre 2011
    Messages : 101
    Points : 134
    Points
    134
    Par défaut Problème de performance avec une variable
    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.

  2. #2
    Membre expérimenté
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    Septembre 2007
    Messages
    814
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Septembre 2007
    Messages : 814
    Points : 1 717
    Points
    1 717
    Billets dans le blog
    6
    Par défaut
    ç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;
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  3. #3
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Novembre 2011
    Messages
    101
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : Novembre 2011
    Messages : 101
    Points : 134
    Points
    134
    Par défaut
    Ha, super, merci.

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


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

  4. #4
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Novembre 2011
    Messages
    101
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : Novembre 2011
    Messages : 101
    Points : 134
    Points
    134
    Par défaut
    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.

  5. #5
    Membre expérimenté
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    Septembre 2007
    Messages
    814
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Septembre 2007
    Messages : 814
    Points : 1 717
    Points
    1 717
    Billets dans le blog
    6
    Par défaut
    quel est letype CUSTCODE
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  6. #6
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 080
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 080
    Points : 30 778
    Points
    30 778
    Par défaut
    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.%'.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  7. #7
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Novembre 2011
    Messages
    101
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : Novembre 2011
    Messages : 101
    Points : 134
    Points
    134
    Par défaut
    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.

  8. #8
    Membre expérimenté
    Avatar de islamov2000
    Homme Profil pro
    Ingénieur d'études & developpement en informatique
    Inscrit en
    Septembre 2007
    Messages
    814
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Algérie

    Informations professionnelles :
    Activité : Ingénieur d'études & developpement en informatique
    Secteur : Industrie

    Informations forums :
    Inscription : Septembre 2007
    Messages : 814
    Points : 1 717
    Points
    1 717
    Billets dans le blog
    6
    Par défaut
    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/
    d'avoir Pensé à voter positivement pour ceux qui vous ont aidés et surtout à mettre si le cas.
    ça encourage.

  9. #9
    Membre habitué
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Novembre 2011
    Messages
    101
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique
    Secteur : Distribution

    Informations forums :
    Inscription : Novembre 2011
    Messages : 101
    Points : 134
    Points
    134
    Par défaut
    Oui oui, mais cet exemple était là juste pour la forme. ^^

  10. #10
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Problème de performance avec une regexp
    Par NicoV dans le forum Collection et Stream
    Réponses: 10
    Dernier message: 21/02/2008, 19h46
  2. Réponses: 3
    Dernier message: 06/05/2007, 21h11
  3. problème d'envoi d'une variable avec get
    Par ANISSS dans le forum Langage
    Réponses: 7
    Dernier message: 23/02/2007, 13h49
  4. [Javascript] problème avec une variable
    Par creatik dans le forum Général JavaScript
    Réponses: 1
    Dernier message: 03/09/2006, 19h38
  5. Réponses: 2
    Dernier message: 14/05/2004, 15h32

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo