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

Tom,

It is most likely that a dbms_job is scheduled within the database that executes a procedure to gather statistics. This would be viewable by connecting with a privileged account via sqlplus and executing the following:

SQL> select * from dba_jobs;

you might the 'what' for a job appear as:

begin; dbms_stats.gather_schema_stats(ownname=><owner>, cascade=>true); end;

it is possible that stats are only being gathered on the tables, and not on the indexes. that would provide the Oracle cost based optimizer with less info than it requires to make informed decisions.

I'd also agree with the other poster than recommended examining the settings for optimizer_index_cost_adj

Pd Received on Thu Oct 30 2003 - 15:20:41 CST