How many open connections can a database server have at a time?

Question

I am trying to understand database resident connection pooling with Oracle 11g. Now one question I have on my mind is: If I have 1 database server in the backend. How many concurrent requests can my database server handle at a given moment, would it be one or would it be more than one at the same time?

To para-phrase my question: If Client1 requests a select query of say top 100 results, and Client2 requests a select of something else, does the server handle both requests at the same moment, or will the server finish the first request before handling the next request?

Answer 1

Per the docs here: http://download.oracle.com/docs/cd/E11882_01/network.112/e10836/listenercfg.htm

this is specific to the platform. (This would also vary by database system, but you mentioned Oracle 11g, so that's what I answered specifically.)

Note:

The default number of concurrent connection requests is operating system-specific. The defaults for TCP/IP on the Linux operating system and Microsoft Windows follow:

◦Linux operating system: 128

◦Microsoft Windows XP Professional SP2: 10

◦Microsoft Windows 2003 Server Enterprise Edition: 200

For other databases, you can always google "Maximum Concurrent Connections (insert DB type here)"

And in reality technically, a single processor can only handle a single calculation at a time, so in reality, when you ask "At the same moment" technically the answer is no.

Threading may make it LOOK like they are happening at the same moment, but likely they are not. Threading, in conjunction with computers powerful enough to do things very quicly makes things appear like they are happening at the same time by handling the individiual tasks, but in reality, it's not. But that's a bigger topic than can be covered here.

Answer 2

A DBMS can handle many connection simultaneously, typically in the hundreds. However normally a few queries (could be 2-3) per core will proceed at the same time.

@David it's true that a single processor can handle only a single calculation at a time, but when the load is disk bound, it has a lot of spare time to process other queries, while waiting for the data to be loaded.