Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: Grzegorz <>
Date: Fri, 29 May 2009 20:48:42 +0200
Message-ID: <gvpam8$dco$>

Jonathan Lewis pisze:

> I was under the impression that CBO used hash_area_size only when
> work_area_policy=manual.
> Are you certain you are asking about the right parameter?

I'm sorry, its about HASH_AREA_SIZE of course.

> I'm thinking anything that can reparse the query could cause a change
> in plan, and there are many things that will cause invalidations
> leading to that. Also, if you are using parallel or shared servers
> other strange things can happen. The way to know for sure is to trace
> your session and look. Instant classic paper:
My problem is that I cant reproduce bad plan even in virtually same environment.

> Other interesting observations about PGA from this old chestnut:
> Earlier versions of 9.2 had many bizarre issues with auto PGA, it's
> possible that has idiosyncracies. Have you set
> session_cached_cursors, cursor_sharing, or do you see anything in the
> alert log or other evidence that too much is happening in your shared
> sql area?

I've not set any of those parameters.
Test was done on no workload instance.

> Assuming of course it isn't the obvious bind variable issue as John
> mentioned, in which case it really wouldn't be the "same query."
My first thought was BV + histograms issue, but cant reproduce that even if i know all skewenes and use that knowledge (query plan is stable ).

> Joel,
> From the way the OP asks the question I don't think he had the
> parameter hash_area_size in mind; to me it sounded like a
> question about "how is the equivalent of the hash_area_size
> derived when using automatic workarea sizing".

Actually I was thinking about HAS :D because only in that way I could reproduce plan change (skip was when HAS ~ 3MB from default 131kb). Regards.
G Received on Fri May 29 2009 - 13:48:42 CDT

Original text of this message