5

We are maintaining a database server that serve a large number of clients. Each client typically running several client-applications. The total number of connections to the database server (Oracle 9i) is reaching 800 connections on peak load. The windows 2003 server is starting to run out of memory. We are now planning to move to 64bit Windows in order to gain higher memory capacity. As a developer I suggest moving to multi-tier architecture with connection pooling, which I believe is a natural solution to this problem. However, in order to support my idea, I want the information on:

  1. what exactly is the typical number of connections allowed for Oracle database ?
  2. What is the problem when the number connections is too high ? Too much memory comsumption ? or too many sockets opened ? or too many context switching between threads ?
  3. To be a little bit specific, how could Oracle Forms application scale to thousand of users without facing this problem ? Shall Oracle RAC applied to this case ?

I'm sure the answer to this question should depend on quite a number of factors, like the exact spec of the hardware being used. I'm expecting a rough estimation or some experience from the real world.

2 Answers 2

7

In order to handle a database connection, a separate Oracle process is required, which may require up to 2 MB of memory. Normally an Oracle installation limits the number of concurrent connections based on the available memory and CPU configuration.

For example, let's say you have 50 customer accounts on the database with a maximum of 100 concurrent connections per account. If every account in average uses only 25% = 25 connections, the total average number would be 1250 connections. That means 2500 MB only for storing connection specific data. There is also processor time needed for context switching.

By setting limits on concurrent connections, you can ensure your transactions get through, and that some very active customer accounts don’t start to block your database server's resources. The number of connections is limited by the sessions parameter and your licensing. The sessions parameter controls the total number of connections including the Oracle background processes.

Adding more RAM to the server using a 64-bit Windows platform is a good idea, since you don’t have to spend a lot, even adding 16 GB of memory.

For detailed information of how configurations perform on realistic workload scenarios, read the Oracle E-Business Suite Standard Benchmark. Typically they have a large number of connections.

1
  • Well, you are suggesting that it's fine to have thousands of connections ? That's interesting and is exactly the information I looking for. The EBiz benchmark you mention can be highly useful had I found it year ago. Unfortunately, we are currently moving from EBiz to SAP.
    – Sake
    May 29, 2009 at 13:34
0

Oracle on Windows opens user threads, not processes, afaik. (This is not good when an user connection crashes as it takes them all with.)

This means that you're limited by memory; few MB of session overhead for thread and PGA per user + the context and SGA which depend on what you are doing. For light work (== users executing the same simple queries over and over again) you only need to count sessions.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .