shared-server or dedicated-server ?

[Jiangxi]

How to find out it's shared-server or dedicated-server ? Which one is more common ? Thx.

[davey23uk]

look in the server column in v$session

[Bore]

1)select name,network from v$dispatcher
select name from v$shared_server
If you see any rows here, then your database operates in shared server mode, what still does not means that the customers uses it
2) lsnrctl services
If you see your dispatchers here ( the names you see from the query 1) then the listener knows about the dispatchers and possibly uses them
3)
select saddr,dispatcher,server from v$circuit;
Here you see all the sessions currently using the shared server

[Jiangxi]

Sounds like the shared-server could improve huge scalability by allowing serverl users ( sessions or client boxes ? ) share the same connection, so, why still most of system still using dedicated-server instead of shared-server, guess my questions is what is the drawback of shared-server ? Is it because of security concern ? Thanks in advance.

[tomcat]

Just my experience, shared server adds some complexity and was mainly for scalability in 2 tier architectures
These days with 3+ tiers, the app layer usually does the connection pooling

[Bore]

Agree with tomcat. Generally shared server makes one server process to serve several sessions. It makes sence when you have many sessions, which are mainly idle ( that's the case with client server OLTP environment, since people there look at the screen, type in some text etc. which is practically idle time for the session)
If you have loaded session ( batch processing, DWH operaions or 3-tire architecture where multiplexing is already done by the mid tier) you generally do not need shared server.
The drawbacks are
1) More complexity.
2) Generally it is widely accepted to consider shared server configuration to be a bit slower than the dedicated server, due to the context switches (in windows this is not the case since all the processes are threads in same service)
3) Sometimes, a session crash in shared server could chrash the entire instance, since the dead server process can cause dispatchers to die and hence finally to crash entire instance.

Oracle recommends to use shared server if you do not have enough memory to handle so many client sessions in dedicated server mode. As long as you can afford dedicated server mode, that should be the choise