Re: optimizer_mismatch and hash_match_failed

From: Tanel Poder <>
Date: Thu, 18 Apr 2013 20:00:21 +0300
Message-ID: <>

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 <> |
 | Exadata<>
 | Voicee App <>

On Thu, Apr 18, 2013 at 7:54 PM, Johan Eriksson <> wrote:

> (Oracle 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.
Received on Thu Apr 18 2013 - 19:00:21 CEST

Original text of this message