Re: Questions about shared server

On 3 Apr 2006 03:45:34 -0700, randiwolner_at_hotmail.com wrote:

>Hello,
>In our application we used dedicated server in Oracle 8.1.7, and this
>was done by adding a comment sign on this line in init.ora
># mts_dispatchers = "(protocol=TCP)"
>
>A tuning expert recommended NOT using multi-threaded server was and it
>worked fine.

Did he give you a reason?

>Having upgraded to Oracle 9.2.0.4 for Linux there are some performance
>problems. Oracle doc says:
>"In general, it is better to be connected through a dispatcher and use
>a shared server process. This is illustrated in Figure 5-2, "Oracle
>Shared Server Processes". A shared server process can be more efficient
>because it keeps the number of processes required for the running
>instance low."

What makes you think shared server will help?

>Question 1: Is this true - is it really better to use a shared server
>than dedicated or should we try to avoid using shared server (as in
>8.1.7) when experiencing performance problems?

Have you tried reading the documentation? tahiti.oracle.com

It's better to use shared servers when
a) You don't have an app server tha has a connection pool to the database (Solution: get one, or enable it) b) You have sessions connecting/disconnecting to the application (Solution: fix your application)
c) You have thousands of connections, and the machine simply won't have enough memory to handle an OS process per connection

It's not a good idea to use shared server if any of your queries/transactions take a long time to execute (longer then a sec or two), since you might saturate all the 'servers' (that handle the actual sql executions). T. Kyte in Oracle Architecture book explains the differences between 2 setups well.

>I have tried to switch the shared server off by following this
>guideline from Oracle:
>
>"After starting an instance, you can change the minimum number of
>shared server processes by using the SQL statement ALTER SYSTEM. Oracle
>will eventually terminate servers that are idle when there are more
>shared servers than the minimum limit you specify.
>If you set SHARED_SERVERS to 0, Oracle terminates all current servers
>when they become idle and does not start any new servers until you
>increase SHARED_SERVERS. Thus, setting SHARED_SERVERS to 0 may be used
>to effectively disable shared server."
>
>However - when take a I look in v$session I can see sessions where
>Server=SHARED..

AFAIK, as long as you have sessions in 'shared' mode, you'll need at least one 'server' around. You'll need to restart all your connections for the 'shared' sessions to go away.

.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Mon Apr 03 2006 - 10:26:22 CDT