library cache lock

  • From: "Barr, Stephen" <Stephen.Barr@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 Jan 2005 21:00:12 -0000

We have a situation where a majority of the sessions in the database are
waiting for "library cache lock".
A simple explain plan against the wh_bills table hangs indefinitely.

READONLY@>select event, count(*)
  2  from v$session_wait
  3  where state = 'WAITING'
  4  group by event
  5  order by 2 desc
  6  /

EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
SQL*Net message from client                                              13
library cache lock                                                       13
rdbms ipc message                                                         9
PX Deq Credit: send blkd                                                  8
PL/SQL lock timer                                                         1
smon timer                                                                1
row cache lock                                                            1
PX Deq: Execute Reply                                                     1
library cache pin                                                         1
pmon timer                                                                1

10 rows selected.

Some of these session have been waiting for days.

The only activity currently happening on the database is a rebuild of the
partitioned indexes on a single table (WH_BILLS). The indexes are being
built by a simple set of statements running in separate sessions which are
actually kicked off via dbms_job and a stored procedure -

Session 1
ALTER INDEX dw_owner.il_bills_001 REBUILD PARTITION p_bills_1998_08

Session 2
ALTER INDEX dw_owner.il_bills_002 REBUILD PARTITION p_bills_1998_08

Session 3
ALTER INDEX dw_owner.akl_bills_1 REBUILD PARTITION p_bills_1998_08

The locking activity produced by this is -

  1  select obj.object_type, obj.object_name,l.session_id, l.locked_mode
  2  from v$locked_object l,
  3  all_objects obj
  4* where obj.object_id = l.object_id

------------------ ------------------------------ ---------- -----------
TABLE              WH_BILLS                              151           2
TABLE PARTITION    WH_BILLS                              151           4
TABLE              WH_BILLS                               17           2
TABLE PARTITION    WH_BILLS                               17           4
TABLE              WH_BILLS                               19           2
TABLE PARTITION    WH_BILLS                               19           4
TABLE              WH_BILLS                               44           2
TABLE PARTITION    WH_BILLS                               44           4

8 rows selected.

Elapsed: 00:00:00.02

How could this situation arise where we can't even read from the table?
Should the indexes be built like this?



Information in this email may be privileged, confidential and is 
intended exclusively for the addressee.  The views expressed may
not be official policy, but the personal views of the originator.
If you have received it in error, please notify the sender by return
e-mail and delete it from your system.  You should not reproduce, 
distribute, store, retransmit, use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.


Other related posts: