Re: Telling When Statistics Present

>parkerr_at_logica.com (Robin Parker) wrote:
>>You can tell when you are using the cost based optimizer - the whole system
>>slows to a crawl !! :-(
>>
>>I made the mistake of doing an ANALYZE TABLE on some tables on one of my live
>>systems. The phone rang hot with complaints until I realised what was
>>happening and deleted the stats. Leason learned the hard way.
>>
>>Robin.
 

>Robin,
 

>This is not always the case. Cost based optimizer attempt to use the
>distribution of the data as part of the optimizing scheme. It can result
>in very efficient queries if your data has pretty similar dispersion most
>of the time, and of course used this with appropriate indexes. Having
>said that, I think cost based optimizer is no good for dynamic tables
>because (unfortunately) most of time it made the wrong "guess".
 

>Andy :)

Hhhmmmm. Another DBA (not me this time!) imported all of the standing data (ie stuff which is very rarely added too/updated) with the STATISTICS=ESTIMATE clause. This also brought the system to its knees until I did the delete statistics.

I'm not sure what you mean by 'similar dispersion', but following these experiences I have no faith in the cost based optimizer.

However, there must be someone out there who has sucessfully used the cost based optimizer? Anyone care to own up?

Robin. Received on Wed Oct 25 1995 - 00:00:00 CET