From oracle-l-bounce@freelists.org Tue Jun 8 16:00:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i58L0YI10397 for ; Tue, 8 Jun 2004 16:00:44 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i58L0N610366 for ; Tue, 8 Jun 2004 16:00:33 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5F8D572CED8; Tue, 8 Jun 2004 15:46:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15451-64; Tue, 8 Jun 2004 15:46:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 99A6572CEC3; Tue, 8 Jun 2004 15:46:07 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Jun 2004 15:44:43 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E7C5472C5D6 for ; Tue, 8 Jun 2004 15:44:40 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15339-39 for ; Tue, 8 Jun 2004 15:44:40 -0500 (EST) Received: from mx10.radisys.com (mx10.radisys.com [206.102.10.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7F75872C8A0 for ; Tue, 8 Jun 2004 15:44:40 -0500 (EST) Received: by mx10.radisys.com (Postfix, from userid 5) id 5CAAB142DFB; Tue, 8 Jun 2004 14:03:52 -0700 (PDT) Received: from UNKNOWN(206.103.52.194), claiming to be "dcblackberry.radisys.com" via SMTP by mx10, id smtpdAAAySOCF_; Tue Jun 8 14:03:40 2004 In-Reply-To: To: oracle-l@freelists.org Subject: Re: Shared server tuning MIME-Version: 1.0 X-Mailer: Lotus Notes Release 6.5.1 January 21, 2004 Message-ID: From: Jared.Still@radisys.com Date: Tue, 8 Jun 2004 14:03:37 -0700 X-MIMETrack: Serialize by Router on DCBlackBerry/Radisys_Corporation/US(Release 6.0.3|September 26, 2003) at 06/08/2004 01:57:26 PM, Serialize complete at 06/08/2004 01:57:26 PM Content-Type: multipart/alternative; boundary="=_alternative 0073ADDD88256EAD_=" X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2292 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Jared.Still@radisys.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org --=_alternative 0073ADDD88256EAD_= Content-Type: text/plain; charset="US-ASCII" 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" Sent by: oracle-l-bounce@freelists.org 06/08/2004 11:47 AM Please respond to oracle-l@freelists.org To oracle-l@freelists.org 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@vtext.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org 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 ----------------------------------------------------------------- --=_alternative 0073ADDD88256EAD_= Content-Type: text/html; charset="US-ASCII"
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@tempe.gov>
Sent by: oracle-l-bounce@freelists.org

06/08/2004 11:47 AM
Please respond to
oracle-l@freelists.org

To
oracle-l@freelists.org
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@vtext.com <mailto:TempeDBA@vtext.com>


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@freelists.org
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
-----------------------------------------------------------------

--=_alternative 0073ADDD88256EAD_=-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org 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 -----------------------------------------------------------------