Re: An ancient mystery

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Sat, 22 Nov 2008 01:05:40 +0800
Message-Id: <200811211705.mALH5eSf020055@smtp17.singnet.com.sg>

Is it a very complex query which joins with other tables hitting OPTIMIZER_MAX_PERMUTATIONS because the potential execution operations against *other* tables has changed (indexes dropped/created or statistics updated on any of the other tables in the query) ?

At 11:05 AM Friday, Dennis Williams wrote:
>List,
>
>The situation:
>
>Oracle <http://8.1.7.4>8.1.7.4 database on Solaris 8 (soon to be
>upgraded to 10g)
>Rule-based Optimizer
>
>A query which has run for years using an indexed access to a very
>large table (maybe 100 million rows)
>suddenly decides to use a full-table scan, shutting down a critical
>business process.
>
>Then after several hours, it switches back to using the index.
>All concerned claim that nothing changed before or after.
>
>Needless to say the business users are nervous. They think maybe the
>database grew beyond some limit.
>
>Can anyone think of an explanation?
>
>Thanks,
>Dennis Williams

Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantscribbles.blogspot.com
and
http://hemantoracledba.blogspot.com

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes
: http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 21 2008 - 11:05:40 CST

Original text of this message