Re: library cache pin wait
Date: Tue, 7 Oct 2008 08:48:06 -0400
No java. All the procedures are similiar. They are rolling up to summary tables in a data warehouse.
So they do the following
- grab a sequence value
- set dbms_application_info
- insert to a log table saying "I am running". procedure that does the insert is an autonomous transaction
- run an insert select if there is no data in the summary, run a merge if there is data in the summary.
- 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 <guillermo.bort_at_eds.com>wrote:
> 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 LC,
> 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 are
> any objects invalidated because of the recompilation).
> Guillermo Alan Bort
> EDS - ITO DBA Main Group
> Arias 1851
> Ciudad Autónoma de Buenos Aires (C1429DXC)
> Tel: +54 11 4704 3132
> Tel: +54 11 4704 3000
> E-mail: guillermo.bort_at_eds.com
> E-mail: ardbagroup_at_eds.com
> E-mail: ardbamain_at_eds.com
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Dba DBA
> Sent: Friday, October 03, 2008 10:50 PM
> To: oracle-l_at_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
> 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 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
> these in v$session
> 2. I check v$sess_io and no IO is incrementing for this session.