Lenteur sur une requête (non identifiée)

**tck-lt** · 13/02/2013, 16h35

Bonjour,

j'ai des agents qui se plaignent de lenteurs sur une application. Dès qu'ils font une recherche sur un dossier ça met des plombes.
Au niveau des ressources de mon serveur de BDD (AIX 5.3), RAS, la CPU et la mémoire ne sont pas saturées (loin de là). Je n'ai donc pas de session qui m'indiquent nettement un souci. Je ne connais pas non plus l'application en revanche je peux tracer une session d'un agent qui la maitrise mais je ne connais pas la manière de faire.

J'en viens donc à mes questions :
- Comment réussir à tracer correctement la session (explain plan ?)
- Cette méthode d'enquête est-elle optimale ?
- Ne faisant jamais de tuning, je ne connais pas non plus les commandes pour le mettre en place.

pga_aggregate_target=31457280
shared_pool_size=115343360
sort_area_size=524288

NB : c'est une BDD qui existe depuis de longues années.

Merci.

**mnitu** · 13/02/2013, 17h18

Vous devez activer la trace SQL étendue. Pour activer cette trace plusieurs solutions sont envisageables :

1° L’application permet de l’activer. La solution la plus simple mais rarement disponible
2° L’application est de type client-serveur avec une connexion permanente. Via la vue v$session vous arrivez à identifier la session de l’agent et activer la trace via la procédure dbms_system.set_sql_trace_in_session. Sinon il est possible d’activer la trace via un trigger after logon.
3° Pour les applications N-tiers ce sont les même techniques sauf que la même tâche fonctionnelle peut se retrouver éparpiller dans plusieurs fichiers. Si l’application utilise le package dbms_application_info vous aurez un moyen de rassembler toutes les pièces à la fin.

Une autre façon de faire est d’utiliser l’utilitaire statspack, .

**ora_home** · 14/02/2013, 12h27

Envoyé par tck-lt

- Comment réussir à tracer correctement la session (explain plan ?)

Code :

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

SQL> alter session set sql_trace = true ;

tu peux ajouter la commande ci-dessous pour identifier ton fichier qui aura comme nom MonFichierTrace :

Code :

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

SQL> alter session set tracefile_identifier = MonFichierTrace ;

Puis tu cherche le SID et SERIALE de ton application au niveau de V$SESSION et tu exécute la commande suivante :

Code :

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

exec sys.dbms_system.set_sql_trace_in_session(SID,SERIAL#,true);

ou bien la commande ci-dessous pour avoir plus d'informations sur les variables BIND et les attents(waits) :

Code :

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

Exec SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, waits=>TRUE, binds=>TRUE );

**tck-lt** · 14/02/2013, 15h10

Bonjour,

merci à tous les 2, j'ai bien réussi à tracer une session ce matin et j'ai lancé tkprof sur mon fichier trace pour avoir quelque chose d'un peu plus lisible mais je ne sais pas comment avancer après. J'ai également l'explain plan de la requête.
tkprof m'indique les requêtes qui sont longues à s'exécuter et j'en ai identifié mais je ne sais pas quelle est la marche à suivre pour poursuivre l'investigation.

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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
********************************************************************************
 
SELECT COUNT(*)
FROM
 ACTPRO A,PROTDO P WHERE P.ID_PROF=A.ID_PROF AND A.ID_ACTR=:B2 AND
  P.ID_TYPEDOSS=:B1 AND PACK_NOMENC.VALIDITE( A.DATEDEBU , A.DATEFIN ) = 'O'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 332149      8.13      13.98          0          0          0           0
Fetch   332149     17.13      30.62         23    2325043          0      332149
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   664299     25.26      44.61         23    2325043          0      332149
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 562     (recursive depth: 1)
********************************************************************************
 
 
 
********************************************************************************
 
Select A.DSOC,E.NOME,E.LIBL,A.DSOC,DECODE(C.INDSPRIN,'O','P',' '),
  DECODE(D.DECE,'O','*',' '),D.NOM ||' '||D.PREN,nvl(lpad(D.JOURNAIS,2,'0'),
  '__') ||'/'|| nvl(lpad(D.MOISNAIS,2,'0'),'__') ||'/'|| nvl(lpad(D.ANNENAIS,
  4,'0'),'____'),H.NOM_PREN,C.APPADOSS,'',D.ID,A.ID
from
 DOSSOC A,INDSOC C,NOMENC E,ACTEUR H,PORTEF G,BASES J,INDIVI D where D.NOM=
  'BOUDOU' and A.NATU='F' AND D.INDI=C.INDI AND C.DSOC=A.DSOC AND E.ID=
  A.ID_TYPEDOSS AND G.ID=A.ID_PORT AND H.ID = G.ID_ACTR AND A.ID_ACTRLOCA =
  J.ID and pack_habi.habilist(328437,A.ID_TYPEDOSS,A.DSOC,'','')='O' order by
  D.NOM,D.PREN,D.ANNENAIS,D.MOISNAIS,D.JOURNAIS,A.DSOC
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.04       0.04          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4     12.07      17.77      13606     858626          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10     12.11      17.82      13606     858626          0          26
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 561
********************************************************************************

Désolé pour mon incompétence mais la partie tuning m'est complètement inconnue (et je n'ai que rarement à mettre les mains dedans).

Merci.

**ora_home** · 14/02/2013, 16h13

Bonjour,
Premièrement, il manque les plan d'exécution des 2 requets, j'aimerai bien de les poster.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT COUNT(*)
FROM
 ACTPRO A,PROTDO P WHERE P.ID_PROF=A.ID_PROF AND A.ID_ACTR=:B2 AND
  P.ID_TYPEDOSS=:B1 AND PACK_NOMENC.VALIDITE( A.DATEDEBU , A.DATEFIN ) = 'O'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 332149      8.13      13.98          0          0          0           0
Fetch   332149     17.13      30.62         23    2325043          0      332149
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   664299     25.26      44.61         23    2325043          0      3321

D'après les résultats de TKPROF:
1) on peux constater qu'il y a une grande différence entre le temp CPU(25.26 s) consacrer au traitement de la requete et le temp ELAPSED(44.61 s) consacrer au traitement et envoi des résultats , ça peut être dû aux evénement d'attents (WAITs), Oracle attend la libération d'une ou plusieurs ressource, c'est pour celà on aura besoin d'un rapport détailler avec les evénement d'attends et les plans d'exécution. Normalement on doit pas y avoir une telle différence.

2) On remarque un nombre très important de (query) = 2325043, c'est dû aux lectures logique, et spécialements des Rollback Segment. C'est un point qu'on peux analyser apres avoir le repport détailler.

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
 
SELECT A.DSOC,E.NOME,E.LIBL,A.DSOC,DECODE(C.INDSPRIN,'O','P',' '),
  DECODE(D.DECE,'O','*',' '),D.NOM ||' '||D.PREN,nvl(lpad(D.JOURNAIS,2,'0'),
  '__') ||'/'|| nvl(lpad(D.MOISNAIS,2,'0'),'__') ||'/'|| nvl(lpad(D.ANNENAIS,
  4,'0'),'____'),H.NOM_PREN,C.APPADOSS,'',D.ID,A.ID
FROM
 DOSSOC A,INDSOC C,NOMENC E,ACTEUR H,PORTEF G,BASES J,INDIVI D WHERE D.NOM=
  'BOUDOU' AND A.NATU='F' AND D.INDI=C.INDI AND C.DSOC=A.DSOC AND E.ID=
  A.ID_TYPEDOSS AND G.ID=A.ID_PORT AND H.ID = G.ID_ACTR AND A.ID_ACTRLOCA =
  J.ID AND pack_habi.habilist(328437,A.ID_TYPEDOSS,A.DSOC,'','')='O' ORDER BY
  D.NOM,D.PREN,D.ANNENAIS,D.MOISNAIS,D.JOURNAIS,A.DSOC
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.04       0.04          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4     12.07      17.77      13606     858626          0          26
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10     12.11      17.82      13606     858626          0          26

1) Même remarque du point N°1 ci-dessu (CPU et ELAPSED).
2) Même remarque du point N°2 ci-dessu (QUERY)
3) On remarque un nombre très élever de lecture physique : 13606, et ce pour retourner juste : 26 lignes, chose qui peut être d^aux absences ou bien à la mauvaise utilisation des indexes. donc c'est un point a améliorer !!!
4) On remarque la requete a été parsé 4 fois, chose qui peut être dû a l'utilisation des valeurs en duur au niveau de la requete au lieu des BIND variable(variable de liaison).

Donc vaut mieux de poster les plans d'exécution de ces requetes avec les evénement d'attends.

A+

**mnitu** · 14/02/2013, 16h18

Avez-vous la possibilité de modifier le code de l’application ?

La première requête est exécutée 332149 fois et elle ne fait que compter les enregistrements qui correspondent à certains critères. Il est forte probable qu’on peut s’en passer de cette requête mais il faut voir la logique de la transaction.

La deuxième requête est parsée 4 fois pour être exécutée que deux fois. Bref ce n’est pas ça qui plombe les performances mais ce n’est pas un bon signe non plus. Le problème avec la deuxième requête est qu’elle visite plus de 800000 buffers pour garder que 26 enregistrements : c’est terriblement inefficace.

Dans les deux requêtes la présence des appels des procédures PL/SQL dans la clause where c’est de mauvais augure. Pareil le la valeur du paramètre optimizer goal : CHOOSE.

**tck-lt** · 21/02/2013, 10h02

Merci beaucoup pour vos retours.

Je viens d'avoir un retour de l'éditeur de l'application. Leur logiciel n'est pas optimisé pour travailler avec les stats Oracle et les rebuild d'index en Oracle 9

Il faut donc les désactiver. Ça c'est du boulot

NB : vos réponses ne me sont pas inutiles, vous me permettez d'en apprendre plus sur le tuning de base, merci beaucoup.

Lenteur sur une requête (non identifiée) [9iR2]

Administration Oracle

Discussions similaires

Partager

Partager