Oracle Connection Pool Performance

[Tim Smith]

We are troubleshooting performance problems with our application.
There are 30-50 users using an web IE OLTP application against a 3-6GB
database, queries often hitting several tables (40,000-1.2M rows but
using indexes) foreign key constraints enabled). The architecture is
ASP.NET - DOT NET 1.1 which means a connection pool using OLEDB.
There are issues with DataReaders not being closed - meaning new
connections opening often - sometimes we see 90-120 open connections.

The clustered dual server is fairly new with a fast dual cpu, INTEL,
WIN2000 server over 9 disks, mostly Raid 5. However there is 1.5GB
RAM and little disk I/O activity. Perhaps the issue lies in the SQL,
lack of caching and connection pool. However there are no queries
with huge buffer gets and the oracle stats do not point to anything
specific.

Are there inefficiencies with dot net 1.0 with Oracle and many users?
Any ideas on next steps - Metalink does not have much that is NT
specific.

Final info Database is 8.0.5 and no not an option to upgrade that yet,
perhaps in 6 months but the issues are here and now

 

[Miha Markic [MVP C#]]

Hi Tim,

We are troubleshooting performance problems with our application.
There are 30-50 users using an web IE OLTP application against a 3-6GB
database, queries often hitting several tables (40,000-1.2M rows but
using indexes) foreign key constraints enabled). The architecture is
ASP.NET - DOT NET 1.1 which means a connection pool using OLEDB.
There are issues with DataReaders not being closed - meaning new
connections opening often - sometimes we see 90-120 open connections.

Can you be a bit more specific?
What does mean "open connections"?
Since you use pooling there could be delay when the connection is actually
closed.
Are you using Oracle specific data provider? I guess not because your Oracle
is 8.0.5.
Is it possible that you don't close a connection?

The clustered dual server is fairly new with a fast dual cpu, INTEL,
WIN2000 server over 9 disks, mostly Raid 5. However there is 1.5GB
RAM and little disk I/O activity. Perhaps the issue lies in the SQL,
lack of caching and connection pool. However there are no queries
with huge buffer gets and the oracle stats do not point to anything
specific.

Are there inefficiencies with dot net 1.0 with Oracle and many users?
Any ideas on next steps - Metalink does not have much that is NT
specific.

Final info Database is 8.0.5 and no not an option to upgrade that yet,
perhaps in 6 months but the issues are here and now

I don't know if it is possible but you might consider using client 8.1.7 and
oracle managed provider.