analyze_database vs. gather_database_stats

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 Received on Wed Apr 05 2006 - 09:53:07 CDT