oracle PL/SQL - 检查内存泄漏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4137893/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL - check for memory leaks?
提问by FrustratedWithFormsDesigner
I have some PL/SQL code that I think might have a memory leak. Everytime I run it it seems to run slower and slower than the time before, even though now I am decreasing the input size. The code that I'm suspicious of is populating an array from a cursor using bulk-collect, something like this
我有一些我认为可能存在内存泄漏的 PL/SQL 代码。每次我运行它时，它似乎都比以前运行得越来越慢，即使现在我正在减少输入大小。我怀疑的代码是使用批量收集从游标填充数组，就像这样
open c_myCursor(in_key);
fetch c_myCursor bulk collect into io_Array; /*io_array is a parameter, declared as in out nocopy */
close c_myCursor;
I'm not sure how to check to see what's causing this slowdown. I know there are some tables in Oracle that track this kind of memory usage, but I'm not sure if it's possible to look at those tables and find my way back to something useful about what my code is doing.
我不确定如何检查导致这种放缓的原因。我知道 Oracle 中有一些表可以跟踪这种内存使用情况，但我不确定是否可以查看这些表并找到有关我的代码正在执行的操作的有用信息。
Also, I tried logging out the session and logging back in after about 10-15 minutes, still very slow.
此外，我尝试注销会话并在大约 10-15 分钟后重新登录，仍然很慢。
Oracle version is 10.2
Oracle 版本是 10.2
So it turns out there wasother database activity. The DBA decided to run some large insert and update jobs at about the same time I started changing and testing code. I suspected my code was the root cause because I hadn't been told about the other jobs running (and I only heard about this other job after it completely froze everything and all the other devs got annoyed). That was probably why my code kept getting slower and slower.
因此，原来在那里是其他数据库活动。大约在我开始更改和测试代码的同时，DBA 决定运行一些大型插入和更新作业。我怀疑我的代码是根本原因，因为我没有被告知正在运行的其他作业（而且我只是在它完全冻结了所有其他工作并且所有其他开发人员都感到恼火之后才听说了其他作业）。这可能就是为什么我的代码越来越慢的原因。
Is there a way to find this out programmatically, such as querying for a session inserting/updating lots of data, just in case the DBA forgets to tell me the next time he does this?
有没有办法以编程方式找出这一点，例如查询插入/更新大量数据的会话，以防 DBA 下次执行此操作时忘记告诉我？
采纳答案by Jon Heller
v$sessmetric is a quick way to see what resources each session is using - cpu, physical_reads, logical_reads, pga_memory, etc.
v$sessmetric 是查看每个会话正在使用哪些资源的快速方法 - cpu、physical_reads、logical_reads、pga_memory 等。
回答by Gary Myers
"I tried logging out the session and logging back in after about 10-15 minutes, still very slow."
“我尝试注销会话并在大约 10-15 分钟后重新登录，但仍然很慢。”
Assuming you are using a conventional dedicated connection on a *nix platform, this would pretty much rule out any memory leak. When you make a new connection to a database, oracle will fork off a new process for it and all the PGA memory will belong to that process and it will get released (by the OS) when the session is disconnected and the process terminated.
假设您在 *nix 平台上使用传统的专用连接，这几乎可以排除任何内存泄漏。当您与数据库建立新连接时，oracle 将为它派生一个新进程，所有 PGA 内存都将属于该进程，并在会话断开连接和进程终止时（由操作系统）释放。
If you are using shared server connections then the session uses memory belonging to both the process but also the shared memory. This would probably be more vulnerable to any memory leak problem.
如果您使用共享服务器连接，则会话使用属于进程和共享内存的内存。这可能更容易受到任何内存泄漏问题的影响。
Windows doesn't work quite the same way, as it doesn't fork a separate process for each session, but rather has a separate thread under a single Oracle process. Again, I'd suspect this would be more vulnerable to a memory leak.
Windows 的工作方式并不完全相同，因为它不会为每个会话派生一个单独的进程，而是在单个 Oracle 进程下有一个单独的线程。同样，我怀疑这会更容易受到内存泄漏的影响。
I'd generally look for other issues first, and probably start at the query underlying c_myCursor. Maybe it has to read through more old data to get to the fresh data ?
我通常会先寻找其他问题，并且可能会从 c_myCursor 底层的查询开始。也许它必须通读更多旧数据才能获得新数据？
回答by andr
If your query returns very muchdata your collection can grow enormously large, say 10 000 000 records - that can be the point of the suspicious memory usage.
如果您的查询返回非常多的数据，您的集合可能会变得非常大，比如 10 000 000 条记录 - 这可能是可疑内存使用的重点。
You can check this on by logging the size of the collection you bulk collect into. If it's larger that 10 000 (just a rough estimate, this depends on data of course) you may consider to split and work with parts of data, smth like this:
您可以通过记录批量收集的集合大小来检查这一点。如果大于 10 000（只是粗略估计，这当然取决于数据），您可以考虑拆分并处理部分数据，如下所示：
declare
cursor cCur is select smth from your_table;
--
type TCur is table of cCur%rowtype index by pls_integer;
--
fTbl TCur;
begin
open cCur;
loop
fTbl.delete;
fetch cCur bulk collect into fTbl limit 10000;
exit when cCur%notfound;
for i in 1 .. fTbl.count loop
--do your wok here
end loop;
end loop;
close cCur;
end;
Since you said that table is declared as in out nocopy I understand that you can't directly rewrite logic like this but just consider the methodology, maybe this can help you.
既然你说表被声明为 in out nocopy 我知道你不能像这样直接重写逻辑而只是考虑方法，也许这可以帮助你。
回答by Gábor Lipták
http://www.dba-oracle.com/t_plsql_dbms_profiler.htmdescribes DBMS_PROFILER. I suppose that the slowest parts of your code can be connected to memory leak. Anyway if you go back to the original problem, that it goes slower and slower, then the first thing to do is to see what is slow, and then to suppose memory leak.
http://www.dba-oracle.com/t_plsql_dbms_profiler.htm描述了 DBMS_PROFILER。我想你的代码中最慢的部分可能与内存泄漏有关。无论如何，如果你回到最初的问题，它变得越来越慢，那么首先要做的是看看什么是慢的，然后假设内存泄漏。
It sounds like you do no commit between executions, and the redo log is larger and larger. Probably this is the cause that DB needs to provide read consistency.
听起来你在执行之间没有提交，并且重做日志越来越大。大概这就是DB需要提供读一致性的原因。
You can also check the enterprise management console. Which version do you use? Never use XE for development, since as far as I know professional version can be used for development purposes. The enterprise management console even give you suggestions. Maybe it can tell you something clever about your PLSQL problem.
您还可以查看企业管理控制台。你使用哪个版本？永远不要使用XE进行开发，因为据我所知专业版可以用于开发目的。企业管理控制台甚至会给你建议。也许它可以告诉你一些关于你的 PLSQL 问题的聪明之处。
