RE: pga_aggregate_target and a memory leak

I have been asking questions on this list recently about a Possible similar problem recently with pl/sql tables. This was on hpux 11.11 with oracle 9.2.0.4 I still haven't found the answer completely but pat=0 and was_pol = manual Is a workaround that seems to be ok. I have a lack of time For further testing but will try do do so and report some more.

Regards,

Jeroen

-----Oorspronkelijk bericht-----
Van: Stephane Faroult [mailto:sfaroult_at_oriole.com] Verzonden: dinsdag 20 januari 2004 20:59 Aan: Multiple recipients of list ORACLE-L Onderwerp: Re: pga_aggregate_target and a memory leak

ryan.gaffuri_at_cox.net wrote:
>
> One of our production DBAs does not want to use pga_aggregate_target on a
9.2.0.3 instance due to a possible memory leak. The only note on memory leaks and pga_aggregate_target I can find on metalink is: 334427.995
>
> doesnt seem to apply to pga_aggregate_target. We are on sun solaris. Dont
know version offhand.
>
> he is under the impression that if we patch to 9.2.0.4 this goes away. not
sure about that either...
>

Be careful with pga_aggregate_target. I have very recently seen a case (Solaris + 9.2 but I cant't tell you exactly which patch level - probably the most recent) where two (by the way atrocious) queries generated by a DSS tool were responding very differently - and in a way that differences in the queries couldn't explain. From an Oracle standpoint, stats were roughly the same. Tracing proved that we were waiting for CPU, and truss that a call to mmap() was the culprit. Why, no idea. We first switched it (pga_thing) off, no more slow call to mmap(). However, it was still slow because we hadn't checked sort_area_size which was ridiculously small. We set sort_area_size to 10M, still with pga_aggregate_target unset, and once again the same very slow calls to mmap(). Memory misalignment? Anything else? Not much time to enquire but it looks like a mine field.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: Jeroen van Sluisdam
  INET: jeroen.van.sluisdam_at_vrijuit.nl

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).