Shared Server

Overview

Shared Server is described as a method to allow more connections to a database than is possible in dedicated mode. It comes with a lot of negative side affects. Just say no to Shared Server! If you want constant unexplained errors, terrible performance and potential data corruption then Shared Server is for you! You should suspect any DBAs credentials if they are proponent of Shared Server for a production server. With Shared Server routine negative database phenomena are guaranteed.

In most cases the best ROI is to change the application connection handling code and not implement Shared Server. Some methods that are effective include:

• Use application connection pooling.
• Create a single connection object and have all your SQL calls use this one (1) connection.
• Ensure connections are closed and as quickly after opening as possible.

There is no reason to open, and keep open, excessive Oracle database connections in this day and age.

Are you still reading this (sorry to hear that)? What follows are the changes required to enable Shared Server for an instance called DB01. This should allow 350-600 connections in most environments. Test it for yourself then change the settings as required after allowing your Shared Server to be active for at least a day (better a week).

It might be a good investment to bounce both the database and listener weekly if you use Shared Server.

Config Files

SPFILE Entries

This is really where all the changes are to enable Shared Server. The other files are included just for reference.

dispatchers          (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.42))(DISPATCHERS=5)
local_listener       LISTENER_DB01
max_shared_servers   50
open_cursors         2000
pga_aggregate_target 1073741824
processes            500
sessions             1200
sga_max_size         1572864000
sga_target           1073741824
shared_servers       30

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = DB01)
      (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
      (GLOBAL_DBNAME = DB01)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.42)(PORT = 1521))
    )
  )

sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Display

Status

-- ss_status.sql
-- spool C:\oracle\scripts\spool\shared_server_info.spool

SET pagesize 9999

COL "%TIME BUSY" FORMAT 99.9999
COL circuit      FORMAT a10
COL network      FORMAT a50
COL busy         FORMAT 999999
COL name         FORMAT a10
COL paddr        FORMAT 999999999
COL requests     FORMAT 999,999,999
COL status       FORMAT a15

clear screen

PROMPT [Shared Server Monitor]
SELECT MAXIMUM_CONNECTIONS "MAX CONN", MAXIMUM_SESSIONS "MAX SESS",
   SERVERS_STARTED "STARTED", SERVERS_TERMINATED "TERMINATED",
   SERVERS_HIGHWATER "HIGHWATER"
FROM V$SHARED_SERVER_MONITOR;
PROMPT

PROMPT [Performance]
SELECT NAME "NAME", PADDR, REQUESTS,
  (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY", STATUS
FROM V$SHARED_SERVER;
PROMPT

PROMPT [Dispatcher Rate]
SELECT * FROM V$DISPATCHER_RATE;
PROMPT

PROMPT [Circuit]
SELECT SADDR, CIRCUIT, DISPATCHER, SERVER, SUBSTR(QUEUE,1,8) "QUEUE",WAITER FROM V$CIRCUIT;
PROMPT

PROMPT [Shared Server]
SELECT NAME,PADDR,STATUS,MESSAGES,BYTES,BREAKS,CIRCUIT,IDLE,BUSY,REQUESTS
FROM V$SHARED_SERVER;
PROMPT

PROMPT [Dispatchers]
SELECT NAME "NAME", SUBSTR(NETWORK,1,23) "PROTOCOL", OWNED,
STATUS "STATUS", (BUSY/(BUSY + IDLE)) * 100 "%TIME BUSY"
FROM V$DISPATCHER;
PROMPT

PROMPT [Queue]
SELECT * FROM V$QUEUE;
PROMPT

PROMPT [Connection Info]
SELECT SERVER, SUBSTR(USERNAME,1,15) "USERNAME",
    SUBSTR(OSUSER,1,8) "OS USER", SUBSTR(MACHINE,1,7) "MACHINE",
    SUBSTR(PROGRAM,1,35) "PROGRAM"
    FROM V$SESSION
    WHERE TYPE='USER';
-- spool off

Connections

-- ss_conns.sql
clear columns
col username format a10 justify center heading "Username"
col sid format 9999 justify center heading "Session ID"
col server format a10 justify center heading "Connection"
col spid format 99999 justify center heading "OS ID"
col status format a10 justify center heading "Status"
col type format a15 justify center heading "Session Type"

select se.username, se.sid, se.server, p.spid, se.status, se.type
from v$session se, v$process p
where p.addr = se.paddr
and se.username is not null
order by se.username desc, se.sid
/

Dispatchers

-- ss_dispatchers.sql
-- If percent busy is too high add more. Whats to high well ask yourself is it slow?
clear columns
col p format a5 justify center heading "Protocol"
col name format a5 justify center heading "Dispatcher"
col cc format 9999 justify center heading "Current|Connections"
col status format a9 justify center heading "Status"
col accept format a6 justify center heading "Accept"
col b format 990.99999 justify center heading "% Busy"

compute sum of cc on p
compute avg of b on p
break on p skip 1

select substr(upper(d.network),instr(upper(d.network),'PROTOCOL=')+9,3) p, d.name,
d.status, d.accept,count(c.dispatcher) cc, (busy/(busy+idle))*100 b
from v$dispatcher d, v$circuit c
where d.paddr = c.dispatcher(+)
group by substr(upper(d.network),instr(upper(d.network),'PROTOCOL=')+9,3), d.name,
d.status, d.accept, (busy/(busy+idle))
order by 1, 2
/

Performance

-- ss_SrvPerf.sql
-- Look for the Percent busy, if its too high add shared servers.
-- Maximum performance means you have a single shared server with 0 requests.

clear columns
col name format a5 justify center heading "Server"
col status format a12 justify center heading "Status"
col requests format 9,999,999
justify center heading "Requests"
col idle/100 format 9,999,999.00 justify center heading "Idle (sec)"
col busy/100 format 9,999,999.00 justify center heading "Busy (sec)"
col b format 999.999 justify center heading "% Busy"

compute avg of b on report
break on report

select name, status, requests, idle/100, busy/100, (busy/(busy+idle))*100 b
from v$shared_server
order by 1
/

<- SpecialTopics