Re: Slow DB

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.
>

Hi Alex

Oracle uses the rule base optimizer for the data dictionary (the tables in schema
sys). If you have now set the optimizer_goal to choose (either in the init.ora file or with alter session command) and there are analyzed tables in your query,
oracle uses the cost based optimizer. I think that oracle has tuned the data
dictionary queries for rule base access. Therefor cost based access path can have a real impact on your performance regrading the data dictionary access
(wich is used in every query).

To deleted the analyze of a schema you can do following:

1. login as sys, system or internal
2. enter the statement "execute dbms_utility.analyze_schema('your schema name in upper case', 'DELETE');

An indicator if a table is analyzed or not might be the fields avg_space, chain_cnt, num_rows, etc. in the dba_tables (or user_tables, or all_tables).
If a table isn't analyzed this fields should be null, when analyzed they have a value. Not the best way, but i don't know any other.

Hope it helps
eugen Received on Fri Feb 27 1998 - 00:00:00 CST