Re: Plan changing after database restart
- From: Tomi Wijanto <restomi_w@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Jul 2007 03:07:12 -0700 (PDT)
The bind value is not a new value, it's still between
low and high value of column BRN.
While I wait for chance to activate 10053, I need your
advice to put hint HASH_AJ temporarily for this case.
I read this hint is already deprecated in 10g, any
special attention for using it?
Query with hint:
select
w.rowid r_id, w.acc, w.prod, w.system_account
from
mytab w
where
w.brn=:b1
and not exists (
select /*+ HASH_AJ */ 1
from myview v
where v.brn=w.brn and v.acc=w.acc and
v.prod=w.prod );
Best Regards,
tomi
--- Niall Litchfield <niall.litchfield@xxxxxxxxx>
wrote:
> The value of the bind on the first parse was most
> likely different.
>
> On 7/19/07, Tomi Wijanto <restomi_w@xxxxxxxxx>
> wrote:
> > Hi all,
> >
> > I have weird problem for one SQL query, which has
> > different execution plan before and after
> restarting
> > database.
> > Before restart, it use NESTED LOOP (ANTI JOIN)
> and
> > never completed (total logical reads > 1 billion).
> > After restart, it use HASH JOIN (RIGHT ANTI) and
> > complete in 2 minutes.
> > Optimizer environment was exactly same. Job was
> > running from sqlplus. There is no histogram also.
> >
> > My concern is not about how to optimize this
> query,
> > but more about WHY the execution plan is different
> > before and after restarting db.
> >
> > The only difference I aware was:
> > Before restart, most of the table and indexes were
> > already in memory.
> > But could it impact execution plan?
> >
> > Your input is highly appreciated.
> >
> > Best Regards,
> > tomi
> >
> >
> > Query:
> > -----
> > select
> > w.rowid r_id, w.acc, w.prod, w.system_account
> > from
> > mytab w
> > where
> > w.brn=:b1
> > and not exists (
> > select 1
> > from myview v
> > where v.brn=w.brn and v.acc=w.acc and
> > v.prod=w.prod );
> >
> > view 'myview' is a complex view with union (all)
> of
> > three subqueries.
> >
> >
> >
> >
>
____________________________________________________________________________________
> > Looking for a deal? Find great prices on flights
> and hotels with Yahoo!
> > FareChase.
> > http://farechase.yahoo.com/
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
____________________________________________________________________________________
Get the Yahoo! toolbar and be alerted to new email wherever you're surfing.
http://new.toolbar.yahoo.com/toolbar/features/mail/index.php
--
//www.freelists.org/webpage/oracle-l
Other related posts:
