Oracle-L: Re: Dedicated Server versus Shared Server

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 16 May 2002 10:40:08 -0800
Message-ID: <F001.00462B28.20020516104008@fatcity.com>

Yup! Easy to prove...

In one SQL*Plus session, connect as MTS and verify shared connection...

$ sqlplus perfstat

SQL*Plus: Release 8.0.6.0.0 - Production on Thu May 16 11:20:52 2002

Enter password:

        Connected to:
        Oracle8 Enterprise Edition Release 8.0.6.2.0 - Production
        With the Partitioning and Objects options
        PL/SQL Release 8.0.6.2.0 - Production

        SQL> select server from v$session where audsid =

userenv('SESSIONID');

        SERVER
        ---------
        SHARED

        SQL> exec dbms_lock.sleep(60);

        PL/SQL procedure successfully completed.

        SQL>

During the 60 seconds when DBMS_LOCK.SLEEP(60) was "running", I connected via INTERNAL in another session and ran the following query...

        SQL> select     s.sid,
          2     s.username,
          3     c.queue c_queue,
          4     c.status c_status,
          5     ss.name ss_name,
          6     ss.status ss_status
          7  from       v$shared_server ss,
          8     v$circuit c,
          9     v$session s
         10  where      s.username = 'PERFSTAT'
         11  and        c.saddr = s.saddr
         12  and        ss.paddr (+) = c.server;

               SID USERNAME                       C_QUEUE          C_STATUS
        ---------- ------------------------------ ---------------- ---------
-------
        SS_NA SS_STATUS
        ----- ----------------
               448 PERFSTAT                       SERVER           NORMAL
        S000  EXEC

I kept running this while the DBMS_LOCK.SLEEP was going, and it showed shared server S000 tied up. When the call completed, the outer-join to V$SHARED_SERVER showed nothing...

Original Message ----- To: <ORACLE-L_at_fatcity.com> Cc: <Tim_at_SageLogix.com> Sent: Thursday, May 16, 2002 10:52 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: Tim Gorman
  INET: Tim_at_SageLogix.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).

Received on Thu May 16 2002 - 13:40:08 CDT