topic Re: LOCK_SGA parameter in Operating System - HP-UX

LOCK_SGA parameter

Yogeeraj_1 — Tue, 09 Apr 2002 10:11:35 GMT

Hi,

I am running Oracle 8.1.7.0 on HP-UX 11 64-bits.

I have a L1000 with single processor and 2 GB of RAM.

I have been advised to add parameter LOCK_SGA=true in my init.ora. This is supposed to help my system by avoiding "execessive paging"

I have already gone through the preliminary tasks of setting the permissions required before setting this parameter.

i.e. Give the oracle Unix user memory locking privileges at the OS level.
1) Login as root:
%su root

2) Created the file "/etc/privgroup":

$vi /etc/privgroup

3) Added line "dba MLOCK" to file.

4) As root, run command:
$/etc/setprivgrp -f /etc/privgroup

My SGA is as follows:
--------------------------------------------
SQL> show sga

Total System Global Area 422849000 bytes
Fixed Size 104936 bytes
Variable Size 340652032 bytes
Database Buffers 81920000 bytes
Redo Buffers 172032 bytes
--------------------------------------------

Any advice and guidance will be most welcomed.
Any word of caution?

Best Regards
Yogeeraj

Re: LOCK_SGA parameter

Steve Steel — Tue, 09 Apr 2002 10:16:38 GMT

Hi

In google search hp-ux oracle lock_sga

lots of data

ex

http://bizforums.itrc.hp.com/cm/QuestionAnswer/1,,0xf853af48a9e5d5118ff40090279cd0f9,00.html

And excellent
http://www.quest.com/whitepapers/orcl_db_mgmt.pdf

Steve Steel

Re: LOCK_SGA parameter

Yogeeraj_1 — Tue, 09 Apr 2002 10:36:14 GMT

hi steve,

Thank you for the super-fast reply ;)

I went through the second document which mentions about "this parameter should not be used if the system is not configured with an optimal amount of memory to begin with".

how will i know if i have the "optimal amount"?

More inputs from people who are using it at their sites will be much appreciated.

Thank you for your time and guidance.

Best Regards
Yogeeraj

MLOCK_SGA/LOCK_SGA/_LOCK_SGA etc. are all parameters that facilitate locking of the SGA in memory and preventing it from being paged/swapped. Proper use of this parameter will prevent the paging algorithm from considering the memory pages used by the SGA for page-outs. This provides a significant performance benefit especially during periods of heavy activity. This parameter should not be used if the system is not configured with an
optimal amount of memory to begin with.

Re: LOCK_SGA parameter

Steven Gillard_2 — Tue, 09 Apr 2002 10:42:22 GMT

I would keep a very close eye on paging activity after making this change. Locking the SGA in memory will reduce the amount of physical memory available for other applications, and could actually increase the amount of paging your system performs and result in an overall decrease in performance. You should only configure this if you have a very good understanding of the memory demands of apps on your system.

If your system has plenty of free memory anyway, its unlikely to make much difference. But if your system is already paging you will improve the performance of Oracle at the expense of other applications.

Regards,
Steve

Re: LOCK_SGA parameter

Yogeeraj_1 — Tue, 09 Apr 2002 10:55:00 GMT

hi steven,

Your words of caution frightens me!!

in fact, i am trying to optimise my system for better performance...

When i look at the top output, i often find that my available memory going down to about 50,000K!!

what would you recommend?

Go or no go?

Thank you for your time and guidance.

best regards
Yogeeraj
-----------------------------------------------
System: L1000 Tue Apr 9 15:59:09 2002
Load averages: 2.56, 2.33, 2.50
224 processes: 208 sleeping, 16 running
Cpu states:
LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
2.56 51.9% 0.0% 17.0% 31.1% 0.0% 0.0% 0.0% 0.0%

Memory: 786036K (428136K) real, 724728K (425428K) virtual, 177592K free Page# 1/16

TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
? 7204 oracle8i 148 20 41024K 11028K sleep 222:48 13.39 13.37 ora_s001_cmtdb
? 5614 oracle8i 156 20 44096K 12736K sleep 37:50 8.12 8.10 ora_d010_cmtdb
? 5558 oracle8i 191 20 41024K 10584K run 147:42 5.25 5.24 ora_s002_cmtdb
? 5560 oracle8i 156 20 41024K 8996K sleep 59:42 1.74 1.74 ora_s003_cmtdb
? 28 root 152 20 0K 0K run 62:02 1.65 1.65 vxfsd
? 16849 ias 154 20 14772K 7052K sleep 0:10 1.40 1.40 f60webm
? 17525 ias 154 20 15028K 7076K sleep 0:17 1.08 1.08 f60webm
? 18524 ias 154 20 13764K 5724K sleep 0:01 0.93 0.92 f60webm
? 18590 ias 154 20 10420K 1844K sleep 0:00 1.83 0.78 f60webm
? 11542 ias 154 20 18228K 10328K sleep 1:38 0.75 0.75 f60webm
? 5598 oracle8i 156 20 44096K 10176K sleep 35:13 0.75 0.75 ora_d002_cmtdb
? 17893 ias 154 20 13876K 5936K sleep 0:05 0.70 0.70 f60webm
----------------------------------------------

Re: LOCK_SGA parameter

Steven Gillard_2 — Tue, 09 Apr 2002 11:22:46 GMT

Hi Yogeeraj,

It all depends on the requirements of the other applications on your system. If the Oracle database is the most important application from a response time point of view then I would say go ahead. If you have other important applications on the system, I would only set this parameter if you are sure that their memory demands are adequately met by the remaining free memory.

I personally don't use this parameter as I have enough free memory so that the SGA never gets paged out anyway.

Regards,
Steve

Re: LOCK_SGA parameter

Steve Steel — Tue, 09 Apr 2002 13:15:48 GMT

Hi

This is maybe clearer to you

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x6dc0c8ecad09d6118ff40090279cd0f9,00.html

Steve Steel

Re: LOCK_SGA parameter

Yogeeraj_1 — Wed, 10 Apr 2002 03:13:52 GMT

Hi steve
thanks you for your time.

My Box runs both Oracle 8.1.7 and 9iAS. Our server is handling both client server connection and thin clients. Both are equally important.
Do you know that "memory demands are adequately met by the remaining free memory"?

Is there a simple way to monitor it? How do i know if my memory is not sufficient?

thank you in advance

Best Regards
Yogeeraj
PS. Below the output of TOP in the morning.
System: L1000 Wed Apr 10 08:20:05 2002
Load averages: 2.56, 3.38, 3.56
205 processes: 191 sleeping, 14 running
Cpu states:
LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
2.56 16.4% 0.0% 6.7% 76.8% 0.0% 0.0% 0.0% 0.0%

Memory: 863964K (595348K) real, 752664K (540068K) virtual, 380160K free Page# 1/15

TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
? 2288 oracle8i 148 20 40128K 9072K sleep 11:58 8.19 8.17 ora_s000_cmtdb
? 2290 oracle8i 156 20 36032K 4976K sleep 6:58 7.64 7.63 ora_s001_cmtdb
? 7623 ias 154 20 16948K 9776K sleep 0:38 3.46 3.45 f60webm
? 8104 ias 154 20 13876K 6128K sleep 0:04 2.85 2.84 f60webm
? 7869 ias 154 20 14516K 6904K sleep 0:12 1.63 1.63 f60webm
? 7675 ias 154 20 14516K 6916K sleep 0:04 1.35 1.35 f60webm
? 2340 oracle8i 156 20 44096K 13056K sleep 0:19 1.03 1.03 ora_d001_cmtdb
? 2292 oracle8i 156 20 40128K 9072K sleep 3:35 0.89 0.89 ora_s002_cmtdb
? 7983 ias 154 20 13492K 5588K sleep 0:02 0.79 0.79 f60webm
? 2346 oracle8i 156 20 44096K 13056K sleep 0:19 0.52 0.52 ora_d004_cmtdb
? 4650 ias 154 20 14644K 7104K sleep 0:13 0.49 0.49 f60webm
? 6320 oracle8i 154 20 64704K 33584K sleep 0:58 0.48 0.48 oraclecmtdb

Re: LOCK_SGA parameter

Yogeeraj_1 — Wed, 10 Apr 2002 03:16:19 GMT

hi Steve Steel

the link you gave us is more about database block sizes! ;)

Regards
Yogeeraj

Re: LOCK_SGA parameter

Steven Gillard_2 — Wed, 10 Apr 2002 08:01:51 GMT

Yogeeraj,

Tools like glance and measureware provide better information than top - you should look at configuring the parm file to collect statistics on each application. The vmstat command also gives useful information on the amount of paging activity on your system (check the 'po' or page out column).

If you're seeing excessive page out rates coupled with low free memory then chances are you're suffering from a memory bottleneck. If your system still has free memory and is not paging during peak times then you are ok.

Theres a lot of good documentation in the ITRC knowledge base and in these forums - try running some searches on 'memory bottleneck' etc. My advice is not to change anything unless you are suffering from a performance problem.

Regards,
Steve

Re: LOCK_SGA parameter

Steve Steel — Wed, 10 Apr 2002 09:02:49 GMT

Hi

http://otn.oracle.co.kr/docs/Oracle8/A64526_01.pdf

Chapter 3 is a great explanation of memory management.

3.12 deals with lock_sga

Steve Steel

Re: LOCK_SGA parameter

Dennis J Robinson — Thu, 11 Apr 2002 03:13:24 GMT

Why is your shared pool larger than your db_block_buffers?

Anyway, if the database performance is critical on this server. ( It almost always should be ), you should always use the MLOCK option on HP servers.

This is important, as the Oracle SGA should NEVER be paged.

I do not know about performance improvement in cases where there is adequate memory on the box.

It is said that the pages that are MLOCK'd are not candidates for paging and performance is improved.

Also take a look @ "pre_page_sga".

Re: LOCK_SGA parameter

Yogeeraj_1 — Thu, 11 Apr 2002 03:47:48 GMT

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Steve

Thank you for the reply.

Unfortunately, i don't have GLANCE and MEASUREWARE on my server.
They have both expired!! (and it seems like the demo version cannot be reinstalled)

I will try automate something to collect values from vmstat.

I will also try to check about "memory bottleneck" in this forum

thanks again

Best Regards
Yogeeraj
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
hi

Thank you Steve Steele for the link.
rather old doc...july 1998 (oracle 8.0.5) ;)

anyway thanks

Best Regards
Yogeeraj
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Hello Dennis

You worry me with your question "Why is your shared pool larger than your db_block_buffers?"

Is there something wrong? (I am not an Oracle Expert. Any more input will be most appreciated)
---------------------------------------------------------------------------
SQL> show parameter shared

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
hi_shared_memory_address integer 0
shared_memory_address integer 0
shared_pool_reserved_size string 4121440
shared_pool_size string 82428800
SQL> show parameter db_block

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_buffers integer 10000
db_block_checking boolean FALSE
db_block_checksum boolean FALSE
db_block_lru_latches integer 1
db_block_max_dirty_target integer 10000
db_block_size integer 8192
SQL>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Steve said:

It all depends on the requirements of the other applications on your system. If the Oracle database is the most important application from a response time point of view then I would say go ahead. If you have other important applications on the system, I would only set this parameter if you are sure that their memory demands are adequately met by the remaining free memory.

And i am not sure whether memory demands are adequate on my system.

For example, i run several TOP queries yesterday and found the following:

On Average:
Memory: 1062920K (727880K) real, 891592K (635860K) virtual, 168828K free
In the morning, with very few users, it is:
Memory: 579560K (404036K) real, 547052K (397892K) virtual, 503312K free Page# 1/14
noon:
Memory: 886976K (374224K) real, 710612K (330596K) virtual, 353580K free Page# 1/17
Memory: 950268K (670600K) real, 883976K (635924K) virtual, 33568K free Page# 1/17
Memory: 943676K (675372K) real, 856972K (633388K) virtual, 56116K free Page# 1/17

Maybe even with my 2 GB RAM, i am suffering from memory problems!! Maybe my system is not properly tuned!!

Best Regards
Yogeeraj
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~