I suspect that if you are getting 4030s with 50 concurrent users that you will get NO benefit from MTS, in fact it will likely cause just the problems you are seeing and you will start to get 4030s sooner than you would have before.
MTS is not appropriate as a tool to address your 4030 errors if the sessions are not inactive for more than a few seconds. If the concurrent sessions are very active (check v$session's last_call_et field) and you are still running into 4030s than MTS will not help you, and can actually cause a performance hit (CPU) that is scary. The key thing to remember is that MTS provides SCALABILITY, performance is indirectly affected if at all. If you are running into 4030s there are a number of other approaches you can take as well. Application tuning is the best place to start, but more often then not, not possible. Then go to the obvious (that others have mentioned) like using statspack to get a look at the usage of your buffer cache. In your case you may have to reduce the size of your SGA to allow for more user space. Don't fall for the buffer cache hit ratio needs to be x%. If you are in an application env where most blocks are rarely seen in more than one query then you need a different approach to buffer cache tuning than hit %. This is a book in and of itself, but fairly intuitive. Given that you are stuck with your current application, the very first thing you also will want to look at is the Orastack utility to reduce the memory usage of each server process' stack. I have seen DBAs in your situation have their 4030s resolved with this alone. The other is upgrading your OS (or properly setting it) to NT EE or Win2k AS. This will allow you to use a 3gb process size. If you haven't done this it will surely fix your memory shortage (at least until you get another 50 users :)). You can also scale out by adding more nodes with OPS, although I'd strongly suggest that you move to 9i before doing this! Finally, you can use OPS/RAC on a single box, a bit of a fancy trick, but it allows you to have multiple instances if you have the system memory available. Ramble, ramble, ramble, for more info you can come to my presenation at OpenWorld (or just send more info/questions!) Regards, Michael Sale Author: Oracle9i for Windows(R) 2000 Tips & Techniques http://www.amazon.com/exec/obidos/ASIN/0072194626 -----Original Message----- Karadeniz Sent: Thursday, June 27, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Michael , I have tried all of them , but still performance is very bad. When switch to dedicated mode I get ora:04030.. Have you ever switched to MTS and seen the same performance , I wonder if you are sure about MTS performance is good. What ever I did did not give the same performance as dedicated connections even not approached. Bunyamin ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 4:53 PM > You really don't need MTS with no more than 50 concurrent users unless > you are running into ora-4030s or other memory problems. > > When you say performance is a problem, have you check OS swapping of memory to disk? Your SGA might be too big (with AS and the 3GB switch no bigger than 3GIG, without the 3GB switch only 2GB) and end up swapping out to disk. > > You also need to make sure that only OLTP users (users with short > txns, NOT long running batch jobs, big updates, etc) are connecting via MTS. All batch/DSS type users should be connecting via dedicated server. You can have them specify this in their tnsnames.ora. > > That said, your MTS config is WAY out of whack given the number of concurrent users: > > You only need ONE dispatcher to start with. Change dispatchers to 1 > unless you have multiple NICs you are working off of for a particular segmentation of users. (50 dispatchers would be appropriate for about 12,000 Concurrent users....) > > If your total number of concurrent OLTP users is truly 50 set your min servers to 60. Max servers is fine. > > You should also set your large pool instead of a massive shared pool, > all kinds of reason for doing this!! > > If you have a chance go to borders or barnes and noble, sit down and > read chapter 7 (Oracle Connectivity) of my book "Oracle9i for Windows 2000 Tips & Techniques". It covers the setup, monitoring and basic tuning of MTS. > > I hope this helps, > > Mike Sale > > > > > > > mts_max_servers=750 > > mts_min_servers=100 > > dispatchers=50 > > users total 750 > > users concurrent at most 50 > > > > > > ----- Original Message ----- > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Wednesday, June 26, 2002 9:58 PM > > > > > >> Bunyamin, > >> > >> What is MTS_MAX_SERVERS & MTS_MIN_SERVERS set to? Also what is > >> the total > >> number of users connecting to the database? > >> > >> Dick Goulet > >> > >> ____________________Reply Separator____________________ > >> Author: "Bunyamin Karadeniz" <[EMAIL PROTECTED]> > >> Date: 6/26/2002 8:49 AM > >> > >> I have changed my 8.1.7.3.2 database to MTS on win2000. But > >> performance is too bad. > >> I have 800 MB of shared pool and not setted large_pool_size. > >> 50 dispathers, 100 processes and 750 max processes. > >> > >> What can I do ? Have you got an idea. How can I check the mts > > performance? > >> > >> Bunyamin > >> > >> > >> > >> > >> > >> > >> > >> > >> I have changed my 8.1.7.3.2 database to MTS > > on > >> win2000. But performance is too bad. > >> I have 800 MB of shared pool and not setted large_pool_size. > >> 50 dispathers, 100 processes and 750 max > > processes. > >> > >> > >> > >> What can I do ? Have you got an idea. > > How can > >> > >> I check the mts performance? > >> > >> Bunyamin > >> > >> -- > >> Please see the official ORACLE-L FAQ: http://www.orafaq.com > >> -- > >> Author: > >> INET: [EMAIL PROTECTED] > >> > >> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > >> San Diego, California -- Public Internet access / Mailing > >> Lists > >> -------------------------------------------------------------------- > >> To REMOVE yourself from this mailing list, send an E-Mail message > >> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > >> the message BODY, include a line containing: UNSUB ORACLE-L (or the > >> name of mailing list you want to be removed from). You may also > >> send the HELP command for other information (like subscribing). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Bunyamin Karadeniz > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing > > Lists > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L (or the > > name of mailing list you want to be removed from). You may also > > send the HELP command for other information (like subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: MICHAEL.SALE > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bunyamin Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Michael P Sale INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
