Shared server | Oracle DBA Tutorial pdf

 Shared server

By default, Oracle runs on dedicated server mode where for each user session connection, a server process is created on server and a user process is created on client machine. When number of concurrent users becomes very high, shared server (known as Multi Threaded Server in earlier versions) is used.
In shared server, several (up to 5) dispatcher background processes are run in server, which accept user requests. They then put the request in a queue.
Thereafter, shared server processes pick the request from queue and process the SQL (i.e. parsing the statement, reading segments from disk and placing them in buffer cache etc.). After processing, they put the result in response queue. The corresponding dispatcher sends back the result to user process.
Shared server is useful for these scenarios – many application users, short application transactions (e.g. railway reservation system, order entry system), non-continuous transactions etc.
Shared server is a scalability improvement feature not performance improvement feature. You will always have same or better performance by using dedicated server feature.

=>  Configuring shared servers

Set following parameters in initialization file
DISPATCHERS = n (0 to 5)
SHARED_SERVERS = n (1 to OS dependent max. value)
Other optional parameters are – MAX_DISPATCHERS, CIRCUITS,
MAX_SHARED_SERVERS, and PROCESSES

=> Measuring shared server performance

Query on V$SHARED_SERVER_MONITOR view.
The following query shows shared server busy ratio
SELECT NAME,
decode(busy+idle,0,0,round((busy/(busy+idle))*100,4)) "busy
rate" FROM v$shared_server WHERE status != 'QUIT'
The next query shows dispatcher busy ratio
SELECT NAME "dispatcher", NETWORK,
(round(SUM(busy)/(SUM(busy)/SUM(idle)),4))*100 "busy rate"
FROM v$dispather GROUP BY NAME, NETWORK
Additional dispatcher is necessary is busy ratio found from above query is more than 50%.
Please note that DBA must connect through dedicated server to perform various DBA activities (e.g. startup/shutdown database).