Problem solve Get help with specific problems with your technologies, process and projects.

SHARED_SERVER_SESSIONS parameter explained

In Oracle 9i, can you please explain (in laymen's terms) just what the parameter SHARED_SERVER_SESSIONS does? I understand the other required and optional initialization parameters. But, this one has got me stumped. According to the Oracle University 9i DBA Fundamentals II book, the SHARED_SERVER_SESSIONS "enables you to reserve user sessions for dedicated servers". I've set up my server in both dedicated and shared server mode and set SHARED_SERVER_SESSIONS=1, but I don't see any impact on the number of sessions I can launch. Thanks!

As you are probably already aware, there is a difference between a dedicated server session and a shared server session. With a dedicated session, each dedicated server process is used by one and only one user. This can hamper scalability. So Oracle also allows a shared server session where more than one user can can use the same shared server process.

The total number of sessions allowed in the database is defined by the SESSIONS parameter. Let's assume that you have SESSIONS=200. SHARED_SERVER_SESSIONS is allowed to be between 0 and SESSIONS-5, or in our case, 0 to 195. If we set SHARED_SERVER_SESSIONS=195, then the other 5 sessions are reserved for dedicated server connections only. This way, the DBA always has a few connections that he can use. By setting SHARED_SERVER_SESSIONS=1, then the other 199 connections are for dedicated server connections.

For More Information

Dig Deeper on Oracle database design and architecture