Re: LOCK_SGA on Oracle 8.1.7 (win2000)

From: <yong321_at_yahoo.com>
Date: 10 Jul 2005 21:07:15 -0700
Message-ID: <1121054835.426066.286800@g43g2000cwa.googlegroups.com>

I did a little more research on the effect of lock_sga on oracle.exe process. This is Oracle 9.2.0.1.0 on XP. If lock_sga is set to true, running strace on oracle.exe while the database is starting up returns the following lines:

c:\>strace -p 3120 | grep -i lock
[snipped]
2588 3120 2260 NtLockVirtualMemory (-1, (0x3070000), 454656, 1, ...
(0x3070000), 454656, ) == 0x0

2590 3120 2260 NtLockVirtualMemory (-1, (0x78400000), 92274688, 1, ...
(0x78400000), 92274688, ) == 0x0

2592 3120 2260 NtLockVirtualMemory (-1, (0x30e0000), 667648, 1, ...
(0x30e0000), 667648, ) == 0x0

[snipped]

This says that the Windows system service NtLockVirtualMemory(), which implements the documented VirtualLock() Win32 function, locks virtual memory starting at the above three hex addresses of size 454656, 92274688 and 667648 bytes, respectively. The LockType 1 indicates that it tries to lock in working set list, not physical memory (which would be 2). Return code is success.

When the database is starting, sqlplus shows: Total System Global Area 93395628 bytes

Fixed Size                   453292 bytes
Variable Size              75497472 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes

suggesting that Oracle locks fixed area of SGA, SGA probably minus fixed area, and then log buffer, in that order.

When I set lock_sga to false or don't set it, database startup does not call NtLockVirtualMemory().

By the way, I reproduced my earlier finding that Mem Usage of Task Manager increases by a large amount when lock_sga is set to true, while VM Size remains the same. So this is true for both 9i and 10g.

Yong Huang

Note: strace.exe is available from
www.bindview.com/Support/RAZOR/Utilities/Windows/strace_readme.cfm. Interpretation of NtLockVirtualMemory() is based on Gary Nebbett's "Windows NT/2000 Native API Reference", pp.87-88 for ZwLockVirtualMemory(). LockType 1 for lock in working set list vs. 2 for lock in physical memory is not explained. I believe VirtualLock() always calls NtLockVirtualMemory() with LockType 1, consistent with the usual caveat that "locked" memory pages can still be paged out if there's no thread of the process running on the CPU. Nevertheless, we see clear difference between setting and not setting lock_sga in Oracle on Windows. The problem of this parameter in older versions of Oracle may have been corrected, but it's better to get confirmation from Oracle support. Received on Sun Jul 10 2005 - 23:07:15 CDT