Re: analyze_database vs. gather_database_stats

swestner wrote:
> Hello,
>
> we have a small Oracle 9.2.0.6-database with about 100 tables and about
> 1-10 million records per table. Some queries runs a long time and
> because of this I ran the following found in google:
>
> connect sys/iwadissys as sysdba
> exec dbms_utility.analyze_database('COMPUTE', 0, 100, 'FOR TABLE')
> exec dbms_utility.analyze_database('COMPUTE', 0, 100, 'FOR ALL
> INDEXES')
> exec dbms_stats.delete_database_stats;
> exec dbms_stats.gather_database_stats;
> exit
>
>
> It speed up the queries a lot but I really don't no what I've done...
> :-(.
>
> I know Oracle has a rule-based and a cost-based optimizer. I read on
> google that it is not a good idea to run the rule-based-stats on
> system-tables because they are designed for cost-based-stats.
>
> Following questions:
> 1.) Does analyze_database and gather_database_stats do the same or is
> one for CBO and one for RBO? Which should be used?
> 2.) Does the above commands affect the system-tables and could lead to
> drawbacks?
> 3.) What is the preffered way to build all statistics of all table and
> indixes belonging to one user using CBO or using RBO?
> 4.) Is there an init-param which tells oracle which optimizer (RBO,
> CBO) it should use for queries? If this parameter is set to RBO does it
> affet the access on the system-tables as well?
> 5.) Is it generally spoken better to use RBO or CBO?
>
> Thanks
>
> Stefan Westner

At 9.2.0.6 you should be using the CBO and using DBMS_STATS to gather statistics ... not ANALYZE.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)