Home » RDBMS Server » Performance Tuning » library cache latch
| library cache latch [message #161212] |
Thu, 02 March 2006 11:14  |
aline
Messages: 92
Registered: February 2002
|
Member |
|
|
ORACLE 9.2.0.6 on redh hat system
hi,
during pics of activity on a production database all my application freeze.
Statspack reports show me latch free problems and particuliary library cache.
library cache kglpndl: child: before pro 0 2,646 3,298
library cache kglpndl: child: after proc 0 2,053 42
library cache kglhdgn: child: 0 1,473 2,568
library cache kglpnp: child 0 1,231 2,094
I think I haven't bind problem.
I don't know if it's an application probleme or a bad parametrage.
I traced a specific application to understand what happen on the session level.
********************************************************************************
INSERT INTO PRIX_PRODUIT (PRODUIT, DATE, PRIX, NOMBRE)
VALUES
(:B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 48372 15.47 245.57 875 3383 750261 48372
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48372 15.47 245.57 875 3383 750261 48372
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 45 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 875 0.82 22.85
latch free 377 2.90 17.20
log file switch completion 26 0.97 11.38
log file sync 925 1.27 61.87
log buffer space 36 0.97 10.17
buffer busy waits 2 0.35 0.35
********************************************************************************
********************************************************************************
call LOAD.SET_PRIX_PRODUIT (:1, :2, :3, :4)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 48372 9.63 77.70 4 77 149 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 48372 9.63 77.70 4 77 149 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 45
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 2343 6.18 184.44
SQL*Net message to client 48372 0.00 0.07
SQL*Net message from client 48372 0.68 41.77
********************************************************************************
=====================
PARSING IN CURSOR #5 len=53 dep=0 uid=45 oct=170 lid=45 tim=1114550297451030 hv=1663224314 ad='b5b6a250'
call LOAD.SET_PRIX_PRODUIT (:1, :2, :3, :4, :5, :6)
END OF STMT
EXEC #5:c=0,e=99727,p=0,cr=7,cu=17,mis=1,r=0,dep=0,og=4,tim=1114550297451025
WAIT #5: nam='latch free' ela= 981 p1=-1418751532 p2=157 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 369 p1=1413697536 p2=1 p3=0
WAIT #6: nam='latch free' ela= 3511 p1=-1418751732 p2=157 p3=0
=====================
EXEC #5:c=0,e=3779739,p=2,cr=7,cu=15,mis=0,r=0,dep=0,og=4,tim=1114550341383273
WAIT #5: nam='latch free' ela= 14577 p1=-1418751332 p2=157 p3=0
WAIT #5: nam='latch free' ela= 157552 p1=-1418751532 p2=157 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 1466 p1=1413697536 p2=1 p3=0
EXEC #5:c=0,e=79520,p=1,cr=7,cu=15,mis=0,r=0,dep=0,og=4,tim=1114550341878306
WAIT #5: nam='latch free' ela= 7322 p1=-1418751332 p2=157 p3=0
WAIT #5: nam='SQL*Net message to client' ela= 2 p1=1413697536 p2=1 p3=0
WAIT #5: nam='SQL*Net message from client' ela= 4726 p1=1413697536 p2=1 p3=0
I dind't understand why the call of the package take so long time.
Of course, the code is not responsible, he just take the effect of the big wait.
Any idea?
thk
|
|
|
|
| Re: library cache latch [message #161224 is a reply to message #161212] |
Thu, 02 March 2006 13:42  |
mchadder
Messages: 224
Registered: May 2005
Location: UK
|
Senior Member |
|
|
Hello.
Difficult to say using this information only, what does load.set_prix_produit do exactly? Is it performing the INSERT seen also?
The things that stand out (certainly to me) are :
INSERT INTO PRIX_PRODUIT (PRODUIT, DATE, PRIX, NOMBRE)
VALUES
(:B6 , :B5 , :B4 , :B3 , :B2 , :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 48372 15.47 245.57 875 3383 750261 48372
For a start, that's incorrect syntax on the INSERT (is this really how it was?), but secondly, this INSERT is being executed
48372 times, which is identical to the calls to the package. Does this imply that the client is executing the INSERT one at a time?
Or, is the insert called as a consequence of the package call (or vice versa)? If so, there may be mileage in "bulk"-ing up this statement, also, the "current" (i.e. db block gets) count is quite high, which is a little surprising if this is a table, are there indexes / triggers on this table?
Rgds
|
|
|
|
Goto Forum:
Current Time: Sat Apr 20 02:38:29 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
