How 32bit Oracle expands SGA

 

Due to the limited number of digits, The 32bit Oracle process can only access virtual memory addresses below 4 GB (the power of 2), which is a headache in many cases, because a lot of memory is left empty, the SGA cannot exceed 1.7 GB by default. For example, we have 8 GB of memory in Linux, but some of them are empty. In this case, we need to consider how to expand the SGA of oracle. First, how should we identify a 32-bit Oracle database? We can query the following information.

Sys @ OCN> select * from V $ version;

Banner
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
PL/SQL release 9.2.0.4.0-Production
Core 9.2.0.3.0 Production
TNS for Linux: Version 9.2.0.4.0-Production
Nlsrtl version 9.2.0.4.0-Production

If it is 64-bit Oracle, the query results will certainly display 64-bit characters, but not appear, it must be 32-bit oracle. Of course, you can also see it on the OS through file oracle.

[Oracle @ ocn2 bin] $ CD $ ORACLE_HOME/bin
[Oracle @ ocn2 bin] $ file Oracle
ORACLE: setuid setgid elf 32-bit LSB executable, Intel 80386, Version 1, dynamically linked (uses SHARED libs), not stripped
[Oracle @ ocn2 bin] $

On some operating systems, such as Aix, 64-bit Oracle displays information normally, while 32-bit Oracle does not.

After confirming 32bit Oracle, we need to understand that our OS process can only access space below 4 GB, except for Redhat Linux as 2.1 or as3.0, they can provide support for the vlm (very large memory) function, so that the conversion can use 36bit to mark the memory address, that is, the 36th power of 2 theoretically supports a maximum of 64 GB memory access. In Oracle, the memory is accessed as a file. The Virtual File System of/dev/SHM is completely composed of memory, this will break through the limitations of 4G. Let's take a look at it. Since the process can access the memory below 4 GB, why is SGA 1.7g in general.

In OS, a process is specified in an application, and the virtual memory space that can be accessed is 0-3 GB, the 3G-4g virtual address space is reserved for the kernel. Note that we emphasize the virtual address space, which is not physical address space. That is to say, assume that there is 8 GB of memory, this 0-3G virtual address space may appear in the 3G-8g memory part of the memory segment, not the physical memory 0-3G segment. In this 0-3G virtual address, How Does Oracle use it? This is a fixed address.

 

++ 4G
++
++
++
++ 3G: Kernel
++
++
++
++ 2G: process stack
++
++
++ 1.25g: SGA start point
++ 1G: starting point for Oracle Shared Library Loading
++
++
++
++ 0g: Oracle program (executable code) loading start point

In the allocation of this virtual address, 1.25g is the starting point of SGA, while the allocation of the private space of the process (the stack part) starts from close to 3G. That is, in fact, the SGA and the process private space share the 1.25 GB-3 GB part. Because the process private space is very small, we usually habitually think that the SGA can reach 1.7 GB. The process private space is 0.05g enough. From the beginning of Oracle, or from the login of any user process, the allocation of all virtual addresses has been fixed, and only the user's private space can be expanded. Let's take a look at the Virtual Address Allocation situation of the pmon process (in fact, any process is the same) after the database is started. Because I run two databases on one machine, let's take a look at the SGA database information first.

[Root @ ocnsb1 root] # Su-Oracle
[Oracle @ ocnsb1 Oracle] $ sqlplus "/As sysdba"

SQL * Plus: Release 9.2.0.4.0-production on Mon Jul 26 11:37:23 2004

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

Connected:
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, real Application Clusters, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production

Select "> sys @ OCN> select instance_name from V $ instance;

Instance_name
----------------
Roocn1

Show "> sys @ OCN> show SGA

Total system global area 437327188 bytes
Fixed size 451924 bytes
Variable Size 301989888 bytes
Database buffers 134217728 bytes
Redo buffers 667648 bytes
Sys @ OCN>

Exit "> sys @ OCN> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, real Application Clusters, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production
[Oracle @ ocnsb1 Oracle] $ IPCS

------ Shared memory segments --------
Key shmid owner perms bytes nattch status
0x73a32bdc 131072 Oracle 640 457179136 50
0x84cc76ac 163841 Oracle 640 1379926016 90

------ Semaphore arrays --------
Key Semid owner perms nsems status
0x8df96364 622592 Oracle 640 64
0x53609d64 753665 Oracle 640 504

------ Message queues --------
Key msqid owner perms used-bytes messages

[Oracle @ ocnsb1 Oracle] $

I only have one shared memory segment here, which is the size of SGA (shmid is 131072 ). This is because the shnmax setting is too large.

[Oracle @ ocn2 kernel] $ more/proc/sys/kernel/shmmax
3221225472
[Oracle @ ocn2 kernel] $

Next, let's take a look at the pmon information. First, find the pmon process number, and then view the Virtual Address allocation information of the Process in/proc/Pid/maps.

[Oracle @ ocnsb1 Oracle] $ PS-Ef | grep pmon
Oracle 13655 1 0 jul24? 00:00:00 ora_pmon_roocn1
Oracle 13926 1 0 jul24? 00:00:00 ora_pmon_ocn1
Oracle 31435 31092 0 00:00:00 pts/3 grep pmon
[Oracle @ ocnsb1 Oracle] $

[Oracle @ ocnsb1 Oracle] $ more/proc/13655/maps
08048000-0a4ba000 R-XP 00000000 681621/opt/Oracle/products/9.2.0/bin/Oracle
0a4ba000-0ad54000 RW-P 02471000 681621/opt/Oracle/products/9.2.0/bin/Oracle
0ad54000-0ae07000 rwxp 00000000 0

This part is the Oracle program loading information. We can see that the space is 0 -- 0ae07000, and the size of this part is less than 256 MB.

40000000-40016000 R-XP 00000000/lib/ld-2.2.4.so

This is the starting point for Oracle Shared Library Loading. 0x40000000 is exactly 1 GB.

40016000-40017000 RW-P 00015000 448102/lib/ld-2.2.4.so
40017000-40018000 RW-P 00000000 0
40018000-40019000 R-XP 00000000 308464/opt/Oracle/products/9.2.0/lib/libodmd9.so
40019000-4001a000 RW-P 00000000 308464/opt/Oracle/products/9.2.0/lib/libodmd9.so
4001a000-40026000 R-XP 00000000 308345/opt/Oracle/products/9.2.0/lib/libskgxp9.so
40026000-4002a000 RW-P rjb000 08:05 308345/opt/Oracle/products/9.2.0/lib/libskgxp9.so
4002a000-40038000 R-XP 00000000 308461/opt/Oracle/products/9.2.0/lib/libskgxn9.so
40038000-40039000 RW-P rjd000 08:05 308461/opt/Oracle/products/9.2.0/lib/libskgxn9.so
40039000-4004d000 RW-P 00000000 0
4004d000-4032c000 R-XP 00000000 308455/opt/Oracle/products/9.2.0/lib/libjox9.so
4032c000-4043c000 RW-P 002de000 08:05 308455/opt/Oracle/products/9.2.0/lib/libjox9.so
4043c000-4043e000 RW-P 00000000 00:00 0
4043e000-40441000 R-XP 00000000 448115/lib/libdl-2.2.4.so
40441000-40442000 RW-P 00002000 448115/lib/libdl-2.2.4.so
40442000-40443000 RW-P 00000000 0
40443000-40465000 R-XP 00000000/lib/libm-2.2.4.so
40465000-40466000 RW-P 00021000 448117/lib/libm-2.2.4.so
40466000-40475000 R-XP 00000000/lib/libpthread-0.9.so
40475000-4047d000 RW-P rje000 448147/lib/libpthread-0.9.so
4047d000-40490000 R-XP 00000000 448120/lib/libnsl-2.2.4.so
40490000-40491000 RW-P 00012000 448120/lib/libnsl-2.2.4.so
40491000-40493000 RW-P 00000000 0
40493000-40494000 R-XP 00000000/usr/lib/libaio. so.1
40494000-40495000 RW-P 00000000/usr/lib/libaio. so.1
40495000-405ca000 R-XP 00000000 448111/lib/libc-2.2.4.so
405ca000-405cf000 RW-P 00134000 448111/lib/libc-2.2.4.so
405cf000-405d3000 RW-P 00000000 00:00 0
405d3000-405d4000 R-XP 00000000 146106/lib/libredhat-kernel.so.1.0.1
405d4000-405d5000 RW-P 00000000 146106/lib/libredhat-kernel.so.1.0.1
405d5000-405f9000 RW-P 00000000 00:00 0
405fa000-40604000 R-XP 00000000 448136/lib/libnss_files-2.2.4.so
40604000-40605000 RW-P 00009000 448136/lib/libnss_files-2.2.4.so
40605000-40685000 RW-P 00000000 69445/dev/zero
40685000-406c6000 RW-P 00000000 00:00 0

Shared libraries consume less than 20 mb of space

50000000-6b000000 RW-s 00000000 131072/sysv73a32bdc (Deleted)

This is the starting point of SGA. 0x50000000 indicates 1.25g.

6b000000-6b001000 r -- s 1b000000 131072/sysv73a32bdc (Deleted)
6b001000-6b0a2000 RW-s 1b001000 131072/sysv73a32bdc (Deleted)
6b0a2000-6b0a3000 r -- s 1b0a2000 131072/sysv73a32bdc (Deleted)
6b0a3000-6b400000 RW-s 1b0a3000 131072/sysv73a32bdc (Deleted)

The SGA virtual space is allocated here. By calculating the hexadecimal number, it is exactly the same as the size of our SGA. 131072 is the shmid we checked in IPCS.

Bffe5000-bffee000 rwxp ffff8000 00: 00 0
Bfff0000-bfff1000 R-xs 00000000 69304/dev/vsys

Because 0xc0000000 is exactly 3G (hexadecimal number C = 12, 4*3 = 12, 0x40000000 represents 1G), it indicates the starting point of private space allocation of the process. When you view any user login process in Oracle, you will find such virtual address allocation. It is easy to see that the space occupied by Oracle program and the shared memory library is very small and there is no need to make it so large. In fact, Oracle program is safe enough for MB, the shared library to 50 m is also safe enough, that is, theoretically, we can compress the Oracle program to 0 x or less, and the shared library to compress the memory to 0 x or less, in this way, the starting point of SGA can be increased to 0x12000000 (0.3g ). Originally, it started from 0x5000000 (1.25g), and only about 1.7g was allocated to SGA. Now, from 0.3g, SGA can be allocated close to 2.7g, for example, 2.65g memory is allocated to SGA. To implement this function, we need to re-compile the Oracle program to reduce the address allocated by the shared library virtual memory and the starting position of the SGA allocation. The starting point for loading the shared library 0x4000000 is determined by the mapped_base of the process.

[Oracle @ ocnsb1 Oracle] $ more/proc/13655/mapped_base
1073741824

This size is 1 GB, which means that the load of the shared library starts from 1 GB of the virtual address. To reduce this address, before starting Oracle, that is, use the root user to reduce the mapped_base of the Oracle-started process to 256 m, so that all processes generated after Oracle-started will inherit this value.

Su-root echo 268435456>/proc // mapped_base
Of course, we can also use shell to implement the mapped_base function automatically after oracle users log on, which can be found on Google or referred.
The article in http://www.puschitz.com/TuningLinuxForOracle.shtml#IncreasingSpaceForLargerSGA is as follows

Giving oracle users the privilege to change the base address for Oracle's shared libraries without giving them root access

As shown above, only root can change the base address "mapped base" for shared libraries. using sudo we can give oracle users the privilege to change "mapped base" for their own shells without giving them full root access. here is the procedure:

Su-Root

# E. g. Create a script called "/usr/local/bin/changemappedbase"

# Which changes the "mapped base" for the parent process,

# The shell used by the Oracle user where the "sudo" Program # is executed (forked). Here is an example:

#/Bin/sh

# Lowering "mapped base" to 0x10000000 echo 268435456>/proc/$ ppid/mapped_base

# Make Sure That owernship and permissions are correct chown root. Root/usr/local/bin/changemappedbase

Chmod 755/usr/local/bin/changemappedbase

# Allow the Oracle user to execute/usr/local/bin/changemappedbase via sudo echo "Oracle all =/usr/local/bin/changemappedbase">/etc/sudoers
Now the Oracle user can run/usr/local/bin/changemappedbase to change "mapped base" for it's own shell:

$ Su-Oracle

$ CAT/proc/$/mapped_base; echo 1073741824

$ Sudo/usr/local/bin/changemappedbase password:

# Type in the password for the Oracle user account

$ CAT/proc/$/mapped_base; echo 268435456 $
When/usr/local/bin/changemappedbase is executed the first time after an oracle login, sudo will ask for a password. the password that needs to be entered is the password of the Oracle user account.

Changing the base address for Oracle's shared libraries automatically during an oracle Login

The procedure in the previous section asks for a password each time/usr/local/bin/changemappedbase is executed the first time after an oracle login. to have "mapped base" changed automatically during an oracle login without a password, the following can be done:

Edit the/etc/sudoers file with your do:

Su-root login do
Change the entry in/etc/sudoers from:

Oracle all =/usr/local/bin/changemappedbase
To read:

Oracle all = nopasswd:/usr/local/bin/changemappedbase
Make sure Bash executes/usr/local/bin/changemappedbase during the login process. You can use e.g .~ Oracle/. bash_profile:

Su-Oracle echo "sudo/usr/local/bin/changemappedbase" >> ~ /. Bash_profile
The next time you login to Oracle, the base address for shared libraries will bet set automatically.

$ SSH Oracle @ localhost

Oracle @ localhost's password: Last login: Sun APR 6 13:59:22 2003 from localhost

$ CAT/proc/$/mapped_base; echo 268435456 $

 

To reduce the SGA start point from 1.25g to 0.3g, you need to re-compile the Oracle program. It must be emphasized that the starting point of SGA is related to the starting point of the shared library mapped_ase. The starting point of SGA is at least 0.05g higher than the starting point of the shared library, otherwise, the database cannot be started or crashed.

Disable Oracle
Su-Oracle
CD $ ORACLE_HOME/rdbms/lib

Modify the file definition of the shared library load address

Genksms-S 0x12000000> ksms. s

Compile the target file

Make-F ins_rdbms.mk ksms. o

Recompile the oracle executable file
Make-F ins_rdbms.mk ioracle
 
 
The use of Oracle vlm in RedHat Linux as 2.1 or later versions is also relatively simple. For more information, see
Http://www1.ap.dell.com/content/topics/topic.aspx/ap/topics/power/zhcn/ps3q03_mahmood? C = cn & l = ZH & s = BSD
Http://otn.oracle.com/global/cn/pub/notes/technote_rhel3.html
Of course, there are more articles on the Internet for your reference.
 
Here I want to point out a problem, which is also a problem we encountered in practice. That is, if the SGA allocation is large but vlm is not used, it is almost close to 3G, around 20 mb. In this way, when a process performs hash join, because our pga_aggregate_target is set to 1g, by default, a single process in Oracle can use PGA to reach pga_aggregate_target * 5% = 50 m, this causes an error during hash join.
ORA-04030: Out of process memory when trying to allocate 254476 bytes (hash-join Subh, kllcqas: kllslibs)
If we reduce pga_aggregate_target to MB, the query is executed successfully. Because vlm is not used, the memory allocation space of a single process must be less than 3 GB, and PGA allocation also belongs to this category. If vlm is used, the PGA has been allocated to the virtual address above 4G. Vlm is not described too much here, because it is also relatively simple to use. In principle, it is extended from 32bit to 36bit through OS, and Oracle uses files to manage the memory, and supports process access to virtual memory above 4 GB. The root cause for the promotion of such usage in Linux is that 64-bit oracle is rarely used. Other methods such as SunOS/hp unix/AIX are widely used in 64-bit Oracle, which will lose value.

 

For more information about the relationship between PGA and SGA, see

Control relationship between sga_max_size and PGA of a single process in 32bit Oracle