Re: optimizer_mismatch and hash_match_failed
Date: Thu, 18 Apr 2013 19:16:07 +0200
Message-ID: <CABz5TyCRbnx3AihpGY1owATuPb6m6riQiEtj67JdaJJLK6_KpQ_at_mail.gmail.com>
So the solution here would simply be to turn off AMM and revert to a more manual configuration?
As for HASH_MATCH_FAILED I think I recall reading something about it could be reduced by using an SQL Profile for this sql? If some of the execution will take longer time due to bad execution plan is more acceptable than creating this high number of parses and library cache lock that we saw in our perfomance test.
On Thu, Apr 18, 2013 at 7:00 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
> It's the MEMORY_TARGET / Automatic Memory Management, which changes your
> PGA_AGGREGATE_TARGET regularly, resulting in some _smm_* parameter changes,
> which happen to be part of the optimizer environment, which cause new hard
> parses when some existing cursors are used again.
>
> The HASH_MATCH_FAILED shows up when you use cursor_sharing_similar with
> "unsafe" bind variables - like where clauses with bind variables on columns
> with histograms or bind variables in various range, like, between or <, >
> conditions.
>
> --
> *Tanel Poder*
> Enkitec (The Exadata Experts)
> Training <http://blog.tanelpoder.com/seminar/> | Troubleshooting<http://blog.tanelpoder.com/>
> | Exadata<http://www.amazon.com/Expert-Oracle-Exadata-Apress/dp/1430233923>
> | Voicee App <http://voic.ee/>
>
>
>
> On Thu, Apr 18, 2013 at 7:54 PM, Johan Eriksson <valpis_at_gmail.com> wrote:
>
>> (Oracle 11.2.0.2.0 on AIX)
>> Hi,
>>
>> I faced something today which I still haven't figured out. On couple of
>> SQL
>> we get a lot of child cursors (cursor_sharing=similiar) and for those of
>> them that has HASH_MATCH_FAILED=Y in v$sql_shared_cursor I understand it
>> is
>> due to that setting.
>>
>>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 18 2013 - 19:16:07 CEST