DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=yahoo.com;
  h=X-YMail-OSG:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
  b=oUJZHTuaD8FHIl+Ij2i91zglskuPsJCboDAketJnVZNSxLDL4oWOg64dKqLnS852GXoeiD3tXdw1kaI9gm88dGON3aoaNKqwhjbaJKq/46nd627svRj+AyTniwEy+bsoikkjkBqIvsTnzx24nLVSZ+IxU9AJgQ1FA+2bS72WyXU=;
Date: Wed, 18 Jul 2007 23:43:37 -0700 (PDT)
From: Tomi Wijanto <restomi_w@yahoo.com>
Subject: Plan changing after database restart
To: oracle-l@freelists.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Message-ID: <927183.43635.qm@web52009.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

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