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.
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@xxxxxxxxx> 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