Re: Rule Based Optimization Going Away?

I hadn't heard the 'dynamic sampling if stats are missing' hypothesis before, until I read a paper by Walter Breitling (OW 2002of IOUG-A 2002, I think, it can be downloaded from http://www.hotsos.com ) which raised it for the express purpose of pointing out that it wasn't true.

You can see this quite easily by running a 10053 trace on a suitable test case, and seeing that Oracle reports something like:

    Stats Not available
followed by

    CDN 12345 ... The most obvious thing you spot after a couple of tests is that the row-based stats are driven by assuming that a typical row is some 'sensible' size (I think it was 100 bytes in 8.1).

I haven't bothered to test 9.0 in similar circumstances.

I believe I have seen the actual assumption printed in the 9.2 performance guide. (That book is getting so good that it's beginning to make me feel redundant).

Sorry, I have received a couple of FAQs - I'll post them 'real soon now'.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

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



Norman Dunbar wrote in message ...


>

>I had heard that this was the way of things before 9.2 in that any query

>using CHOOSE as the optimiser mode and where stats existed on at least

>one table,, then the stats would be dynamically gathered, used and

>discarded hence adding a performance overhead. If the same query was run

>again later, the stats would be gathered dynamically again and so on.

>