Please read << 32bit Oracle Extension SGA Principles before reading this article >>
In my test, because I reduced the starting point of SGA (lower SGA starting point and this article did not have a substantive relationship, just because my test database is already lowered by the SGA starting point), it is reduced from 0x5000000 to 0x42000000, which is Said that when there is no reduction, the theoretical limit of SGA is less than 1.75g. After I decrease the starting point, the limit value of the SGA is approximately 1.95g.
Show "> sys @ ocn> show SGA
Total System Global Area 2064719084 Bytesfixed Size 453868 Bytesvariable Size 385875968 Bytesdatabase Buffers 1677721600 BYTESREDO BUFFERS 667648 BYTESSYS @ OCN>
The size of my SGA_MAX_SIZE is 2064719084.
Show "> SYS @ o> show parameters sga_max_size
Name Type Value --------------------------------- ------------------------------ SGA_MAX_SIZE BIG INTEGER 2064719084
In fact, the SGA_max_size at this time is bigger than the real SGA setting, which is my manual. Let's observe the MAPS for a single database process.
[Oracle @ Ocnsb2 Oracle] $ PS - EF | GREP PMONORACLE 18346 1 0 12:16? 00:00:00 Ora_PMON_ROOCNORACLE 18535 18493 0 13:04 PTS / 0 00:00:00 Grep PMON [Oracle @
Ocnsb2 oracle] $ more / proc / 18346 / maps08048000-0A4B9000 r-xp 00000000 08:05 324868 /opt/racle/products/9.2.0/bin/oracle0a4b9000-0AD53000 RW-P 02470000 08:05 324868 / OPT / ORACLE / Products / 9.2.0 / bin / oracle0ad53000-0ADEC000 RWXP 00000000 00:00 040000000-40016000 r-xp 00000000 08:02 448102 /Lib/ld-2.2.4.so40016000-40017000 RW-P 00015000 08:02 448102 / lib / LD-2.2.4.SO40017000-40018000 RW-P 00000000 00:00 040018000-40019000 r-xp 00000000 08:05 1038540 /opt/oracle/ProductS/9.2.0/lib/libodmd9.so40019000-4001a000 RW-P 00000000 08 : 05 1038540 /opt/oracle/products/9.2.0/lib/libodmd9.so4001a000-40026000 r-xp 00000000 08:05 1038423 /opt/oracle/products/9.2.0/lib/libskgxp9.so40026000-4002a000 rw-p 0000B000 08:05 1038423 /opt/oracle/products/9.2.0/lib/libskgxp9.so4002a000-40038000 r-xp 00000000 08:05 1038537 /opt/oracle/products/9.2.0/lib/libskgxn9.so40038000-40039000 rw -p 0000d000 08:05 1038537 /opt/oracle/products/9.2.0/lib/libskgxn9.so40039000-4004d000 rw-p 00000000 00:00 04004d000-4032C000 r-xp 00000000 08:05 1038531 /opt/oracle/products/9.2.0/lib/libjox9.so4032C000-4043C000 RW-P 002DE000 08:05 1038531 /opt/oracle/ProductS/9.2.0 /LIB/LIBJOX9.SO4043C000-4043E000 rw-p @000000 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 00000000 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.so4046000-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 @000000 00:00 040493000-405C8000 r-xp 00000000 08: 02 448111 /Lib/Libc-2.2.4.so405c8000-405cd000 rw-p 00134000 08:02 448111 /lib/libc-2.2.4.so405cd000-405f4000 RW-P 000,000 00:00 0405F5000-405FF000 r-xp 00000000 08: 02 448136 /lib/libnss_files-2.2.4.so405ff000-40600000 rw-p 00009000 08:02 448136 /lib/libnss_files-2.2.4.so40600000-40680000 rw-p 00000000 08:02 69445 / dev / zero40680000-406c1000 rw- p 00000000 00:00 042000000-be400000 rw-B 00000000 00:04 524288 / SYSV0676004c (deleted) bffe4000-bffee000 rwxp ffff7000 00:00 00a4b9000-0ad53000 rw-p 02470000 08:05 324868 /opt/oracle/products/9.2.0 / BIN / ORACLE0AD53000-0ADEC000 RWXP 00000000 00:00 040000000-40016000 R-XP 00000000 08:02 448102 /Lib/ld-2.2.4.so40016000-40017000 RW-P 00015000 08:02 448102 /Lib/LD-2.2.4 . SO40017000-40018000 RW-P 000,000 00:00 040018000-40019000 r-xp 00000000 0 8:05 1038540 /opt/oracle/products/9.2.0/lib/libodmd9.so40019000-4001a000 rw-p 00000000 08:05 1038540 /opt/oracle/products/9.2.0/lib/libodmd9.so4001a000-40026000 r- XP 00000000 08:05 1038423 /opt/oracle/products/9.2.0/lib/libskgxp9.so40026000-4002a000 rw-p 0000b000 08:05 1038423 /opt/oracle/products/9.2.0/lib/libskgxp9.so4002a000-40038000 R-xp 00000000 08:05 1038537 /opt/oracle/products/9.2.0/lib/libskgxn9.so40038000-40039000 rw-p 0000d000 08:05 1038537 /opt/oracle/ProductS/9.2.0/lib/libskgxn9.so40039000 -4004D000 rw-p @ 000000 00:00 04004D000-4032C000 r-xp 00000000 08:05 1038531 /opt/racle/products/9.2.0/lib/libjox9.so4032C000-4043C000 RW-P 002DE000 08:
05 1038531 /opt/oracle/products/9.2.0/lib/libjox9.so4043C000-4043E000 RW-P @ @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 @ 1000000 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 00000000 00:00 040493000-405c8000 r-xp 00000000 08:02 448111 /lib/libc-2.2.4.so405c8000-405 CD000 RW-P 00134000 08:02 448111 /Lib/Libc-2.2.4.so405cd000-405f4000 rw-p 00000000 00:00 0405F5000-405FF000 r-xp 00000000 08:02 448136 /Lib/libnss_files-2.2.4.so405ff000-40600000 RW-P 0000900 0 08:02 448136 /Lib/libnss_Files-2.2.4.so40600000-40680000 RW-P 00000000 08:02 69445 / DEV / ZERO40680000-406C1000 RW-P 00000000 00:00 0420000-BE400000 RW-B 00000000 00:04 524288 / Sysv0676004c (Deleded) This can be seen in a single process based on SGA_MAX_SIZE pre-allocated this virtual memory space, although the actual SGA is not so big, it is the reservation of this space, an increase in dynamic in 9i SGA provides possible. That is to say, the virtual address space that the process can be accessed is determined when the database is logged in, so even if the SGA truly changes, as long as it is within SGA_MAX_SIZE, the process can access these virtual memory spaces.
SELECT "> sys @ OCN> Select (to_number ('be400000', 'xxxxxxxxx') - TO_NUMBER ('42000000', 'xxxxxxxxx') / 1024/1024 from DUAL
(TO_NUMBER ('be400000', 'xxxxxxxxx') - TO_NUMBER ('42000000', 'xxxxxxxxx') / 1024/1024 ----------------------- -------------------------------------------------- -------- 1988sys @ o>>
Bffe4000-bffee000 rwxp fff7000 00:00 0 [Oracle @ Ocnsb2 Oracle] $
From here you can see the vertices of the private memory allocation of the process, let's see
0xBe400000--0XBffe4000 belongs to the space size of the PGA that can be used by the Oracle process
SELECT "> sys @ @ o> select to_number ('bffe4000', 'xxxxxxxxxx') - to_number ('be400000', 'xxxxxxxxx') from DUAL;
TO_NUMBER ('bffe4000', 'xxxxxxxxx') - to_number ('be400000', 'xxxxxxxxxx') ----------------------------- ---------------------------------------- 29245440
Sys @ o>
From here you can see that the space can use is about 292454440 bytes.
Let's do a test
Show "> sys @ ocn> show parameters PGA
Name Type Value --------------------------------- ------------------------------ PGA_AGGREGATE_TARGET BIG INTEGER 524288000SYS @ o o>
The principle of using the PGA of a single process in Oracle is less than min (PGA_AGGREGATE_TARGET * 5%, 100MB), and I execute the query for two big tables under such settings, the result is successful completion. Then I modified PGA_AGGREGATE_TARGET = 1024M
Alter "> SYS @ o> ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1024M;
SYSTEM altered.
Show "> sys @ ocn> show parameters PGA
Name Type Value --------------------------------- ------------------------------ PGA_AGGREGATE_TARGET BIG INTEGER 1073741824SYS @ OCN>
When I run this query, I also observe the PGA usage of the process.
Execute in Session 1
1 * select * from company c, member m where c.admin_member_id = m.login_id / "> alibaba @ OCN> / ERROR: ORA-04030: out of process memory when trying to allocate 254476 bytes (hash-joinsubh, kllcqas: kllsltba ) no rows selected
Execution Plan
Observe the results in session2 (constantly performing the following query observation changes)
Sys @ OCN> SELECT SPID, UserName, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM from V $ Process;
SPID Username PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM ----------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------- 0 0 0 018346 oracle 147425 476981 0 47698118348 oracle 244313 570985 0 57098518350 oracle 1304737 1635765 0 163576518354 oracle 2945957 3300193 0 330019318356 oracle 200089 534729 0 53472918358 oracle 4368569 4741969 0 474196918360 oracle 151577 551357 0 55135718362 oracle 165841 510101 0 51010118364 oracle 156625 485493 0 48549318366 oracle 157009 485493 0 48549318368 oracle 4362441 4748549 0 474854918370 oracle 4362441 4715245 0 471524518388 oracle 191285 790553 196608 79055318406 oracle 29041865 30095501 0 30095501
