RE: Database Performance Question

Srini,

If you have 400 concurrent users (2000 total users, with about 400 concurrent sessions typical for the busy time of the day), then you should probably be using MTS instead of dedicated connections. Using Microsoft pooling in COM may work out, but please check with Oracle support on whether you are encountering a bug. What version of Oracle are you on? This will determine if you may be encountering the bug. If you are encoutering the bug, the COM is not closing down connections quickly enough, running your system out of resources. You are probably short on memory to start with if you are running that many users dedicated on an (NT? WIN2K?) box.

This could also very well be a problem with the size of the data being returned, but you will need to eliminate networking/COM connection issues as well as the problems in the database. The suggestion made to look at V$SYSTEM_EVENT is right on the money. That should determine where the main problem is: sorts, I/O, memory, etc.

As you can tell from the replies, this kind of thing is a little difficult to diagnose without getting a lot of information up front.

Regards,
Chris Gait

On 29 Jan 2002, at 9:35, SRAJENDRAN_at_nlfs.com wrote:

Date sent:              Tue, 29 Jan 2002 09:35:41 -0800
To:                     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Send reply to:          ORACLE-L_at_fatcity.com
Organization:           Fat City Network Services, San Diego, California

> Kirti:
> Thanks for the insight.
>
> Here are my Answers:
>
> 1. I feel database is the problem because when only a subset of the total
> data is used the performance is excellent (with one tenth of the total
> data). The no. of users are the same. Only amount data that is retrieved
> is enormous (almost 10 times) when we have the performance problem.
>
> 2. Partioning may/may not help as it still has to traverse through all of
> the partitions to get the data required in order to satisfy the join
> condition. May improve response time but not significantly.
>
> 3. I will look into this as well.
>
> 4. Connections on the database side are DEDICATED and on the web side they
> are shared.(Using Microsoft COM objects, have certain set of database
> connections as POOL that are shared by different users.)
>
> Please let me know what you think...
>
> Srini
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: cjgait_at_earthlink.net

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: ListGuru_at_fatcity.com (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).