Exceptional High Memory consumption of Oracle 10.2.0.x on Windows EMT64
While testing 64-bit Oracle EE 10.2.0.2 on Windows 2003 SP1, EMT64 I noticed exceptional high Virtual Memory usage of oracle.exe process during the startup (for example if SGA was sized at 1.5GB, then 4-6GB of VM memory will be reported by Task Manager at instance startup) .
I thought Oracle fixed the problem with memory management on EMT64 in patchset 10.2.0.3 where they claim they fixed the bug 5205552 “EXCEPTIONAL HIG VM SIZE USAGE FOR ORACLE.EXE ON 64 BIT X86 WINDOWS PLATFORM”.
Perhaps they did, but there is another bug left in the code and (at least in my case) quite easily reproduced.
– reproduced on Windows 2003 x64, Enterprise Edition with SP1/SP2
– reproduced with 64-bit Oracle 10.2.0.2/10.2.0.3/10.2.0.3 + Patch 5 bundle for Windows x64
– tested on three different servers (one Dell and two IBM servers)
1) make sure you don’t have the following line in the SQLNET.ORA (server side of course):
2) try to create some dummy database link with no TNS entry present in TNSNAMES.ORA, such as:
sql> connect scott/tiger
sql> create database link dummy connect to dummy identified by dummy using 'dummy';
3) the above statement will “hang” for awhile; at this time observe the “Virtual Memory Size”, “Peak Memory Usage” and “Page Faults” within Task Manager for the oracle.exe process. You’ll likely see the excessive growth of Memory usage. Roughly three times the SGA will be used during create database link statement, before Oracle returns control to the user. Imagine this happening on production server where two or three users at the same time send rogue create database link command to the server? They can easily bring the server down.
The only workaround known to me is to make sure that NAMES.DIRECTORY_PATH is present in sqlnet.ora (server side), such as:
I also noticed that this bug is semi reproducible, for example in my case the first create db link will show excessive memory growth, then two or three similar statements will go smoothly, then again, one statement will cause excessive memory allocation etc.