Re: Slow DB

The default optimizer mode on most platforms is choose. This will cause Oracle to use the all_rows optimization mode on all queries if any of the tables accessed in the query have statistics in the data dictionary. It will perform rule based optimization otherwise. The sys tables work very well under rule based optimization and you could run into problems if you compute statistics on them.

The decision on whither or not to use cost or rule based optimization will depend on your application. Some off the shelf applications are built for rule based optimization and should be left that way. Otherwise, you might consider testing application performance using cost based optimization. To do so, make sure the optimizer_mode is set to choose in your init.ora. You will then need to gather statistics on the tables and inidexes used by the application. An easy way to accomplish this is to use dbms_utility.analyze_schema(check the Oracle 7 application developer's guide for how to use it).

To see if you have statistics on the sys tables execute the following query:

select table_name, avg_space
from dba_tables
where owner = 'SYS';

If any of the tables have a value (not null) for avg_space(one of many columns that will be populated), then you have statistics on those tables.

To remove statistics use the dbms_utility.analyze_schema.

Alex Vilner wrote:
>
> Eugene,
>
> What exactly do you mean by sys schema being/not being analyzed?
> How does one go about checking it as well as deleting whatever is needed?
>
> Thank you in advance.
>
> Alex
>
> Eugen Nyffeler <eugen.nyffeler_at_ubs.com> wrote in article
> <34F59FB9.393160ED_at_ubs.com>...
> > Hi
> >
> > Check if the sys schema was not analyzed by accident. If so delete it.
> >
> > rgds
> > eugen
> >
Received on Fri Feb 27 1998 - 00:00:00 CST