32bit Oracle Extension SGA Principle

zhaozj2021-02-16  82

32bit Oracle Since the number of digits, the Oracle process can only access the virtual memory address below 4G (2 32), in many times this is a very distressed problem, because there are many memory can't be used, and By default, SGA cannot exceed 1.7g. For example, there is 8G memory under our Linux, but some empty can't worry. At this time we have to consider how to expand Oracle's SGA. So first, how do I identify the 32bit Oracle? We can get the following query

Sys @ OCN> SELECT * FROM V $ VERSION;

Banner ------------------------------------- --------------- Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionPL / SQL Release 9.2.0.4.0 - ProductionCore 9.2.0.3.0 ProductionTns for Linux: Version 9.2.0.4. 0 - Productionnlsrtl Version 9.2.0.4.0 - Production

If it is 64bit Oracle, 64bit characters will be displayed in the query result. If there is no appearance, it must be 32bit oracle. Of course, you can see it through File Oracle on the OS.

[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 @ o 22 bin] $

In some OS, such as AIX, 64bit Oracle will display information normally, and 32bit is not displayed normally.

After confirming 32bit Oracle, we must understand that usually our OS process can only access space below 4G, redhat Linux AS 2.1 or AS3.0 version exception, they can provide VLM (VERY LARGE MEMORY) function support, make Through the conversion, you can use 36bit to mark the memory address, then the 36th theoretically supports 64G memory access. In Oracle, the file system is completely consisting of memory by using memory as a file, virtual one / dev / shm file system, this file system is completely consisting of memory, which will break through 4G restrictions. That time, let's see, since the process can access 4G below, why usually SGA is 1.7G.

In the OS, a process is specified in the application, the virtual memory space that can be accessed is 0-3g, while 3G - 4G virtual address space is reserved for Kernel. It is important to pay attention to the virtual address space here, and did not say physical address space, that is, if there is 8G memory, this 0-3G virtual address space may appear in 3G - 8G part memory segments of 8G memory It is not to be a 0-3g section of physical memory. In this 0-3G virtual address, how is ORACLE to use it, this is fixed.

3G: Kernel 2G: Process Stack 1.25g: SGA starting 1G: Oracle shared library load start point 0g: Oracle Program (executable code) loaded starting point in this virtual address, 1.25 g is the starting point of the SGA, and the allocation of the private space of the process (STACK part) is from close to 3G. That is, the SGA and process private space is a total of 1.25g --- 3G, because the process is particularly small, usually we habitually think that SGA can reach 1.7g. The process private space is 0.05g enough. Start from Oracle, or log in from any user process, all virtual addresses have been fixed, and only private space can be expanded. Let's take a look at the virtual address allocation of the PMON process after the database starts (any one of the processes). Since I run 2 databases on a machine, we look at one of them, first look at the database SGA related information.

[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, 2002, Oracle Corporation. All Rights Reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Productionselect "> sys @ OCN> select INSTANCE_NAME from v $ instance;

Instance_name ---------------- ROOCN1

Show "> sys @ ocn> show SGA

Total system global area 437327188 bytesfixed size 451924 bytesvariable size 301989888 bytesdatabase buffers 134217728 BYTESREDO BUFFERS 667648 BYTESSYS @ OCN>

exit "> sys @ OCN> exitDisconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production [oracle @ ocnsb1 oracle] $ ipcs

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

------ 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] $

There is only one shared memory section here, and it is the size of the SGA (ShmID 131072). This is because Shnmax sets too much.

[Oracle @ ocn2 kernel] $ more / proc / sys / kernel / shmmax3221225472 [oracle @ ocn2 kernel] $

Next, let's take a look at the PMON information, first find the PMON process number, then go to / proc / pid / maps to see the virtual address assignment information of the process.

[Oracle @ oscnsb1 oracle] $ ps -ef | grep pmonoracle 13655 1 0 jul24? 00:00:00 ora_pmon_roocn1racle 13926 1 0 JUL24? 00:00:00 Ora_PMON_OCN1Oracle 31435 31092 0 11:51 PTS / 3 00:00:00 GREP PMON [Oracle @ Ocnsb1 Oracle] $ [Oracle @ Ocnsb1 Oracle] $ More / Proc / 13655 / Maps08048000-0A4BA000 R-XP 00000000 08:05 681621 /opt/oracle/products/9.2.0/bin/oracle0a4ba000-0AD54000 RW- P 02471000 08:05 681621 /opt/oracle/products/9.2.0/bin/oracle0ad54000-0AE07000 rwxp 00000000 00:00 0

This part is Oracle Program loading information, we can see that the space is used 0--0AE07000, this part is less than 256MB

40000000-40016000 r-xp 00000000 08:02 448102 /Lib/ld-2.2.4.so

This is the starting point for Oracle sharing libraries, 0x40000000 is exactly 1G

40016000-40017000 RW-P 00015000 08:02 448102 / lib/ld-2.2.4.SO40017000-40018000 RW-P 聽 0000000 00:00 040018000-40019000 R-XP 00000000 08:05 308464 /opt/oracle/ProductS/9.2. 0 / lib / libodmd9.so40019000-4001a000 rw-p 00000000 08:05 308464 /opt/racle/ProductS/9.2.0/lib/libodmd9.so4001a000-40026000 r-xp 00000000 08:05 308345 / OPT / Oracle / Products / 9.2.0 / lib / libskgxp9.so40026000-4002a000 rw-p 0000b000 08:05 308345 /opt/oracle/ProductS/9.2.0/lib/libskgXp9.SO4002A000-40038000 r-xp 00000000 08:05 308461 / OPT / Oracle / Products / 9.2.0 / LiB / libskgxn9.so40038000-40039000 rw-p 0000d000 08:05 308461 /opt/racle/products/9.2.0/lib/libskgxn9.so40039000-4004d000 RW-P 00000000 00:00 04004D000-4032C000 R -xp 00000000 08:05 308455 /opt/racle/products/9.2.0/lib/libjox9.so4032c000-4043C000 RW-P 002DE000 08:05 308455 /opt/oracle/products/9.2.0/lib/libjox9.so4043C000- 4043E000 RW-P 00000000 00:00 04043E000-40441000 r-xp 00000000 08:02 448115 /Lib/Libdl-2.2.4.so40441000-40442000 rw -p 00002000 08:02 448115 /lib/libdl-2.2.4.so40442000-40443000 rw-p @ 000000 00:00 040443000-40465000 r-xp 00000000 08:02 448117 /lib/libm-2.2.4.so40465000-40466000 rw -p 00021000 08:02 448117 /lib/libm-2.2.4.so40466000-40475000 r-xp 00000000 08:02 448147 / Lib/Libpthread-0.9.so40475000-4047d000 rw-p 0000E000 08:02 448147 / lib / libpthread- 0.9.so4047d000-40490000 r-xp 00000000 08:02 448120 /Lib/Libnsl-2.2.4.so40490000-40491000 rw-p 00012000 08:02 448120 /Lib/libnSL-2.2.4.so40491000-40493000 RW-p 0000000000 00 00 00 00 00 : 00 040493000-40494000 r-xp 00000000 08:02 352330 /usR/LIB/LIBAIO.SO.140494000-40495000 RW-P 00000000 08:02 352330 /usR/LIB/LIBAIO.SO.140495000-405CA000 r-xp 00000000 08 :

02 448111 /Lib/Libc-2.2.4.so405ca000-405cf000 rw-p 00134000 08:02 448111 /lib/libc-2.2.4.so405cf000-405d3000 rw-p 聽 0000000 00:00 0405D3000-405D4000 r-xp 00000000 08: 02 146106 /Lib/LibredHat-kernel.so.1.0.1405d4000-405d5000 rw-p 00000000 08:02 146106 /Lib/Libredhat-kernel.so.1.0.1405d5000-405f9000 rw-p @40604000 r-000-40604000 r xp 00000000 08:02 448136 /lib/libnss_files-2.2.4.so40604000-40605000 rw-p 00009000 08:02 448136 /lib/libnss_files-2.2.4.so40605000-40685000 rw-p 00000000 08:02 69445 / dev / zero40685000 -406C6000 RW-P 00000000 00:00 0 Shared library consumes less than 20MB space

50000000-6B000000 RW-S 00000000 00:04 131072 / SYSV73A32BDC (Deleded)

This is the starting point of SGA, 0x50000000 represents 1.25g

6b000000-6b001000 r - s 1b000000 00:04 131072 / SYSV73a32bdc (deleted) 6b001000-6b0a2000 rw-s 1b001000 00:04 131072 / SYSV73a32bdc (deleted) 6b0a2000-6b0a3000 r - s 1b0a2000 00:04 131072 / SYSV73a32bdc (deleted) 6B0A3000-6B400000 RW-S 1B0A3000 00:04 131072 / SYSV73A32BDC (DELETED)

SGA virtual space is allocated here, by calculating 16-based number, just consistent with our SGA size, 131072 is the shmid in the IPCS

Bffe5000-bffee000 RWXP fff8000 00:00 0BFFF0000-BFF1000 R-XS 00000000 08:02 69304 / dev / vsys

Since the 0xC0000000 is exactly 3G (16) C = 12, 4 * 3 = 12, 0X40000000 represents 1G), the starting point of the allocation of the process private space is indicated here. Seeing Oracle Any user login process will also discover such virtual address assignments. Here we are easy to see, the space occupied by Oracle Program and the shared memory library is small, there is no need to give so big, in fact, oracle program is safe enough to give 256M, and the shared library is also safe enough, that is In theory, we can compress the Oracle Program below 0x10000000, the shared library needs memory compression below 0x12000000, so the starting point of SGA can be increased to 0x12000000 (0.3g). It turns out that from 0x5000000 (1.25g), only about 1.7g is allocated to SGA, and the SGA is now assigned from 0.3g, such as 2.65g memory to SGA. To implement this feature, we need to recompile Oracle Program to reduce the address of the shared library virtual memory allocation and the start point position of SGA. 0x4000000 This shared library load started, is determined by the process's mapped_base [Oracle @ Ocnsb1 Oracle] $ more / proc / 13655 / mapped_base1073741824

This size is 1G, which means that the loading of the shared library starts from the virtual address 1G position. If you want to reduce this address, you need to reduce the mapped_base that will start the Oracle process before the Oracle is booted, so The generated process after Oracle starts will inherit this value.

Su - root echo 268435456> / proc // mapped_base Of course, we can also automatically reduce the function of mapped_base after the Oracle user login, this can be found on Google, or refer to http://www.puschitz.com / TUNINGLINUXFOROracle.shtml#increasingSpaceForLorgergersga Chinese seal 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

# Lying "mapped base" to 0x10000000 echo 268435456> / proc / $ PPID / mapped_base # make sure what wNSHIP 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 ounce:

$ 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 Automatical 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 visudo:

Su - root visudochange the entry in / etc / sudoers from:

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

Oracle all = NOPASSWD: / usr / local / bin / changemappedbasemake supe / frequency. You can use during the login process. You can use e.g. ~ Oracle / .bash_profile:

Su - Oracle Echo "Sudo / USR / local / bin / changemappedbase" >> ~ / .bash_profilethe next time you login to oracle, the base address for shared libraries Will Bet Set Automatic. $ ssh oracle @ localhost

Oracle @ localhost's password: Last Login: Sun Apr 6 13:59:22 2003 from localhost

$ cat / proc / $$ / mapped_base; echo 268435456 $

The SGA starting point is reduced from 1.25g to 0.3g, and the Oracle Program needs to be recompiled. It must be emphasized that the starting point of the SGA is related to the starting point mapped_ase of the shared library. The starting point of the SGA is at least 0.05G above the shared library is safe, otherwise the database will not start or crash.

Close Oracle Su - Oracle CD $ Oracle_Home / RDBMS / LIB

Modify the file definition of the shared library loading address

Genksms -s 0x12000000> ksms.s

Compile the target file

Make -f INS_RDBMS.MK Ksms.o

Recompass the Oracle executable Make -f ins_rdbms.mk iracle to redhat Linux AS 2.1 The above version of Oracle's VLM is also relatively simple, refer to http://www1.ap.dell.com/content/topics/topic .ASPX / AP / TOPICS / POWER / ENCN / PS3Q03_MAHMOOD? C = CN & L = zh & s = bsdhttp: //otn.racle.com/global/cn/pub/notes/technote_rhel3.html of course, there is more articles on the Internet. for reference. Here I have to point out a problem, it is also a problem we encountered in practice, that is, if the SGA is allocated, but not using VLM, it is almost very close to 3G, about only 20m. This is a process for Hash Join, because our PGA_AGGREGATE_TARGET is set to 1G, Oracle default single process uses PGA_AGGREGATE_TARGET * 5% = 50m, so it makes it error ORA-04030: ORA-04030: ORA-04030: ORA-04030: Out of process memory when Trying To alltes (Hash-Join Subh, Kllcqas: Kllsltba) We adjust the PGA_AGGREGATE_TARGET to reduce 400M, the query is successful. Since there is no VLM, the memory allocation space of a single process must be below 3g, and the distribution of PGA is also in this category. If the PGA has been assigned to a virtual address of 4G or more, it is no longer there. There is no longer excessive elaboration in the use of VLM, because the use is relatively simple, as in principle is through the OS extension 32bit to 36bit, Oracle uses files to manage memory, and supports the process to access the virtual memory of 4G above. This usage is promoted on Linux because its 64bit Oracle is rarely used, and other 64bit Oracle is widely used as SunOS / HP UNIX / AIX, which will lose value.

For more information on PGA and SGA, please refer to

SGA_MAX_SIZE in 32bit Oracle and Signal Relationship between Single Process PGA

转载请注明原文地址:https://www.9cbs.com/read-13247.html

New Post(0)