SQLDeveloper 使用超过 100MB 的 PGA
也许这很正常，但在我的 Oracle 11g 数据库中，我发现使用 Oracle SQL Developer 的程序员经常消耗超过 100MB 的 UGA 和 PGA 内存组合。我想知道这是否正常以及可以采取什么措施。我们的数据库运行在 32 位版本的 Windows 2008 上，因此内存限制越来越受到关注。我使用以下查询来显示内存使用情况：
SELECT e.SID, e.username, e.status, b.PGA_MEMORY
FROM v$session e
LEFT JOIN
(select y.SID, y.value pga,
TO_CHAR(ROUND(y.value/1024/1024),99999999) || ' MB' PGA_MEMORY
from v$sesstat y, v$statname z
where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b
ON e.sid=b.sid
WHERE (PGA)/1024/1024 > 20
ORDER BY 4 DESC;
似乎每次在 SQLDeveloper 中打开表时资源使用量都会上升，但即使关闭该表，内存也不会消失。如果表在打开时进行排序，问题会更严重，因为这似乎会使用更多内存。我理解这将如何在排序时使用内存，甚至在它仍然打开时使用内存，但在关闭后使用内存对我来说似乎是错误的。谁能证实这一点吗？
更新： 我发现我的数字由于不理解 专用服务器模式下UGA存储在PGA中。这使得数字低于原来，但问题仍然是 SQL Developer 似乎使用了过多的 PGA。
Perhaps this is normal, but in my Oracle 11g database I am seeing programmers using Oracle's SQL Developer regularly consume more than 100MB of combined UGA and PGA memory. I'd like to know if this is normal and what can be done about it. Our database is on the 32 bit version of Windows 2008, so memory limitations are becoming an increasing concern. I am using the following query to show the memory usage:
SELECT e.SID, e.username, e.status, b.PGA_MEMORY
FROM v$session e
LEFT JOIN
(select y.SID, y.value pga,
TO_CHAR(ROUND(y.value/1024/1024),99999999) || ' MB' PGA_MEMORY
from v$sesstat y, v$statname z
where y.STATISTIC# = z.STATISTIC# and NAME = 'session pga memory') b
ON e.sid=b.sid
WHERE (PGA)/1024/1024 > 20
ORDER BY 4 DESC;
It seems that the resource usage goes up any time a table is opened in SQLDeveloper, but even when it is closed the memory does not go away. The problem is worse if the table is sorted while it was open as that seems to use even more memory. I understand how this would use memory while it is sorting, and perhaps even while it is still open, but to use memory after it is closed seems wrong to me. Can anyone confirm this?
Update:
I discovered that my numbers were off due to not understanding that the UGA is stored in the PGA under dedicated server mode. This makes the numbers lower than they were, but the problem still remains that SQL Developer seems to use excessive PGA.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
也许 SQL Developer 没有关闭它打开的游标。
因此，如果您运行一个对 100 万行进行排序的查询，并且 SQL Developer 仅从那里获取前 20 行，那么如果您想向下滚动并获取更多行，它需要保持游标打开。
因此，只要游标打开并且尚未到达 EOF（取结束），就需要保留与游标排序区域关联的一些 PGA 内存（称为保留排序区域）。
选择一个会话并运行：
这应该显示某些游标是否仍然以其内存保持打开状态......
Perhaps SQL Developer doesn't close the cursors it had opened.
So if you run a query which sorts a million rows and SQL Developer fetches only first 20 rows from there, it needs to keep the cursor open should you want to scroll down and fetch more.
So, it needs to keep some of the PGA memory associated with the cursor's sort area still allocated (it's called retained sort area) as long as the cursor is open and hasn't reached EOF (end-of-fetch).
Pick a session and run:
This should show whether some cursors are still kept open with their memory...
您正在使用自动内存管理吗？如果是的话，我就不用担心使用的 PGA 内存了。
请参阅文档：
自动内存管理： http:// /download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11011
MEMORY_TARGET：http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams133.htm
有吗您使用 32 位 Oracle 的原因是什么？最新的硬件支持 64 位。
Are you using Automatic Memory Management? If yes, I would not worry about the PGA memory used.
See docs:
Automatic Memory Management: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/memory003.htm#ADMIN11011
MEMORY_TARGET: http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/initparams133.htm
Is there a reason you are using 32 bit Oracle? Most recent hardware supports 64 bit.
Oracle，尤其是 AMM，将使用您提供的机器上的每一块内存。如果没有理由取消分配内存，它就不会这样做。存储空间也是如此：如果删除 20 GB 的用户数据，该空间不会返回给操作系统。 Oracle 将保留它，除非您显式压缩表空间。
我相信一个简单的测试就可以消除您的担忧。如果它是 32 位，并且每个 SQL Developer 会话使用 100MB 以上的 RAM，那么您只需要打开几百个会话即可导致内存不足问题……如果确实存在的话。
Oracle, especially with AMM, will use every bit of memory on the machine you give it. If it doesn't have a reason to de-allocate memory it will not do so. It is the same with storage space: if you delete 20 GB of user data that space is not returned to the OS. Oracle will hold on to it unless you explicitly compact the tablespaces.
I believe a simple test should relieve your concerns. If it's 32 bit, and each SQL Developer session is using 100MB+ of RAM, then you'd only need a few hundred sessions open to cause a low-memory problem...if there really is one.