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

What this article shares with you is about how to use lock_sga and pre_page_sga parameters to ensure that SGA resides in physical memory. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

By modifying the parameters of lock_sga and pre_page_sga, we can ensure that SGA will not be swapped out to virtual memory, which can improve the efficiency of SGA. Through this small text to show you the modification process of these two parameters, do not be too optimistic, the modification process is "small bumpy".

When the lock_sga parameter is set to TRUE (the default is FALSE), the entire SGA is guaranteed to be locked in physical memory, which prevents SGA from being swapped out to virtual memory. As long as you set lock_sga to "TRUE" to ensure that SGA is locked in physical memory, the pre_page_sga parameter is also set to "TRUE" by the way, because this ensures that the entire SGA is read into physical memory when starting the database, in order to improve the efficiency of the system (although it will increase the startup time of the system).

The modification process is as follows:

1. View the default values of the lock_sga and pre_page_sga parameters

Sys@ora10g > show parameter sga

NAME TYPE VALUE

Lock_sga boolean FALSE

Pre_page_sga boolean FALSE

Sga_max_size big integer 5G

Sga_target big integer 5G

two。 Note: both parameters are static. Confirm it.

Sys@ora10g > alter system set lock_sga=true

Alter system set lock_sga=true

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

Sys@ora10g > alter system set pre_page_sga=true

Alter system set pre_page_sga=true

*

ERROR at line 1:

ORA-02095: specified initialization parameter cannot be modified

3. Use the "scope=spfile" option to modify it successfully.

Sys@ora10g > alter system set lock_sga=true scope=spfile

System altered.

Sys@ora10g > alter system set pre_page_sga=true scope=spfile

System altered.

4. Restart Oracle for the changes to spfile to take effect

Sys@ora10g > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Sys@ora10g > startup

ORA-27102: out of memory

Linux-x86_64 Error: 12: Cannot allocate memory

"Little bumps" appeared, think about it, why did it fail to start?

The reason is simple: the Linux operating system limits the maximum value that each task can lock in physical memory! It needs to be adjusted manually.

5. Dealing with "ORA-27102" and "Cannot allocate memory" problems

1) use the "ulimit-a" command to get the default size of "max locked memory"

Ora10g@secDB / home/oracle$ ulimit-a

Core file size (blocks,-c) 0

Data seg size (kbytes,-d) unlimited

Scheduling priority (- e) 0

File size (blocks,-f) unlimited

Pending signals (- I) 266239

Max locked memory (kbytes,-l) 32

Max memory size (kbytes,-m) unlimited

Open files (- n) 65536

Pipe size (512 bytes,-p) 8

POSIX message queues (bytes,-Q) 819200

Real-time priority (- r) 0

Stack size (kbytes,-s) 10240

Cpu time (seconds,-t) unlimited

Max user processes (- u) 16384

Virtual memory (kbytes,-v) unlimited

File locks (- x) unlimited

It can be seen that the maximum amount of physical memory a task can lock is 32kbytes, which is simply not enough for our SGA's 5G size requirements.

2) modify it to infinity

(1) oracle users cannot complete this modification task.

Ora10g@secDB / home/oracle$ ulimit-l unlimited

-bash: ulimit: max locked memory: cannot modify limit: Operation not permitted

(2) switch to root user

Ora10g@secDB / home/oracle$ su-root

Password:

(3) an attempt was made under the root user, and the modification was successful.

[root@secDB ~] # ulimit-l unlimited

[root@secDB] # ulimit-a

Core file size (blocks,-c) 0

Data seg size (kbytes,-d) unlimited

Scheduling priority (- e) 0

File size (blocks,-f) unlimited

Pending signals (- I) 266239

Max locked memory (kbytes,-l) unlimited

Max memory size (kbytes,-m) unlimited

Open files (- n) 1024

Pipe size (512 bytes,-p) 8

POSIX message queues (bytes,-Q) 819200

Real-time priority (- r) 0

Stack size (kbytes,-s) 10240

Cpu time (seconds,-t) unlimited

Max user processes (- u) 2047

Virtual memory (kbytes,-v) unlimited

File locks (- x) unlimited

6. After adjusting the operating system restrictions, we tried to start the database again. Success!

[root@secDB ~] # su-oracle

Ora10g@secDB / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0-Production on Sun Dec 20 22:21:40 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

NotConnected@ > startup

ORACLE instance started.

Total System Global Area 5368709120 bytes

Fixed Size 2080320 bytes

Variable Size 905970112 bytes

Database Buffers 4445962240 bytes

Redo Buffers 14696448 bytes

Database mounted.

Database opened.

The description of the 7.lock_sga and pre_page_sga parameters in the official Oracle 10gR2 documentation.

LOCK_SGAPropertyDescriptionParameter typeBooleanDefault valuefalseModifiableNoRange of valuestrue | falseBasicNo

LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space. This parameter is ignored on platforms that do not support it.

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams168.htm#REFRN10174

PRE_PAGE_SGAPropertyDescriptionParameter typeBooleanDefault valuefalseModifiableNoRange of valuestrue | false

PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.

Note:

This setting does not prevent your operating system from paging or swapping the SGA after it is initially read into memory.

PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA. The cost of this strategy is fixed; however, you might simply determine that 20000 pages must be touched every time a process starts. This approach can be useful with some applications, but not with all applications. Overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.

The advantage that PRE_PAGE_SGA can afford depends on page size. For example, if the SGA is 80 MB in size and the page size is 4 KB, then 20000 pages must be touched to refresh the SGA (80000 pages must be touched to refresh the SGA 4 = 20000).

If the system permits you to set a 4 MB page size, then only 20 pages must be touched to refresh the SGA (800000 pages must be touched to refresh the SGA). The page size is operating system-specific and generally cannot be changed. Some operating systems, however, have a special implementation for shared memory whereby you can change the page size.

By changing the parameter values of lock_sga and pre_page_sga to "TRUE", the whole SGA can be effectively locked in physical memory, which can effectively improve the performance of the system, and it is recommended to adjust it as appropriate.

Note: different operating systems have different support for this lock_sga parameter, and if the operating system does not support this locking, the lock_sga parameter will be ignored.

The above is how to use lock_sga and pre_page_sga parameters to ensure that SGA resides in physical memory. The editor believes that there are some knowledge points that we may see or use 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.