Re: Analyze table problem

Tobie Berthon wrote:

> I ran an "analyze table .. estimate statistics" command against one
> table to find the average row length of the data in the table. Later
> that day, I received complaints that a number of our batch jobs had
> slowed to a crawl (2 min job running for an hour). I knew that it
> wasn't the programs at fault and we were able to determine that the
> joins to that particular table weren't working. It was like the
> primary key had become invalid. Once I deleted the statistics the jobs
> all ran fine again. What happened here? By the way, I'm running Oracle
> 8.0.5 on AIX 4.3.3. Thanks
>

Hi Tobie

You probably are running optimizer_mode = choose with the consequences that if no statistics exist on any table in a query, the optimizer is Rule Based (god old predictable RBO), but if any table has statistics on it, then the cost based optimizer(CBO)is used with ALL_ROWS, optimizing for all rows as fast as possible. Given that only one table has statistics, the CBO uses default values for statistics on the table(s) without. That will mostly give poor to very bad access/execution paths and prolonged query times.
Not knowing your applications, I would try to collect statistics on all or schema tables (indexed columns) and indexes, and see how things run. Use the dbms_stats.gather_schema_stats (ownname => schema, estimate_percent => some% - try between 5 and 20, block_sample => true, method_opt => 'FOR ALL [INDEXED] COLUMNS SIZE 1', DEGREE => NULL [parallel default on table], granularity => 'ALL', CASCADE => TRUE,
stattab => NULL, statid => NULL,
options => 'GATHER', statown => NULL)
If things turn out very bad, remove the statatistis again with same package. Anyway the cost based optimizer IS better as a hole (I know there are always some eks. of the opposite, but overall seen, it IS better).

rgds Received on Tue Aug 27 2002 - 16:08:04 CDT