Re: oracle-l Digest V3 #207

["Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>]

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

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

Other related posts:

Re: oracle-l Digest V3 #207