| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Automatic Shared Memory Management
On Fri, 24 Mar 2006 08:36:18 -0800, zak.khalid wrote:
> How do I know if Automatic Shared Memory Management is on? I've just > migrated to 10g from 9i, and I started getting ORA-4030 errors all over > the place, for sql statements, PL/SQL and cronned oracle jobs.....
With automatic PGA workarea sizin policy enabled, ORA-04030 come automatically, you don't have to work for it.
Normally, ORA-04030 means that your OS is unable to allocate more memory.I recently had a discussion about that and a guy started babbling about ulimit, at which point I stopped caring. If you are on a Unix system, ORA-04030 means that your virtual address space cannot be extended. You have to increase the maximum address space of the oracle processes on your system. Linux (FC3) has the file /etc/security/limits.conf which looks like this:
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain> <type> <item> <value>
#
#Where:
#<domain> can be:
# - an user name
# - a group name, with @group syntax
# - the wildcard *, for default entry
# - the wildcard %, can be also used with %group syntax,
# for maxlogin limit
#
#<type> can have the two values:
# - "soft" for enforcing the soft limits
# - "hard" for enforcing hard limits
#
#<item> can be one of the following:
# - core - limits the core file size (KB)
# - data - max data size (KB)
# - fsize - maximum filesize (KB)
# - memlock - max locked-in-memory address space (KB)
# - nofile - max number of open files
# - rss - max resident set size (KB)
# - stack - max stack size (KB)
# - cpu - max CPU time (MIN)
# - nproc - max number of processes
# - as - address space limit
# - maxlogins - max number of logins for this user
# - priority - the priority to run user process with
# - locks - max number of file locks the user can hold
#
#<domain> <type> <item> <value>
The parameter that you want to increase is "as". The units are pages, which means that you will define the maximum number of pages oracle processes can use. If you try to go over that value, you will get 4030. On some operating systems default limit is around 1GB. The limit is checked by computing the size of the process page tables and all pages count, even the shared ones. If an oracle process maps SGA in its address space (and they usually do just that) you will have to subtract the size of SGA from your virtual address space. You will also have to subtract shared libraries and the oracle code size. With a decent sized SGA of 700MB or 800MB you will not have much address space left and you will start getting 4030 errors all over the place. Particularly sensitive system is HP-UX as it has very low segment limit, which is where PGA is allocated from. The parameter in question is MAXDSIZE and increasing the parameter will need to be followed by kernel relink. Unix SYSVR4, which is what HP-UX is, has two levels of indirections: it has segment table and each segment has a page table. Process address space is comprised of segments. There are several types of segments:
1) Data segments
2) Text segments (also called "code segments")
3) Stack segments
4) BS segments (also called "management segments" or unitialized data
segments. The proper abbreviation is "BSS", but I
prefer leaving the trailing "S" out)
5) Shared memory segments.
PGA is allocated from the data segments, which can be seen either from
/proc or by using a tool like pmap. The pmap output looks like this:
$ pmap 6270
6270: oracle10G (LOCAL=NO)
005dd000 84K r-x-- /lib/ld-2.3.6.so 005f3000 4K r-x-- /lib/ld-2.3.6.so 005f4000 4K rwx-- /lib/ld-2.3.6.so 005f7000 132K r-x-- /lib/tls/libm-2.3.6.so 00618000 4K r-x-- /lib/tls/libm-2.3.6.so 00619000 4K rwx-- /lib/tls/libm-2.3.6.so 0061c000 8K r-x-- /lib/libdl-2.3.6.so 0061e000 4K r-x-- /lib/libdl-2.3.6.so 0061f000 4K rwx-- /lib/libdl-2.3.6.so 0070d000 1168K r-x-- /lib/tls/libc-2.3.6.so 00831000 4K ----- /lib/tls/libc-2.3.6.so 00832000 8K r-x-- /lib/tls/libc-2.3.6.so 00834000 8K rwx-- /lib/tls/libc-2.3.6.so 00836000 8K rwx-- [ anon ] 0084a000 56K r-x-- /lib/tls/libpthread-2.3.6.so 00858000 4K r-x-- /lib/tls/libpthread-2.3.6.so 00859000 4K rwx-- /lib/tls/libpthread-2.3.6.so 0085a000 8K rwx-- [ anon ] 07fbc000 72K r-x-- /lib/libnsl-2.3.6.so 07fce000 4K r-x-- /lib/libnsl-2.3.6.so 07fcf000 4K rwx-- /lib/libnsl-2.3.6.so 07fd0000 8K rwx-- [ anon ] 08048000 77576K r-x-- /oracle/product/10g/bin/oracle 0cc0a000 356K rwx-- /oracle/product/10g/bin/oracle 0cc63000 516K rwx-- [ anon ] 20000000 331776K rwxs- [ shmid=0x12200013 ] b6b65000 64K rwx-- /dev/zero b6b75000 64K rwx-- /dev/zero b6b85000 64K rwx-- /dev/zero b6b95000 64K rwx-- /dev/zero b6ba5000 128K rwx-- /dev/zero b6bc5000 128K rwx-- /dev/zero b6be5000 704K ----- /dev/zero b6c95000 64K rwx-- /dev/zero b6ca5000 64K rwx-- /dev/zero b6cb5000 64K rwx-- /dev/zero b6cc5000 64K rwx-- /dev/zero b6cd5000 64K rwx-- /dev/zero b6ce5000 512K ----- /dev/zero b6d98000 128K rwx-- /dev/zero b6db8000 128K rwx-- /dev/zero b6dd8000 128K rwx-- /dev/zero b6df8000 128K rwx-- /dev/zero b6e18000 128K rwx-- /dev/zero b6e38000 64K rwx-- /dev/zero b6e48000 64K rwx-- /dev/zero b6e58000 64K rwx-- /dev/zero b6e68000 64K rwx-- /dev/zero b6e78000 64K rwx-- /dev/zero b6e88000 64K rwx-- /dev/zero b6e98000 64K rwx-- /dev/zero b6ea8000 64K rwx-- /dev/zero b6eb8000 64K rwx-- /dev/zero b6ec8000 64K rwx-- /dev/zero b6ed8000 64K rwx-- /dev/zero b6ee8000 64K rwx-- /dev/zero b6ef8000 64K rwx-- /dev/zero b6f08000 64K rwx-- /dev/zero b6f18000 64K rwx-- /dev/zero b6f28000 64K rwx-- /dev/zero b6f38000 64K rwx-- /dev/zero b6f48000 64K rwx-- /dev/zero b6f58000 64K rwx-- /dev/zero b6f68000 64K rwx-- /dev/zero b6f78000 64K rwx-- /dev/zero b6f88000 64K rwx-- /dev/zero b6f98000 64K rwx-- /dev/zero b6fa8000 64K rwx-- /dev/zero b6fb8000 64K rwx-- /dev/zero b6fc8000 64K rwx-- /dev/zero b6fd8000 64K rwx-- /dev/zero b6fe8000 64K rwx-- /dev/zero b6ff8000 64K rwx-- /dev/zero b7008000 64K rwx-- /dev/zero b7018000 64K rwx-- /dev/zero b7028000 64K ----- /dev/zero b7038000 128K rwx-- /dev/zero b7058000 128K rwx-- /dev/zero b7078000 128K rwx-- /dev/zero b7198000 64K rwx-- /dev/zero b71a8000 64K rwx-- /dev/zero b71b8000 64K rwx-- /dev/zero b71c8000 64K rwx-- /dev/zero b71d8000 64K rwx-- /dev/zero b71e8000 64K rwx-- /dev/zero b71f8000 64K rwx-- /dev/zero b7208000 64K rwx-- /dev/zero b7218000 36K r-x-- /lib/libnss_files-2.3.6.so b7221000 4K r-x-- /lib/libnss_files-2.3.6.so b7222000 4K rwx-- /lib/libnss_files-2.3.6.so b7223000 52K rwx-- /dev/zero b7230000 64K rwx-- /dev/zero b7240000 64K rwx-- /dev/zero b7250000 64K rwx-- /dev/zero b7260000 64K rwx-- /dev/zero b7270000 64K rwx-- /dev/zero b7280000 64K rwx-- /dev/zero b7290000 64K rwx-- /dev/zero b72a0000 12K rwx-- /dev/zero b72a3000 1500K rwx-- [ anon ] b741a000 4K r-x-- /usr/lib/libaio.so.1.0.1 b741b000 4K rwx-- /usr/lib/libaio.so.1.0.1 b7439000 1908K r-x-- /oracle/product/10g/lib/libnnz10.so b7616000 156K rwx-- /oracle/product/10g/lib/libnnz10.so b763d000 4K rwx-- [ anon ] b763e000 92K r-x-- /oracle/product/10g/lib/libdbcfg10.so b7655000 8K rwx-- /oracle/product/10g/lib/libdbcfg10.so b7657000 32K r-x-- /oracle/product/10g/lib/libclsra10.so b765f000 4K rwx-- /oracle/product/10g/lib/libclsra10.so b7660000 4K rwx-- [ anon ] b7661000 7040K r-x-- /oracle/product/10g/lib/libjox10.so b7d41000 264K rwx-- /oracle/product/10g/lib/libjox10.so b7d83000 4K rwx-- [ anon ] b7d84000 344K r-x-- /oracle/product/10g/lib/libocrutl10.so b7dda000 16K rwx-- /oracle/product/10g/lib/libocrutl10.so b7dde000 4K rwx-- [ anon ] b7ddf000 272K r-x-- /oracle/product/10g/lib/libocrb10.so b7e23000 4K rwx-- /oracle/product/10g/lib/libocrb10.so b7e24000 444K r-x-- /oracle/product/10g/lib/libocr10.so b7e93000 4K rwx-- /oracle/product/10g/lib/libocr10.so b7e94000 4K r-x-- /oracle/product/10g/lib/libskgxn2.so b7e95000 4K rwx-- /oracle/product/10g/lib/libskgxn2.so b7e96000 884K r-x-- /oracle/product/10g/lib/libhasgen10.so b7f73000 16K rwx-- /oracle/product/10g/lib/libhasgen10.so b7f77000 20K rwx-- [ anon ] b7f7c000 108K r-x-- /oracle/product/10g/lib/libskgxp10.so b7f97000 8K rwx-- /oracle/product/10g/lib/libskgxp10.so bfe69000 192K rwx-- [ stack ] ffffe000 4K ----- [ anon ]
PGA is located in the areas mapped to /dev/zero (which is dynamically allocated process memory - /dev/zero returns any number of binary zeros, great if you want to initialize something). On HP-UX maximum data segment was 64MB (I don't work with HP-UX since 2004, so I don't know if this has changed) which will be easily taken by a hash join in a DW environment. All modern Unix systems and some of non-Unix systems (VMS) have tools for studying process address space. This same output could have been obtained by "cat /proc/6270/maps", which would work on Solaris, HP-UX, Linux and probably even AIX.
-- http://www.mgogala.comReceived on Fri Mar 24 2006 - 20:54:24 CST
 |
 |