From oracle-l-bounce@freelists.org Wed Jun 2 09:00:56 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i52E0fO14759 for ; Wed, 2 Jun 2004 09:00:51 -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 i52E0Q614712 for ; Wed, 2 Jun 2004 09:00:37 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 87BB172C7F6; Wed, 2 Jun 2004 08:46:50 -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 13942-68; Wed, 2 Jun 2004 08:46:50 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BC96E72C7F1; Wed, 2 Jun 2004 08:46:49 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 02 Jun 2004 08:45:17 -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 60A5B72C2BE for ; Wed, 2 Jun 2004 08:45:16 -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 13799-21 for ; Wed, 2 Jun 2004 08:45:14 -0500 (EST) Received: from mailgate04.slac.stanford.edu (mailgate04.slac.stanford.edu [134.79.18.85]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 28B1072C30A for ; Wed, 2 Jun 2004 08:45:14 -0500 (EST) Received: from ATREUS.win.slac.stanford.edu (atreus.slac.stanford.edu [134.79.16.83]) by mailgate04.slac.stanford.edu (8.12.10/8.12.10) with ESMTP id i52E3LQO019818 for ; Wed, 2 Jun 2004 07:03:26 -0700 (PDT) (envelope-from ian@slac.stanford.edu) Received: by atreus.slac.stanford.edu with Internet Mail Service (5.5.2657.72) id <29N3PTW2>; Wed, 2 Jun 2004 07:01:37 -0700 Message-ID: <26E3EC48949D134C94A1574B2C89466113A8E0@exchange2.slac.stanford.edu> From: "MacGregor, Ian A." To: "'oracle-l@freelists.org'" Subject: RE: interpret v$mts Date: Wed, 2 Jun 2004 07:03:20 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1761 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ian@slac.stanford.edu Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org It would be helpful to know what is slow. Is it a query? Is it DML? Has the operation been traced. One cannot reduce all performance problems in an MTS environment to the need for more shared servers. There is the possibility that the system is not suitable for MTS at all. MTS is meant for OLTP systems running properly tuned queries with small return sets, ideally one row. Other queries can tie-up shared servers for long periods of time, resulting in the need for more of them. If this is what's going on the choice to use MTS over dedicated servers needs to be re-examined. You can also look at having two connect strings, one for true OLTP operations and another with "server=dedicated" for your reporting queries. MTS is to be avoided unless absolutely!! necessary. Has the number of simultaneous connetions to be supported been dtermined. Has the private memory needs for a shadow process been examined. Is the SGA properly tuned? Ian MacGregor Stanford Linear Accelerator Center ian@slac.stanford.edu -----Original Message----- From: Jeroen van Sluisdam [mailto:jeroen.van.sluisdam@vrijuit.nl] Sent: Monday, May 31, 2004 11:02 PM To: 'oracle-l@freelists.org' Subject: RE: interpret v$mts Hi, Just looking at these statistics is not enough Is the problem in the dispatchers or the servers? Try monitoring busy rate and available servers in this period. Anything known about the scope of this problem all users concerned? Regards, Jeroen My set of sql to show mts info set term off set feedb off head on col network heading 'Protocol' format A50 justify c col busy_rate heading 'Busy Rate %' format 990.0 justify right set term on PROMPT Network statistics Prompt Prompt Busy rate should not be more then 50% select network, sum(busy)*100 / (sum(busy) + sum(idle)) busy_rate from v$dispatcher group by network / set head off PROMPT Prompt Prompt There should be servers and dispatchers waiting for new requests: select 'Dispatchers ' ||status, count(STATUS) from v$dispatcher group by status / select 'Shared servers:'||status,count(status) from v$shared_server group by status / set head on select * from v$mts; exit -----Oorspronkelijk bericht----- Van: Barbara Baker [mailto:barbarabbaker@yahoo.com] Verzonden: Friday, May 28, 2004 11:42 PM Aan: oracle-l@freelists.org Onderwerp: interpret v$mts Dick (if you're out there) or someone who understands this mts stuff: We're still having problems with slowness. I know you told me to just keep adding servers till the screaming stopped. I didn't listen too carefully. . . I'm wondering if you can tell me if I'm interepreting this correctly. mts_servers is set to 30. At 10:09 this morning, my servers_highwater was 34. Here's select * from v$mts at 10:09 MAXIMUM_CONNECTIONS : 894 MAXIMUM_SESSIONS : 893 SERVERS_STARTED : 4 SERVERS_TERMINATED : 4 SERVERS_HIGHWATER : 34 ----------------- at 10:49, looked like this: MAXIMUM_CONNECTIONS : 936 MAXIMUM_SESSIONS : 936 SERVERS_STARTED : 37 SERVERS_TERMINATED : 37 SERVERS_HIGHWATER : 52 ----------------- So does that mean that in 40 minutes, it started 33 servers? If so, isn't that, like, obscene?? I assume there's overhead associated with starting up these servers. Should I be setting mts_servers to something extremely high (like around 50)? It was within this 40 minutes that we saw extreme slowness. I don't know if I'm looking cause or effect, but it seems pretty strange to me that we needed an additional 30 servers in a 40 minute period. Thanks for any advice on this one. Barb __________________________________ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------