Re: same query, different parsing users, different plans ...
From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 05 Jun 2008 09:52:54 -0700
Message-ID: <1212684771.472571@bubbleator.drizzle.com>
>
> I have 10053's for each scenario - the telling thing in them is that
> Oracle doesn't even consider the index on V2/T2 when it chooses the
> bad plan - search the entire file for it, and all you get is the part
> that lists its stats.
>
> On the good plan, you can see it trying the join scenarios and costing
> a full scan of T2 and then an Index probe (which wins by a mile - cost
> of 3/4 instead of 20000 odd).
>
> If you think you will have time to look I can post the full query and
> 10053's - its a lot of stuff to post to here though.
Date: Thu, 05 Jun 2008 09:52:54 -0700
Message-ID: <1212684771.472571@bubbleator.drizzle.com>
stephen O'D wrote:
>> Not without seeing the plans and possibly 10053 traces for both. >> Also, you're running 10.2.0.2 and some optimizer-related issues were >> fixed in 10.2.0.3 so possibly you should consider patching?
>
> I have 10053's for each scenario - the telling thing in them is that
> Oracle doesn't even consider the index on V2/T2 when it chooses the
> bad plan - search the entire file for it, and all you get is the part
> that lists its stats.
>
> On the good plan, you can see it trying the join scenarios and costing
> a full scan of T2 and then an Index probe (which wins by a mile - cost
> of 3/4 instead of 20000 odd).
>
> If you think you will have time to look I can post the full query and
> 10053's - its a lot of stuff to post to here though.
Step 1: Patch
Step 2: Use an INDEX HINT if that doesn't fix the problem.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Thu Jun 05 2008 - 11:52:54 CDT