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

Oracle Discussion :

Optimisation requête multi-exécution


Sujet :

Oracle

  1. #1
    Membre régulier
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    140
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 140
    Points : 71
    Points
    71
    Par défaut Optimisation requête multi-exécution
    bonjour
    Je souhaite optimiser une requête qui s'exécute selon le rapooert AWR en 1200 fois et chaque exécution prend 7 sec => temps total de 8400 sec ( 2h 20min environ , c'est trop !!!).

    Selon le rapport AWR , elle apparait au top des SQL BY ELAPSED TIME.
    et elle est surtout bindée (bind variable).

    Ma question est :

    Quelles sont les causes possibles de l'exécution mutipliée ( 1200 fois dans mon cas) d'une même requête (même sql_id) ?
    - Une boucle dans le traitement ?
    - L’utilisation d'un index ?
    - L'utilisation d'une jointure de type Nested Loop ?
    - autre ?

    Le traitement générant cette requête est une boîte noire (progiciel).

    Afin d'optimiser :
    Faut-il dans ce cas focaliser sur le temps d'exécution unitaire ( 7 secondes) pour les optimiser et gagner au final sur le temps total d'exécution.
    OU
    Eviter cette exécution multipe et essayer de la faire en ONE SHOT par exemple ? En proposant à l'éditeur une autre écriture de la requête ?
    merci les optimiseurs

    Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
    8400,00 1200,00 7,00 35.21 89.71 11.38 78f54g5448v23c SQLDEVELOPER SELECT /*+ INDEX(PRO PRODUCTS */ ...

  2. #2
    Membre éclairé Avatar de jkofr
    Homme Profil pro
    Senior Consultant DBA (Trivadis SA)
    Inscrit en
    Octobre 2006
    Messages
    484
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 55
    Localisation : Suisse

    Informations professionnelles :
    Activité : Senior Consultant DBA (Trivadis SA)
    Secteur : Conseil

    Informations forums :
    Inscription : Octobre 2006
    Messages : 484
    Points : 724
    Points
    724
    Par défaut
    La raison des exécution multiple peux vraisemblablement être lié à l'applicatif...

    Souvent des tables de référence pour populer une listbox, ou autre.

    Merci de poster la requete avec le plan d'exécution et éventuellement un jeux de test.

    Une solution pourrait être un hint result cache...

    jko
    OCM 11g, RAC and Performance & Tuning Expert 11g
    RMAN Backup & Recovery, Data Guard and Grid Control

  3. #3
    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
    Optimiser c’est éliminer le travail inutile.
    Si la requête en prenant les 7 secondes fait un travail inutile (par exemple en lisant en index scan au lieu de full scan) elle peut être optimisée en changeant son plan d’exécution.

    Si la requête est exécutée 1200 fois quand 100 fois suffit elle peut être optimisée en éliminant le 1100 exécutions inutiles.
    Exécutée 1200 fois dans AWR signifie 1200 appels à une fonction Oracle d’exécution de la requête (comme dans le SQL dynamique).

    Méfiez-vous des requêtes ayant des variables de binding quand les valeurs des tables interrogées sont répartie d'une manière non-uniforme. Il n’existe pas toujours un seul plan optimal pour toutes les valeurs possible dans ce cas. En Oracle 11 il existe le mécanisme d’adaptive cursor sharing qui essaye de résoudre ce problème mais si la requête est issue du moteur PL/SQL ce mécanisme ne marche pas.

    Il n’est pas toujours possible dans les applications d’aujourd’hui d’éviter l’exécution multiples d’une même requête coté applicatif. Le mécanisme de result caching est là pour améliorer ce genre de situations.

  4. #4
    Membre régulier
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    140
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 140
    Points : 71
    Points
    71
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Si la requête est exécutée 1200 fois quand 100 fois suffit elle peut être optimisée en éliminant le 1100 exécutions inutiles.
    Peux-tu stp donner un exemple concret de type d'optimisation en nombre d'exécutions.
    Citation Envoyé par mnitu Voir le message
    En Oracle 11 il existe le mécanisme d’adaptive cursor sharing qui essaye de résoudre ce problème mais si la requête est issue du moteur PL/SQL ce mécanisme ne marche pas.
    A ma connaissance cela fonctionne quelque soit le moteur SQL ou PL/SQL !!!

  5. #5
    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
    Citation Envoyé par zidane2012 Voir le message
    ...A ma connaissance cela fonctionne quelque soit le moteur SQL ou PL/SQL !!!
    Adaptive Cursor Sharing

  6. #6
    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
    Citation Envoyé par zidane2012 Voir le message
    Peux-tu stp donner un exemple concret de type d'optimisation en nombre d'exécutions.
    ...
    Comme cela a été dit c'est applicatif cela veut dire que l'algorithme doit être revu.
    Voici un exemple banal, en PL/SQL, de ce que les gens codes souvent
    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
     
    Declare
      deptname   dept.dname%Type;
      --
      Function GetDnameForDeptno (
        deptno  In dept.deptno%Type
      ) Return dept.dname%Type Is
        l_dname    dept.dname%Type;            
      Begin
        Select dname
          Into l_dname 
          From dept d
         Where d.deptno = GetDnameForDeptno.deptno;
        --
        Return l_dname;
        --
      End;
    Begin
      For rec In (Select empno, deptno
                    From emp               
                 )
      Loop
         dbms_output.put(rec.empno||'/');
         deptname := GetDnameForDeptno(rec.deptno);
         dbms_output.put_line(deptname);       
      End Loop;              
    End;
    /
    Il arrive souvent que des programmeurs mal formé appliquent cet algorithme dans leur langage préféré au lieu d'utiliser une jointure.
    Avec une trace sql vous pouvez constater que la requête qui cherche le libellé du département est exécutée 14 fois
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
     
    SELECT DNAME
    FROM
     DEPT D WHERE D.DEPTNO = :B1
     
     
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute     14      0.00       0.00          0          0          0           0
    Fetch       14      0.00       0.00          0         28          0          14
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total       29      0.00       0.00          0         28          0          14
    Or comme la table dept contient 4 enregistrement parmi lesquels seulement 3 sont référencé dans la table emp cela signifie qu'on algorithme optimal ne devrait exécuter cette requête que 3 fois.

  7. #7
    Membre régulier
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    140
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 140
    Points : 71
    Points
    71
    Par défaut
    Citation Envoyé par mnitu Voir le message
    Pourriez-vous me confirmer que ce mécanisme est actif seulement si :
    - Les histogrammes sont calculés.
    et
    - Utilisation des bind variables dans la requête.
    et
    - paramètre _optim_peek_user_binds=TRUE

  8. #8
    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
    Avez-vous déjà lu l'article que j'ai vous ai indiqué?
    Citation Envoyé par zidane2012 Voir le message
    Pourriez-vous me confirmer que ce mécanisme est actif seulement si :
    - Les histogrammes sont calculés.
    et
    - Utilisation des bind variables dans la requête.
    et
    - paramètre _optim_peek_user_binds=TRUE
    Pas de histogrammes => l'optimiseur ne peut pas prendre en compte les distributions non-uniformes des valeurs
    Pas de bind variables => pour chaque valeur codées en dur l'optimiseur peut faire une meilleur estimation de la sélectivité
    Il existe divers paramètres "internes" Oracle qui permet de activer/désactiver divers fonctionnalités.

Discussions similaires

  1. Optimisation requête multi jointures
    Par MimiWoOlf dans le forum Requêtes
    Réponses: 4
    Dernier message: 29/05/2015, 20h12
  2. Requêtes multi-base
    Par nicolchr dans le forum PostgreSQL
    Réponses: 9
    Dernier message: 12/11/2014, 12h50
  3. Requête multi-base
    Par Vituret dans le forum Débuter
    Réponses: 1
    Dernier message: 04/04/2005, 17h41
  4. optimisation requête
    Par alex2205 dans le forum Décisions SGBD
    Réponses: 5
    Dernier message: 09/02/2005, 15h15
  5. optimisation requête SQL!!! help!!
    Par anathem62 dans le forum Requêtes
    Réponses: 2
    Dernier message: 24/05/2004, 17h26

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