Re: optimizer_mismatch and hash_match_failed

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 18 Apr 2013 20:00:21 +0300
Message-ID: <CAMHX9JKGUDDNmF6cDDQP7h55G_NbPL=ZQXugrJPM1e7ea2OWDA_at_mail.gmail.com>

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

Received on Thu Apr 18 2013 - 19:00:21 CEST