Oracle 会话和连接池的关系

Relation between Oracle session and connection pool

提问by Sathya

Let me explain the set up first.

让我先解释一下设置。

We have an oracle server running on a 2GB RAM machine. The Db instance has the init parameter "sessions" set to 160.

我们有一个运行在 2GB RAM 机器上的 oracle 服务器。Db 实例的 init 参数“sessions”设置为 160。

We have the application deployed on Websphere 6.1. The connection pool settings is Min 50 and Max 150.

我们在 Websphere 6.1 上部署了应用程序。连接池设置为 Min 50 和 Max 150。

When we run Load test on 40 Users (concurrent, using jMeter), everything goes fine. But when we increase the concurent users to Beyond 60, Oracle throws and exception that it is out of sessions.

当我们对 40 个用户（并发，使用 jMeter）运行负载测试时，一切正常。但是当我们将并发用户数增加到超过 60 时，Oracle 会抛出它超出会话的异常。

We checked the application for any connection leaks but could not find any.

我们检查了应用程序是否有任何连接泄漏，但找不到任何泄漏。

So does it mean that the concurrency of 40 is what this setup can take ? Is increasing the Oracle sessions/process the only way to obtain higher concurrency ?

那么这是否意味着 40 的并发性是这个设置可以接受的？增加 Oracle 会话/进程是获得更高并发性的唯一方法吗？

How exactly are the DB sessions and Connection in the Connection pool related ? In my understanding, the connections cannot exceed the sessions and so setting the Max Connection pool to more than sessions may not really matter. Is that correct ?

连接池中的数据库会话和连接究竟是如何相关的？根据我的理解，连接数不能超过会话数，因此将最大连接池设置为超过会话数可能并不重要。那是对的吗 ？

采纳答案by mamboking

Check out this bookon google books. It explains the differences between connections and sessions.

在谷歌图书上查看这本书。它解释了连接和会话之间的区别。

回答by DCookie

Metalink gives the following advice about the SESSIONS parameter:

Metalink 给出了以下关于 SESSIONS 参数的建议：

Recursive sessions are an essential part of the normal functioning of the RDBMS. It is impossible to identify every circumstance that would require such sessions, but in general, if a user-initiated operation requires the manipulation of data dictionary objects, then recursive sessions may get created. To take a simple example, say you create a table while logged in as some ordinary user. Behind the scenes this has to insert rows into obj$, tab$ etc which are owned by the SYS user. Since a normal user would have no privilege to insert into these objects, a recursive session is created which logs in as SYS.

Solution:

Increase the SESSIONS parameter.

Recommendation is to preserve 50% of the SESSIONS value for recursive sessions. So, for example if it is expected to have 30 client sessions open, then set the SESSIONS parameter to 60.

递归会话是 RDBMS 正常运行的重要组成部分。不可能确定需要此类会话的每种情况，但一般而言，如果用户启动的操作需要操作数据字典对象，则可能会创建递归会话。举一个简单的例子，假设您在以普通用户身份登录时创建了一个表。在幕后，这必须将行插入到 SYS 用户拥有的 obj$、tab$ 等中。由于普通用户没有权限插入这些对象，因此会创建一个递归会话，它以 SYS 身份登录。

解决方案：

增加 SESSIONS 参数。

建议为递归会话保留 50% 的 SESSIONS 值。因此，例如，如果希望打开 30 个客户端会话，则将 SESSIONS 参数设置为 60。

So, depending on what websphere and your user process are doing this could partially explain what you're seeing.

因此，根据 websphere 和您的用户进程正在执行的操作，这可以部分解释您所看到的内容。

回答by Gary Myers

My v$session contains 30 entries, 4 of which have a username (one of which is a background job).

我的 v$session 包含 30 个条目，其中 4 个具有用户名（其中一个是后台作业）。

If you've got background processes (eg batch jobs), they could be chewing up sessions.

如果您有后台进程（例如批处理作业），它们可能会占用大量会话。

But it could be that you are simply running out of memory. 2GB seems a bit low for a conneection pool of 50 sessions. Assuming Oracle 10g, you're RAM is divided into shared (SGA) and process (PGA). Say you've got 1.5GB for SGA, that leaves 500MB for all the sessions. If sessions grab 10MB each, you'll hit your limit around 50 sessions.

但也可能是您的内存不足。对于 50 个会话的连接池，2GB 似乎有点低。假设Oracle 10g，你的RAM 分为共享（SGA）和进程（PGA）。假设您有 1.5GB 的 SGA 空间，那么所有会话都有 500MB 空间。如果会话每个占用 10MB，您将达到大约 50 个会话的限制。

In reality, 1. You'll have some other 'stuff' running on the box, so won't have a full 2GB available to Oracle

实际上，1. 你会在盒子上运行一些其他的“东西”，所以不会有完整的 2GB 可用于 Oracle

1. Your SGA may be smaller or larger
2. You may be on 11g and letting Oracle allocate PGA and SGA out a single pool
3. You may be using PGA_AGGREGATE_TARGET (letting Oracle guess at the PGA settings based on the number of sessions) or setting memory limits yourself.
4. You may have some memory hungry processes that chew up stuff

2. 您的 SGA 可能更小或更大
3. 您可能在 11g 上并让 Oracle 将 PGA 和 SGA 分配给一个池
4. 您可能正在使用 PGA_AGGREGATE_TARGET（让 Oracle 根据会话数猜测 PGA 设置）或自己设置内存限制。
5. 你可能有一些需要内存的进程来咀嚼东西

PS. Does the 2GB mean you are on Windows ?

附注。2GB 是否意味着您使用的是 Windows？

回答by Eric Petroelje

Are all your connections using the same user account? If so, you might want to check to see if you have a per-user session limit for that user account.

您的所有连接都使用相同的用户帐户吗？如果是这样，您可能需要检查该用户帐户是否有每个用户的会话限制。

Also, are you licensed for more than 40 connections? (Check if you have LICENSE_MAX_SESSION set in your parameter file)

另外，您是否获得超过 40 个连接的许可？（检查您的参数文件中是否设置了 LICENSE_MAX_SESSION）

回答by Brad Bruce

The session pool is client-side driven. It doesn't know (or control) how many sessions the database will allow.

会话池是客户端驱动的。它不知道（或控制）数据库将允许多少个会话。

You should look on the server to determine the actual number of connections that are allowed and set the session pool number based on what the server will allow.

您应该查看服务器以确定允许的实际连接数，并根据服务器允许的内容设置会话池数。

Your connection pool should not use all of the connections allowed. This will let other IDs connect. If you have an application using USER_1, you'd set the connection pool to use some amount of the allowed connections, but leave enough connections for... Oh, say DBA to log in.

您的连接池不应使用所有允许的连接。这将让其他 ID 连接。如果您有一个使用 USER_1 的应用程序，您可以将连接池设置为使用一些允许的连接数，但要保留足够的连接数...哦，假设 DBA 登录。

-- Edit --
Processes are probably runing out before your connection pool maxes out.

-- 编辑 --
在您的连接池用完之前，进程可能已经用完了。

SQL> show parameter processes

NAME                                 TYPE        VALUE
--------------------------------------------------------------------------------
processes                            integer     40

This is the total # of processes allowed Now see how many are already used - many of them are background procs, you'd never think of.

这是允许的进程总数 现在看看已经使用了多少 - 其中许多是后台进程，您永远不会想到。

SQL> select count(*) from v$process;