Re: How do you un-analyze tables?

When the optimizer_mode is set to CHOOSE, the choice of optimizer is for each query as the query is parsed. Oracle checks
for the presence of statistics on each of the objects involved in the query - if none exist the target is set to RULE, if any exist the target is to ALL_ROWS, and Oracle
makes a few guesses about any missing
statistics.

The reason for the resulting dramatic
swings in performance appear because
Oracle's guesses can easily be inappropriate.

Typically, it assumes that un-analyzed tables are much smaller than they really are, so can do daft things like driving a nested-loop full tablescan off a 1000 row table. This is an issue that also appears when using
global temporary tables.

Your solutions seem to be

    make sure that none of the tables
    used by discoverer has any stats

    switch the optimizer_mode to RULE

    switch the optimizer_mode to all_rows     and make sure that all tables are
    reasonably analyzed.

--
Jonathan Lewis

Host to The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases
See http://www.jlcomp.demon.co.uk/book_rev.html

Seminars on getting the best out of Oracle
See http://www.jlcomp.demon.co.uk/seminar.html



George Barbour wrote in message <3b3c331e$1_at_pull.gecm.com>...



>Which optimiser is CHOSEN by the system at runtime. The Oracle system (the

>system not me) has to make a decision at some point about which optimiser

 it


>is going to use. Here are the facts,

>Oracle ver -  8.1.5.

>OS ver Sun Solaris - 2.8

>Application - Oracle Discoverer.

>The init.ora parameter optimizer_mode is set to CHOOSE as recommended by

>Oracle. Some queries from Discoverer choose RULE based some choose COST

>based; the selection is arbitrary as far as I can see. The difference

>between the two optimisers in some cases  is downright spectacular! For

>example, one query makes the decision to use the CBO, it takes over three

>(3) hours to run. When, as an exercise,  I add a hint to the query  /* +

>RULE */ it only takes seconds (19.37 secs) to run; these figures can be

>taken as typical.