The Blog from the DBA Classroom

By: Joel Goodman

Shared Sessions and Shared Servers

Posted by Joel Goodman on 19/03/2009

One of my roles in Oracle is that of “Ambassador” or “Liaison” to several of the technical SIG meetings held by the UKOUG that are of interest to DBAs. They are the RDBMS, UNIX, RAC & HA and Oracle on Windows SIGs.

At the RDBMS SIG held in Slough on 17/03/2009 a new format for one of the presentations was piloted with good results.  The Session was called “Bits and Pieces” and invited SIG delegates to speak spontaneously for as little as five minutes to share useful tips and experiences with their colleagues. So in effect this was a “Shared Session” of 30-45 minutes.

The session was started with a 10-15 minute presentation by Paul Hancock who discussed options for Installation and covered Interactive Installs, Silent Installs using a responsefile and Cloning of Oracle Homes.

There were few other volunteers prepared to have a go and there was time so I volunteered to share some experiences that I had encountered recently which generated some lively discussion and debate. Here is one of them:

SYSDBA Connections Vs Normal Connections

I am often asked in the DBA Classroom about differences if any between sessions connected as SYS or as another user with the DBA Role. The easy answer is that SYSDBA has additional priveleges and can start up or shut down database instances but there is more. I presented a Master class on Database Links for the US RAC SIG earlier this month and discovered a more complicated distintion whilst writing part of my demo script.  In attempting to show how Shared Database Links work in conjunction with Shared Server Connections I discovered that  SYSDBA sessions do not release the shared server that processes the first network round trip after login.

To demonstrate this I have done the following:

1. Set max_shared_server = 2

2. Connected one normal session using shared server as user SYSTEM

3. Connected one SYSDBA session using shared server as user SYS

4. Connected one normall session using local bequeath  as user SYSTEM

I then did the following query from the local bequeath session:

SQL> select name,circuit,status from v$shared_server;

——–      ———–               ——————–
S000      25D6C85C        WAIT(RECEIVE)
S001       00                   WAIT(COMMON)

Shared Server S001 is in the usual “WAIT(COMMON)” state waiting for input from any dispatcher process on the common inbound queue. But Shared Server S000 is in the “WAIT(RECEIVE)” state waiting for input from the session.

By joining v$session, v$circuit and v$shared_server it was easy to prove that the SYSDBA session was the culprit:

SQL> select,  s.status,  s.circuit, c.status, n.username
2  from v$shared_server s, v$circuit c, v$session n
3  where s.circuit = c.circuit and c.saddr = n.saddr;

——–    ————————         ————–    ————-   —————–
S000    WAIT(RECEIVE)    25D6D1F8   NORMAL   SYS

After discussing this with some support colleagues it was confirmed that the kernel treats both SYSOPER and SYSDBA sessions differently and that these sessions do NOT share their servers when using shared server. This means that several idling sessions connected as SYSOPER or SYSDBA could severely deplete the shared server pool and adversely affect performance.

The recommendation of logging in as another user with the DBA role rather than as SYSDBA or SYSOPER privileged users is therefore a good one for those who use shared server connections but there may be other differences that I have not yet discovered.

In summary use SYSDBA or SYSOPER connections only when required such as for RMAN Channels or when using the Data Guard Broker. For all other normal DBA admin work connect as users with the DBA role or some other role appropriate to the requirement.


6 Responses to “Shared Sessions and Shared Servers”

  1. […] P.S. Just interesting fact about the shared server connection from Joel Goodman blog […]

  2. […] P.S.Интересныйфакт по теме shared server из блога Joel Goodman […]

  3. […] Shared Sessions and Shared Servers […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: