PL / SQL - check for memory leaks?

Question

I have PL / SQL code that I think may have a memory leak. Every time I run it, it runs slower and slower than before, although now I am reducing the size of the input. The code I suspect is populating an array from a cursor using massive collection, something like this

    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 what causes this slowdown. I know that there are several tables in Oracle that track the use of this type of memory, but I'm not sure if you can look at these tables and find your way to something useful in what my code does.

In addition, I tried to withdraw the session and return to it after 10-15 minutes, still very slowly.

Oracle Version - 10.2

---

So, it turns out that there was a different database activity. The database administrator decided to run several large tasks for inserting and updating at about the same time as he began to change and test the code. I suspected that my code was the main reason because I was not told about other work being done (and I only heard about this other work after it completely froze everything and all the other developers got angry). This is probably why my code is getting slower and slower.

Is there a way to find this programmatically, for example, requesting a session by inserting / updating a lot of data, in case the database administrator forgets to tell me the next time he does it?

Answer 1

v $sessmetric - , - cpu, physical_reads, logical_reads, pga_memory ..

Answer 2

" 10-15 , ".

, * nix, . , oracle , PGA , ( ), .

, , , . , , .

Windows , , Oracle. , , .

, , , c_myCursor. , , ?

Answer 3

http://www.dba-oracle.com/t_plsql_dbms_profiler.htm DBMS_PROFILER. , . , , , , , , , .

, , . , , .

. ? XE , , , . . , - PLSQL.

Answer 4

, , , 10 000 000 - .

You can verify this by registering the size of the collection you have collected. If it is more than 10,000 (just a rough estimate, it depends on the data, of course), you can consider splitting and working with pieces of data, for example:

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 the table is declared as in nocopy, I understand that you cannot directly rewrite such logic, but just consider the methodology, maybe this can help you.