Re: Analyzing tables

ramesh wrote:
> Hi All,
> I was running Analyze command on a large table (Oracle
> database) thinking that it would help in query performance. But it was
> taking long
> time and so I "Stopped" the command in the middle. Now I see that
> the query which was working reasonably fast on the table is taking
> "very" large amount of time. Any pointers to this.
>
> Thanks in advance,
> Ramesh

Well, you've probably got duff statistics for the table now, so do an

analyze table X delete statistics;

to get rid of them completely.

A couple of things to learn from this. One is that, depending on your version, you should be using dbms_stats to collect table statistics, not 'analyze table' commands, if for no other reason than that you can parallelize the collection of statistics with the package, but can't with the old-style command. Hence dbms_stats is likely to run a good deal faster than 'analyze table', depending on your CPU numbers of course.

Second, it teaches us that collecting statistics for a large object is itself a large exercise, and that's presumably why Oracle invented the ability to *estimate* statistics rather than to compute them to the nth degree.

Third, we hopefully learn that blindly computing statistics just because one "thinks" it "might" help performance is a mug's game: first, define what the performance issue is, and then you'll be in a better position to judge whether it's an issue to do with dodgy execution plans, which might in turn be improved by having better statistics. Explain plans? Statspack reports? Wait events?

Fourth, it becomes painfully apparent that it is extremely difficult to give detailed and meaningful advice in the absence of any actual details   *from you* about the problem... you know, such minor details in this case as what version of Oracle you're running on, what optimiser mode you're working with, what the nature of the performance problem you diagnosed was and so on.

Regards
HJR Received on Tue Apr 27 2004 - 02:05:48 CDT