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/，如需轉載，請註明出處，否則將追究法律責任。