Hi Tom,
we have a serious contention for the library cache latch in our production db.
I'm trying to find SQLs that are being parsed most intensively.
Below is the SQL
that is parsed most frequently (from statspack report).
Further, I have traced a couple of sessions mostly
waiting for latch free and see that similar procedure calls behave differently
from the parsing point of view: some are parsed every time when executing,
some are parsed only once. Could you give me a clue why, what's the difference
between them and is there anythingthe coders can do to force "bad" calls
to be parsed once.
And is this the right way of reducing the library cache latch contention?
TIA,
Alexander
% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
438,213 438,250 5.98 3352235821
Module: ModuleName
begin :msg:=IBS.executor.get_pipe_text(:pipe);end;
********************************************************************************
BEGIN Z#MAIN_DOCUM#INTERFACE.LOCK_OBJECT(:OBJ_ID,:INFO,:CLASS,:WAIT); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 421 0.05 0.50 0 0 0 0
Execute 421 0.82 3.32 0 522 179 421
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 842 0.87 3.82 0 522 179 421
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 373 0.16 2.62
library cache pin 4 0.00 0.00
********************************************************************************
BEGIN Z$DOCUM_RC_TR_1.TR_1_EXECUTE(:OBJ_ID,:CLASS); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 414 0.10 0.88 0 0 0 0
Execute 414 2.23 15.91 7 6419 874 414
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 828 2.33 16.79 7 6419 874 414
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 23 (recursive depth: 1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 1384 0.43 13.06
wait list latch free 3 0.02 0.05
********************************************************************************
begin
:GDT_MYOUTPUT:=IBS.EXECUTOR.GET_DEBUG_TEXT(:GDT_DIRECTION);
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 8 0.02 0.45 0 5 3 8
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 8 0.02 0.45 0 5 3 8
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 284
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
latch free 41 0.05 0.60
SQL*Net message to client 8 0.00 0.00
SQL*Net more data to client 3 0.00 0.00
SQL*Net message from client 8 54.00 75.63
********************************************************************************
begin
:LOCK_RES := IBS.executor.lock_request(:LOCK_NAME, 'Nav', :TIME_OUT);
END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 138 0.63 4.75 0 61 3 138
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 140 0.63 4.76 0 61 3 138
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 284
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 146 0.00 0.00
SQL*Net message from client 146 1.27 1.48
latch free 444 0.11 4.78
wait list latch free 10 0.02 0.19
pipe get 4 0.43 0.45
library cache pin 3 0.00 0.01
********************************************************************************
we parse when a developer says "PARSE THIS ORACLE"
we do not parse when they do not ask us to do that.
it is entirely up to the coder - they either:
a) parse
b) bind
c) execute
d) close
a statement over and over and over again or they:
a) ask if they already parsed this
a-prime) if not, parse it and remember they did
b) bind
c) execute
The developers can use language specific features like jdbc statement caching:
http://www.oracle.com/technology/products/oracle9i/daily/jun24.html if applicable as well...
but, this ultimately can only be corrected entirely in the application itself (session_cached_cursors can *help* but not remove the problem, an init.ora parameter)
and yes, over parsing (hard or soft) is a major scalability inhibitor.