 Bothering "Sudden Death" by unexpected dropping efficient execution plan [message #205195] |
Thu, 23 November 2006 18:44 |
oranje66
Messages: 4
Registered: November 2006
Location: Banking Industry, Germany
|
Junior Member |
|
|
Hello,
At my site I encountered the following problem: oracle 10R1 looses the efficient execution plan and puts in place an execution plan that has about 1.000 times as much I/O.
My (simplified) Query:
select /*+ HINTS */
*
from a (11.000.000 recs)
, z (500 recs)
, y (10 recs)
, b (16.000.000 recs)
, c (16.000.000 recs)
, d1 (26 Partitions / 180.000.000 recs)
, d2 (26 Partitions / 180.000.000 recs)
where ...
and not exists (select /*+ HINTS */
null
from d (26 Partitions / 180.000.000 recs)
where ...
)
This query (one of many) is executed about 100.000 / Day.
Avg.Result set: about 10 Rows
Avg.DB Blocks Read: 3-5 / execution
All involved tables are properly indexed / statistics etc.
But, whatever it causes, its execution plan is dropped suddenly and replaced by a poor one. Presumably : AWR + ADDM, based on "environmental" metrics. Turning off AWR+ADDM implies lost of OEM-Functionality.
The poor execution plan has the Anti-Join Sub-Query located between table Y and table B instead of last.
Query 1 : How can I force Oracle to evaluate the sub-query at the end ?
Add.Info: The Hints of the main-query are
index( a a_ux )
index( z z_pk )
index( y z_pk )
index( b b_ux )
index( c c_ux )
index( d1 d_ix )
index( d2 d_ix )
use_nl( a z y b c d1 d2 )
Add.Info: The Hint of the sub-query is
index( d d_ix )
Query 2 : must use_nl be accompanied with a join-order hint (ordered)
Query 3 : Is the hint no_push_subq in the sub-query all ? Or, does this hint require addition hints to be effective and rock-solid.
Any help is appreciated.
Many thanks in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
