Home » RDBMS Server » Performance Tuning » Bothering "Sudden Death" by unexpected dropping efficient execution plan
icon5.gif  Bothering "Sudden Death" by unexpected dropping efficient execution plan [message #205195] Thu, 23 November 2006 18:44
Messages: 4
Registered: November 2006
Location: Banking Industry, Germany
Junior Member

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 */
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.

Previous Topic: latch waits
Next Topic: "+0 trick undesirable", why?
Goto Forum:

Current Time: Thu Jul 27 11:51:15 CDT 2017

Total time taken to generate the page: 0.13216 seconds