RE: Shared server tuning

  • From: Jared.Still@xxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 9 Jun 2004 17:55:06 -0700

Hi Aaron,

Whether or not MTS is causing your problems, you should probably switch
to dedicated sessions anyway. MTS is a lot of overhead for 30 users.

Do you have a test or development system to try this on first?

You just need to ensure that you understand the tnsnames.ora file 
configuration.

Or just use netmgr to do the config, it works pretty well.

If not, you can always setup tnsnames.ora to accept both dedicated and
shared server connections, depending on the alias used.

As for the performance problem, have you run a 10046 trace?

While this will show what your sessions are waiting on, and how long
they are waiting, it is complicated a bit by the MTS connections.

The trace file will have multiple sessions in it.

You could query v$session_wait while you *know* that a session
is waiting:

select
   s.username username,
   e.event event,
   s.sid,
   e.p1text,
   e.p1,
   e.p2text,
   e.p2,
   e.wait_time,
   e.seconds_in_wait,
   e.state
from v$session s, v$session_wait e
where s.username is not null
   and s.sid = e.sid
order by s.username, upper(e.event);

Not as good as a trace file, but better than nothing, and a place to 
start.

HTH

Jared







"Sentell, Aaron" <aaron_sentell@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
06/09/2004 04:53 PM
Please respond to
oracle-l@xxxxxxxxxxxxx


To
oracle-l@xxxxxxxxxxxxx
cc

Subject
RE: Shared server tuning






First of all, thanks to everyone for their responses so far. Your input is
very much appreciated.

So now I'm talking about and considering changing this instance over to 
use
dedicated connections, but before I charge ahead "blindly" I would like to
have a valid reason for doing this rather than just crossing my fingers 
and
hoping MTS is the problem.

...

Any input would be greatly appreciated.

Thanks!

Aaron


Other related posts: