Re: library cache load lock - major problems

norman.dunbar_at_lfs.co.uk wrote:
>
> Seesm that if I startup a half dozen SQLPlus session as the application
> login user and have each one run a function from the offending package
> which calls UTL_TCP, I can get the first 5 to return a result but
> number 6 hangs up (as does 7 onwards). If I then exit from one of the
> sessions which returned, session 6 then completes and so on.
>
> There is nothing in V$SESSION_WAIT for the hung session at this point.
> No waits, no nothing.
>
> Turns out that there is a setting in the application which limits the
> number of sessions that can call these functions and procs that use
> UTL_TCP to 5. Once 5 have done it, no more sessions can do it until one
> of the 5 has logged out.
>
> I have to confess to complete ignorance as to why the application shows
> sessions waiting on L.C.L.L. when everything hangs, but when I run
> straight SQLPlus sessions I get nothing in V$SESSION_WAIT.
>

Thanks, Norm, for this update. In the first paragraph, are you saying even with 6 sqlplus connections, not connections through the app, you can reproduce the problem by running that package? If so, the limit of 6 is not in the application configuration on the midtier but instead possibly in the Oracle PL/SQL package the app is using, or a logon trigger?

When you see library cache load locks in v$session_wait, what are the p1,p2,p3 values? Are they exactly the same? Can you join p1raw to x$kglob.kglhdadr to find the object instead of going through v$sql and v$session (I assume that's what you did without your sessionwait.sql script)?

Yong Huang Received on Fri Jul 01 2005 - 09:49:20 CDT