Plan changing after database restart
- From: Tomi Wijanto <restomi_w@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 18 Jul 2007 23:43:37 -0700 (PDT)
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
Other related posts:
