| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Rule Based Optimization Going Away?
Comments in-line ...
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Posted At: Friday, October 04, 2002 10:27 AM
Posted To: server
Conversation: Rule Based Optimization Going Away?
Subject: 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.
I shall have a look and see what it says then. I haven't come across that before. However, I have come across the following :
Oracle Performance Tuning 101 by Vaidyanatha et al, page 67. Chapter 3 Application Tuning - Issues that concern a DBA.
applies if OPTIMZER_MODE is set to CHOOSE
<QUOTE>
it is important that statistics be generated for all objects in all the
application schemas. This is because the presence of partial statistics
could cause the server process servicing the SQL statement to estimate
statistics on the objects without statistics only for the execution of
that SQL statement. Such dynamic runtime sampling of statistics in not
permanently stored in the data dictionary and hence is repeated for
every run of the same query. This can and will cause performance
degredation.
</QUOTE>
There is no mention of whether this is Oracle 9i or not, so it is misleading based on what you have said.
>> 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 ...
I shall be trying this out very soon, just to appease my own curiosity.
>> Sorry, I have received a couple of FAQs - I'll post them
>> 'real soon now'.
There is no rush, I was just concerned that you may not have received them - we've had email problems again !
Cheers,
Norman.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com -------------------------------------Received on Fri Oct 04 2002 - 06:35:10 CDT
 |
 |