Optimisation requête multi-exécution

Version imprimable

05/12/2013, 23h52
zidane2012

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 :)

Citation:

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 */ ...
06/12/2013, 09h14
jkofr

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
06/12/2013, 11h04
mnitu

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.
09/12/2013, 01h24
zidane2012

Citation:

Envoyé par mnitu

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

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 !!!
09/12/2013, 10h32
mnitu

Citation:

Envoyé par zidane2012

...A ma connaissance cela fonctionne quelque soit le moteur SQL ou PL/SQL !!!

Adaptive Cursor Sharing

Citation:

Envoyé par zidane2012

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:

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:

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.

11/12/2013, 17h03
zidane2012

Citation:

Envoyé par mnitu

Adaptive Cursor Sharing

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
11/12/2013, 17h27
mnitu

Avez-vous déjà lu l'article que j'ai vous ai indiqué?

Citation:

Envoyé par zidane2012

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.