Home » SQL & PL/SQL » SQL & PL/SQL » Dummy variable assignment not releasing pl/sql variable memory (Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi)
Dummy variable assignment not releasing pl/sql variable memory [message #301548] Thu, 21 February 2008 00:44 Go to next message
mschhayagupta
Messages: 4
Registered: February 2006
Junior Member
Hi All,

Even after assigning dummy variable to my pl/sql variable memory is not released!

First I have designed two similar object collection types named v_order_status and v_order_dummy;

After processing using v_order_status, I am reassigning it to dummy variable before starting new loop.
v_order_status := v_order_dummy;

But this assignment is not releasing pl/sql memory assigned to v_order_status.

What can be the reason behind it and how this memory can be released?

-Chhaya

Re: Dummy variable assignment not releasing pl/sql variable memory [message #301554 is a reply to message #301548] Thu, 21 February 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to put more code and the way you used to know memory is not released.
Not just describe it, copy and paste the actual code and execution.

Before, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Dummy variable assignment not releasing pl/sql variable memory [message #306606 is a reply to message #301554] Sat, 15 March 2008 00:08 Go to previous messageGo to next message
mschhayagupta
Messages: 4
Registered: February 2006
Junior Member
In our oracle procedure we are using some pl/sql tables and objects for some data processing. We tried to track the pga usage by the procedure. Even after using delete function and assigning dummy object to the collection object. We are unable to see any decrease in the pga value. What can be the reason behind it? We have auto PGA management in the database.
When we use DBMS_SESSION.free_unused_user_memory function then we can see the decrease in pga value.

Here is the code segment to track pga usage by my session:

/*
v_nw and v_nw_dummy are the objects of same type. V_nw is used for data processing and v_nw_dummy acts as dummy object for the same like:
TYPE typ_arc_tbl IS TABLE OF typ_arc_rec INDEX BY BINARY_INTEGER;

v_nw typ_arc_tbl;
v_nw_dummy typ_arc_tbl;

*/

--Stat logic begins
str := NULL;

FOR rec IN (SELECT nam.NAME, stat.VALUE
FROM v$mystat stat, v$statname nam
WHERE stat.statistic# = nam.statistic#
AND nam.NAME LIKE '%ga memory%')
LOOP
str := str || rec.NAME || ': ' || rec.VALUE || CHR (10);
END LOOP;

INSERT /*+ APPEND */ INTO ses_stat
(stime, loc, msg
)
VALUES (SYSDATE, 'Before array RESET', str
);
--Stat Logic ends

--Clear the array for the next loop
v_nw.delete;
v_nw := v_nw_dummy;
DBMS_SESSION.free_unused_user_memory;

--Stat logic begins
str := NULL;
FOR rec IN (SELECT nam.NAME, stat.VALUE
FROM v$mystat stat, v$statname nam
WHERE stat.statistic# = nam.statistic#
AND nam.NAME LIKE '%ga memory%')
LOOP
str := str || rec.NAME || ': ' || rec.VALUE || CHR (10);
END LOOP;

INSERT /*+ APPEND */ INTO ses_stat
(stime, loc, msg
)
VALUES (SYSDATE, 'After array RESET', str
);
--Stat Logic ends

Above code segement shows following output after using DBMS_SESSION.free_unused_user_memory function.
03/14/2008 Before array RESET session uga memory: 416952
session uga memory max: 482360
session pga memory: 5128656
session pga memory max: 5194192
03/14/2008 After array RESET session uga memory: 416952
session uga memory max: 482360
session pga memory: 3359184
session pga memory max: 5194192

Without this function there is no decrease in session pga memory value even after using .delete function on the object i.e session pga memory is 5128656 before and after the array reset. We are doing the data processing in a loop so this pga value continues to increase thought the processing till it reaches the maximum limit. Can my procedure fails with memory storage error because of it? How I can decrease pga value with in session without using this dbms_session value?
Re: Dummy variable assignment not releasing pl/sql variable memory [message #306610 is a reply to message #306606] Sat, 15 March 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You didn't follow the format request
2/ You didn't tell your version as requested
3/ You know the solution but you don't want to use it

Regards
Michel
Re: Dummy variable assignment not releasing pl/sql variable memory [message #306624 is a reply to message #306610] Sat, 15 March 2008 02:56 Go to previous messageGo to next message
mschhayagupta
Messages: 4
Registered: February 2006
Junior Member
1. Sorry Michel, for not following the required standards. In future I will be more careful while posting.

2. Version Detail:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi
PL/SQL Release 10.1.0.5.0 - Production
CORE 10.1.0.5.0 Production

3. DBMS_SESSION.free_unused_user_memory can be used to free pga within session. But oracle itself does not recommend its usage as it is a resource intensive operation. Is there any other way with which we can reduce pga within session?

4. In such pga usage programs can reach to maximum pga value for the session/user. Such things should not happen as the same program can fail with the memory storage error.



Re: Dummy variable assignment not releasing pl/sql variable memory [message #306632 is a reply to message #306624] Sat, 15 March 2008 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But oracle itself does not recommend its usage as it is a resource intensive operation. Is there any other way with which we can reduce pga within session?

No, this is only way to enforce Oracle to behave how it is not inclined to do.

This said there may be a bug in your version.
If you have a Metalink account, read the forum thread 618857.992.

Regards
Michel
Re: Dummy variable assignment not releasing pl/sql variable memory [message #306635 is a reply to message #306624] Sat, 15 March 2008 04:27 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I found an interesting Metalink note reporting memory leak bugs in 10.1: Bugs, Fixed Versions and Workarounds in PL/SQL 10.1.0.X, note 403169.1

Regards
Michel
Previous Topic: How to update only year in date?
Next Topic: granting
Goto Forum:
  


Current Time: Sat Dec 03 11:52:50 CST 2016

Total time taken to generate the page: 0.08356 seconds