Re: LOCK_SGA on Oracle 8.1.7 (win2000)

From: Heini Nolsøe <HNO_at_dlg.dk>
Date: Mon, 18 Jul 2005 11:22:35 +0200
Message-ID: <42db74dd$0$70202$edfadb0f@dread14.news.tele.dk>

>>> <yong321_at_yahoo.com> 11-07-2005 06:07 >>>
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.

Btw. sorry I'm not so quick to answer, but its because I'm struggling to understand alot of this stuff and have other things to take care of also.

Well I found out why Lock_SGA didnt seem to make any difference even on Oracle 10g. The reason is that I had set PRE_PAGE_SGA = true and having this parameter set hides the effect of LOCK_SGA (at database startup anyway) - sorry a newbie mistake. LOCK_SGA does actually seem to work on Oracle 10g.

I then proceded to test LOCK_SGA on at Oracle 9.2. Setting LOCK_SGA = true generated the very same error described in Metalink note 274092.1 but amazingly setting ORA_%SID%_WORKINGSETMIN = 2 makes it work.

In fact I also tried out this strace.exe program. It was very interesting to watch how this Lock-memory-function was called only when LOCK_SGA was set.

Sadly LOCK_SGA still can't be made to work on Oracle 8.1.7, but there is a chance that we can upgrade to 9.2 on our production systems. I just need to proof that it boosts performance before being allowed to upgrade :)

Anyway I just want to say THANKS ALOT to everybody answering my questions - belive me ... I REALLY appreciate it. Received on Mon Jul 18 2005 - 04:22:35 CDT