RE: large uga/pga and scan rates

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Fri, 15 Dec 2000 11:10:39 -0500
Message-Id: <10711.124719@fatcity.com>

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.=20

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).=20

Any ideas where to go with this?

Thanks.

Henry

-----Original Message-----
From: Henry Poras=20
Sent: Monday, December 11, 2000 7:07 PM
To: Multiple recipients of list ORACLE-L Subject: RE: large uga/pga and scan rates

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).=20

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.=20

Henry Poras <Henry.Poras_at_ctp.com> a =E9crit=A0: > I have come across a strange problem on one of my
> production machines and
> the more I think about it the more confused I'm
> getting (maybe the right
> approach is to act first, think later). The problem
> is coming from a LARGE
> group-by query (with functions in the select). Over
> time, the user process
> memory (Sun Solaris. Measured using ps -el) is
> growing ever larger. The size
> is consistent to what I read from v$sesstat (session
> uga memory, session pga
> memory). After a few hours, scan rates go through
> the roof.=20
>=20
> Now, I thought that PGA size was fixed, and couldn't
> grow indefinitely. What
> is going on here and what can I do about it (besides
> killing the user or
> adding more memory).
>=20
> Thanks.
>=20
> Henry
> --=20
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --=20
> Author: Henry Poras
> INET: Henry.Poras_at_ctp.com
>=20
> 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).

=3D=3D=3D=3D=3D
Stephane Paquette
DBA Oracle
stephane_paquette_at_yahoo.com

Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products. http://shopping.yahoo.com/
--=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
Author: =3D?iso-8859-1?q?paquette=3D20stephane?=3D 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). --=20
Please see the official ORACLE-L FAQ: http://www.orafaq.com --=20
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
--------------------------------------------------------------------

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------