Re: How do you un-analyze tables?

From: George Barbour <george.barbour_at_gecm.com>
Date: Fri, 29 Jun 2001 10:32:04 +0100
Message-ID: <3b3c48d5$1@pull.gecm.com>

Jonathan,
 Thank you for your prompt reply.
The database is back to running with optimizer_mode = CHOOSE, but there are now no stats in the Discoverer schemas, as you suggested, and life is almost back to normal......but.

 This exercise started when I was investigating why some 'similar queries ran much faster than others. The result eventually came down to optimizer selection, and hence my postings on this newsgroup. During the investigation I made changes to the database and waited for user reactions, I was not disappointed. Some users found their queries running in seconds instead of minutes, and some cried loudly about their queries now taking minutes (hours) when previously they took seconds (well less than a minute). I have subsequently restored the database back to its original configuration. However, I now have the situation where, lets call them the 'previously patient users' are now aware of how fast their queries can actually go if the DBA (me) would allow it. I now have a new exercise of diplomacy and awkward questions answering. <sigh!>.
It is important now for me to make sure that any new applications can efficiently use the CBO, as both yourself and Sybrand et al, have suggested. I will also have to work through the existing queries one at a time in order to optimise them.

I bought your book the other day, stood in the bookshop browsing it. I had a bundle of other books in my hand..... yours was so fascinating and interesting that I decided to lighten my load a little and replace one or two of the books back onto the high shelves where they came

from...............so I did..................while I was reading your
book.......................one of them fell of the shelve and hit me on the

head.
I am not sure who I can sue you or the other guy. ;-)

George Barbour.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:993802617.7786.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> 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.
>
>
>
>
Received on Fri Jun 29 2001 - 04:32:04 CDT