Re: Performace question

Evan, the information you provided is too thin for anyone to form a definite conclusion. How is this table used? You say there were only 10 rows but how many rows did the optimizer think there were (dba_tables.num_rows). If a lot of data had been inserted and then deleted the 10 rows could have been spread over many extents while the CBO was under the impression that full scanning the table would be the fastest way to access the rows when because of the few number of rows spread over a wide area indexed access would have been better.

Under the assumption that this will happen again run explain plans on the most common SQL that runs against this table and capture the statistics. When the problem reoccurs rerun the plans. Compare looking for changes, update the statistics, rerun plans again. Compare. This will give you some clues to what might be happening.

Also include the Oracle version and platform in future posts. HTH -- Mark D Powell -- Received on Fri Jan 14 2005 - 09:04:59 CST