Dick, I don't think there is any argument that DBMS_LOCK.SLEEP ties up a shared server needlessly. Same with long-running SQL statements...
The question is whether that is a problem... It certainly is a problem if you have fifty sessions concurrently issuing that call and MTS_MAX_SERVERS = 40. If you keep increasing that parameter, then exactly why are you using the mechanism? What benefits would it provide? How do you think the MTS architecture reacts to inefficient, long-running queries? Think back to basic queueing theory and the example of a bank of tellers. What is the impact of "shutting down" all teller stations? Does leaving only one or two teller stations open help throughput tremendously, when there used to be 40-50? Look at the impact of shutting down one lane for 90 minutes on a busy 3-lane highway... This is why I suggested that the only feasible use of MTS is in a "mixed" mode, with dedicated servers handling application modules that perform intensive, long-running operations (i.e. batch programs, reports, pooled connections from servlets, etc) while MTS handle the types of connections where it can provide some benefit (i.e. large numbers of short-duration connections, issuing small operations, mostly idle, etc)... -Tim ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, May 16, 2002 12:28 PM > 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: [EMAIL PROTECTED] > 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" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 05/15/2002 09:58 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Tim Gorman INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
