Re: Deleting statistics speeds up server?

From: mcstock <mcstock_at_enquery.com>
Date: Wed, 29 Oct 2003 16:22:31 -0500
Message-ID: <u82dnd2B1_ojsj2iRVn-gQ@comcast.com>

hmmm

wasn't there a post recently about never needing database hints now that the 9.2 optimizer is so clever?

the optimizer is obviously making a poor choice when you have statistics -- which may not be its fault, if the statistics are not correct (i.e., on tables but not on indexes)

try setting AUTOTRACE ON in SQL*Plus (or otherwise trace the SQL statements) and examine the different execution plans with and without statistics, then work from there to try to determine why the optimizer is stumbling

---

Mark C. Stock
www.enquery.com
(888) 512-2048

"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
>
>
Received on Wed Oct 29 2003 - 15:22:31 CST