Oracle会话和连接池之间的关系

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个用户上运行Load test(并发,使用jMeter)时,一切都很顺利。但是当我们将concurent用户增加到Beyond 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 ?

连接池中的数据库会话和连接到底有何关联?根据我的理解,连接不能超过会话,因此将Max Connection池设置为多于会话可能并不重要。那是对的吗 ?

5 个解决方案

#1

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

在谷歌书籍上查看这本书。它解释了连接和会话之间的差异。

#2

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.

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

Solution:

Increase the SESSIONS parameter.

增加SESSIONS参数。

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.

建议是为递归会话保留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和您的用户进程所做的事情,这可以部分解释您所看到的内容。

#3

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)

#4

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;

#5

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)。假设您有15GB的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内容

Your SGA may be smaller or larger

您的SGA可能更小或更大

You may be on 11g and letting Oracle allocate PGA and SGA out a single pool

您可能使用11g并让Oracle将PGA和SGA分配到单个池中

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.

您可能正在使用PGA_AGGREGATE_TARGET(让Oracle根据会话数量猜测PGA设置)或自己设置内存限制。

You may have some memory hungry processes that chew up stuff

你可能有一些内存饥渴的过程会咀嚼东西

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

PS。 2GB是否意味着您使用的是Windows?

#1