Re: MTS Oracle_XA performance tuning

The typical behavior is to have up to 3 sessions per in-call object that is participating in a distributed (XA) transaction. If your objects are marked either 'Requires ...' or 'Requires New ...' then they will utilize more than one session while in-call; you may also observe INACTIVE sessions that remain long after the calls are complete.

select status, count(*) from v$session
group by status;

This should show a number of ACTIVE, CACHED & INACTIVE sessions, with the inactive typically being much larger than the sum of active + cached when under heavy loading by multiple client applications.

If your components are marked 'Supports ...' or 'No Transactions' and are directly instantiated by a client application, then no txn will be active and the objects will require but a single connection. You will not observe a significant creep in sessions when not using distributed transactions due to pooling of either OLE DB sessions or ODBC connections by ADO.

Mike

jcora_at_my-deja.com wrote in message <83m50f$foj$1_at_nnrp1.deja.com>...
>I work for a software development firm which develops a warehouse
>management system. The system is setup in a 3-tier mode using
>Microsoft Transaction Server as the Transaction Monitor (middleware).
>Its database access model consists of using ADO classes which make use
>of the Oracle_XA interface to access the Oracle Resource Manager.
> One of the operations we perform, is 'UPDATE'-intensive (about 1000
>updates)and we've noticed that during the operation (By way of 'select
>count(*) from v$session') there is only a total of 10 sessions
>connected to the database. The memory and CPU usages stay at about 50%
>and there is no activity on the disk I/O end either. Which leads me to
>believe that the problem might be in sequential access to the
>database.
> Is there a way to bump up the # of connections allowable in
>Oracle? My license_max_sessions = 0 (infinite # of sessions). But was
>wondering if there might be another issue as well. I've run the estat
>bstat tests on the db and it seems to be well tuned. Any suggestions?
>
>
>Thanks
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Dec 23 1999 - 22:54:05 CST