RE: large uga/pga and scan rates

From: Henry Poras <Henry.Poras_at_ctp.com>
Date: Mon, 11 Dec 2000 19:05:52 -0500
Message-Id: <10707.124260@fatcity.com>

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-----
From: Henry Poras=20
Sent: Monday, December 11, 2000 10:41 AM To: Multiple recipients of list ORACLE-L Subject: RE: large uga/pga and scan rates

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
--------------------------------------------------------------------