Re: optimizer and MTS

On Tue, 26 Jun 2007 01:05:41 -0700, sybrandb <sybrandb_at_gmail.com> wrote:

>On Jun 25, 11:11 pm, wagen..._at_yahoo.com wrote:
>> Oracle 9.2.x
>> solaris 2.7
>>
>> Does MTS in anyway influence the optimizer?
>>
>> I think the answer is NO, but just wanted to get some input.
>>
>> Thanks
>> wagen
>
>The answer is NO. MTS is just a connection mechanism.
>Be aware though you can't trace a MTS connection, as there is no
>guarantee the entire session will be executed by one shared server
>process.

1. I have seen the behavior to be different once (http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/e746d860c96b6d6/beb9f27a95b08d3d?lnk=st&q=&rnum=1#beb9f27a95b08d3d), but generally the explain plans are the same, provided the other params are the same (pga_aggregate_target does not work well with MTS)
2. you can trace MTS session with session identifiers
3. contrary to my beliefe of MTS's supposedly low overhead, in my (recent) experience MTS has a pretty high overhead (judged by average run queue), as dispatchers stay constantly 'pegged' handling requests

Don't use MTS unless you absolultely have to, it's been very buggy since it's inception, and continues to be so. The latest bug I've dealt with (I think 5324905), crashed the server multiple times to the point where it required media recovery. There were at least a dozen bugs that we have encountered over the years that's been 'fixed in the next version', but next version just introduces replacement bugs. The majority of bugs had to do with poor session clean up in case the connection terminates unexpectedly.

Try to invest more into memory, or use an application server with connection pooling.

-Andrey
.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email Received on Thu Jul 05 2007 - 13:46:32 CDT