Library Cache Lock Held For Long Time For Queries

TOMSYAN

Library Cache Lock Held For Long Time For Queries Involving Too Many Binds [ID 1315929.1]

修改时间 22-APR-2011类型 PROBLEM状态 MODERATED

In this Document

Symptoms
Changes
Cause
Solution

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.

Symptoms

Generally, the Library cache lock is held up during parsing of the sql and the lock is held up on the object in shared mode and released after the parse. This is the expected behaviour.
Mostly, The Library cache lock would not be held up in the execution phase.
Example: A query with joins going for merge join cartesian taking long time in execution would not block others with Library cache lock. Because Library cache lock would have been released after the parse.
This is the behaviour of Library cache lock.

Changes

Should the SQL has bind variables specified in the where clause, then the Library cache lock is held up even beyond the parse phase.Because, the bind replacement happens after the parse phase and before the execute phase. There is a intermediate stage called "BIND" which comes after the parse and thus Library cache lock has to be held even after the parse and till all the binds have been replaced.

Should the SQL has too many bind variables in the where clause or huge inlist with bind variables, then the Library cache lock would get held up for long time till the bind replacement finishes. In those circumstances, Library cache lock will be seen till execute phase.

Cause

Example:

Following example shows where the SQL has huge inlist with 9001 bind variables in the inlist and the Library cache lock is held up for long time after parse phase.

select * from test.tbl_quelle1 t1, test.tbl_quelle1 t2 , test.tbl_quelle1 t3, test.tbl_quelle1 t4, test.tbl_quelle1 t5
where
t1.rn=t2.rn and
t1.rn=t3.rn and
t1.rn=t4.rn and
t1.rn=t5.rn and
( t1.rn in (:"SYS_B_0000",...,:"SYS_B_8999");

From TKPROF

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 13.38 13.46 0 0 0 0
Execute 1 154.68 154.60 0 0 0 0
Fetch 68 0.37 3.85 338 2990 0 1000
------- ------ -------- ---------- ---------- ---------- ----------
total 70 168.43 171.93 338 2990 0 1000

SQL> select sid, serial#, event, sql_id, seconds_in_wait, state from v$session where sql_id=<sql_id>;

SID SERIAL# EVENT SQL_ID SECONDS_IN_WAIT STATE
---------- ---------- ------------------------------ -------------
2183 1 library cache lock 167 WAITING bc3n13pnrfv1g

The parse time of the query is 13.46 seconds. But the Library cache lock is held up for 167 seconds.Here the library cache lock is being held also after the query is parsed.

Solution
Why the LC lock held up for long time even after parse time of 14s?

As per the behaviour of LC lock, it has to release the lock on the object once the parse is done.

But there are some exemptions where in bind variables are used or literal replacement occurs as part of cursor_sharing=similar. Because, bind replacement occurs after the parse tree has been created for the cursor. Since there are huge inlist and those are converted to bind variables as part of cursor_sharing=similar, the bind replacement here takes long time for 9001 bind values, ie 167 secs.

Because the execution of the sql depends upon the bind values and those are traversed after the parse tree had been made, the LC lock has to be held till the execution takes place. The reason is there is potential chances of change in explain plan than with the plan generated with parse tree because of feature called bind peeking. Hence the LC lock holds for the time till the final execution plan has been generated and executed. Thats why, Oracle acquires LC lock here for 167s.

Excerpts from the raw 10046:
=============================
PARSING IN CURSOR #13 len=126725 dep=0 uid=5 oct=3 lid=5 tim=437160281069 hv=91324704 ad='3b67e54c8' sqlid='43ycb442r3090'
select * from test.tbl_quelle1 t1, test.tbl_quelle1 t2 , test.tbl_quelle1 t3, test.tbl_quelle1 t4, test.tbl_quelle1 t5
...
order by :"SYS_B_9000"
END OF STMT
PARSE #13:c=13630000,e=14389433,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=437160281069
++parse is finished in 14 seconds, /* e=14389433 */

++Bind replacement follows after parse++

BINDS #13:
Bind#0
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
kxsbbbfp=ffffffff7aaffbe0 bln=22 avl=02 flg=09
value=1
...
Bind#9000
oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0500 frm=00 csi=00 siz=24 off=0
kxsbbbfp=ffffffff7ab23fa0 bln=22 avl=02 flg=09
value=1

EXEC #13:c=163420000,e=171631775,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=285361322,tim=437331932600

The elapsed time of EXEC is e=171631775 ie 170s. The time taken here is due to the bind value replacement.

Inference with reduced bind variables:

The Library cache lock is held up for lesser time when the binds are reduced.

With 9000 binds:

call count cpu elapsed disk query current rows
------- ------ ------- --------- --------- --------- --------- -----------
Parse 1 2.26 2.30 0 1 0 0
Execute 1 640.95 644.00 0 10 0 0
Fetch 68 0.13 0.14 0 2790 0 1000
------- ------ ------- --------- --------- --------- --------- -----------
total 70 643.34 646.45 0 2801 0 1000

With 4500 binds:

call count cpu elapsed disk query current rows
------- ------ ------- --------- --------- --------- --------- -----------
Parse 1 0.46 0.45 0 1 0 0
Execute 1 160.01 160.14 0 10 0 0
Fetch 35 0.08 0.07 0 1420 0 500
------- ------ ------- --------- --------- --------- --------- -----------
total 37 160.55 160.67 0 1431 0 500

With 900 binds:

call count cpu elapsed disk query current rows
------- ------ ------- --------- --------- --------- --------- -----------
Parse 1 0.03 0.02 0 1 0 0
Execute 1 7.21 7.22 0 10 0 0
Fetch 8 0.01 0.01 0 310 0 100
------- ------ ------- --------- --------- --------- --------- -----------
total 10 7.25 7.26 0 321 0 100

With 90 binds:

call count cpu elapsed disk query current rows
------- ------ ------- --------- --------- --------- --------- -----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.18 0.16 0 10 0 0
Fetch 2 0.00 0.00 0 70 0 10
------- ------ ------- --------- --------- --------- --------- -----------
total 4 0.19 0.17 0 81 0 10

So, it is clearly seen that the Library cache lock is not held up for long time when the no.of binds are reduced.

Conclusion:

The Library cache lock gets held up for the objects involving the SQL with binds till the execute phase of the SQL is completed. This is not the case for SQLs without binds. This is expected behavior and it is not a bug.

Oracle usually recommend not to use such a huge inlist going for bind replacement. The application SQLs have to be modified in such a way to avoid this kind of situation.

相关的

产品

Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition