RE: How to get a 10053 trace on a recursive query
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Gints Plivna" <gints.plivna@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 29 Apr 2008 16:58:14 -0700
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
