"Jaap W. van Dijk" wrote:
Check the parameter 'optimizer_mode' in the
view v$parameter which can have
values 'RULE', meaning just that, and 'CHOOSE', meaning rule based
if none
of the involved tables and indexes has values in the statistics fields,
otherwise cost based. In the latter case default values for statistics
are
assumed for tables and indexes without them.
Jaap.
So I have a database :
-
V$parameter say optimizer_mode = choose
-
The tables (using toad) have no statistics
(the last analyzed date is empty as well as the others like num_rows fields).
-
Megs of data is inserted/updated and deleted
daily/frequently. The tables are never dropped or rebuilt (ditto the indexes).
-
The SQL does not contain any hints.
So I believe that this database is quite a
mess. And what I want to know is:
-
How much will analyzing the tables and indexes
"help" performance given all the other problems (frag, poor planning, age).
-
Is there any risk of running analyze to build
statistics during work time? Is there any way that building statistics
could "hurt" the efficiency of a database. If a query is currently running
and I analyze a table it uses will it or the next query use the statistics?
-
What are the rules of thumb/tools for frequency
of analyzing your data beside "nightly or whenever it changes".
-
Since the optimizer_mode = choose if I add
a hint then will it override the statistics or "blend" the hint into what
it already knows about the table from the analysis. Is a hint a suggestion
or a query plan edict? Can you make a poor hint?
-
Any other points to ponder or links to good
articles on this area on-line?
-
Yes I'll read my Oracle books tonight and
post what they say.
STeve
Received on Fri Feb 04 2000 - 11:32:51 CST
