Re: An ancient mystery

List,
Apologies for not getting back to the list. This problem happened on a database that a colleague manages so my information is a little sketchy.
The problem was noticed because the business function "stopped" working because the FTS took so long to return. The local DBA diagnosed the problem. I believe he noticed the FTS in progress. He ran EXPLAIN PLAN to confirm the behavior. During the time this problem occurred, EXPLAIN PLAN showed FTS would occur, after the problem went away, explain plan returned to showing the index would be used. Crude, but that is all the information I was able to gather in crisis.
I really appreciate all the excellent suggestions. I have passed the ones I thought applied along to that DBA, especially Jared's reminder about ANALYZE INDEX VALIDATE STRUCTURE. I have used that a lot, but forgot it over the years.
The problem has not recurred since last Thursday.

Thanks,
Dennis Williams

On Sat, Nov 22, 2008 at 10:02 AM, Madhu Sreeram <madhusreeram_at_gmail.com>wrote:

> Here is a thought.
> May be some implicit data conversion is happening? For example, assuming
> the query involves bind values, passing a number type instead of character
> type can lead to "unexpected" behavior. The table has possibly grown large
> enough that you are able to feel the "pain" now.
>
> -Madhu Sreeram
>
>
> On Thu, Nov 20, 2008 at 9:05 PM, Dennis Williams <
> oracledba.williams_at_gmail.com> wrote:
>
>> List,
>>
>> The situation:
>>
>> Oracle 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
>>
>
>

--
http://www.freelists.org/webpage/oracle-l