| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: tracking oracle memory usage
Jim,
I just got into a disagreement with damagement about this recently. I don't think that there is a formula to calculate the PGA memory based on init.ora parameters, as an application can do things which can grab large amounts of memory. For example, I've seen programs written by developers who seem to think it is a good idea to allocate large plsql tables and select a table's worth of data into it. If each of your 400+ connections tries to allocate a 40Mb plsql table, each PGA will grow by 40mb and you'll eat up memory real fast...
Gary
Gary Kirsh
www.nextextent.com <http://www.nextextent.com>
-----Original Message-----
Sent: Friday, May 09, 2003 2:32 PM
To: Multiple recipients of list ORACLE-L
Thanks for your reply John. The method you describe is basically what I was using to check the memory on the 400+ connections I had.. just wondering if anyone out there had any other suggestions/experience tracking down memory leaks? how accurate is oracle in detecting memory leaks? From my understanding of the process memory in the PGA, it should not change much once the session is established? either the session can get the required fixed portion of the memory or it errors out and cannot connect? I know the initial size is dependent on several parameters including SORT_AREA_RETAINED_SIZE, OPEN_LINKS, DB_FILES, etc. BTW I am not using MTS. Is there a formula for calculating total PGA size based on parameter settings, etc?
is statistic#20 "session pga memory" and statistic #21 "session max pga memory" in v$sesstat accurate and all inclusive for memory being used by each session outside of sga memory? is value in v$sesstat in bytes for these stats?
does anyone have a white paper on oracle memory usage outside the SGA? There does not seem to be a lot of detail on this subject in any book or manual I have.
TIA! Jim
John Kanagaraj <john.kanagaraj_at_hds.com> wrote:
Jim,
Since you are on Solaris, you can use the 'pmap' utility to determine the
heap component of identified processes and monitor them for large changes..
Ignore the other values (except maybe the stack). 'ps' whill show up the RSS
which will include the SGA sizes as well, so that isn't reliable. Example
below:
John Kanagaraj (back now!)
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
I don't know what the future holds for me, but I do know who holds my future!
$ pmap 7511
7511: oracleC4PR (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
00010000 26520K read/exec /u01/c4prdb/8.1.7/bin/oracle
01A04000 264K read/write/exec /u01/c4prdb/8.1.7/bin/oracle
01A46000 3368K read/write/exec [ heap ]
80! 000000 1789608K read/write/exec/shared [ ism shmid=0x1c00 ]
FEA60000 16K read/exec /usr/platform/sun4u/lib/libc_psr.so.1
FEA70000 8K read/write/exec [ anon ]
FEA80000 16K read/exec /usr/lib/libmp.so.2
FEA94000 8K read/write/exec /usr/lib/libmp.so.2
FEAA0000 120K read/exec /usr/lib/libelf.so.1
FEACE000 8K read/write/exec /usr/lib/libelf.so.1
-----Original Message-----
Sent: Thursday, May 08, 2003 2:38 PM
To: Multiple recipients of list ORACLE-L
Hello All -
I am in need of some advice on tracking memory usage for oracle connected processes. We thought we may have a memory leak in some of the processes attached to oracle on one of our servers... turns out that it was probobly another process on the server that ate up all the memory.. I would like to have a script or process ready next time to make it easier to track memory utilization for all processes attached to the database. I am using 8.1! .7.3 on Solaris 2.6.
I know there are memory stats in v$sgastat, what about PGA memory stats? Are there v$view(s) or sys tables that contain memory information on the session level? I am currently using unix pmap to get heap size for each session and storing the PID, heap size, and timestamp in a table to see if heap is growing for any sessions... is there an easier way to identify possible memory leak(s) if oracle does not detect it?
Thanks!
Jim Neugebauer
Oracle DBA
Ameritrade Holding Corp
Do you Yahoo!?
The New
Yahoo! Search - Faster. Easier. Bingo.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
INET: john.kanagaraj_at_hds.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting
services--------------------------------------------------------------------
Jim Neugebauer
Oracle DBA
Ameritrade Holding Corp
Do you Yahoo!?
The New <http://us.rd.yahoo.com/search/mailsig/*http://search.yahoo.com>
Yahoo! Search - Faster. Easier. Bingo.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirsh, Gary
INET: gary.kirsh_at_gs.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri May 09 2003 - 14:51:12 CDT
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |