Memory per process
Rukshan Soza, May 02, 2002 - 5:05 am UTC
Tom,
We are not running on Solaris therefore pmap is not available. Is there any other way, perhaps querying v$ tables to get this information (ie. PGA memory usage per session).
Thanks & Rgds
Rukshan
May 02, 2002 - 7:15 am UTC
v$sesstat joined to v$statname.
Reader
A reader, May 02, 2002 - 7:29 am UTC
Tom,
"
The UGA normally comes from the SGA to allow migration of sessions across processes.
"
Are you referring to "migration of session enabled" as MTS
and "across processes" to shared servers
Thanks
May 02, 2002 - 9:17 am UTC
MTS is shared server. They are synonymous.
I am referring to the ability to use MTS/shared server, yes.
parameters for PGA
GVN, February 05, 2003 - 8:55 am UTC
In your book you mention that
<>One of the largest impacts of size on your PGA/UGA will be the init.ora or session-level parameters, sort_area_size and sort_area_retained_size.<>
1) What other ini parameterts determine the size of PGA and UGA in a dedicated server connection?
2) The values sort_area_size and sort_area_retained_size are for each session and not for the whole database. Right?
3) If you get a large pool error in a dedicated server connection, what would that mean? since the UGA is part of PGA why would I be getting a large pool error?
4) How would I know if the DB is running out of PGA or UGA?
5) How do you decide on setting the # of db_block_buffers?
February 05, 2003 - 9:11 am UTC
1) hash area size, bitmap merge area size are the other two.
use of features is the last. if you fill a plsql table type up with a million entries, that'll take a bit of memory from your UGA
2) they are session level, yes..
3) show me the error stack and I'll comment. we use the large pool for other things (like parallel query)
4) ora-4030 tells you that. OS tools can watch real memory on the OS (thats PGA) and if you use MTS, looking at the v$sgastat can help you watch the amount of large pool you are using.
If you have 9i -- use workarea_size_policy = auto and pga_aggregate_target and forget about sort/hash/bitmap sizes. It uses memory in a much nicer way - can even release memory back to the OS when it doesn't need it anymore.
5) depends on the system..... depends on the amount of ram, number of users, configuration (shared/dedicated). You want to
a) use all of the memory since you cannot put it in the bank and save it for later
b) use at MOST all of the memory (mimize paging/eliminate swapping)
c) use it for the RIGHT things -- if you put it all in the buffer cache but short change the shared pool -- it could be deadly. if you put it all in the large pool and dont use it, you wasted it and so on.
pmap example
A reader, February 05, 2003 - 9:26 am UTC
Tom, below is an output from pmap. Could you please break it down and explain the main memory segments used here ? What are the main SGA and PGA structures here ?
oracle[oraprd]@srv16:/ora/92> pmap 10355
10355: oracleoraprd (LOCAL=NO)
0000000100000000 52360K read/exec /ora/92/bin/oracle
0000000103420000 1040K read/write/exec /ora/92/bin/oracle
0000000103524000 528K read/write/exec [ heap ]
0000000380000000 364544K read/write/exec/shared [ ism shmid=0x3e8 ]
FFFFFFFF7AC00000 8K read/write [ anon ]
FFFFFFFF7AC10000 8K read/write [ anon ]
FFFFFFFF7AC50000 192K read/write [ anon ]
FFFFFFFF7CE00000 24K read/exec /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7CF06000 8K read/write/exec /usr/lib/sparcv9/nss_files.so.1
FFFFFFFF7D000000 8K read/write/exec [ anon ]
FFFFFFFF7D100000 16K read/exec /usr/platform/sun4u/lib/sparcv9/libc_psr.so.1
FFFFFFFF7D200000 16K read/exec /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D304000 8K read/write/exec /usr/lib/sparcv9/libmp.so.2
FFFFFFFF7D400000 88K read/exec /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D516000 8K read/write/exec /usr/lib/sparcv9/libm.so.1
FFFFFFFF7D600000 8K read/exec /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D702000 8K read/write/exec /usr/lib/sparcv9/libkstat.so.1
FFFFFFFF7D800000 8K read/write/exec [ anon ]
FFFFFFFF7D900000 32K read/exec /usr/lib/sparcv9/librt.so.1
FFFFFFFF7DA08000 8K read/write/exec /usr/lib/sparcv9/librt.so.1
FFFFFFFF7DB00000 32K read/exec /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DC08000 8K read/write/exec /usr/lib/sparcv9/libaio.so.1
FFFFFFFF7DD00000 712K read/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DEB2000 56K read/write/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7DEC0000 8K read/write/exec /usr/lib/sparcv9/libc.so.1
FFFFFFFF7E000000 32K read/exec /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E108000 8K read/write/exec /usr/lib/sparcv9/libgen.so.1
FFFFFFFF7E200000 56K read/exec /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7E30E000 16K read/write/exec /usr/lib/sparcv9/libsocket.so.1
FFFFFFFF7E400000 5264K read/exec /ora/92/lib/libjox9.so
FFFFFFFF7EA22000 376K read/write/exec /ora/92/lib/libjox9.so
FFFFFFFF7EA80000 8K read/write/exec /ora/92/lib/libjox9.so
FFFFFFFF7EB00000 8K read/write/exec [ anon ]
FFFFFFFF7EC00000 664K read/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EDA6000 56K read/write/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EDB4000 40K read/write/exec /usr/lib/sparcv9/libnsl.so.1
FFFFFFFF7EE00000 32K read/exec /ora/92/lib/libskgxn9.so
FFFFFFFF7EF06000 8K read/write/exec /ora/92/lib/libskgxn9.so
FFFFFFFF7F000000 8K read/exec /ora/92/lib/libskgxp9.so
FFFFFFFF7F100000 8K read/write/exec /ora/92/lib/libskgxp9.so
FFFFFFFF7F200000 8K read/write/exec [ anon ]
FFFFFFFF7F300000 8K read/exec /ora/92/lib/libodmd9.so
FFFFFFFF7F400000 8K read/write/exec /ora/92/lib/libodmd9.so
FFFFFFFF7F500000 8K read/exec /usr/lib/sparcv9/libdl.so.1
FFFFFFFF7F600000 136K read/exec /usr/lib/sparcv9/ld.so.1
FFFFFFFF7F720000 16K read/write/exec /usr/lib/sparcv9/ld.so.1
FFFFFFFF7F724000 8K read/write/exec /usr/lib/sparcv9/ld.so.1
FFFFFFFF7FFE2000 120K read/write [ stack ]
total 426632K
February 05, 2003 - 11:48 am UTC
No, I cannot really. I can say:
0000000380000000 364544K read/write/exec/shared [ ism shmid=0x3e8 ]
is the shared memory (obviously) that's the SGA this process has attached.
The rest -- just code, data, stuff.
heap -- that will be pga, thats dynamically allocated memory.
Great lesson on pmap
Mike, December 17, 2003 - 7:35 pm UTC
I ran a test on an 8.1.7 database (dedicated server) where I did the following in an interactive SQL*Plus session:
SQL> alter session set sort_area_size = 100000000;
Ran a query with order-by/group-by that threw > 100M of sort.
I then ran "pmap" and found an expected growth in the heap size:
oracle@grover$ pmap 12662
12662: oraclelims16 (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
00010000 26576K read/exec /app/oracle/product/817/bin/oracle
01A12000 272K read/write/exec /app/oracle/product/817/bin/oracle
01A56000 102952K read/write/exec [ heap ]
80000000 277472K read/write/exec/shared [ ism shmid=0x3eb ]
FEA82000 8K read/write/exec [ anon ]
FEA94000 8K read/write/exec [ anon ]
.
.
.
Now, let's say above example is NOT an interactive SQL*Plus session but rather a
background snapshot processes ( ora_snp0_SID - ora_snp3_SID). Those processes do not disconnect from the database, right? Therefore, won't the heap size persist until the instance is bounced?
That seems to be our situation now because if I run "pmap" on our background snapshot processes, the heap size for all of them is about 100MB. We have a sort-intensive job that runs via dbms_job every hour. I suspect the job is getting distributed round-robin between the four background processes and that explains why each process has a heap size of 100MB.
Can I/should I free the heap memory between dbms_job runs? It would seem the memory would be better utilized within the hour if the four background processes freed the heap for the O/S to reclaim and use. What do you think?
Thanks for your time, -Mike
December 18, 2003 - 10:42 am UTC
yup, the heap will persist HOWEVER, operating systems are smart.
They "use it" or "lose it". if the snp process isn't really using that 100meg of allocated memory, well -- it gets paged out. that is what operating systems do.
Have you IDENTIFIED THIS HEAP ISSUE as a real issue? I mean, have you zeroed in on this and said "hey, you know what, we've figured out that our 100% performance hit is due exactly to the fact these guys have 100meg heaps". Or, is it just sort of "bothering you"?
If you use 9i, with a pga_aggregate_target, you'll find all of these work areas are dynamically allocated using memmap() on OS's that allow for it and then released. the heaps won't grow.
You can alter system set job_queue_processes=0 and then alter system set job_queue_processes=4
ops$tkyte@ORA817DEV> !ps -auxww | grep '^ora817.*snp'
ora817 24793 0.0 4.79864071392 ? S 10:45:58 0:00 ora_snp0_ora817dev
ops$tkyte@ORA817DEV> alter system set job_queue_processes=0;
System altered.
ops$tkyte@ORA817DEV> !ps -auxww | grep '^ora817.*snp'
ops$tkyte@ORA817DEV> alter system set job_queue_processes=4;
System altered.
ops$tkyte@ORA817DEV> !ps -auxww | grep '^ora817.*snp'
ora817 25163 0.2 4.79860870392 ? S 10:51:54 0:00 ora_snp1_ora817dev
ora817 25165 0.1 4.79860870392 ? S 10:51:54 0:00 ora_snp2_ora817dev
ora817 25167 0.1 4.79860870392 ? S 10:51:54 0:00 ora_snp3_ora817dev
ora817 25161 0.1 4.79864071320 ? O 10:51:54 0:00 ora_snp0_ora817dev
all new snaps.
Thanks again
Mike, December 18, 2003 - 1:33 pm UTC
> Have you IDENTIFIED THIS HEAP ISSUE as a real issue?
No real issue yet. Our site is blessed with a great Solaris Sys Admin who pro-actively monitors our system metrics. One of his metrics, freemem (from sar -r), as been diminishing over time and so we dug into the system/database processes to find out why. This led us to the snapshot background processes with RSS values significantly higher than others on the system.
> ops$tkyte@ORA817DEV> alter system set job_queue_processes=0;
> ops$tkyte@ORA817DEV> alter system set job_queue_processes=4;
Interesting idea. I tried this and it indeed freed the memory held (per vmstat).
> They "use it" or "lose it". if the snp process isn't really using that
> 100meg of allocated memory, well -- it gets paged out. that is what
> operating systems do.
Hmmm, good point. Although, it still seems like something to watch especially if free memory were tight to avoid excessive paging.
Thank you sir for the great lesson on using "pmap" to show the PGA memory allocated to a process as well as the shared memory segment for the SGA.
-Mike
A reader, June 28, 2004 - 12:16 pm UTC
is there a way to limit java PGA?
Gianluca Albera, August 15, 2004 - 11:20 am UTC
Dear Tom,
we are experiencing a similar problem. We run a 8.1.7 Database under Solaris 2.6 and have many concurrent jobs running as snmp processes. Most of the job we run, are java stored procedure generating pdf reports and sending them as attachment in emails, plus many other pl/sql jobs as well.
We have 4 cpus and job_queue_processes=8 (in order to have at most 2 running jobs per cpu).
They run as concurrent job at lower priority using Dbms_Session.Switch_Current_Consumer_Group, so that online users still get their query running at a reasonable pace.
The problem is that each one of the job fills up to 400MB PGA, consuming almost 4GB of memory. We have 4GB of phisical ram and have much trouble in finding more that 4GB for swap (software constraints limit us to upgrade the machine due to solaris 2.6 contraints).
Here it follows a pmap example:
root@cti2:# ps -ef | grep 26511
oracle 26511 1 0 19:58:14 ? 100:50 ora_snp3_CCFO
root 15080 17966 0 12:40:13 pts/1 0:00 grep 26511
root@cti2:# /usr/proc/bin/pmap 26511
26511: ora_snp3_CCFO
00010000 25088K read/exec /appl/oracle8i/app/oracle/product/8.1.7/bin/oracle
0189E000 256K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/bin/oracle
018DE000 403464K read/write/exec [ heap ]
20000000 218664K read/write/exec/shared [ shmid=0x2 ]
80000000 933968K read/write/exec/shared [ shmid=0x1 ]
ED182000 8K read/write/exec [ anon ]
ED194000 8K read/write/exec [ anon ]
ED1A6000 8K read/write/exec [ anon ]
ED1B8000 8K read/write/exec [ anon ]
ED1CA000 8K read/write/exec [ anon ]
ED1DC000 8K read/write/exec [ anon ]
ED1EE000 8K read/write/exec [ anon ]
ED1FC000 8K read/write/exec [ anon ]
ED200000 8K read/write/exec [ anon ]
ED20E000 8K read/write/exec [ anon ]
ED212000 8K read/write/exec [ anon ]
ED220000 8K read/write/exec [ anon ]
ED224000 8K read/write/exec [ anon ]
ED232000 8K read/write/exec [ anon ]
ED236000 8K read/write/exec [ anon ]
ED244000 8K read/write/exec [ anon ]
ED248000 8K read/write/exec [ anon ]
ED256000 8K read/write/exec [ anon ]
ED25A000 8K read/write/exec [ anon ]
ED268000 8K read/write/exec [ anon ]
ED26C000 8K read/write/exec [ anon ]
ED27A000 8K read/write/exec [ anon ]
ED27E000 8K read/write/exec [ anon ]
ED28C000 8K read/write/exec [ anon ]
ED290000 8K read/write/exec [ anon ]
ED29E000 8K read/write/exec [ anon ]
ED300000 1328K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_text.so
ED45A000 56K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_text.so
ED480000 1768K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_util.so
ED648000 88K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_util.so
ED680000 928K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_text_resources.so
ED776000 40K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_text_resources.so
ED790000 336K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_util_zip.so
ED7F2000 24K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_util_zip.so
ED800000 736K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_net.so
ED8C6000 40K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_net.so
ED900000 416K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_dbaccess.so
ED976000 32K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_dbaccess.so
ED9A0000 248K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net_www.so
ED9EC000 24K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net_www.so
EDA00000 632K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms_security.so
EDAAC000 32K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms_security.so
EDB00000 400K read/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libcorejava.so
EDB72000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libcorejava.so
EDB76000 8K read/write/exec [ anon ]
EDB80000 2304K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_driver.so
EDDCE000 96K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_driver.so
EDE00000 936K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_security_provider.so
EDEF8000 40K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_security_provider.so
EDF20000 272K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_kprb.so
EDF72000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_jdbc_kprb.so
EDF80000 1568K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_sql.so
EE116000 72K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_sql.so
EE140000 112K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net.so
EE16A000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net.so
EE180000 776K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_misc.so
EE250000 40K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_misc.so
EE280000 856K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_security.so
EE364000 48K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_security.so
EE380000 2208K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_io.so
EE5B6000 72K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_io.so
EE5D0000 80K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net_www_protocol_file.so
EE5F2000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_net_www_protocol_file.so
EE600000 1648K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_io.so
EE7AA000 72K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_io.so
EE7D0000 88K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms_url_jserver.so
EE7F4000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms_url_jserver.so
EE800000 1128K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_gss_util.so
EE928000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_gss_util.so
EE950000 96K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang_reflect.so
EE976000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang_reflect.so
EE980000 1112K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms.so
EEAA4000 56K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_rdbms.so
EEAC0000 24K read/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/liboraawt.so
EEAD4000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/liboraawt.so
EEAE0000 40K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_memoryManager.so
EEAF8000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_memoryManager.so
EEB00000 440K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_vm.so
EEB7C000 32K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_vm.so
EEB90000 8K read/write/exec [ anon ]
EEBA0000 280K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_sql.so
EEBF4000 32K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_sql.so
EEC00000 2144K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_util.so
EEE26000 96K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_util.so
EEE50000 88K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_realm.so
EEE74000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8oracle_aurora_realm.so
EEE80000 1152K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang.so
EEFAE000 64K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang.so
EEFD0000 64K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_math.so
EEFEE000 16K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_math.so
EF000000 4056K read/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libjox8.so
EF404000 160K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libjox8.so
EF42C000 8K read/write/exec [ anon ]
EF440000 8K read/write/exec [ anon ]
EF450000 8K read/write/exec [ anon ]
EF460000 8K read/write/exec [ anon ]
EF470000 96K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang_ref.so
EF496000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8java_lang_ref.so
EF4A0000 24K read/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_security_action.so
EF4B4000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/javavm/admin/libjox8sun_security_action.so
EF4C0000 8K read/write/exec [ anon ]
EF4E0000 16K read/exec /usr/platform/sun4u/lib/libc_psr.so.1
EF4F0000 16K read/exec /usr/lib/libmp.so.2
EF502000 8K read/write/exec /usr/lib/libmp.so.2
EF510000 88K read/exec /usr/lib/libm.so.1
EF534000 8K read/write/exec /usr/lib/libm.so.1
EF540000 8K read/exec /usr/lib/libkstat.so.1
EF550000 8K read/write/exec /usr/lib/libkstat.so.1
EF560000 24K read/exec /usr/lib/libposix4.so.1
EF574000 8K read/write/exec /usr/lib/libposix4.so.1
EF580000 600K read/exec /usr/lib/libc.so.1
EF624000 32K read/write/exec /usr/lib/libc.so.1
EF62C000 8K read/write/exec [ anon ]
EF640000 24K read/exec /usr/lib/libaio.so.1
EF654000 16K read/write/exec /usr/lib/libaio.so.1
EF660000 8K read/exec /usr/lib/libsched.so.1
EF670000 8K read/write/exec /usr/lib/libsched.so.1
EF680000 456K read/exec /usr/lib/libnsl.so.1
EF700000 40K read/write/exec /usr/lib/libnsl.so.1
EF70A000 16K read/write/exec [ anon ]
EF720000 8K read/write/exec [ anon ]
EF730000 8K read/write/exec/shared [ anon ]
EF740000 32K read/exec /usr/lib/libsocket.so.1
EF756000 8K read/write/exec /usr/lib/libsocket.so.1
EF758000 8K read/write/exec [ anon ]
EF760000 32K read/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libdsbtsh8.so
EF776000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libdsbtsh8.so
EF778000 8K read/write/exec [ anon ]
EF780000 8K read/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libskgxp8.so
EF790000 8K read/write/exec /appl/oracle8i/app/oracle/product/8.1.7/lib/libskgxp8.so
EF7A0000 8K read/exec /usr/lib/libdl.so.1
EF7B0000 8K read/write/exec [ anon ]
EF7C0000 144K read/exec /usr/lib/ld.so.1
EF7F2000 16K read/write/exec /usr/lib/ld.so.1
EFFEA000 88K read/write/exec [ stack ]
total 1613248K
During peak hours we end up consuming all our virtual memory, since we have lots of processing running oracle and other software.
Is there a way to limit java heap in the virtual machine similar to sun's java virtual machine command line switch Xmx128m (for 128MB for example)? We don't want oracle to kill our session if 128MB are hit in the example above, but just to inform the garbage collector to free some resources... in fact even of the snp process is no longer running the java heap is not released by the garbage collector!!!
Thank you in advance (your help is always invaluable).
best regards,
Gianluca Albera
August 15, 2004 - 12:10 pm UTC
I'll ask a java resource to look at this if they have time, but.....
you might consider programming the java outside of the database and just use the job to "run" the external java (heck, be nice for the java to be running on a different machine from the server even -- you could use AQ and a jms client on a remote machine)
you might be able to just call the garbage collector whenever you want -- and dbms_session.free_unused_user_memory.
A reader, August 16, 2004 - 7:06 pm UTC
RE: Java PGA
Gianluca Albera, August 17, 2004 - 3:10 am UTC
I tried implementing dbms_session.free_unused_user_memory but it seems to me ha no effect on java part of pga (I'm still investigating though!).
About your suggestion to move "avay" the java part using a different machine and AQ or some other heck, ... well I'm having some difficulties to try it out.
Our setup is still "old fashioned" in this world of multi layered servers: we run a couple of sun clusters in which we stick databases, application servers, web servers, pl/sql jobs, shell jobs, and so on...
for the java guys (or the last hype guys) this may seems non appropriate, but working this way, everything is so simple to code and much more important, to let rut smoothly.
In this particular case, we created some daemons in pl/sql and java that do a bunch of stuff creating reports and sending them via mail, MQ Series, file transfer (via external scheduled shells) and so on.
The more external part is a pl/sql job that runs every minute: during normal activity on daytime, it works some records and just exits, while during the night it may stay up and running for hours and hours since we are still operating in a batch-like mode for some reporting. We multiplexed the activity in order to use all cpus we have.
Creating the same setup with a couple of external machine via AQ is a pain we would like to avoid: running a pl/sql is just that simple! We have automatic failover (it follows DB in our cluster); we don't need to worry about DB being up or down and so on....
we already have to do that with some external written applications that run only on windows and we end up to operate on a one process-one machine way. It is just terrible to mantain...; since we luckily work on unix we can stuff how many processes we want on our box!
Anyway, I usually follows your suggestions (I found them invaluable), so I will think about it, even if it sounds me a little strange reading Tom suggesting adding a couple of machine and implementing some custom stuff, while Oracle guys gave it us for free...
Best regards,
Gianluca Albera
P.S. probably the next stop will we removing the "hog-sink" java stuff in favour of some C code... if it were just a little more simple to write and integrate C stored procedures (we have many unexperienced C coders, it seems the market wants only java in these days).... (and having the possibility of using GNU gcc instead of sun compiler on sun machines...)
August 17, 2004 - 11:40 am UTC
asked around -- that -X you were talking about will do the same as the java_max_sessionspace_size, it'll fling an exception when you run out of memory, it doesn't tell java "please be more efficient" or anything -- it kills you basically (not much you can do after an out of memory exception")
sorry i am being so stupid!
aman, February 06, 2005 - 5:35 am UTC
sir i know this is the stupidiest question but dont know how i just got so much confused may be i am just thinking so much! sir in both the cases of dedicated server /shared server the PGA is contained onthe server or is it on the client side?i am sure its on the server side in the SGA but when this thought comes that in the dedicated machine the connection is independant then it can be on the client side!dont know how it came?Scold as much you want but have no more resource to ask!
with best regards
aman
February 07, 2005 - 3:49 am UTC
pga and uga is always a server side concept. they are oracle memory regions.
pga is PROCESS global area, it is in dedicated and shared servers (never in the SGA)
the uga is USER global area, it is session memory and it is in the PGA when using dedicated server, in the SGA when using shared server.
this is a restatement of the original answer above -- which basically says "the same thing" :)
always the best resource !
aman, February 07, 2005 - 9:59 am UTC
sir i am much much clear now about this idea that they will always be at the server side.sir yiu said that PGA is allocated in the Dedicated/Shared Servers ,but not in the SGA.what i had assumed that they are the memory structures and Oracle will allocate the memory to them only where it has the whole memory ,SGA i mean to say!why its vice-versa?dedicated/shared servers are the server processes ,yes they will acqire memory for them but that will be like every process has in o/s.then that means that the PGA will be allocated in that memory of the Server Process?means if the PGA setting is done or set like 2MB(Dedicated Connection) ,that means its the memory that will be allocated for tat coonection or in other words for that dedicated server process then this means that the memory for that process will be more than 2MB?and this memory will be taken from the RAM only so so that means if the RAM is 250MB and we allocate SGA =100MB and we have 10 connections (Dedicated) then each will be acquiring a seperate memory of 2MB for itself besides the total SGA memory?
please clarify
February 07, 2005 - 10:53 am UTC
pga is process (that is the P in PGA) memory. it is memory needed by the individual process, that does not need to be shared globally in the SGA.
It could be thought of as "private" -- for P.
It is allocated "as needed". It is "process memory". It is not a fixed size, it is dynamically allocated on an as needed basis.
Mark, December 03, 2006 - 9:48 pm UTC
Tom, is there a way to calculate the amount of memory consumed by a user connection (Solaris)? We are looking to move from shared to a dedicated connection and I would like to include this in my estimate.
December 04, 2006 - 7:20 am UTC
v$sesstat
ops$tkyte%ORA10GR2> select a.name, b.value
2 from v$statname a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.name like '%memory%';
NAME VALUE
------------------------------ ----------
session uga memory 680028
session uga memory 90852
.....
what different session and process?
tramplai, December 25, 2010 - 9:05 pm UTC
Hi Tom,
Thanks all answer.
You said: The User Global Area (UGA) is memory that is associated with a user session, it is session memory, PGA is process in OS, its memory is private.
I want to know what different about session and process because after i connect to database via sqlplus, database will create a session and OS will create a process, and the session and the process will communicate.
December 26, 2010 - 7:00 pm UTC
it takes a lot of pages to explain this in any level of detail...
A connection might have zero (yes, zero), one or more sessions.
A sessions might use zero (yes, zero), one or more connections.
You either want to check out the concepts guide (free on otn.oracle.com) or my book Expert Oracle Database Architecture (linked to from homepage of asktom) for all of the details.
A session is a logical thing where as a process is a physical thing. A process might have a single session (perhaps the most common thing in dedicated server mode), but it is not unusual for a process to have more than one session - if you use autotrace in dedicated server mode - you'll have two sessions!
A session and process do not "communicate", a process is code - it just executes stuff, one of the things it executes is you session code.