| 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: large uga/pga and scan rates
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.
-- 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-LReceived on Sun Dec 17 2000 - 21:16:10 CST
(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
 |
 |