Discussion :

[devra]

Bonjour,
J'ai une requete qui utlise des variables bindé, alors que la requete est parsé tant de fois que de variable. ci-dessous la requete :

Code :

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

1
2
3
SELECT  reference,date
 FROM commande 
WHERE reference = :"VAR_B_0"

Le TKPROF de cette requete est le suivant :

Code :

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

1
2
3
4
5
6
7
8
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      788      0.02       0.02          0          0          0           0
Execute   1989      0.06       0.05          0          0          0           0
Fetch     1989      0.08       0.07          0       7956          0        1989
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     4766      0.18       0.15          0       7956          0        1989

Alors je vois que la requete est parsé 788 fois, chose qui est anormale.

Merci d'avance

[Mohamed.Houri]

Pourriez-vous poster la totalité du fichier TKPROF. Ou à la limite la partie qui reprend la requête en question.

[mnitu]

La requête est parsée 788 fois et exécutée 1989 fois. Le TKPROF enregistre les deux types des parse : soft et hard. Si le code à été écrit pour demander le parsing, TKPROF ne peut que l’enregistrer. Voire l'exemple ou la demande de parsing est placée d'une manière erronée à l'intérieur de la boucle.

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
 
declare
  last_name hr.employees.last_name%Type;
  ch        integer;
  r         integer;
Begin
  ch := dbms_sql.open_cursor;
  for i in 1..100 Loop
    DBMS_SQL.PARSE(ch, 'Select last_name from hr.employees WHERE employee_id = :id',
                     DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(ch, ':id', i);
    r := DBMS_SQL.EXECUTE(ch);       
  End Loop;                 
  DBMS_SQL.CLOSE_CURSOR(ch);
Exception
  When Others Then
    DBMS_SQL.CLOSE_CURSOR(ch);
    Raise;
End;
/

et le TKPROF

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Select last_name
from
 hr.employees WHERE employee_id = :id
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      100      0.00       0.00          0          0          0           0
Execute    100      0.00       0.09          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      200      0.00       0.09          0          0          0           0

[ojo77]

En complément du message de Mnitu, si vous êtes en version 11R2, la requête est peut être sensible à la valeur de la variable ce qui peut expliquer plusieurs "hard parse"

[devra]

Pourriez-vous poster la totalité du fichier TKPROF. Ou à la limite la partie qui reprend la requête en question.

Merci pour ves feedback. je suis en version 11g. ci-joint le TKPROF complét.

[ora_home]

Bonjour,
Pout le cas cité, Oracle faite un HARD PARSE pour chaque valeur de la variable "VAR_B_0".
Alors si tu veux parser la requete une seule fois, pense à changer le paramétre : CURSOR_SHARING

Code :

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

alter system set cursor_sharing= FORCE;

[mnitu]

La présence des ":SYS_B_"X indique que vous est en CURSOR_SHARING = FORCE.
Le message "Misses in library cache during parse: 0" indique des soft_parses.

[devra]

La présence des ":SYS_B_"X indique que vous est en CURSOR_SHARING = FORCE.

Non mnitu, après avoir vérifié j'ai trouvé CURSOR_SHARING = SIMILAR

[Mohamed.Houri]

Non mnitu, après avoir vérifié j'ai trouvé CURSOR_SHARING = SIMILAR

Hmmm.!!!!

cursor_sharing = similar est très dangereux.

Et je suppose que vous avez aussi des histogrammes sur certaines colonnes apparaisant dans vos requêtes (par exemple sur la colonne CDE_REFERENCE).

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
 
SELECT CDE_REF_PARENT  
FROM
 DONNEE_COMMANDE WHERE CDE_REFERENCE=:"SYS_B_0"
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      363      0.01       0.01          0          0          0           0
Execute    921      0.02       0.02          0          0          0           0
Fetch      921      0.05       0.82        316       3684          0         921
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     2205      0.09       0.86        316       3684          0         921
 
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: 58

Dans ce cas, Oracle non seulement remplace les valeurs mises en dur par des variables de liaisons (bind variable) mais décide aussi d'examiner cette valeur pour une re-optimisation à chaque remplacement de variable. Ce qui veut dire que grâce à la valeur du cursor_sharing = SIMILAR, votre requête est retrouvée dans la mémoire (library cache) ce qui explique le 0 de Misses in library cache during parse: 0, mais qu'à cause de cette valeur(SIMILAR) justement, chaque re-execution de votre requête est sujette (voir les conditions comme l'existence d'un histogramme sur la colonne ou un prédicat en range scan) à une ré-optimisation (un nouvel explain plan) ce qui expliquerait peut-être le nombre de parse assez elevé (40%) par rapport au nombre d'exécutions.

[devra]

Merci beaucoup Mohamed pour cette explication, et pour le temps que vous avez consacré pour l'analyse. Alors que ce que vous me proposé alors comme solution ?

[mnitu]

Non mnitu, après avoir vérifié j'ai trouvé CURSOR_SHARING = SIMILAR

Oui en effet, ce que j'aurais voulu dire ce qu'il n'est pas EXACT. Donc il est FORCE ou bien SIMILAIRE. Et dans ce cas chaque changement de la valeur de la variable redemande le parsing.
En fait vous avez plusiers requêtes de type

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT  reference,date
 FROM commande 
WHERE reference = 1
/
SELECT  reference,date
 FROM commande 
WHERE reference = 1
SELECT  reference,date
 FROM commande 
WHERE reference = 2

Qui sont toutes transformées en

Code :

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

1
2
3
4
 
SELECT  reference,date
 FROM commande 
WHERE reference = :"VAR_B_0"

mais avec un parsing pour chaque changement de la valeur en dur.

La solution est de mettre cursor_sharing à la valeur exact et de modifier l'application pour quelle utilise des variables de liaison (binding variable).

[devra]

Voilà le rapport TKPROF avec CURSOR_SHARING = FORCE.
Que ce que vous proposez Monsieurs !!!

[Mohamed.Houri]

Merci beaucoup Mohamed pour cette explication, et pour le temps que vous avez consacré pour l'analyse. Alors que ce que vous me proposé alors comme solution ?

1. Ne pas utiliser cursor_sharing= SIMILAR.
2. Si vous êtes en environnement OLTP (Online Transactional Process) ne pas générer des histogrammes.

Si votre application n'utilise pas correctement les variables de liaison vous pouvez à la limite utiliser le cursor_sharing= FORCE. J'ai eu à intervenir sur une application qui avait un problème de mémoire (SGA) si bien que parfois la seule solution dont on disposait c'était de stopper la base de données. J'ai résumé ceci dans cet article


Edit : il semblerait que le cursor sharing FORCE n'a pas eu l'effet escompté.

Pourriez-vous exécuter la requête suivante:

Code :

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

1
2
3
4
 
select sql_text
    , executions 
from v$sql where executions < 2;

[devra]

Si votre application n'utilise pas correctement les variables de liaison vous pouvez à la limite utiliser le cursor_sharing= FORCE. J'ai eu à intervenir sur une application qui avait un problème de mémoire (SGA) si bien que parfois la seule solution dont on disposait c'était de stopper la base de données. J'ai résumé ceci dans cet article


Edit : il semblerait que le cursor sharing FORCE n'a pas eu l'effet escompté.

Pourriez-vous exécuter la requête suivante:

Code :

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

1
2
3
4
 
select sql_text
    , executions 
from v$sql where executions < 2;

Merci beaucoup pour la réponse et pour l'article aussi, c'est très intéressant.

ci-joint le résultats de la requête que vous avez demandé.
Merci beaucoup

[Mohamed.Houri]

Merci beaucoup pour la réponse et pour l'article aussi, c'est très intéressant.

ci-joint le résultats de la requête que vous avez demandé.
Merci beaucoup

Désolé, c'était plutôt un select count qu'il aurait fallu. Je vais essayer d'écrire ce que j'ai compris et ce que j'essaie de comprendre. Malgré le passage en cursor_sharing = FORCE, les choses ne se sont pas vraiment améliorées. Par exemple

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
 
select TERM_EAN_FTT  
from
 donnee_commande dc  inner join param_terminal pt on pt.term_id = dc.term_id 
  where CDE_REFERENCE = :"SYS_B_0"
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      338      0.01       0.00          0          0          0           0
Execute    663      0.01       0.01          0          0          0           0
Fetch      663      0.03       0.03          8       4641          0         663
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1664      0.07       0.06          8       4641          0         663
 
select TERM_EAN_FTT  
from
 donnee_commande dc  inner join param_terminal pt on pt.term_id = dc.term_id 
  where CDE_REFERENCE = :"SYS_B_0"
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      338      0.01       0.00          0          0          0           0
Execute    663      0.01       0.01          0          0          0           0
Fetch      663      0.03       0.03          8       4641          0         663
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1664      0.07       0.06          8       4641          0         663
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1

Avec en plus un Misses in library cache during execute: 1

Ce qu'il faut faire maintenant c'est directement consulter la mémoire (v$sql) à la recheche du nombre d'instructions SQL qui ne sont pas re-exécutées (problème de variable de liaison) par rapport à ceux qui le sont. Pour cela vous pouvez faire ceci

Code :

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

1
2
3
 
select count(1) from v$sql where executions < 2;
select count(1) from v$sql;

Une autre remarque aussi, chaque fichier trace contient l'instruction suivante

Code :

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

1
2
 
ALTER SESSION SET NLS_LANGUAGE= 'FRENCH' ....

Ceci peut s'avèrer aussi un ingrédient à la re-optimisation (nouveau plan d'exécution).

Egalement dans votre v$sql le nombre d'appel au package DBMS_STATS est impressionant. Etes-vous en train de calculer les statistiques maintenant.

Enfin, il se peut que l'effet du cursor_sharing=FORCE ne soit pas immediat et qu'il faille attendre un peu; ou faire un flush du sharel pool; ceci n'est pas une bonne suggestion mais au vu de l'état de votre sharel pool !!!!

[devra]

Egalement dans votre v$sql le nombre d'appel au package DBMS_STATS est impressionant. Etes-vous en train de calculer les statistiques maintenant.

Enfin, il se peut que l'effet du cursor_sharing=FORCE ne soit pas immediat et qu'il faille attendre un peu; ou faire un flush du sharel pool; ceci n'est pas une bonne suggestion mais au vu de l'état de votre sharel pool !!!!

Non, je calcule par les statisticques.
pour le résultat des deux requetes les voilà :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SQL> SELECT count(1) FROM v$sql WHERE executions < 2;
 
  COUNT(1)                                                                      
----------                                                                      
      1684                                                                      
 
SQL> SELECT count(1) FROM v$sql;
 
  COUNT(1)                                                                      
----------                                                                      
      3451

[mnitu]

Voilà le rapport TKPROF avec CURSOR_SHARING = FORCE.
Que ce que vous proposez Monsieurs !!!

La solution est de mettre cursor_sharing à la valeur exact et de modifier l'application pour qu'elle utilise des variables de liaison (binding variable).