Connection pool management

Question

I'm developing a high load web service that would provide as fast response as possible. The service should keep a bunch of connections to various databases for faster performance. I'm suggesting using connection pool for that. There may be connection problems to DB because we have a lot of remote access to the DB through VPN. As I have said, service should retain connection as long as possible.

What is the connection pool management algorithm?

I have a connection string: Code:

User Id=inet;Password=somePassw0rd;Data Source=TEST11;Min Pool Size=5;Max Pool Size=15;Pooling=True

Then I simply open and close connection in my code. That's it.

At this moment everything is OK. There are five sessions on DB side. So I would kill a session to simulate connection problems. And in some cases the connection will be restored by pool manager and in some cases it won't.

If I kill all five connections they are never restored back.

How can I confiure pooling manager? Any settings for duration between checking DB connections?

I have used validate connection=true; it seems to work fine for me, but it would need some effort if reconnect to DB is needed, and therefore it would be more efficient to have an already good connection.

The component I used is devArt dotConnect for Oracle. Thanks in advance!

Dessalines

I restructured your sentences because they were very unclear. You need to put in effort to write out your question clearly rather than a lump of words and be sympathetic to allow people to read your question easily and comprehensively. – Noisome

Thank you so much for editing my question! My english is not so good as yours:) – Dessalines

does the built in connection pool not work with Oracle? It works fine with SQL Server with no effort required. – Lateritious

Yeah, it works with Oracle, but there are some issues I have described in my post. The main thing is hwo can I control connection pool manager behavior. – Dessalines

imho you should not control the behavior. The ADO.NET driver author will most likely have a lot more experience than you to create effective pooling. Don't try to solve a problem that doesn't exist. – Academe

@Academe Are you agree with me that have always open and correct connection is better for performance than validate connection every time or reopen it when user should get response but not waiting for opening DB connection? May be I don't understand that stuff enough, though. – Dessalines

What kind of problems are you experiencing? Why isn't the default connection pooling behavior enough for you? Connections will most often only get closed due to idle timeout. – Academe

The reason why I'm taking care is that a lot of DBs is hosted on remote servers in other cities and there is the long physical distance so that is why connection is not so robust. – Dessalines

Would your requirements be satisfied if you cached some of the data after retrieval or does the data need to be updated in real-time? – Thumbsdown

The data needs to be updated in real-time because there is a log of updates in DBs made hourly. – Dessalines

Answer 1

I'm not sure what you're exactly looking after, but this can be useful: pools are automatically cleared if a connection is idle for some time or closed by the server. However you can force pool clearance, using OracleConnection's ClearPool or ClearAllPools methods (these methods usually exist on most ADO.NET providers, also it's not a requirement).

Note that if you're using Oracle 11g, DotConnect also supports Oracle's Database Resident Connection Pooling (DRCP) which is presumably the best way to do pooling, since it's provided by Oracle itself (I don't have any experience on this though).

Tramp