Re: Deleting statistics speeds up server?

"Tom Dyess" <tdyess_at_dysr.com> wrote in message news:<fvudnfSkNonIuD2iRVn-jw_at_fdn.com>...
> One of our clients is running Oracle 9.2.0 on AIX. Our application that runs
> on oracle was dog slow on their box. They brought in a consultant in and he
> deleted the statistics on the tables and indexes and it sped it back up
> again. Doubting the remedy, our net admin, reanalyzed the tables and it was
> slow again, then deleted statistics and it was again fast. Does anyone know
> what would cause such an unintuitive response?
>
> Tom Dyess
> OraclePower.com

Hi Tom,

We had a problem once before where the optimizer would not use an index if the data was skewed and bind variables were used, which is the only problem I have ever experienced with the optimizer. If you have 5 million rows with a status of 'DONE' and 5 with a status of 'READY', the optimizer may choose a full scan even if you are searching for 'DONE' if it doesn't know the literal value being searched due to binds being used. In that case, even if you have histograms, it won't matter. As soon as you delete statistics, it is forced to use the index.

If you are in this position you can use outline hints or hints to the query itself.

Regards,

Steve Received on Fri Oct 31 2003 - 09:38:43 CST