Re: oracle-l Digest V3 #207

Considering only the optimizer the most likely problems on a migration from 8i to 10g come from:

Subquery unnesting - use /*+ no_unnest */ hint in the subquery for special cases, _unnest_subquery = false if too many to handle individually.

Complex view merging - use /*+ no_merge */ hint in the subquery for special cases, _unnest_subquery = false if too many to handle individually.

btree/bitmap conversions - burning up the CPU - in 10g you can use the /*+ no_index_combine(table index1 index2 ...) */ hint for special cases, _b_tree_bitmap_plans = false if too many to handle individually.

Subquery pushing - the push_subq hint now goes in each subquery to be pushed, not in the main query

hash aggregation - if you have code that gets the data in the right order after doing a group by without an order by, you may now find the data coming out in the wrong order because oracle has switch to "hash group by" instead of "sort group by". Add order by clauses - or disable hash aggregation by setting

    _gby_hash_aggregation_enabled = false

dynamic sample is enabled at level 2 automatically you get stats collection happening automatically every 24 hours unless you disable the job - this will do too much work too often and generate too many histograms

AWR kicks in every hour - and you're not allowed to use the gathered data unless you have the right licences, so you might want do disable it.

PL/SQL
    for r in (query) loop

        .....
    end loop

Implicit cursor for loop - 10g turns this into array fetching 100 rows at a time under the covers. In the odd case where you don't want this to happen you can recompile individual packages with the plsql_optimize_level set to 1.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> Date: Tue, 18 Jul 2006 04:07:05 -0700 (PDT)
> From: Paula Stankus <paulastankus_at_yahoo.com>
> Subject: Re: 8i to 10g migration
>
> We have the following environment:
>
> -a great deal of database links
> -materialized views over database links
> -Oracle 8.1.7
> -Solaris 2.9
> - .Net with ODP
> -Powerbuilder
> -Data Junction
> -Siebel
> -Cobol programs precompiled
>
> and are planning a migration from 8.1.7 to 10g.
>
> Where is the best place to look for gotchas:
>
> -database links
> -hints that have been desupported
> -Issues with .Net
> -Issues with Cobol precompilers
> -Performance concerns
> -migration path - direct migration or export/import????
>
> I am planning to clone production, give ample time to multiple development
> teams to test this out. I am planning to take sample queries generating
> execution plans in both environments and traces. I am planning a stress-test.
>
> Any advice would be greatly appreciated.
> -required PL/SQL changes
>

--
http://www.freelists.org/webpage/oracle-l