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

NetComrade wrote:
> 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.

Yes it's an appendix at the end of the book.

> >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

You definitely should considering doing more more reading and setting up 10 somewhere before planning a migration.

I picked up last year at Open World Arun Kumar's "Oracle Database 10g Insider Solutions" and read it a couple of times. It is a pretty good summarization and wrapup of many of the most important 10g new features.

There are a bunch of 10g New Feature's books out there ( beside the oracle documentation ).

> 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)

There's a bunch of things in 10g that will probably impact you here ...

Oracle 10g silently ignores the monitoring on and off options for tables ( although index monitoring usage is still supposed to work I believe ). The STATISTICS_LEVEL parameter setting now controls this.

You may want to consider ( or at least understand the implications ) of abandoning your own procedure driven collection and letting oracle decide when and what need statistics.

OPTIMIZER_MODE no longer supports CHOOSE and RULE. Received on Tue Apr 11 2006 - 09:50:41 CDT