Shulou(Shulou.com)06/01 Report--

This article to share with you is about the steps in AIX5.3 Oracle SGA set in memory is how, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after harvest, not much to say, follow Xiaobian to see it.

On some operating system platforms, Oracle's SGA can be placed in memory, which avoids page swapping and improves Oracle performance. Under AIX, set the v_pinshm parameter of the operating system to 1, otherwise it will not work even if LOCK_SGA is set to TRUE in Oracle. However, knowing just these two parameters is far from enough; you must have some understanding of AIX memory management. This article requires that the operating system is above 5.3 ML01 and Oracle is above 9.2.0.4.

First let's check the operating system version:

XXIBM:# oslevel -r

5300-07

The operating system version meets our requirements. If the output is 5300-00, then the OS needs to be patched first. Many of Oracle's problems are closely related to the operating system.

Let's see how much memory there is. There are many ways to view memory, use any of them.

XXIBM:# bootinfo -r

64749568

The output above shows that the operating system has 64 gigabytes of memory.

Then use rmss -p to see if the currently available memory matches the actual memory. Because sometimes we may use rmss to simulate memory to a certain size (only small, of course) for testing purposes.

XXIBM:# rmss -p

Simulated memory size is 63231.9375 Mb.

If the output above is smaller than the actual memory, consider using rmss -r to restore the memory to its actual size.

Next let's examine a few parameter settings related to memory. Default memory parameters for AIX 5.3

First check the settings for lru_file_repage. This parameter defaults to 1, but IBM recommends setting it to 0 after ML01.

XXIBM:# vmo -L lru_file_repage

NAME CUR DEF BOOT MIN MAX UNIT TYPE DEPENDENCIES

--------------------------------------------------------------------

lru_file_repage 1 1 1 0 1 boolean D

In the output above, CUR represents the current value of the parameter, DEF represents the default value of the parameter, and BOOT represents the next startup value.

Set lru_file_repage to 0 with the following command: The following settings are currently in effect and do not change the settings for restart.

XXIBM:# vmo -o lru_file_repage=0

Setting lru_file_repage to 0

Next check v_pinshm, it should be changed to 1.

XXIBM:# vmo -L v_pinshm

NAME CUR DEF BOOT MIN MAX UNIT TYPE DEPENDENCIES

--------------------------------------------------------------------

v_pinshm 1 0 0 0 1 boolean D

XXIBM:# vmo -o v_pinshm=1

Setting v_pinshm to 1

Check minperm%, maxperm%, etc. Before using lru_file_repage, we used to set maxperm% very small, such as 20%. But starting with 5.3, IBM recommends making it bigger. This parameter defaults to 80, IBM suggests changing it to 90. As for minperm%, the default is 20. If 32G-64G exists in the memory, it can be changed to 10, less than 32G, changed to 5, greater than 64G, and kept at the default of 20.

XXIBM:# vmo -o minperm%=10

Setting minperm% to 10

XXIBM:# vmo -o maxperm%=90

Setting maxperm% to 90

After adjusting the operating system parameters, the rest of the work is simple. Log in to Oracle and check the LOCK_SGA parameter settings:

XXIBM:# su - oracle

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Fri Sep 19 08:40:10 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> show parameter lock_sga

NAME TYPE VALUE

------------------------------------ -----------

lock_sga boolean FALSE

This parameter is currently FALSE. To set SGA in memory, set this parameter to TRUE.

SQL> alter system set lock_sga=true scope=spfile;

System altered.

Next, calculate the size of the current SGA:

SQL> select sum(value)/1024/1024 from v$sga;

SUM(VALUE)/1024/1024

--------------------

35941.0215

This size should not exceed 60% of physical memory. Too small is not good, underutilized. Judging from the output above, the current SGA size is generally appropriate. Of course, you can further check whether the parameter settings such as DB_CACHE_SIZE are reasonable to determine whether to adjust them, which is skipped here.

After setting up, restart the database. If the database can start smoothly, then the settings are OK.

So how can I tell if ORACLE's SGA is negative in memory? This can be viewed with the svmon command. This command requires superuser to run.

$ su -

root's Password:

XXIBM:# svmon -P -t 100|grep -p Pid|head

--------------------------------------------------------------------

Pid Command Inuse Pin Pgsp Virtual 64-bit Mthrd 16MB 225546 oracle 9313207 9270407 2232 9308982 Y N N

--------------------------------------------------------------------

Pid Command Inuse Pin Pgsp Virtual 64-bit Mthrd 16MB

119692 oracle 9312614 9270438 2232 9308978 Y N N

--------------------------------------------------------------------

Pid Command Inuse Pin Pgsp Virtual 64-bit Mthrd 16MB

Note the Inuse and Pin output above, as well as Command. Command corresponds to oracle, indicating that the process information is Oracle. Inuse stands for memory pages in use, Pin stands for the number of pages set in memory, and each page is 4KB in size. If these two values are far apart, it means that they are not set in memory with SGA, and if they are very close, they are set in memory.

If you want Oracle to keep SGA in memory and work properly after the operating system restarts, you should set v_pinshm, lru_file_repage, etc., which are set at the beginning of this article, to the values you want after restarting the operating system. For example:

XXIBM:# vmo -p -o v_pinshm=1

Setting v_pinshm to 1 in nextboot file

Setting v_pinshm to 1

The above is how to set Oracle SGA in memory in AIX 5.3. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.