Re: Shared Server intermittently hanging.

I might suspect that you are crashing into one of the potential pitfalls of MTS (or s'shared server' as we must now learn to call it since 9i!).... namely, server process contention.

When you pre-spawn X shared servers, your SQL requests go on a job queue. When a server process becomes free having done its work for someone else, it retrieves the next job from the job queue. If you haven't enough server processes, then you can be waiting on the job queue for a while before you're dealt with.

Having said that, Oracle spawns more shared servers automatically whenever it detects that there is a waiting problem... up to the limit provided by your MTS_MAX_SERVERS init.ora parameter... so make sure that's high enough... but if it is, then this shouldn't necessarily be a dramatic problem.

One thing you can do to check is to see the average waittime per request with the following query:

select decode (totalq,0,'No. requests', wait/totalq || 'hundredths of a second')
"Average Wait Time Per Request"
from v$queue
where type='COMMON';

It should be as close to zero as you can possibly get it.

Regards
HJR "Steven Pannell" <steven.pannell_at_gmx.net> wrote in message news:a1g15h$qgarg$1_at_ID-82797.news.dfncis.de...
> Hi,
>
> I have setup two shared servers using Oracle 8i on a Sun Solaris(7) box.
> Since then I have experienced problems whereby the servers seems to
> intermittently hang when returning results from a select statement. When
I
> execute a basic select * from x where .... clause sometimes it returns
very
> fast and other times it appears to hang for sometime before returning the
> results. There is no real logical pattern. At times it just seems to
hang
> for a while before returning the results. The statements are the same and
I
> am the only user on the database. If I switch back to dedicated servers I
> do not get this problem. Has anyone else experienced such a problem?
>
> Would appreciate your feedback,
>
> thanks in advance,
> Steve,
>
>
>
Received on Wed Jan 09 2002 - 02:35:57 CST