Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: MTS tuning

Re: MTS tuning

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 15 May 2002 17:16:24 +0200
Message-ID: <clu4eu41n5hnembuksqslq25sk5drphi0b@4ax.com>


On Wed, 15 May 2002 19:16:04 +0530, "Pinto" <ucanmailme_at_india.com> wrote:

>Hello All,
>Recently we started testing our application after changing our Database from
>dedicated sever mode to MTS.
>I am not very familiar with MTS. i have collected some data by running some
>scripts that i found in metalink.
>
>MTS related parameters are set as follows
>
>mts_dispatchers (protocol=TCP)(dispatchers = 3)
>mts_max_dispatchers 5
>mts_servers 20
>mts_max_servers 40
>
>large_pool_size 83886080
>
>select name "NAME",OWNED, substr(network,1,15) "PROTOCOL", status "STATUS",
>(busy/(busy + idle)) * 100 "%TIME BUSY" from v$dispatcher
>
>NAME OWNED
>---- ----------
>PROTOCOL
>----------------------------------------------------------------------------
>------------------------
>STATUS %TIME BUSY
>---------------- ----------
>D000 42
>(ADDRESS=(PROTO
>RECEIVE 47.8563578
>
>D001 41
>(ADDRESS=(PROTO
>ACCEPT 46.906154
>
>D002 40
>(ADDRESS=(PROTO
>WAIT 47.5323848
>
>
>select name "NAME", paddr,requests,(busy/(busy + idle)) * 100 "%TIME
>BUSY",status from v$shared_server
>
>NAME PADDR REQUESTS %TIME BUSY STATUS
>---- -------- ---------- ---------- --
>--------------
>S000 06E521BC 6029664 25.1842041 EXEC
>S001 06E5280C 7271603 18.2575696 WAIT(COMMON)
>S002 06E52B34 6511361 17.1122631 WAIT(COMMON)
>S003 06E52E5C 4223594 12.2070631 WAIT(COMMON)
>S004 06E53184 2352045 9.67785749 WAIT(COMMON)
>S005 06E534AC 826153 4.74286627 WAIT(COMMON)
>S006 06E537D4 376053 2.83275286 WAIT(COMMON)
>S007 06E53AFC 159785 1.37010382 WAIT(COMMON)
>S008 06E53E24 64173 .584247999 WAIT(COMMON)
>S009 06E5414C 26253 .320127824 WAIT(COMMON)
>S010 06E54474 16667 .217846538 WAIT(COMMON)
>
>S011 06E5479C 7025 .140089365 WAIT(COMMON)
>S012 06E54AC4 3537 .101109164 WAIT(COMMON)
>S013 06E54DEC 2137 .080097132 WAIT(COMMON)
>S014 06E55114 1685 .068811734
>WAIT(COMMON)
>S015 06E5543C 1367 .060347571
>WAIT(COMMON)
>S016 06E55764 947 .051987931
>WAIT(COMMON)
>S017 06E55A8C 545 .047238902
>WAIT(COMMON)
>S018 06E55DB4 424 .044451358
>WAIT(COMMON)
>S019 06E560DC 488 .043019874
>WAIT(COMMON)
>
>
>my question is, why aren't all the server process equally busy? looks like,
>the first 5 server processs are very busy and the others are not. is there
>anyway where we can ensure that all the server process is equally loaded. or
>is this the expected behaviour?
>
>select maximum_connections "MAX CONN",servers_started "STARTED",
>servers_terminated "TERMINATED",servers_highwater "HIGHWATER" from v$mts
> MAX CONN STARTED TERMINATED HIGHWATER
> -------------- ----------- ----------------- ---------------
> 168 0 0
>20
>
>
>
>we have also found that, after switching to MTS, our CPU usage has almost
>doubled! and certain applications have slowed down. is it expected or am i
>missing out something.
>
>
>also
>
> 1 select p.spid thread, s.username,
> 2 decode(nvl(p.background,0),1,bg.description, s.program )
>program,
> 3 ss.value/100 CPU,physical_reads disk_io from v$process p,
>v$session s,
> 4 v$sesstat ss, v$sess_io si, v$bgprocess bg where
>s.paddr=p.addr
> 5 and ss.sid=s.sid and ss.statistic#=12 and si.sid=s.sid
> 6* and bg.paddr(+)=p.addr order by ss.value desc
>20:25:50 SQL> /
>
>THREAD USERNAME PROGRAM CPU
>--------- ------------------------------ -------------------- ----------
> DISK_IO
>----------
>2028 NETMSQLUSERS ans.exe 69.51
> 16869
>
>2028 DBO Bctsched.exe 65.46
> 2008
>
>1512 DBO Bctsched.exe 60.47
> 163
>
>2028 DBO CVCache.exe 32.12
> 0
>
>most of the cpu activity is on a single thread (2028).
>is there anyway we can distribute these loads??
>
>SYSTEM CONFIG:
>
>Pentium III dual processor with 1 GB RAM
>Oracle 8.1.7
>Win 2000
>
>thanks and regards,
>Pinto.
>
>
>
>
>
>
>

>my question is, why aren't all the server process equally busy? looks like,
>the first 5 server processs are very busy and the others are not. is there
>anyway where we can ensure that all the server process is equally loaded. or
>is this the expected behaviour?
>

Your configuration doesn't seem to need more than 5 servers. Remember extra servers are being added on demand. Your mts_servers parameter is already outrageously high, given the fact that MTS is intended to *reduce* the number of active connections.

The query you use to identify cpu usage simply must be wrong. No thread can run multiple executables at the same time, which is what your output states.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed May 15 2002 - 10:16:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US