Oracle-L: RE: Re[2]: Dedicated Server versus Shared Server

From: Grabowy, Chris <cgrabowy_at_fcg.com>
Date: Thu, 16 May 2002 11:24:50 -0800
Message-ID: <F001.00462C8D.20020516112450@fatcity.com>

It sounds to me that Tim is more right then wrong.

Theoretically, if you have an MTS ratio of 100 users to 10 shared servers, and one kicks in a DBMS_LOCK.SLEEP then theoretically it just hurt 9 users, plus the other 90 users. The load on the other nine shared servers just increased, while shared server number 1 is simply spinning it's wheels.

Can't we push "the sleep" down to the client? Why are we abusing the resources on the database server just to sleep or wait?

Theoretically...

-----Original Message-----
Sent: Thursday, May 16, 2002 2:28 PM
To: Multiple recipients of list ORACLE-L

Jared,

Since we have MTS around here for some applications and we also use DBMS_LOCK.SLEEP, Tim is right and wrong. It does tie up a shared server for the
time of the sleep, but since a shared server can service one and only one session at a time it should not affect anyone else. Of course if there are no
shared servers that are idle during this period the effect is the same. The trick with MTS is always having at least one shared server that is idle. Difficult, but not totally impossible to attain. BTW: I don't depend on the Oracle kernel to start an additional server if none are idle, since it does not
always do so. I'm told by OTS that there is a scanning algorithm that is used
that works on the idea that if a session waits a short period for a shared server to process it's requests that's OK. The problem is with the word 'short'. It appears to have several definitions.

Dick Goulet

____________________Reply Separator____________________
Author: Jared.Still_at_radisys.com
Date:       5/16/2002 10:05 AM

Tim,

If I understood this correctly, you are saying that a DBMS_LOCK.SLEEP(600) call
would tie up an MTS shared server for 10 minutes causing other sessions connected to it
to hang for 10 minutes?

Jared

"Tim Gorman" <Tim_at_SageLogix.com>
Sent by: root_at_fatcity.com
05/15/2002 09:58 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        Re: Dedicated Server versus Shared Server

multiplexing architecture like this: long-running SQL operations tying up a
shared server, DBMS_LOCK.SLEEP calls tying up a shared server, various types
of locks or slowly-serviced operations tying up a shared server. Each of these scenarios deny service to other sessions who might be waiting for it...

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------