LOCK_SGA on Oracle 8.1.7 (win2000)
Heini Nolsøe
I am aware of that LOCK_SGA is buggy until Oracle 9.2, but the guys at Oracle Metalink are saying that there is a workaround. My problem is that the official workaround at Metalink does not work, and it seems more or less impossible to get any real help via the Metalink supporters - they more or less just copy/paste Metalink documents back at me.
In desperation I have tried to install an Oracle 10G database, but even on this version of the database LOCK_SGA does not seem to work at all.
I have really been searching allover for info on this subject, but there really is not much to be found. I have read all available docs on Metalink and am running out of ideas.
In short ..... HELP !!!!
DA Morgan
My understanding is that LOCK_SGA is not supported by the Win2K
operating system.
Source: http://www.orafaq.com/cgi-bin/search/query?q=lock_sga
Can anyone confirm this?
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)
yon...@yahoo.com
usage. See
http://rootshell.be/~yong321/oranotes/WinMemUsedByOracle.txt
But it's for 10g.
Can you describe what you observed exactly?
Yong Huang
Heini Nolsøe
The system:
Windows 2000 SP4
512 MB RAM
I've watched the memory usage on the computer using Task Manager and Performance counters (Start>Settings>Control Panel>Administrative Tools>Performance). The Task Manager reports that the total memory usage on the system is about 450MB, wich in theory means that no swapping to the pagefile is needed. I have conducted a test by watching the memory usage on oracle.exe when changing LOCK_SGA from false to true:
LOCK_SGA=FALSE
TASKMANAGER
MEM USAGE : 156.908 K
VM SIZE : 154.352 K
PERFORMANCE (Average)
Page File Bytes : 158.072 K
Private Bytes : 158.072 K
Virtual Bytes : 251.179 K
Working Set : 160.527 K
LOCK_SGA=TRUE
TASKMANAGER
MEM USAGE : 158.168 K
VM SIZE : 154.544 K
PERFORMANCE (Average)
Page File Bytes : 158.253 K
Private Bytes : 158.253 K
Virtual Bytes : 251.310 K
Working Set : 161.964 K
As you can see for yourself the numbers are almost identical no matter what value LOCK_SGA has. Needless to say ... to me this is a sign of LOCK_SGA not working as it is supposed to.
ORA600
Yes, in principal LOCK_SGA scheme in not supported on Windows 2000
rather i should say Windows platform. doesn't matter which SP you are
on.
lock_sga and pre_page_sga are meant generally for UNIX where we have
much better and stable control over memory and some kernel parameters
that govern memory management.
Yet, there is an optimisation method that is well described here,
Oracle9i Database and Client Release Notes
Release 2 (9.2.0.2.1) for 64-Bit Windows
Part Number B10694-01
I don't think this will work for Oracle 8i, so you have to try it for
Oracle 9i or 10g. I don't know if it will work on 10g because i don't
see these instructions in the 10g Release Notes. Yet, you could try it
since you have already installed 10g.
Are you referring to Metalink Note: 274092.1? Refer it, if you haven't
yet.
Check it and let me know if that works.
Just curious, what are you trying to accomplish? why would you want to
use LOCK_SGA on Win2k?
windows memory managment is so horrible that you are lucky that it
actually recognises 8GB or 16GB of RAM, let alone manage it.
you might as well just add 512MB of RAM which is less of an headache
than getting lock_sga to work on Win2K.
ski
yon...@yahoo.com
> As mentioned before I have set up an Oracle 10G database to test LOCK_SGA. ...
> LOCK_SGA=FALSE
>
> TASKMANAGER
> MEM USAGE : 156.908 K
> VM SIZE : 154.352 K
> LOCK_SGA=TRUE
>
> TASKMANAGER
> MEM USAGE : 158.168 K
> VM SIZE : 154.544 K
I don't know how to explain. Your Mem Usage and VM Size are about the
same. Would changing your database SGA to a much higher and lower value
make any difference? Say, 100M and 200M. What is it set to now?
You did stop and restart the OS service when restarting the database,
didn't you? I mean OracleService<SID>. And you don't have
ORA_WORKINGSETxxx set in registry?
Yong Huang
DA Morgan
ORA600 brings up two excellent points and repeats what I said a day
or two ago that you are trying to use a parameter not suppored on
your operating system.
1. What are you trying to accomplish and why?
2. Why don't you get a 'real' operating system?
Matthias Hoys
"Heini Nolsře" <H...@dlg.dk> wrote in message
news:42ce3f3f$0$59778$edfa...@dread14.news.tele.dk...
> As mentioned before I have set up an Oracle 10G database to test LOCK_SGA.
> Just for the record ... there is no activitiy on this database, its just
> sitting there with no users connected and no batch jobs running.
>
> The system:
> Windows 2000 SP4
> 512 MB RAM
>
> I've watched the memory usage on the computer using Task Manager and
> Performance counters (Start>Settings>Control Panel>Administrative
> Tools>Performance). The Task Manager reports that the total memory usage
> on the system is about 450MB, wich in theory means that no swapping to
> the pagefile is needed. I have conducted a test by watching the memory
> usage on oracle.exe when changing LOCK_SGA from false to true:
I believe Windows always uses the pagefile, even if you have plenty of RAM
available ...
yon...@yahoo.com
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.
Heini Nolsøe
SQL> show sga
Total System Global Area 117440512 bytes
Fixed Size 787728 bytes
Variable Size 74447600 bytes
Database Buffers 41943040 bytes
Redo Buffers 262144 bytes
Anyway ...
Oracle9i Database and Client Release Notes
Release 2 (9.2.0.2.1) for 64-Bit Windows
Part Number B10694-01
references 64-bit versions of windows (windows 2003), so this does not apply to windows 2000 - have tested but didn't work.
I also have already referenced Metalink Note: 274092.1, but setting ORA_WORKINGSETMIN = 2 does not make any difference. Further more if you read Metalink Note: 46001.1 setting ORA_WORKINGSETMIN = 2 does not make any sense. Generally I am getting alot of conflicting information from different people. A good example of this is some Metalink supporters suggesting that ORA_WORKINGSETMIN has something to do with how big the windows "Page Size" is. But all of a sudden I'm reading about ORA_LPENABLE wich also does defines the windows page size. I suspect that there are different "workarounds" on different versions of windows (NT, 2000, 2003) and this is confusing people.
The whole reason for me getting involved is because I was given the task to performance tune oure databases. I have some knowledge of Oracle but am very inexperienced as a DBA. During reading trough alot of performance tuning methods I consistently came across " ... make sure the OS is not swapping to disk ...". At first I didn't think much of it because I thought windows surely would not swap if there was enough memory. But suddenly it dawned upon me that acctually windows does always use the pagefile (thus is swapping to disk). Then I got confused an went on to read about ORA_WORKINGSETMIN, LOCK_SGA and ORA_LPENABLE. I also read alot to find out how much of the oracle.exe acctually was in the pagefile. Watching the Task Manager and the Performance counters it looks like half of the oracle.exe is paged out to disk at anyone given time. This whole thing is making me worry about performance on our databases to say the least. This is why I feel like it is nessasary to lock the SGA in memory.
Anyway this article http://rootshell.be/~yong321/oranotes/WinMemUsedByOracle.txt combined with Oracle9i Database and Client Release Notes Release 2 (9.2.0.2.1) for 64-Bit Windows has made me decide to test LOCK_SGA on windows 2003.
Heini Nolsøe
1. Task Manager and Performance counters on Win2000 indicate that a large amount of oracle.exe pages are in the pagefile, even if there is plenty of physical RAM on the system. I am trying to make windows stop swapping oracle.exe to the pagefile.
2. Its at matter of getting things to work on our current OS system since Oracle says LOCK_SGA is supported on win2000 :)
Preston Kemp
> I am having alot of trouble making LOCK_SGA work on my Oracle 8.1.7
> running windows 2000, and am really looking for somebody with
> experience at this specific task.
>
> I am aware of that LOCK_SGA is buggy until Oracle 9.2, but the guys
> at Oracle Metalink are saying that there is a workaround. My problem
> is that the official workaround at Metalink does not work, and it
> seems more or less impossible to get any real help via the Metalink
> supporters - they more or less just copy/paste Metalink documents
> back at me.
Have you tried changing the DisablePagingExecutive registry entry to 1?
That'll make Windows page to memory rather than disk, which might solve
your problem, assuming you're trying to stop disk-swapping.
--
Preston.
Heini Nolsøe
>>> <yon...@yahoo.com> 11-07-2005 06:07 >>>
Yong Huang
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.