Re: Shared server tuning

Servers are shared, but only service one SQL statement at a time.

Sessions queue up behind busy servers if some SQL statements
are running too long

Been a long time since I've messed with MTS, and I don't recall
how to detemermine this.

google for "queue mts shared server oracle" and you will find
a number of hits with some SQL queries to examine share server
performance views.

The problem could also be less than optimal SQL tying up
the server and dispatcher.

Jared




"Sentell, Aaron" <aaron_sentell@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
06/08/2004 11:47 AM
Please respond to
oracle-l@xxxxxxxxxxxxx


To
oracle-l@xxxxxxxxxxxxx
cc

Subject
Shared server tuning






I have a six-month-old Oracle 9.0.1.3 database that is using shared server
connections. Users are complaining about system slowness sporadically
throughout the day, but I don't see any CPU, i/o, or network-related 
issues
with the server itself. I have run a few Statspack reports through
oraperf.com, and they all say that I can get a 90%+ gain by tuning MTS. In
addition, in the waits section, virtual circuit status is by far the event
with the most wait time.

I have checked the v$shared_server_monitor view, and nothing raises a flag
with me. SERVERS_HIGHWATER has never come close to approaching
MAX_SHARED_SERVERS. There are some servers started and terminated 
throughout
the business day (3-4), but I don't think this would causes the symptoms I
am seeing.

Personally, I am tempted to switch to using dedicated connections. Our
Peoplesoft Financials and HR databases use this, so I doubt this 
application
would have a problem with it.

Does anybody have any thoughts or ideas? 

Thanks,

Aaron Sentell
Database Administrator
City of Tempe, Arizona
-------------------------------------
Office: (480) 350-8468
Pager: TempeDBA@xxxxxxxxx <mailto:TempeDBA@xxxxxxxxx> 


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: