Prev: ORA-1113 signalled during: alter database open
Next: Oracle 10 RAC to 10G RAC standby
From: norman.dunbar on 30 Jun 2005 11:04
Well, what's worse is the fact that I know that there are some latch
problems solved at 8174 - so I'm hoping for an upgrade soon. I won't
hold my breath. As to who the third party is - I don't actually know,
I'm not directly in contact with them. (Much to my chagrin!)

Cheers,
Norm.

From: norman.dunbar on 1 Jul 2005 06:08
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.

From: yong321 on 1 Jul 2005 10:49
norman.dunbar(a)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

From: norman.dunbar on 1 Jul 2005 11:50
Thanks Yong.

My script does produce the P1 etc but I've never been able to join them
to X$KGLOB so far. I shall try in future but I'm off for a couple of
weeks holiday.

The developer tells me that there is a limit in the application (which
calls the package I was executing from SQLPlus) of 5 sessions using TCP
stuff. It seems to be a limitation of yet another application they use.

We have a fix coming soon !


Cheers,
Norm.

From: Joel Garry on 1 Jul 2005 14:32


norman.dunbar(a)lfs.co.uk wrote:
>and that's the last time I trust anything I'm told
> around here :o)

"Trust - but verify"

>
> 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 !

Could there be licensing issues?

I had an app once that was squiggly about the licensing - you could
have many sessions open from one IP, but would count licenses with the
same login or different login from many IP's. Anyways, some bug would
randomly kill sessions, leaving the tcp cleanup to the timeouts, and
long running SQL running. And apparently X'ing Windows (as opposed to
cleanly exiting the app) would sometimes do the same thing. The
licensing was from another party added into the app by the app vendor.

>
>
> Cheers,
> Norm. (Slipping back behind the firewall yet again)
>
> PS. Gravity sucks.

jg
--
@home.com is bogus.
And Winblows.

First  |  Prev  | 
Pages: 1 2
Prev: ORA-1113 signalled during: alter database open
Next: Oracle 10 RAC to 10G RAC standby