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?

Any one of a number of things.

Insufficiently accurate stats (was this a compute or an estimate?). The presence, or lack, of appropriate histograms if you have skewed data.

Having been down this route of analyse/delete/mess around with histograms/mess around with hints/mess around with parameters myself in the past here is what I would now recommend.

1. Identify what 'dog slow' means. Does it mean that specific processes are slow - I bet it does.
2. Trace a representative session of interest.
3. Find which statement(s) are taking the most time.
4. concentrate on the problem statement.
5. repeat with all problem processes.

-- 
Niall Litchfield
Oracle DBA
Audit Commission Uk