Re: library cache load lock - major problems

I think I have the solution.

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.

Even better, the vedor *has not* not validated Oracle 8174 etc, as they are currently running their app on 9206 - so I 'lied' when I said that it wasn't validated. We're doing an upgrade to 9206 in a couple of weeks. Hooray - and that's the last time I trust anything I'm told around here :o)

Just thought I'd let you know what the solution was - reconfigure the application. Of course,this raises the question of why they need this limit in the application in the first place !

Cheers,
Norm. (Slipping back behind the firewall yet again)

PS. Gravity sucks. Received on Fri Jul 01 2005 - 05:08:16 CDT