Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Estimate statistics
Marc wrote:
> Yes, there are several bugs with analyze even in 7.2. However, depending which
> problem you are encountering there are different work-arounds. The best method
> is to use 'compute'. However, this is also buggy for very large tables (> 6 Gb)
> so that you can't use compute at all. Then, the only work around is to estimate
> with approximate 25-30% and do manual updates to the tab$ table to have correct
> stats. If you need more information you can e-mail me directly. It is not simple
I experienced a lot of problems with the cost based analyzer in 7.2. In
several cases queries that were happily working became hopelessly
slow/inefficient after re-estimating statistics - a scary scenerio on a
production system. I simply concluded that cost based optimization was
an young/imperfect and science. It never occurred to me that estimate
statistics might be generating rubbish. My workaround was to supply
hints for queries with the potential for large joins.
The lesson I have learned is that it is dangerous to rely on Oracle for acceptance testing of new releases. Expensive and painful though it is, you have to setup parallel production environment(s) and test as much as possible yourself.
James Powrie Received on Wed Mar 19 1997 - 00:00:00 CST