Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: large uga/pga and scan rates

RE: large uga/pga and scan rates

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Mon, 18 Dec 2000 13:16:10 +1000
Message-Id: <10713.124796@fatcity.com>


Hi Henry,

This is probably caused by a PL/SQL array. If you have access to the code, and can change it, try assigning an empty array to the array variable each time when have finished with it.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/
@   http://www.christianity.net.au/


-----Original Message-----
From: Henry Poras [mailto:Henry.Poras_at_ctp.com] Sent: Saturday, 16 December 2000 2:11
To: Multiple recipients of list ORACLE-L Subject: RE: large uga/pga and scan rates

I have a little more information on this. First a thank you to Steve Adams for suggesting doing the heapdump at level 4 instead of level 1. I measured the process a couple of times before killing it. According to ps -el, it grew by about 44M. Most of the new entries in the heapdump over time were new extents allocated to 'PLS non-lib hp'. In my first heapdump I had about 3100 extents allocated to this, while in the second it grew to about 14,000
(There were some other changes, but this was the bulk of it) Each extent was
~4K, so 11,000 extents * 4K/extent accounts for the 44M (in addition, the total heap size listed in the dump grew from 13M to 57M so all of these measures are self consistent). OK, I've got some consistent data, all I have to do now is to understand it.

I assume the 'PLS non-lib hp' entries refer to PL/SQL in the heap. The query has about 10 functions in the SELECT statement (including some nested functions) and one in the WHERE clause. I have no idea what part of PL/SQL ends up in the PGA (or UGA) and without knowing this I can't tell if there is a workaround to this problem. (I'm also just curious as to what ends up in the PGA since that is not very well documented).

Any ideas where to go with this?

Thanks.

Henry

-----Original Message-----
Sent: Monday, December 11, 2000 7:07 PM
To: Multiple recipients of list ORACLE-L

Well I ran /usr/proc/bin/pmap and all the memory growth shows up in

	/read/write/exec
		[heap]

I also ran a heapdump (oradebug setospid; oradebug dump heapdump 1 from svrmgrl) and every entry but 2 (about 65,000 lines) were labeled freeable "session heap". Finally I looked at x$ksmpp and x$ksmup. There was no change over time in these tables.

This does nothing for me. Any ideas?

Henry

-----Original Message-----
Sent: Monday, December 11, 2000 10:41 AM To: Multiple recipients of list ORACLE-L

That is exactly what I am seeing. But I also remember reading what Ron quoted from the Concepts manual "Once connected, a user can never run out of PGA space; there is either enough or not enough memory to connect in the first place." If true, how can the process memory (dedicated) grow with no bounds? I'll try running some pmap(s) today and see if that gives any clues
(not that I'm too familiar with the output). I'll post that data when I've
got it.

Thanks.

Henry

-----Original Message-----
Sent: Monday, December 11, 2000 4:21 AM
To: Multiple recipients of list ORACLE-L

Hi,

The pga memory is not fixed (I am talking if you're not using MTS).

The process will take what it needs until it reaches the OS limit. I had a similar case on Oracle 8.0.4/HP 10.20 with a function in a select. The select was bringing back more than 500 000 rows. Oracle was not releasing the memory after the function call.


Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?paquette=20stephane?=
  INET: stephane_paquette_at_yahoo.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: Henry.Poras_at_ctp.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: Henry.Poras_at_ctp.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: Henry.Poras_at_ctp.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
Received on Sun Dec 17 2000 - 21:16:10 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US