| 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
Thanks Steve,
But I don't think that is the problem with this lousy piece of SQL. I dug a
bit more and have a few ideas and a few questions (big surprise).
First of all, all of the functions in this SQL are pretty simple, just a few lines of code. Most are in the SELECT clause, one is in the WHERE clause. The tables in the join are fairly large so each function will be called thousands (maybe hundreds of thousands) of times. But, (and this is why I don't think the empty array strategy will work) they are not looped.
My guess about the heap dump label of 'PLS non-lib hp' is that it refers to
non-library supplied (i.e. not supplied from Oracle) PL/SQL. To test that
this is a possible (though not necessary) condition I created some test
functions. One had a loop of SQL selecting the max(object_id) from
dba_objects, one had a single SQL statement selecting the max(object_id)
from dba_objects, and one had a loop calling the function with the single
SQL statement. When I ran the SQL statement looped anywhere from 10-10,000
times, 'PLS non-lib hp' did not appear in the heap dump. When I ran the
function calling the function, a short loop barely showed that heap dump
label, but a long loop had a measurable (~ 40 extents) increase in the 'PLS
...' label. Therefor it seems (as expected) to be associated with function
(pl/sql) calls.
My best guess is that every time PL/SQL is called, the declared variables grab a piece of PGA. If a function is called thousands of times, this can add up. I can think of three possible ways to deal with this. None of these have been tested as of yet.
1.) Reduce/eliminate the amount of memory assigned with each call. I am not sure if this can be done. I have so far been unsuccessful in my attempts. Is there any reason, however, if memory must be assigned, why heap space cannot be reused?
2.) Place the functions in a package and move the variables to global variables (if the code permits). This would assign the variables once when the package is called, removing the multiplicitive factor of the multiple function calls.
3.) Straight tuning of the SQL reducing the number of calls of the functions
(if possible).
So what do you think? Are these reasonable solutions? One other question. I noticed each extent in the heap dump has a variable ds, which has the same value throughout. What is this?
Thanks for your help.
Henry
-----Original Message-----
From: Steve Adams [mailto:steve.adams_at_ixora.com.au]
Sent: Sunday, December 17, 2000 10:16 PM
To: Henry Poras; Multiple recipients of list ORACLE-L
Subject: 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 Mon Dec 18 2000 - 22:11:29 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
 |
 |