Re: optimizer_mismatch and hash_match_failed

From: Taral Desai <taral.desai_at_gmail.com>
Date: Thu, 18 Apr 2013 12:08:10 -0500
Message-ID: <CAO4+9HVoOKJEwYkdMpRdO_DPiim3H865wSFcnSe6L+9C-h3Fmg_at_mail.gmail.com>

As Tanel already gave you solution. Here is one more link with Example http://blog.yannickjaquier.com/oracle/cursor_sharing.html

On Thu, Apr 18, 2013 at 11:54 AM, 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.
>
> but for one sql we get optimizer_mismatch=y (and 10% of them also
> hash_match_failed). I have checked them on v$sql_optimizer_env but no
> values there differs between the child cursors.
>
> As reason in v$sql_shared_cursor we get :
>
> <ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer
> mismatch(12)</reason><size>2x212</size><_smm_max_size> 688120 KB
> 678290 KB </_smm_max_size><_smm_px_max_size> 3440640 KB
> 3391488 KB
>
> </_smm_px_max_size></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer
> mismatch(12)</reason><size>2x212</size><_smm_max_size> 701230 KB
> 691400 KB </_smm_max_size><_smm_px_max_size> 3506176 KB
> 3457024 KB
>
> </_smm_px_max_size></ChildNode><ChildNode><ChildNumber>0</ChildNumber><ID>3</ID><reason>Optimizer
> mismatch(12)</reason><size>2x212</size><_smm_max_size> 648800 KB
> 678290 KB </_smm_max_size><_smm_px_max_size> 3244032 KB
> 3391488 KB </_smm_px_max_size></ChildNode>
>
>
> and these numbers on _smm_px_max_size and the others reported varies
> between the child cursors. But I don't know what is causing these values...
> The sessions comes from an application server with a shared pool of
> connections and is using jdbc against the db.
>
> Where should I be looking further to find more information about these
> values? Could there be something that the application server forces? Could
> need some advice here :)
>
> Thanks in advance
> /johan
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 

Thanks & Regards,
Taral Desai


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

Received on Thu Apr 18 2013 - 19:08:10 CEST