optimizer_mismatch and hash_match_failed
- From: Johan Eriksson <valpis@xxxxxxxxx>
- To: Oracle Discussion List <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 18 Apr 2013 18:54:30 +0200
(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
Other related posts:
