Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost
Date: Fri, 29 May 2009 20:48:42 +0200
Jonathan Lewis pisze:
> I was under the impression that CBO used hash_area_size only when
> 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 18.104.22.168 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 ).
> 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).
G Received on Fri May 29 2009 - 13:48:42 CDT