getting request timeout exceptions from a asp.net application using the Oracle client with multiple schemas

Question

We have an asp.net application that uses the Oracle client. In the connection string, we specify the 3 basic attributes: data source, User Id, and Password. We are using connection pooling. Our data resides in 2 different Oracle schemas. Most of the requests are for data from Schema1. The timeouts are coming from calls for data from schema2

Is it possible that the connections are implicitly tied to a schema?

That would explain a lot. Since most of the calls are for data from schema1, the pool gets filled with connections that are only good for schema1. Then a call comes for data from schema2 and none of the connections in the pool can satisfy the request and we get the timeout.

Thanks for any help, - Greg

Answer 1

An Oracle connection is always tied to a schema, the default is the user's own schema. If you use two different connection strings, you should already have two separate pools, so I suspect it may be that you have hit the maximum sessions overall for the database.

What is your "SESSIONS" instance parameter. Compare that to your "Max Pool Size" setting in the connection. Default I think is 100 for 11g, but you didn't mention which version. You may need to bump PROCESSES up, or lower Max Pool Size.

You should query V$SESSION at the time you see these timeouts to see how many actual Oracle sessions.

select username, count(1) from v$session group by username;