| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> shared pool kghupr1
Hello,
on my client's site running Oracle 9.1.2 on HPUX with a huge active user population (over 1000 sessions simultaneously) the database since recently shows a high value for latch free event (for the shared pool). The time waiting on this latch free event has gradually pushed itself to the top of the list. Belows is an extract from a recent statspack report taken over a period of 30 minutes:
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms)
/txn
---------------------------- ------------ ---------- ---------- ------
latch free 100,730 0 14,115 140
0.6
db file sequential read 1,354,105 0 1,728 1
7.4
db file scattered read 1,003,044 0 1,435 1
5.5
log file parallel write 145,329 0 405 3
0.8
SQL*Net message from dblink 10,406 0 226 22
0.1
log file sync 35,469 0 201 6
0.2
SQL*Net more data to client 143,947 0 156 1
0.8
buffer busy waits 49,269 0 121 2
0.3
log file sequential read 506 0 20 39
0.0
log buffer space 327 0 17 53
0.0
process startup 47 12 15 319
0.0
wait list latch free 244 0 5 21
0.0
enqueue 77 0 5 67
0.0
control file parallel write 603 0 5 8
0.0
SQL*Net break/reset to clien 7,553 0 4 1
0.0
db file parallel read 31 0 1 29
0.0
log file switch completion 9 0 1 75
0.0
SQL*Net more data from dblin 167 0 0 2
0.0
db file single write 40 0 0 4
0.0
control file sequential read 600 0 0 0
0.0
direct path read 8,626 0 0 0
0.0
LGWR wait for redo copy 2,362 0 0 0
0.0
direct path write 9,085 0 0 0
0.0
log file single write 4 0 0 9
0.0
SQL*Net message to dblink 10,406 0 0 0
0.1
row cache lock 1 0 0 1
0.0
async disk IO 125 0 0 0
0.0
library cache pin 4 0 0 0
0.0
SQL*Net message from client 2,687,566 0 3,665,221 1364
14.7
pipe get 1,745 1,064 5,309 3042
0.0
jobq slave wait 895 863 2,654 2966
0.0
SQL*Net more data from clien 49,247 0 95 2
0.3
SQL*Net message to client 2,687,267 0 3 0
14.7
-------------------------------------------------------------
Latch Name Where Misses Sleeps
Sleeps
shared pool kghupr1 0 22,864
41,796
shared pool kghalo 0 15,737
5,028
shared pool kghfrunp: alloc: wait 0 7,967
218
shared pool kghfrunp: clatch: nowait 0 2,654
0
shared pool kghfrunp: clatch: wait 0 2,415
4,318
shared pool kghfre 0 730
614
shared pool kghalp 0 479
243
shared pool kghfen: not perm alloc cla 0 148
39
shared pool kghfru 0 11
7
shared pool kghdmp 0 8
0
-------------------------------------------------------------
Can someone conclude from the shared pool wait details what is going on in the system? I do not have DBA role at this site but am supposed to assist in the problem diagnose. Hence this posting.
Thanks in advance for any clarification. Received on Thu Mar 25 2004 - 04:52:21 CST
 |
 |