Requêtes longues sur client mais rapides sur serveur

**devplanete** · 02/02/2009, 10h38

J'ai certains traitements qui sont assez longs sur mes postes clients.
Pourtant, quand je lance la même requête sur le serveur, elle est 10x plus rapide (30s sur les clients, 3s sur le serveur oracle).

Voici le plan d'exécution de la requête sur le client :

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
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1445 Card=83378 Byte
          s=6169972)
 
   1    0   SORT (ORDER BY) (Cost=1445 Card=83378 Bytes=6169972)
   2    1     HASH JOIN (OUTER) (Cost=428 Card=83378 Bytes=6169972)
   3    2       HASH JOIN (OUTER) (Cost=331 Card=83378 Bytes=5169436)
   4    3         HASH JOIN (OUTER) (Cost=246 Card=83378 Bytes=4252278
          )
 
   5    4           TABLE ACCESS (FULL) OF 'TABLE1' (Cost=1
          75 Card=83378 Bytes=3335120)
 
   6    4           TABLE ACCESS (FULL) OF 'TABLE2' (Cost=4 Card=695
          Bytes=7645)
 
   7    3         TABLE ACCESS (FULL) OF 'TABLE2' (Cost=4 Card=695 By
          tes=7645)
 
   8    2       TABLE ACCESS (FULL) OF 'TABLE3' (Cost=2 Card=41 Byte
          s=492)
 
Statistics
----------------------------------------------------------
          0  recursive calls
         13  db block gets
       1190  consistent gets
       2074  physical reads
          0  redo size
    7244483  bytes sent via SQL*Net to client
     729257  bytes received via SQL*Net from client
      11130  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
      83446  rows processed

Voici le plan d'exécution de la requète sur le serveur:

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
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1445 Card=83378 Byte
          s=6169972)
 
   1    0   SORT (ORDER BY) (Cost=1445 Card=83378 Bytes=6169972)
   2    1     HASH JOIN (OUTER) (Cost=428 Card=83378 Bytes=6169972)
   3    2       HASH JOIN (OUTER) (Cost=331 Card=83378 Bytes=5169436)
   4    3         HASH JOIN (OUTER) (Cost=246 Card=83378 Bytes=4252278
          )
 
   5    4           TABLE ACCESS (FULL) OF 'TABLE1' (Cost=1
          75 Card=83378 Bytes=3335120)
 
   6    4           TABLE ACCESS (FULL) OF 'TABLE2' (Cost=4 Card=695
          Bytes=7645)
 
   7    3         TABLE ACCESS (FULL) OF 'TABLE2' (Cost=4 Card=695 By
          tes=7645)
 
   8    2       TABLE ACCESS (FULL) OF 'TABLE3' (Cost=2 Card=41 Byte
          s=492)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1190  consistent gets
        930  physical reads
          0  redo size
    4757780  bytes sent via SQL*Net to client
      61697  bytes received via SQL*Net from client
       5565  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      83452  rows processed

Le volume de données envoyé par SQL*Net est beaucoup + petit sur le serveur (est-ce que mon réseau peut-être en cause ?).
Enfin sur le serveur le tri est fait en mémoire alors qu'il est fait sur le disque pour le client.

Bref, je fais appelle à vous pour m'aidez à analyser ces rapports.

Merci de votre aide.

**fatsora** · 02/02/2009, 11h40

Bonjour,

C'est quel OS que tu utilises ?

Il y a des parametres a configurer en foncion de l'OS ...

Sinon en Oracle pur

Quel type client Oracle tu utilise pour te connecter : (php,sqlplus ,java, ...)

**13thFloor** · 02/02/2009, 12h15

Un souci avec l'arraysize ?
Essaies, sous SQL+, de modifier le paramètre arraysize : set arraysize 200 avant de lancer ta requête et compare le temps de réponse obtenu avec le temps précédent. Tu peux essayer également de positionner l'arraysize un poil plus grand. La valeur par défaut (15) est souvent bien trop petite.
Lances ta requête avec un event 10046 en level 8 afin de voir ou se situent les attentes (je parierais pour du réseau) :

Code :

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

 alter session set events '10046 trace name context forever, level 8';

Le fichier généré sera dans le répertoire défini par le paramètre user_dump_dest.

**fatsora** · 02/02/2009, 14h17

Envoyé par 13thFloor

Un souci avec l'arraysize ?
Essaies, sous SQL+, de modifier le paramètre arraysize : set arraysize 200 avant de lancer ta requête et compare le temps de réponse obtenu avec le temps précédent. Tu peux essayer également de positionner l'arraysize un poil plus grand. La valeur par défaut (15) est souvent bien trop petite.
Lances ta requête avec un event 10046 en level 8 afin de voir ou se situent les attentes (je parierais pour du réseau) :

Code :

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

 alter session set events '10046 trace name context forever, level 8';

Le fichier généré sera dans le répertoire défini par le paramètre user_dump_dest.

Et si le client n'est pas sqlplus ?

**13thFloor** · 02/02/2009, 15h01

Si c'est du java c'est le paramètre setDefaultRowPrefetch à augmenter.
Sql+ permettra de tester le temps de réponse client et serveur car cet outil est présent sur les 2 (en principe).
Cela permettra d'isoler la piste réseau.
Il faudra sans doute optimiser le paramètre tdu du listener et du tnsnames s'il n'est pas possible d'accroître l'arraysize de l'outil client de connexion à la base.
C'est clair que coté client, les requêtes ne doivent pas être envoyées sous sql+.

**devplanete** · 02/02/2009, 16h27

Serveur : Windows 2003 R2 SP2 et oracle 9.2i
Clients : Windows 2000 Pro avec Client Oracle 9.0.1 i.

J'utilise bien sqlplus pour mes tests (java pas utilisé).

je vais tester set arraysize 200 et ALTER session SET events '10046 trace name context forever, level 8'; je vous tiens au courant.

Merci pour ces pistes

**devplanete** · 02/02/2009, 17h56

Avec ALTER session SET events '10046 trace name context forever, level 8', J'ai plein de message comme ci-dessous dans le fichier dump :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=26231519732
WAIT #0: nam='SQL*Net message from client' ela= 1 p1=1297371904 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1297371904 p2=1 p3=0
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=26231520032
WAIT #0: nam='SQL*Net message from client' ela= 2 p1=1297371904 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1297371904 p2=1 p3=0
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=26231520325
WAIT #0: nam='SQL*Net message from client' ela= 2 p1=1297371904 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 2 p1=1297371904 p2=1 p3=0
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=26231520624
WAIT #0: nam='SQL*Net message from client' ela= 2 p1=1297371904 p2=1 p3=0
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1297371904 p2=1 p3=0
FETCH #1:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=26231520917
WAIT #0: nam='SQL*Net message from client' ela= 2 p1=1297371904 p2=1 p3=0

set arraysize 200 réduit de 25% le temps d'exécution sur les clients.
Par contre comment intégrer cette modif dans le tnsname de mon client ?
Comment voir déjà les valeurs courantes de tdu, mtu... ?
Puis-je modifier seulement un client ou il faut aussi modifier les valeurs du tsnname du serveur pour prendre en compte les modif ?

Merci de vos réponses en tout cas

**pifor** · 02/02/2009, 18h00

L'utilisation d'espace disque ou mémoire par le tri dépend d'abord de la valeur des paramètres suivants:

PGA_AGGREGATE_TARGET au niveau de l'instance
SORT_AREA_SIZE au niveau de l'instance ou de la session.

et de l'activité des sessions concurrentes si PGA_AGGREGATE_TARGET est utilisé.

**13thFloor** · 03/02/2009, 10h51

Envoyé par devplanete

Avec ALTER session SET events '10046 trace name context forever, level 8', J'ai plein de message comme ci-dessous dans le fichier dump

Il faut ensuite utiliser tkprof pour générer un fichier exploitable dans lequel apparaitront les attentes qui se produisent durant la requête.

Envoyé par devplanete

set arraysize 200 réduit de 25% le temps d'exécution sur les clients.
Par contre comment intégrer cette modif dans le tnsname de mon client ?
Comment voir déjà les valeurs courantes de tdu, mtu... ?
Puis-je modifier seulement un client ou il faut aussi modifier les valeurs du tsnname du serveur pour prendre en compte les modif ?

arraysize est un paramètre de sqlplus seulement. Mais une piste est identifiée. Il va falloir optimiser le trafic réseau entre le client et le serveur en envoyant le maximum de données dans une trame tcp.

Voir les notes Metalink 1005123.6 (Tuning SQL*Net for better performance)
44694.1 (SQL*Net Packet Sizes)
274483.1 (The relation between MTU (Maximum Transmission Unit), SDU (Session Data Unit) and TDU (Transmission Data Unit))
67983.1 (Oracle Net Performance Tuning)

En très résumé, tu pourras/devras accroître le SDU et faire correspondre sa valeur dans le fichier tnsnames.ora avec celle du listener.ora.
Il me semble qu'en 10g l'un des 2 paramètres (tdu/sdu) n'est plus pris en compte, mais je ne me rappelle plus lequel.
Le tnsnames du serveur n'est pris en compte que pour les connexions locales qui veulent passer par la couche réseau. Donc seuls les tnsnames.ora des clients seront à modifier (et le listener.ora of course).