RE: interpret v$mts

  • From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 2 Jun 2004 10:12:15 -0400

Really, the need for MTS is going away.  It was designed for connection
pooling, but all of the application software vendors now provide connection
pooling with the app server.  And it does it better than MTS ever did.

Given a choice, I always choose app-server connection pooling over MTS.  It
is just too hard to tune.

Tom Mercadante
Oracle Certified Professional


-----Original Message-----
From: MacGregor, Ian A. [mailto:ian@xxxxxxxxxxxxxxxxx] 
Sent: Wednesday, June 02, 2004 10:03 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: interpret v$mts


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@xxxxxxxxxxxxxxxxx

        

-----Original Message-----
From: Jeroen van Sluisdam [mailto:jeroen.van.sluisdam@xxxxxxxxxx] 
Sent: Monday, May 31, 2004 11:02 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxx]
Verzonden: Friday, May 28, 2004 11:42 PM
Aan: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: