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?
4条答案
按热度按时间enyaitl31#
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.
ojsjcaue2#
一个DBMS可以同时处理许多连接，通常是数百个。但是，通常每个核心会同时处理几个查询（可能是2-3个）。
@大卫确实，单个处理器一次只能处理一个计算，但是当加载是磁盘绑定的时，它有很多空闲时间来处理其他查询，同时等待数据加载。
0aydgbwb3#
正如大卫所说，处理器在给定的时刻只能做一件事。
也就是说，你的问题不容易给予一个简单的答案。在最简单的情况下，服务器将以排队的方式处理查询（忽略诸如资源限制之类的事情）。然而，一旦第一个查询由于磁盘I/O或任何其他资源请求而不得不给予控制，第二个查询就有机会了。它很可能在第一个查询之前完成，这取决于每个查询所执行的操作。
oiopk7p54#
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.