Library Cache Lock Held For Long Time For Queries Involving Too Many Binds [ID 1315929.1]【每日一译】-1114

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 and later
Oracle Server - Personal Edition - Version 9.2.0.1 and later
Oracle Server - Standard Edition - Version 9.2.0.1 and later
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.

 症状

一般来说，LC LOCK被占有的在解析SQL并且锁以共享模式占有在对象上并且在解析后释放。这是个常见的行为。

通常，LC LOCK在执行阶段是不会被占有的。

比如：一个查询采用合并联合笛卡尔长时间的执行不会阻塞其它会话用LC LOCK。因为LC LOCK在解析后将释放的。

这个LC 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.

更改

SQL语句拥有绑定变量指定在WHERE语句里，然后LC LOCK一直占有甚至在解析完成之后。因为绑定变量发生在解析阶段并且在执行阶段之前。这个中间阶段叫做“绑定”它在解析后出现并且LC LOCK占有甚至在解析后并且直到所有的绑定变量都被替换。

SQL有许多的绑定变量在WHERE语句里或者大的IN菜单作为绑定变量，那么LC LOCK将占有相当长的时间直到绑定变量替换完成。在这种情况下，LC LOCK直到执行阶段都将是可见的。

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.
接下来的例子显示SQL拥有IN菜单中的9001个绑定变量在iN菜单里并且LC LOCK占有了很长的时间在解析阶段后

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.
这个查询的解析是13.46秒，但是LC LOCK持有的时间为167秒。所以LC LOCK被占有的在查询被解析后。


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.

解决

为什么LC LOCK持有了这么长的时间甚至在14秒的解析时间后？

每个LC LOCK的行为在解析完成后它将释放在对象上的锁。

但是也有一些特例当绑定变量的使用或者文字交换发生作为 cursor_sharing=similar的一部份。因为绑定替换发生在解析树已经创建对于游标。当有大量的IN菜单并且有许多转成绑定变量作为cursor_sharing=similar的一部份，绑定变量的替换花费了很长的时间对于9001个绑定变量，即167秒。

因为SQL的执行取决于绑定变量的值并且它们在解析树已经创建后取得的。LC LOCK被占有的直到执行发生时。理由是存在一个隐性的改变机会在执行计划用解析树时，因为一个被叫做绑定PEEK的功能。所以LC LOCK占有锁直到最后的执行计划已经创建和执行。那也就是ORACLE要求LC LOCK要167秒的原因。

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.

结论：

LC LOCK使用含有绑定变量的SQL占有对象直到SQL执行解析的完成。对于不含绑定变量的SQL是没有这个问题的。这是一个常见的问题它不是一个BUG.

ORACLE通常建议不要使用超大IN菜单对于绑定变量的替换。应用的SQL应通过这种方法去修改以避免这种情形。