From oracle-l-bounce@freelists.org Wed Jan 12 15:51:33 2005 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id j0CLpXI11824 for ; Wed, 12 Jan 2005 15:51:33 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id j0CLpXn11817 for ; Wed, 12 Jan 2005 15:51:33 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DF36872CC58; Wed, 12 Jan 2005 16:58:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09410-35; Wed, 12 Jan 2005 16:58:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7CA7872CC8E; Wed, 12 Jan 2005 16:54:35 -0500 (EST) Message-ID: <20050112214900.72070.qmail@web80505.mail.yahoo.com> Date: Wed, 12 Jan 2005 13:49:00 -0800 (PST) From: Fuad Arshad Subject: Re: library cache lock To: Stephen.Barr@bskyb.com, oracle-l@freelists.org In-Reply-To: <6B4A3CF190E26E4781E7F56CCDEE408302DB0614@sssl_exch_usr3.sssl.bskyb.com> MIME-Version: 1.0 Content-type: text/plain Content-Transfer-Encoding: 8bit X-archive-position: 14728 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: fuadar@yahoo.com Precedence: normal Reply-To: fuadar@yahoo.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Stephan, i think the situation you are explain ing matches with ours . i've currently working with oracle but we think we've hot a bug what version of oracle are you if 9.2.0.4 look at this bug descriptiopn to see if it matches your scenario Doc ID: Note:3443747.8 Subject: Bug 3443747 - A session can spin holding a library cache latch during fixed table lookup Type: PATCH Status: PUBLISHED Content Type: TEXT/X-HTML Creation Date: 30-APR-2004 Last Revision Date: 22-SEP-2004 "Barr, Stephen" wrote: 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 NOPARALLEL NOLOGGING Session 2 ALTER INDEX dw_owner.il_bills_002 REBUILD PARTITION p_bills_1998_08 NOPARALLEL NOLOGGING Session 3 ALTER INDEX dw_owner.akl_bills_1 REBUILD PARTITION p_bills_1998_08 NOPARALLEL NOLOGGING 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 READONLY@>/ OBJECT_TYPE OBJECT_NAME SESSION_ID LOCKED_MODE ------------------ ------------------------------ ---------- ----------- 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 READONLY@> How could this situation arise where we can't even read from the table? Should the indexes be built like this? Thanks, Steve. ----------------------------------------------------------------------- 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. ----------------------------------------------------------------------- -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l