DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
  b=dDc7qgVYOJiV7CDOTwhli4znCkmN5Qrz6zfFeOSK8hWgs3WZEvsdiB98Hlqbeoj/57MZivu4CioejH8VY0ljibcIOU7wFYIpdCczblghDpW+Xiug9gIW0JK9Sv1DufxxQBz6mR4BuAfy+D+VutMTwvfu4XLiyT7HED7aLptWPz8=;
Date: Thu, 19 Jul 2007 03:07:12 -0700 (PDT)
From: Tomi Wijanto <restomi_w@yahoo.com>
Subject: Re: Plan changing after database restart
To: Niall Litchfield <niall.litchfield@gmail.com>, oracle-l@freelists.org
In-Reply-To: <7765c8970707190145h62d71ddftb45b22b945e66f15@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Message-ID: <890655.35405.qm@web52002.mail.re2.yahoo.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: restomi_w@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@gmail.com>
wrote:

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