Re: optimizer_mismatch and hash_match_failed

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Thu, 18 Apr 2013 20:02:01 +0300
Message-ID: <CAMHX9JKg0Gprp9wCOapn9KS7HFuMJK3jWZJF7BsHNQ-ZzLO10Q_at_mail.gmail.com>

I would not use cursor_sharing = SIMILAR at all, it's deprecated and in my experience has caused more problems than it solved. If you definitely do need Oracle to replace literals with binds for you, then use FORCE. The adaptive bind peeking & cardinality feedback should deal with the "unsafe" bind conditions well enough by now.

-- 
*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 8: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/>

>

>

>



--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 18 2013 - 19:02:01 CEST