Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 4206 invoked from network); 7 Oct 2008 07:50:17 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 7 Oct 2008 07:50:11 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 14466A0D9E8; Tue, 7 Oct 2008 08:50:11 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07297-10-5; Tue, 7 Oct 2008 08:50:10 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 83BCCA0D9E6; Tue, 7 Oct 2008 08:50:10 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 07 Oct 2008 08:48:08 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 46F43A0D985 for ; Tue, 7 Oct 2008 08:48:08 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06910-07 for ; Tue, 7 Oct 2008 08:48:08 -0400 (EDT) Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.231]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EC6A5A0D997 for ; Tue, 7 Oct 2008 08:48:06 -0400 (EDT) Received: by wr-out-0506.google.com with SMTP id c47so1852703wra.10 for ; Tue, 07 Oct 2008 05:48:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type:references; bh=jEZJ58cJp7FBraEMRA9eGkg7D1RFjS7j2EtkLFrOo34=; b=nQSFNL6iRmAGygUM9sPOImpbLKaqU1mdCPw3UDKgGzBk10yjZLG95dQE7kyJLwezD4 +LG+R+l4x/72LDjEcy6r4M6mAChFdIZr2+/jcLxCFr2unMovALqUA9XTIgt+esqwYr7v qXKd5C9mSszOvtxtnQYuvfhdg9IaU89mTlihc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:references; b=w9F9kpcWty2JhGvRIQJi8ih6u8NECNiDUa1/cPuh8u+gpc23XJKnavPBMMxLhv+dto 7z/1thpEsJ/HwDWksRXNzqsQsmy+TdVGNCi8gGxDrG0SlC9Ecc2lFfrhuhRTEhTytYfy Q9cWYvtN0gycxmXxlxcHQBjEE1Ux1453ikCag= Received: by 10.151.11.19 with SMTP id o19mr9707832ybi.162.1223383686253; Tue, 07 Oct 2008 05:48:06 -0700 (PDT) Received: by 10.151.106.11 with HTTP; Tue, 7 Oct 2008 05:48:06 -0700 (PDT) Message-ID: Date: Tue, 7 Oct 2008 08:48:06 -0400 From: "Dba DBA" To: oracle-l@freelists.org Subject: Re: library cache pin wait In-Reply-To: <785A4E1EF4D9E745BAC909B7941BEC0094F4A9@usplm201.amer.corp.eds.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_90771_919141.1223383686253" References: <785A4E1EF4D9E745BAC909B7941BEC0094F4A9@usplm201.amer.corp.eds.com> X-archive-position: 11456 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: oracledbaquestions@gmail.com Precedence: normal Reply-to: oracledbaquestions@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_90771_919141.1223383686253 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline No java. All the procedures are similiar. They are rolling up to summary tables in a data warehouse. So they do the following 1. grab a sequence value 2. set dbms_application_info 3. insert to a log table saying "I am running". procedure that does the insert is an autonomous transaction 4. run an insert select if there is no data in the summary, run a merge if there is data in the summary. 5. call the log procedure again to update the log when done That is it. I have similiar code in other packages. This only seems to happen now, right after I recompile the code to make changes. It only happens in our production RAC environment (I do not have a dev or test RAC environment). once it completes once, I don't get library cache waits anymore. The other package that is very similiar, does not have this problem. On Sat, Oct 4, 2008 at 9:43 PM, Bort, Guillermo wro= te: > Does this happen if you run the same procedures again? Does any procedure > contain any java code or call to any java program? If I recall admin > workshop II correctly there is a way to explicitly pin an object to the L= C, > are you certain the code does not do this? > > Does this happen only the first time you run the procedures or package > after compilation? Have you run utlrp after compilation (in case there ar= e > any objects invalidated because of the recompilation). > > Regards. > > > Guillermo Alan Bort > EDS - ITO DBA Main Group > Arias 1851 > Ciudad Aut=F3noma de Buenos Aires (C1429DXC) > Argentina > > Tel: +54 11 4704 3132 > Tel: +54 11 4704 3000 > E-mail: guillermo.bort@eds.com > E-mail: ardbagroup@eds.com > E-mail: ardbamain@eds.com > > From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org= ] > On Behalf Of Dba DBA > Sent: Friday, October 03, 2008 10:50 PM > To: oracle-l@freelists.org > Subject: library cache pin wait > > I recompiled a package. It compiled successfully. > > The package has several procedures. Each has 1 large insert select. The a= ll > select from the same table, but insert to different tables. > > This has happened to me before. > > 1. I run the package with 1 procedure from 1 session. > 2. i try to run the same package with a different procedure in another > sessions. > > The second procedure waits with a library cache wait until the first > sessions completes and exits. (I am nohupping this, so I dont know if the > exit is required, it is in the script). > > any ideas? > > the code is NOT being recompiled. When I look at dba_blockers, I don't se= e > any blocking lockers. i am looking at the wait on the second session. I k= now > the package is not running by. > 1. I have dbms_application_info calls inside the package and I do not see > these in v$session > 2. I check v$sess_io and no IO is incrementing for this session. > > ------=_Part_90771_919141.1223383686253 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
No java. All the procedures are similiar. They are rolling= up to summary tables in a data warehouse.

So they do the following=

1. grab a sequence value
2. set dbms_application_info
3. inse= rt to a log table saying "I am running". procedure that does the = insert is an autonomous transaction
4. run an insert select if there is no data in the summary, run a merge if = there is data in the summary.
5. call the log procedure again to update = the log when done

That is it. I have similiar code in other packages= . This only seems to happen now, right after I recompile the code to make c= hanges. It only happens in our production RAC environment (I do not have a = dev or test RAC environment).

once it completes once, I don't get library cache waits anymore. Th= e other package that is very similiar, does not have this problem.

=
On Sat, Oct 4, 2008 at 9:43 PM, Bort, Guillermo = <guillermo.b= ort@eds.com> wrote:
Does this happen = if you run the same procedures again? Does any procedure contain any java c= ode or call to any java program? If I recall admin workshop II correctly th= ere is a way to explicitly pin an object to the LC, are you certain the cod= e does not do this?

Does this happen only the first time you run the procedures or package afte= r compilation? Have you run utlrp after compilation (in case there are any = objects invalidated because of the recompilation).

Regards.


Guillermo Alan Bort
EDS - ITO DBA Main Group
Arias 1851
Ciudad Aut=F3noma de Buenos Aires (C1429DXC)
Argentina

Tel: +54 11 4704 3132
Tel: +54 11 4704 3000
E-mail: guillermo.bort@eds.com
E-mail: ardbagroup@eds.com
E-mail: ardbamain@eds.com

From: oracle-l-bounce@free= lists.org [mailto:orac= le-l-bounce@freelists.org] On Behalf Of Dba DBA
Sent: Friday, October 03, 2008 10:50 PM
To: oracle-l@freelists.org Subject: library cache pin wait

I recompiled a package. It compiled successfully.

The package has several procedures. Each has 1 large insert select. The all= select from the same table, but insert to different tables.

This has happened to me before.

1. I run the package with 1 procedure from 1 session.
2. i try to run the same package with a different procedure in another sess= ions.

The second procedure waits with a library cache wait until the first sessio= ns completes and exits. (I am nohupping this, so I dont know if the exit is= required, it is in the script).

any ideas?

the code is NOT being recompiled. When I look at dba_blockers, I don't = see any blocking lockers. i am looking at the wait on the second session. I= know the package is not running by.
1. I have dbms_application_info calls inside the package and I do not see t= hese in v$session
2. I check v$sess_io and no IO is incrementing for this session.


------=_Part_90771_919141.1223383686253-- -- http://www.freelists.org/webpage/oracle-l