RE: How to get a 10053 trace on a recursive query

Yes, it seems that recursive queries use CHOOSE for optimizer_mode even though optimizer_mode=ALL_ROWS at the instance level. This is apparent from my trace files and also seems to be confirmed in Metalink 66481.1 (see note 2 at the bottom of the metalink note). I'm surprised I've never noticed this behavior before, but I guess I haven't had to tune many recursive queries. So, now that I noticed this - I ran the recursive query with optimizer_mode=CHOOSE for my session to match the mode for the recursive query and sure enough - I got the same bad execution plan as the recursive query so that confirms it's related to the optimizer_mode setting and now I can get the 10053 trace and try to figure out how to stop the recursive query from getting a bad plan. Still no way to get a 10053 trace on an actual recursive query I guess, but this is close enough.

Thanks,
Brandon

-----Original Message-----
From: Allen, Brandon

<snip>

One thing I just noticed though in reviewing my tkprof output below is that the optimizer_mode appears to switch from ALL_ROWS to CHOOSE for the recursive queries. I've never noticed that before, but maybe it's always done that?

<snip>

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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