Here is an excerpt from the Metalink note 35934.1 entitled:
"Cost Based Optimizer - Common Misconceptions and Issues"
- Prior to Oracle9i the advice of support was that users should "NOT analyze
the data dictionary tables (SYS tables) unless you have a very strong reason to
do so". With the desupport of the RBO pending in 10i there will only be a CBO
so the dictionary will need to be analyzed. The dictionary can now be analyzed
without any problems. Note that whilst DBMS_UTILITY.ANALYZE_SCHEMA can be used
against the SYS schema Oracle does not perform any regression tests with these
tables analyzed and so it is possible that deadlocks or performance problems
may be encountered. Also note that a significant number of large databases run
extremely succesfully with an analyzed dictionary.
On Mon, 26 May 2003 22:55:48 +0800, Connor McDonald wrote:
> Howard J. Rogers wrote:
>>
>> No. Let's be clear:
>>
>> The rule-based optimizer goes in 10i (though support in various forms
>> continues). Therefore, you are still supposed to use rule-based on the data
>> dictionary in 9i.
>>
>> No doubt some sort of pre-10i migration to the CBO has been made in 9i,
>> which is probably why it isn't so painful to make the switch as it was in
>> earlier versions. They aren't going to unleash a CBO-accessed dictionary on
>> an unsuspecting world (without having tested it on an unsuspecting 9i
>> world).
>>
>> In a similar way, if Dictionary Managed Tablespace is disappearing in 10i,
>> or 11i, it makes sense to switch to an LMT SYSTEM tablespace a bit earlier
>> than that so the bugs can be ironed out before no-one has a choice in the
>> matter.
>>
>> But a CBO SYSTEM tablespace still isn't recommended in 9i.
>>
>> Will it do harm? Probably.
>>
>> Will it do the sort of harm that you would have experienced in 8.0 or 8i?
>> Probably not: a lot of the code that 10i will contain must have already
>> crept into 9i (version 2, at any rate).
>>
>> Regards
>> HJR
>>
>> "Ryan" <rgaffuri_at_cox.net> wrote in message
>> news:cB8Aa.97348$823.47068_at_news1.east.cox.net...
>> >
>> > "Jack Silvey" <depifster_at_yahoo.com> wrote in message
>> > news:25c7944d.0305251049.4b6064fc_at_posting.google.com...
>> > > Ryan,
>> > >
>> > > Without stats on the DD, your recursive data dictionary queries run
>> > > under rule based optimization, which is the intent in earlier versions
>> > > of Oracle (<9i).
>> > >
>> > > If you analyze the DD, the optimizer will try to use cost based
>> > > optimization in dictionary queries. This can be a disaster since the
>> > > dictionary was designed long ago to run under rule. The CBO could
>> > > start making very bad choices about execution plans and this can
>> > > really slow down all your users (sort segment allocation, query
>> > > parsing, space allocation, stats gathering, etc.)
>> > >
>> > > A good thing might be to test on your dev/qa systems.
>> > >
>> > > hth,
>> > >
>> > > Jack Silvey
>> > > warehouse DBA somewhere in Texas
>> > >
>> > >
>> > >
>> > > > acceptable? does it help at all or its just not a bad thing?
>> >
>> > pre-9i? Wait a second, everything I have read and seen has said that 9i
>> > still uses the RBO on the system tablespace? Its not until version 10 that
>> > the CBO will be used on the data dictionary.
>> >
>> > Or have I been reading the wrong material?
>> >
>> >
>
> I'm sure there's a metalink note that somewhat contradicts this - along
> the lines that in 9.2 cbo stats on the dictionary is fully supported.
> The only issue is deciding whether 'supported' = 'advantageous'
>
> hth
> connor
--
Mladen Gogala
Software is like sex, it is better when it is free.
Linus Torvalds
Received on Mon May 26 2003 - 15:06:23 CDT
