Re: Deleting statistics speeds up server?

"Tom Dyess" <tdyess_at_dysr.com> wrote in
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
>
>

Was everything slow - I doubt that very much - or just some crucial, often executed sql?
Without statistics the sql is parsed by the RBO which will jump on any available index. That obviously is the right choice in this case. The question then becomes "Why is the CBO not using indexes" (provided that is catually the case. Contrary to the other posters I am not so quick in advocating changing the optimizer_index_... parameters. Especially in 9.2 I'd rather look at collecting system statistics.

-- 
What lies behind us and what lies before us are small matters when compared 
to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP