Plan changing after database restart

From: Tomi Wijanto <restomi_w_at_yahoo.com>
Date: Wed, 18 Jul 2007 23:43:37 -0700 (PDT)
Message-ID: <927183.43635.qm@web52009.mail.re2.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 Received on Thu Jul 19 2007 - 01:43:37 CDT