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@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. > > 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 > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Thanks & Regards, Taral Desai -- //www.freelists.org/webpage/oracle-l