Hi,
dbms_stats package introduced since 8i, but Tobie, as he said, is using
8.0.5.
Regards,
--
Roman Mirzaitov
Brainbench MVP for Oracle Administration
www.brainbench.com
"Svend Jensen" <svend.jensen_at_it.dk> wrote in message
news:3D6BEA34.5010608_at_it.dk...
> 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 - 21:58:24 CDT
