Re: optimizer_mismatch and hash_match_failed
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: valpis@xxxxxxxxx
- Date: Thu, 18 Apr 2013 20:00:21 +0300
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@xxxxxxxxx> 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.
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
