Heapdump Interpretation

Hi all,

I have a process which is taking up way more memory than I'd expected. The process runs a PL/SQL that does some nested loop joins on a PL/SQL table.

The background process is using > 200Mb of private memory and this number goes up if we tweak the WHERE clause in the join to return more data.

I did a heapdump of the process and the trace file looks like this (lots of stuff trimmed):

...

EXTENT 437

   Chunk  925dfe4 sz=     1836    perm      "perm           "  alo=1836
   Chunk  925e710 sz=     1156    recreate  "session heap   "  latch=0
      ds  92693fc sz= 30315156 ct=      440
          b7aa56c sz=     3980
          92f30a0 sz=     1072
          afb6e34 sz=    16472
          afb2dcc sz=    16472
          afaed64 sz=    16472

I presume that "session heap" is the UGA for this process' session. Basically it goes on like this for several pages with sz anywhere between 16k and 1Mb. How can I interpret this? I presume the memory is to do with cursor information. This is a sort but the sort area size is only 10Mb and cannot account for all the private memory in use.

I'm just trying to decide if this is a reasonable amount of memory to be using (i.e. explain what it is using it *for*) and just put up with it, or if something has gone wrong. I'm on 8.1.5 on Linux 2.2 (I know, I know...)

Thanks for any insight!

• Bill.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Bill Buchan
  INET: wbuchan_at_uk.intasys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).