Discussion :

[aline]

Bonjour,

J'ai actuellement des problèmes de performance et quand je regrde la vue v$session wait, j'ai des valeurs bizarres à propos de ces processus.

Quelqu'un en connait l'explication?

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
 
 
select * from v$session_wait t
where t.EVENT not in('SQL*Net message from client','rdbms ipc message')
 
 
SID	SEQ#	EVENT		P1TEXT	P1		P1RAW		P2TEXT	P2	P2RAW		P3TEXT	P3	P3RAW	W_TIME	SC_WAIT	STATE			
 
 
6	49049	smon timer	sleep time	300	0000012C	failed	0	00		0	00	0	734	WAITING
1	10864	pmon timer	duration	300	0000012C		0	00		0	00	0	709	WAITING
2	38474	latch free	address	2796069608	A6A8A2E8	number	98	00000062	tries	1	00000001	2	3	WAITED KNOWN TIME
267	55487	rdbms ipc reply	from_process	6	00000006	timeout	21474834	0147AE12		0	00	0	1	WAITING
28	344	latch free	address	2865140912	AAC694B0	number	157	0000009D	tries	0	00	28	1	WAITED KNOWN TIME
173	4	latch free	address	352595128	15042CB8	number	156	0000009C	tries	0	00	1	0	WAITED KNOWN TIME

[Jaouad]

Est ce que tu peux déterminer de quel type de latch free il s'agit ?


[/code]

[aline]

Bonjour,
au fait j'oubliai je suis en 9.2.0.6

J'ai des problèmes de latch que je suis en train d'investiguer.
Il s'agit de cache buffers lru chain ,cache buffers chains et library cache.

J'ai du mal à bien identifier le problème.
Mais par contre, une autre information me semble encore plus troublante.

Il s'agit des deux première lignes. Les events sont smo timer et pmon timer et les temps d'attentes exorbitants (734 et709 secondes)

[bouyao]

Les events sont smon timer et pmon timer et les temps d'attentes exorbitants (734 et709 secondes)

Ca peut être normal si tu n'a pa redemarré ta base depuis longtemp.

pour

cache buffers lru chain

ca peut être :

Des E/S execives suite a un full scans.
Le buffer cache est trop petit
Structures des indexes sont inapropriés
Le code SQL n'est pas bien optimisé

pour

Cache buffer chains

ca peut être :
nombreuses activités DML avec un ratio d'E/S elevés dans le code SQL
voir le hot bloc

[aline]

Les events sont smon timer et pmon timer et les temps d'attentes exorbitants (734 et709 secondes)

Ca peut être normal si tu n'a pa redemarré ta base depuis longtemp.

Tu peux m'expliquer?
Est-ce grave?
Et le fait que p2text soit à failed pour smon timer?

pour

cache buffers lru chain

ca peut être :

Des E/S execives suite a un full scans.
Le buffer cache est trop petit
Structures des indexes sont inapropriés
Le code SQL n'est pas bien optimisé

pour

Cache buffer chains

ca peut être :
nombreuses activités DML avec un ratio d'E/S elevés dans le code SQL
voir le hot bloc

Sinon, pour les latchs, j'ai déjà tout inspecté.
A mon avis, cela vient du fait que les disques sont trop lent par rapport à l'activité de la base. Peux être je me trompe, mais j'ai déjà investigué tout les cas que tu donne pour le lru chain et je n'ai rien trouvé d'anormal.

Pour le buffer chain, je regarde

Et merci à tous pour les réponses!

[Jaouad]

Pour le cache buffer chain , cela provient en régle génèral d'une mauvaise optimisation du code ( Hot bloc) et non pas de la lenteur du disque.

Le cache buffer LRU chain :

Est un latch qui est acquis lorsque l'on à besoin qu'un nouveau bloc entre dans le data buffer.Ici Oracle va donc scanner la LRU chaine afin de pouvoir detecter quel est le candidat potentiel a être evacué du buffer.
En régle génèral augmenter la data cache permet d'avoir plus de place libre et donc on sollicite moins la LRU chain pour detecter quelles sont les dirty blocks.Cependant cela n'est qu'un palliatif de courte durée car le probléme se poserat donc mais plus tard. Il est préfèrable de monter en mémoire que les blocs neccessaire à l'opération ( peut être eviter les FULL Table scan) ce qui rejoindrait l'hypothése du Cache buffer chain et du code mal optimisé

[Jaouad]

Library cache :

Ici il s'agit de latch qui protége les reuqêtes SQL qui sont déja la library. Avec le prinicipe : demande de latch , Willing to wait ..
Donc Oracle ,pour une requête non présente dans la library cache ,demande un latch avant d'introduire le latch dans la library.La meilleure maniére de diminuer ce type d'évènement et de se pencher sur la ré utilisabilité du code et donc des Hard parses. Les soft parse ne demande pas de latch.( Bind variable , centralisation du code , partage des curseurs... ). Et enfin on peut également augmenter la shared pool
Attention à ne pas se précipiter sur le paramétre _KGL_LATCH_COUNT sans consulter le support Oracle

[bouyao]

Tu peut fermer les yeux sur les : pmon timer et smon timer (idle)

EDIT :

smon timer
This is the main idle event for SMON. SMON will be waiting on this event most of the time until it times out or is posted by another process.

Wait Time: 5 minutes (300 seconds)

Parameters:

sleeptime
The amount of time that SMON tries to wait on this event in seconds.

failed
The number of times SMON was posted when there some kind of error.

[aline]

Library cache :

Ici il s'agit de latch qui protége les reuqêtes SQL qui sont déja la library. Avec le prinicipe : demande de latch , Willing to wait ..
Donc Oracle ,pour une requête non présente dans la library cache ,demande un latch avant d'introduire le latch dans la library.La meilleure maniére de diminuer ce type d'évènement et de se pencher sur la ré utilisabilité du code et donc des Hard parses. Les soft parse ne demande pas de latch.( Bind variable , centralisation du code , partage des curseurs... ). Et enfin on peut également augmenter la shared pool
Attention à ne pas se précipiter sur le paramétre _KGL_LATCH_COUNT sans consulter le support Oracle

oui, je sait bien cela.
Le problème, c'est que j'ai beaucoup de mémoire disponible donc pas de problème de place et que quasiment toutes les requêtes (en tout cas toutes celle qui sont beaucoup utilisées) sont bindées.
Et pourtant j'arrive encore à avoir beaucoup de latchs.....

Je dis que je pense que cela peux être du à des disques lents car en plus de cela sur mes requêtes la ddifférence entre le temps cpu et le temps elapsed est énorme (j'avais déjà fait un post à ce sujet, personne n'a pu trouvé pourquoi)

[Jaouad]

Et pour le cache buffer chain ?
Peux t'on connaitre ta config matérielle ?
Est ce que tu as encore les réfèrences du post ?

[bouyao]

Ca donne quoi ?

Code :

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

1
2
3
4
 
      select count(*), name latchname from v$session_wait, v$latchname
      where event='latch free' and state='WAITING' and p2=latch#
      group by name order by 1 desc;

[Marc Musette]

on peut faire le test pour voir si effectivement tu n'as pas de sql sans bind variable :

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
 
create table t1 as select sql_text from v$sqlarea;
 
alter table t1 add sql_text_wo_constants varchar2(1000);
 
create or replace function 
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);
 
select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

[aline]

Et pour le cache buffer chain ?
Peux t'on connaitre ta config matérielle ?
Est ce que tu as encore les réfèrences du post ?

ma config materielle:
linux rh3 sur bi proc avec 4 Go de RAM et des disques 10000 tours.
Je n'ai plus les réferences du post. seuls les posts résolus sont sauvegardés!
Comme il datait de mai....

sinon, j'ai quand même un tkprof pour que tu vois le problèmes entre le cpu et l'elapsed comme jete l'avais dit.

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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
 
 
TKPROF: Release 9.2.0.4.0 - Production on Ma Mai 3 18:54:53 2005
 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
 
Trace file: cfmr_ora_29092.trc
Sort options: fchela
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************
 
select   t.designation, t.expiration, t.strike,  u.quantite, u.demande, u.volume
from
 ARTICLES t, PRIX u  where t.categorie = :"SYS_B_0"  and u.article_id = t.article_id
  and u.date_prix = :"SYS_B_1"  and (t.expiration - u.date_prix) <= :"SYS_B_2"  ORDER by t.expiration
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        8      0.12       2.57        629       7091          0          74
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.12       2.57        629       7091          0          74
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     68  NESTED LOOPS  (cr=5012 r=422 w=0 time=1881251 us)
   1530   TABLE ACCESS BY INDEX ROWID ARTICLES (cr=347 r=72 w=0 time=767189 us)
   1530    INDEX RANGE SCAN ARTICLES_I1 (cr=13 r=6 w=0 time=62860 us)(object id 720040)
     68   TABLE ACCESS BY INDEX ROWID PRIX (cr=4665 r=350 w=0 time=1108733 us)
     68    INDEX UNIQUE SCAN PRIX_PK (cr=4597 r=348 w=0 time=1091214 us)(object id 720030)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  db file sequential read                       629        0.04          2.33
  SQL*Net message from client                     8        0.00          0.00
********************************************************************************
 
select  t.designation, t.expiration, t.strike,  u.quantite, u.demande, u.volume
from
 ARTICLES t, PRIX u  where t.categorie = :"SYS_B_0"  and u.article_id = t.article_id
  and u.date_prix = :"SYS_B_1"  and (t.expiration - u.date_prix) <= :"SYS_B_2"  ORDER by t.expiration
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        3      0.10       1.42        528       6526          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.10       1.42        528       6526          0          18
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Rows     Row Source Operation
-------  ---------------------------------------------------
     18  NESTED LOOPS  (cr=6526 r=528 w=0 time=1425486 us)
   2098   TABLE ACCESS BY INDEX ROWID ARTICLES (cr=210 r=99 w=0 time=665056 us)
   2098    INDEX RANGE SCAN ARTICLES_I1 (cr=14 r=11 w=0 time=29965 us)(object id 720040)
     18   TABLE ACCESS BY INDEX ROWID PRIX (cr=6316 r=429 w=0 time=754258 us)
     18    INDEX UNIQUE SCAN PRIX_PK (cr=6298 r=426 w=0 time=729583 us)(object id 720030)
 
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  db file sequential read                       528        0.03          1.35
  SQL*Net message from client                     3        0.00          0.00
********************************************************************************
 
alter session set events '10046 trace name context forever, level 8'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
 
alter session set WORKAREA_SIZE_POLICY=manual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
 
alter session set  sort_area_size=33554432
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
 
alter session set  hash_area_size=67108864
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        0.00          0.00
********************************************************************************
 
alter session set events '10046 trace name context off'
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 25
 
 
 
********************************************************************************
 
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        7      0.00       0.00          0          0          0           0
Execute      8      0.00       0.00          0          0          0           0
Fetch       11      0.22       3.99       1157      13617          0          92
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       26      0.22       4.00       1157      13617          0          92
 
Misses in library cache during parse: 0
 
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                      15        0.00          0.00
  SQL*Net message from client                    15        0.00          0.01
  db file sequential read                      1157        0.04          3.68
 
 
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
 
    8  user  SQL statements in session.
    0  internal SQL statements in session.
    8  SQL statements in session.
********************************************************************************
Trace file: cfmr_ora_29092.trc
Trace file compatibility: 9.00.01
Sort options: fchela
       1  session in tracefile.
       8  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       8  SQL statements in trace file.
       7  unique SQL statements in trace file.
    1285  lines in trace file.

Et aussi voici la première page de statspack qui montre bien mes problèmes de latch.

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
51
52
53
54
55
56
57
58
59
60
61
62
 
 
STATSPACK report for
 
DB Name         DB Id    Instance     Inst Num Release     Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
PROD          4186492765 PROD                1 9.2.0.6.0   NO      oracle-prod
 
 
              Snap Id     Snap Time      Sessions Curs/Sess Comment
            --------- ------------------ -------- --------- -------------------
Begin Snap:      9312 01-Sep-05 11:00:16      253       9.9
  End Snap:      9313 01-Sep-05 11:15:22      244      10.2
   Elapsed:               15.10 (mins)
 
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
               Buffer Cache:     2,172M      Std Block Size:          8K
           Shared Pool Size:       176M          Log Buffer:        512K
 
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             73,933.75              3,539.07
              Logical reads:             11,037.28                528.33
              Block changes:                487.32                 23.33
             Physical reads:                513.27                 24.57
            Physical writes:                 26.61                  1.27
                 User calls:                354.77                 16.98
                     Parses:                 29.90                  1.43
                Hard parses:                  0.24                  0.01
                      Sorts:                129.53                  6.20
                     Logons:                  0.24                  0.01
                   Executes:                315.11                 15.08
               Transactions:                 20.89
 
  % Blocks changed per Read:    4.42    Recursive Call %:     76.98
 Rollback per transaction %:    0.62       Rows per Sort:     90.76
 
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.98       Redo NoWait %:     99.99
            Buffer  Hit   %:   95.43    In-memory Sort %:    100.00
            Library Hit   %:   99.54        Soft Parse %:     99.21
         Execute to Parse %:   90.51         Latch Hit %:     99.83
Parse CPU to Parse Elapsd %:    0.28     % Non-Parse CPU:     98.56
 
 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   97.78   97.67
    % SQL with executions>1:   88.45   88.15
  % Memory for SQL w/exec>1:   85.83   86.05
 
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
latch free                                         34,952       4,841    53.16
db file sequential read                           140,335       2,311    25.37
db file scattered read                             13,102         734     8.06
log file sync                                      18,384         431     4.73
CPU time

Ca donne quoi ?

Code :

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

1
2
3
4
 
      select count(*), name latchname from v$session_wait, v$latchname
      where event='latch free' and state='WAITING' and p2=latch#
      group by name order by 1 desc;

Cela donne le vide la plupart du temps.

J'utilise aussi cette requête pour trouver de quelle table vient le latch, mais cela me donne plusieures tables candidates et je ne sais pas de toute manière quoi faire de cette information

Code :

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

1
2
3
4
 
select t.* from sys.x$bh t , (select p1raw from       v$session_wait t where t.event='latch free') u
where t.hladdr=u.p1raw
order by tch desc

on peut faire le test pour voir si effectivement tu n'as pas de sql sans bind variable :

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
 
create table t1 as select sql_text from v$sqlarea;
 
alter table t1 add sql_text_wo_constants varchar2(1000);
 
create or replace function 
remove_constants( p_query in varchar2 ) return varchar2
as
    l_query long;
    l_char  varchar2(1);
    l_in_quotes boolean default FALSE;
begin
    for i in 1 .. length( p_query )
    loop
        l_char := substr(p_query,i,1);
        if ( l_char = '''' and l_in_quotes )
        then
            l_in_quotes := FALSE;
        elsif ( l_char = '''' and NOT l_in_quotes )
        then
            l_in_quotes := TRUE;
            l_query := l_query || '''#';
        end if;
        if ( NOT l_in_quotes ) then
            l_query := l_query || l_char;
        end if;
    end loop;
    l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
    for i in 0 .. 8 loop
        l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
        l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
    end loop;
    return upper(l_query);
end;
/
update t1 set sql_text_wo_constants = remove_constants(sql_text);
 
select sql_text_wo_constants, count(*)
  from t1
 group by sql_text_wo_constants
having count(*) > 100
 order by 2
/

Très joli code!
J'ai effectivement 4 requêtes mais qui dépassent de justesse les 100 et le taux de bind est quand même très elevé (>99%)

[Jaouad]

Effectivement tu as une bonne différence entre CPU et elapsed , mais je trouve que tu remonte bcp de block pour peu de lignes finalement :

Query Rows
7091 74
6526 18
13617 92

Est ce que tes indexs ou ta méthode de jointure sont pertinents?
Tu remontes bcp trop de blocs par rapport au lignes, ce qui confirme ce que je t'ai dit tout à l'heure

[bouyao]

Une petite remarque.

Ton buffer cache est enorme

Buffer Cache: 2,172M

Ca ce peut que les hash chains sont longues, comme on sait qu' un seul latch peut s'occuper de plusiuers hash buckets.
Et si les hash chains sont longues (c.a.d contient plusieurs noeuds de bloc tampon) implique une recherche plus longues dans la liste + conso CPU et le latch n'est pas liberé rapidement ce qui donne l'event free latch.

Il faut calculer le nombe de latch et le nombre de hash bucket pour être sûre.

Soloution ca peut de diminiuer la taille du buffer cache

[aline]

Une petite remarque.

Ton buffer cache est enorme

Buffer Cache: 2,172M

oui, cela ne devrait pas être un problème, au contraire.

Ca ce peut que les hash chains sont longues, comme on sait qu' un seul latch peut s'occuper de plusiuers hash buckets.
Et si les hash chains sont longues (c.a.d contient plusieurs noeuds de bloc tampon) implique une recherche plus longues dans la liste + conso CPU et le latch n'est pas liberé rapidement ce qui donne l'event free latch.

Il faut calculer le nombe de latch et le nombre de hash bucket pour être sûre.

Tu peux me dire comment le faire. J'imagine que dans ce cas, on doit pouvoir augmenter le nombre de latch. Je m'étonne qu'ils (les latches) n'augmentent pas proportionnelement à la taille du buffer cache. Ce serait quand même dingue que l'on soit pénalisé en augmentant cette taille.

Soloution ca peut de diminiuer la taille du buffer cache

Si c'est vrai, alors cela pose un gos problème. Cela veux quand même dire qu'il est dommageable d'avoir une grosse sga!

[bouyao]

Je m'étonne qu'ils (les latches) n'augmentent pas proportionnelement à la taille du buffer cache

le nombre de latch alloué par défaut dans 9i est égale au double du mémoire cache (des fois arroundi au prochain nombre premier)

Cela veux quand même dire qu'il est dommageable d'avoir une grosse sga!

il faut toujours trouver un equilibre, un sga grosse peut resoudre ceratins problème mais peut créer d'autres problemes (swaping ==> lenteurs disques). Blayage de grandes tables (==> lecture physique) donc ca sert à rien d'avoir une grosse SGA.

je continue les autres points ... (le temps de trouver les scripts dans 2 mn)

[bouyao]

comment faire

1. Trouver les hot blocs

Code :

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

1
2
3
4
5
 
SELECT obj object,dbarfil file#,dbablk block#,tch touches
FROM x$bh
WHERE tch > 10
ORDER BY tch desc;

2. Trouver les clones

Code :

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

1
2
 
select dbarfil,dbablk ,count(*) from x$bh group by dbarfil,dbablk having count(*) > 2;

Essaye de trouver la valeur de

Code :

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

_DB_BLOCK_HASH_BUCKET

peut être il faut l'augmenter

[Jaouad]

Bouyao ,
Je ne suis pas sur qu'un hot bloc soit un bloc avec un touch count élevé !

[bouyao]

Salut Jaouad,

Bouyao ,
Je ne suis pas sur qu'un hot bloc soit un bloc avec un touch count élevé !

Moi je pense que plus qu'on touche un bloc plus qu'il est a été acceder par SQL

Metalink :

Depending on the TCH column (The number of times the block is hit by a SQL
statement), you can identify a hotblock. The higher the value of the TCH column,
the more frequent the block is accessed by SQL statements.

EDIT: et dans ce cas

1)Examine the application to see if the execution of certain DML
and SELECT statements can be reorganized to eliminate contention
on the object.
2)Decrease the buffer cache -although this may only help in a small amount of cases.
3)DBWR throughput may have a factor in this as well.
If using multiple DBWR's then increase the number of DBWR's
4)Increase the PCTUSED / PCTFREE for the table storage parameters via ALTER TABLE
or rebuild. This will result in less rows per block.
5)Consider implementing reverse key indexes