Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

> I was under the impression that CBO used hash_area_size only when
> work_area_policy=manual.
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#19385
> http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#49321
>
> Are you certain you are asking about the right parameter?
> http://otn.oracle.com/pls/db92/db92.initora?remark=homepage#index-HAS

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:
> http://www.tanelpoder.com/files/Advanced_Oracle_Troubleshooting.pdf
My problem is that I cant reproduce bad plan even in virtually same environment.

> Other interesting observations about PGA from this old chestnut:
> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47466211228419
> Earlier versions of 9.2 had many bizarre issues with auto PGA, it's
> possible that 9.2.0.8 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