5分钟的ORA-04021(zt)

 

http://yumianfeilong.com/2008/09/08/5%E5%88%86%E9%92%9F%E7%9A%84ora-04021/

 

基于Oracle dictionary得锁会等待5分钟后，自动timeout,报ORA-04021错误。

Session 1 创建并执行存储过程binzhang_t:

SQL> create or replace procedure binzhang_t is
2 begin
3 dbms_output.put_line('hello');
4 dbms_lock.sleep(12000);
5 end;
6 /

Procedure created.

SQL> set serveroutput on
SQL> exec binzhang_t

Session 2修改这个存储过程，但无法获得这个存储过程的object lock (library cache pin)

SQL> conn /
Connected.
SQL> create or replace procedure binzhang_t is
2 begin
3 dbms_output.put_line('hello');
4 dbms_lock.sleep(1220);
5 end;
6 /

产生library cache pin的等待信息

SID USERNAME MACHINE EVENT
------ ---------- ------------------ ---------------------
413 ORACLE sqlplus@LOCALHOST library cache pin
272 ORACLE sqlplus@LOCALHOST PL/SQL lock timer

5分钟过后，session 2自动time-out,提示4021错误。

create or replace procedure binzhang_t is
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object ORACLE.BINZHANG_T

更详细的library cache lock 和 library cache pin 真的很难搞明白。文档上也说不全面。

出问题的时候就是找waiting/blocked session胡乱杀；过后再找为什么会有dictionary object lock发生。

=================
library cache pin
=================
不全面(我觉得)
~~~~~
An Oracle instance has a library cache that contains the description of
different types of objects e.g. cursors, indexes, tables, views, procedures,
… Those objects cannot be changed when they are used. They are locked by a
mechanism based on library locks and pins. A session that need to use an object
will first acquire a library lock in a certain mode (null, shared or exclusive)
on the object, in order to prevent other sessions from accessing the same
object (e.g. exclusive lock when recompiling a package or view) or to maintain
the object definition for a long time. Locking an object is sometimes referred
as the job to locate it in the library cache and lock it in a certain mode.
If the session wants to modify or examine the object, it must acquire after
the lock also a pin in a certain mode (again null, shared or exclusive).

Each SQL statement that want to use/modify objects that are locked or pinned
and whose lock/pin mode is incompatible with the requested mode, will wait
on events like ‘library cache pin’ or ‘library cache lock’ until a timeout
occurs. The timeout normally occurs after 5 minutes and the SQL statement
then ends with an ORA-4021. If a deadlock is detected, an ORA-4020 is given
back.

Dealing with slow downs related to “mysterious” library cache pins
and load locks we should look for the reason of the database object
invalidations. They are likely to be triggered by actions causing
changes to “LAST_DDL” attribute of database objects that have other
dependent ones. Typically they are the object maintenance operations -
ALTER, GRANT, REVOKE, replacing views, etc. This behavior. is described
in Oracle Server Application Developer’s Guide as object dependency
maintenance.

After object invalidation, Oracle tries to recompile the object at the
time of the first access to it. It may be a problem in case when other
sessions have pinned the object to the library cache. It is obvious that
it is more likely to occur with more active users and with more complex
dependencies (eg. many cross-dependent packages or package bodies).
In some cases waiting for object recompilation may even take hours
blocking all the sessions trying to access it.

ORA-04021 timeout occurred while waiting to lock object %s%s%s%s%s”.
Cause: While trying to lock a library object, a time-out occurred.
Action: Retry the operation later.

ORA-04020 deadlock detected while trying to lock object %s%s%s%s%s
Cause: While trying to lock a library object, a deadlock is detected.
Action: Retry the operation later.
(see Note.166924.1)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-474600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-474600/