Re: top things to watch out for when migrating from 9i r2 to 10g r2

On 10 Apr 2006 14:31:33 -0700, "hpuxrac" <johnbhurley_at_sbcglobal.net> wrote:

>NetComrade wrote:
>> I am in the early stages of studying the migration path to 10gr2.
>>
>> Anything particular to watch out for?
>
>Have you looked at the last chapter's in Jonathan new book? He has a
>pretty good summary of optimizer related things that could burn you.

Are you referring to the CBO book? Planning to get that.

>Tom also has a new book out that is more 10.1 but also has some good
>info on 10.2 .

I haven't finished reading neither Effective Oracle by Design nor the Architecture book

>> I kind of recall that 10g now automatically collects table index
>> statistics?
>
>Depends on if you convert a database manually or use DBUA.
>
>If you are defaulting and letting the stats get collected automatically
>then yes you "should be able to" abandon some of the analyze type of
>stuff.
>
>But in 9.2 and below you could do this via the dbms_stats procedure's
>anyhow.

Currently I have a procedure that I wrote that for each non system user
a) collects stale stats
b) collects stats for tables that don't have monitoring set or that are not temporary & enable monitoring for those tables (e.g. new tables)
c) deletes stats for tables with 0 rows (e.g., if they're quickly populated, I'd rather have the optimizer use 'rule' then think it has 0 rows)

One less thing to maintain.
.......
We run Oracle 9.2.0.6 on RH4 AMD
remove NSPAM to email Received on Tue Apr 11 2006 - 09:25:21 CDT