Oracle MTS (multi-threaded server) Operation - Applicable in a 3 Tier Environment?

[Alzhy]

MTS is supposedly a way to both increase an Oracle instance performance and resource optimization (memory conserving).

On our old Solaris Environment we've a 2 tier environment -- Desktop Apps connecting directly to a large DB instance which runs as MTS. This has resulted in excellent performance and scalability over the years and we've really not been bothered with memory issues. 12cpusx400Mhz + 10GB of memory + 2500 Users.

On our 11.11 environment - 3 Tier. Client machines connect to about 6 application servers (Weblogic). Those 6 application servers then connect to a large DB instance via direct server connections. Those connections between the Weblogic App servers and the instance can vary widely. The DB server is 16x1Ghz cpux x 80 GB Memory (yes 80 GB memory) because of those dedeicated server conections averaging about 100MB each.. New App Servers are planned to be added so it means more memory needing to be purchased. The users total only about 3000.

My question then is -- on our 3Tier HP-UX 11i environment - would MTS apply for the purpose of perhaps reducing our memory needs on our DB server?

Thanks.

[TwoProc]

Hello once again Nelson,

I can't speak directly for your Weblogic Application, as I've not used or administered it. I do have lots o' experience with one type of three tier applications on Oracle,however.

Oracle Applications specifically advise against using MTS on their own Applications because most find the latency unnacceptable. Several years back, I turned it on anyways (I had read good things about it) on some servers loaded with test data and upon which we had benchmarking software (Mercury). We did notice some ill effects, so we turned it off. It adversely affected overall throughput measurably, but the biggest notice was by users (including myself) who use the screen interfaces (click a button, highlight a field which should populate a list-of-values for instance) and wait for ... a long time for the screens to come back with data. Apparently, my request on screen was being pooled up with other stuff, so I was waiting for the pooling to finish and get my data. The caveat to this was, we really had no experts at tuning MTS, and beyond twiddling a few things (minimum number of connections kept alive in the pool, initial number of connections, etc), I couldn't seem to make it any better. So, it was turned off. When I asked someone with Oracle Consulting about this, he admitted that he had tried it with Oracle Apps in the past also, and said that his experience was the same.

Of course, things change, and there are newer databases out there than what I tested on (8i), I just wanted to give you my experiences with it.

[Jean-Luc Oudart]

Nelson,

MTS was/is a quick fix for environment where memory was a bottleneck. The downside is I believe it was a round robin distribution for the connections and therefore not efficient for some people.

In a 3-tier environment the application server will/should spool the connections and therefore MTS is completely irrelevant as you will add an overhead.

I believe tuning Weblogic connections to the database is key to your issue. Adding more memory should be last resort but I don't know your application.

I would not go near MTS as you may "solve" (or more likely hide) your memory pb but create a performance one !

Regards
Jean-Luc

[Alzhy]

Hmm.

Okay, so App servers like Bea, Oracle IAS, etc.. are better off w/ dedicated server connections to the DB instance instead of MTS.

I will now then completely drop this request at our DBAs looking at MTS and will instead plan on growing our server's memory....

Thanks.