Systems:
The following was tested on two different machines. Both were running the same version of Redhat Linux. The older machine was 10.2.0.4 and 4 Gigs of memory. The newer machine has 11.1.0.6 and 8 Gigs of memory. The processor is better of course too.
Test procedure:
I ran the following updated script three times on both systems. On the older system, I ran it 300,000 times. On the newer system, I ran it 600,000 times (which still ran faster than the older system). You can see by the comments what two lines of code I added in run #2 and the one line of code I added in run #3. After doing each run, I would close that window/session and start a new one. I'm using PL/SQL Developer and have it setup so each window runs as a separate database session.
DECLARE
l_dom_doc dbms_xmldom.DOMDocument;
l_node dbms_xmldom.domnode; -- added run 2
PROCEDURE p_local_open_close IS
BEGIN
l_dom_doc := dbms_xmldom.newDOMDocument;
l_node := dbms_xmldom.makenode(dbms_xmldom.createelement(l_domdoc, 'root')); -- added run 2
dbms_xmldom.freeNode(l_node); -- added run 3
dbms_xmldom.FreeDocument(l_dom_doc);
END p_local_open_close;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',
action_name => 'SLEEPING');
dbms_lock.sleep(20);
/* Sleeps so have time to find OS PID via
select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid
from gv$session s,
gv$process p
where s.module = 'user_domDocument_mem_leak_test'
and s.paddr = p.addr;
*/
dbms_application_info.set_action('RUNNING');
FOR i IN 1..300000 -- adjust as needed for your system
LOOP
p_local_open_close;
END LOOP;
dbms_application_info.set_action('COMPLETE');
END;
To determine memory usage, I was using top to see what the VIRT (total virtual memory) for the OS session was. The SQL in the script shows how to get the OS session based on the Oracle session that was running. If your version of top doesn't support top -p
Run results:
Runs #2 and #3 were identical in memory usage to run #1. For the 10.2.0.4 system, VIRT would start out at/around 438m and increase to 470m after the run. For the 11.1.0.6 system, memory would start at 1181m and stay there.
Conclusion:
It would appear that using dbms_xmldom.freeNode is completely optional as Oracle handles the memory from this correctly. This is determined by the fact that memory usage from the first run, which had no dbms_xmldom.domnode in it, was exactly the same as the second and third runs.
Additional observation:
I was expecting that memory usage on the 10.2.0.4 system to remain a constant like it did on the 11.1.0.6 system. As my previous post said, the fix was applied to a 10.2.0.3 system and has since been upgraded to 10.2.0.4. As 10.2.0.4 was released sometime in early 2008 (or so it appears) it is highly possible the patch applied to 10.2.0.3 was not part of the 10.2.0.4 release. Looks like I will have to verify this on the client's system and see about getting this fixed if it still occurs on their system as well.
No comments:
Post a Comment