I just found a strange behavior in one of our PL/SQL procedures that is designed to load the data from external tables (files that arrive on the server). During one run, I noticed that “session PGA memory” was constantly increasing which made me look for a leak in the first place. To cut the very long story short, the part that caused the leak was ‘alter table’ command that changes the location of the external tables. This was done for each file that had to be loaded and there were tens of thousands of them. Here is the SQL in question:
execute immediate 'ALTER TABLE my_external_table LOCATION( FILELOC:''newfile.txt'')';
Heap dump can be used to see the leak. I ran the above command from 500 times to make sure it generates leak big enough to notice in the dump
begin for i in 1..500 loop execute immediate 'ALTER TABLE my_external_table LOCATION( FILELOC:''newfile.txt'')'; end loop; execute immediate 'ALTER SESSION SET EVENTS ''immediate trace name heapdump level 536870913'''; end;
536870913 (or more easy to read: 0x20000001) means we want to dump PGA with level 2 depth. The generated dump will appear as .trc file in user_dump_dest folder and can be analyzed through Tanel’s very simple but also extremely usefull heapdump_analyzer utility. And with that we get this kind of output:
-- Heapdump Analyzer v1.00 by Tanel Poder ( http://www.tanelpoder.com ) Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason ---------- ------- ------------ ----------------- ----------------- ----------------- 2746816 167 16448 , pga heap, freeable, koh-kghu call 2740136 167 16408 , koh-kghu call , freeable, kollalo2 888800 55 16160 , pga heap, free, 327160 5 65432 , top uga heap, freeable, session heap 262048 4 65512 , top call heap, free, 82880 20 4144 , session heap, freeable, kxsFrame4kPage 65432 1 65432 , top uga heap, recreate, session heap 49224 1 49224 , top call heap, free, 41392 1 41392 , session heap, perm, perm ... (and so on)
What immediately gets our attention are the first two rows. “koh-kghu call, kollalo2” was increasing each time ALTER TABLE was called. Metalink search didn’t show any results regarding this, however we opened SR and they resolved it by saying it’s an unpublished bug number 6737245 and that it will be fixed in version 10.2.0.5.
PS. This post is about Oracle 10.2.0.4 on AIX 5.3, i don’t know if it is reproducible elsewhere.
5 thoughts on “Memory leak when changing external table location”
Awesome to see that my stuff has been useful for people out there! 🙂
What is awesome is that there are good places and blogs on the net to find and learn this stuff. Keep it up with your new site. 🙂
Hi Tanel !
Please, let me know if Oracle Support has given you a work-around for bug# 6737245 instead of upgrade to 10.2.0.5
Thanks in advance.
Nope. they said that there is no workaround.
I’m not Tanel. I just used his analyzer. 🙂
Hi Feign … I tried to get the Tanel’s analyzer but the link is not longer available. Do you know if it is still available in another link?