Ivan Boesing wrote:
>
> Hi Oracle gurus,
>
> I recently changed the optimizer mode of my database. I collect
> statistics with the analyze command for some tables (about 10). The
> optimizer works fine with this tables and statistics.
> Howewer, I have some doubts. Do I collect statistics for each table in
> database?? If so, how can I do it automatically (or I have do it manually,
> table per table). I should do this by a midnight script, but for every new
> table I would need to update the script..... By other hand, if
> statistics are necessary just for the most used tables I can collect it
> manually, but if the table change in size so frequently how the optimizer
> will work?
> Sumarizing, how can I proceed with statistics and cost based
> optimization method?
>
> Thanks in advance
>
> Ivan Boesing
> boesing_at_geocities.comYou can analyze all tables for a schema owner (and indexes) using
Oracle's analyze_schema package. It needs parameters for Schema Owner
and analyze command options. It will analyze tables and indexes (prior
to 7.3) for that schema owner. You can execute this package from a shell
script at certain time intervals via cron facility (on UNIX). Or you may
want to check another Oracle package (dbms_jobs). This jobs package is
Oracle's 'cron' facility. Up-to-date statistics on objects will
help the CBO to select correct execution plan. If you do not have stats
on some tables and have it on others, then, in cases where these tables
are invloved in a 'join' Oracle will use 'Rule' based optimization.
Hope this will help.
Received on Fri Aug 15 1997 - 00:00:00 CDT
