Re: Plan changing after database restart

From: Tomi Wijanto <restomi_w_at_yahoo.com>
Date: Thu, 19 Jul 2007 03:07:12 -0700 (PDT)
Message-ID: <890655.35405.qm@web52002.mail.re2.yahoo.com>

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_at_gmail.com> wrote:

> The value of the bind on the first parse was most
> likely different.
>
> On 7/19/07, Tomi Wijanto <restomi_w_at_yahoo.com>
> 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/
> > --
> > http://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

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

Received on Thu Jul 19 2007 - 05:07:12 CDT