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

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开发者_StackOverflow中文版 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?

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.

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.

As @David states, a processor can do only one thing at a given instant.

That said, your question is not easy to give a simple answer for. The server is going to handle the queries in a queued fashion in it's simplest case (ignoring such things as resource limits). However, as soon as the first query has to give up control due to disk I/O or any other resource request, the second query gets a chance. It very well may complete before the first one, depending on what each query is doing.

The queries will be executed concurrently, usually. The number of connections that can be handled depends on the server, the listener configuration, and the processes and sessions initialisation parameters. If you try to open too may connections you'll get an error.

If you're using connection pooling then each of those pooled connections is using up one of the database instance's sessions and one of its processes. (Someone may correct me on the difference between dedicated and shared listeners, but that's roughly right). Each request (query) over a pooled connection uses the already set-up connection, so it doesn't need a new TCP/IP socket and doesn't have the overhead associated with socket/session creation - which is one of the reasons you use pooling in the first place.

Whether you're using a pooled or standalone connection, the process that executes your query is independent of any others and doesn't wait for any other queries to finish. If you're doing updates then that's not necessarily true - you may wait for another process to finish its own update - but if you're just querying then that isn't a factor.

You may get contention for resources, so two queries running at the same time might take slightly longer than either of them takes to run on their own, but they're still both running at the same time.