Re: PGA_AGGREGATE_TARGET sizing of work areas and HJ cost

From: joel garry <joel-garry_at_home.com>
Date: Fri, 29 May 2009 10:29:45 -0700 (PDT)
Message-ID: <9ea7c513-ee55-4f7a-af36-b3f0fb965989_at_a36g2000yqc.googlegroups.com>



On May 29, 9:14 am, Grzegorz <fgrzegor..._at_finteria.pl> wrote:
> Hi,
>         I'm doing some investigations
> because of query plan changing in the same session (from NL Index RS to
> HJ FTS), and got question about Hash_Join_Area_Size when
> work_area_policy=auto.
> Is that possible that the same session can have different
> HJAS for same query ?
> When auto pga allocation is in place its quite possible in my opinion.
> Looks like depending on instance workload (in PGA terms) some queries
> may switch between NL Index RS and HJ FTS in the same session.
> I'm on 9.2.0.8 .
> Regards.
> GG

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

In 11g you can flush a single query: http://el-caro.blogspot.com/search?q=flush+shared+pool

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

jg

--
_at_home.com is bogus.
http://www3.signonsandiego.com/stories/2009/may/29/1n29cyberwar234433-us-military-preparing-battles-c/?uniontrib
Received on Fri May 29 2009 - 12:29:45 CDT

Original text of this message