Re: library cache pin wait

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 <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).
>
> Regards.
>
>
> Guillermo Alan Bort
> EDS - ITO DBA Main Group
> Arias 1851
> Ciudad Autónoma de Buenos Aires (C1429DXC)
> Argentina
>
> 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
> 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 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.
>
>

--
http://www.freelists.org/webpage/oracle-l