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

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our PL/SQL, database administration and data warehousing gurus are waiting to answer your toughest questions.