Re: 10g vs 9i

Ujang,

I had similar problem when upgrading application from 9.2.0.7 to 10.2.0.2last year. I discovered my problem was caused the bug 5095456 in
10.2.0.2. Oracle's workaround for the bug is to set the following parameters to:

_gby_hash_aggregation_enabled=FALSE
_unnest_subquery=false
_complex_view_merging=false

Try them first and see how your query reacts.

Hope this helps,

Limin.

On 6/12/07, Ujang Jaenudin <ujang.jaenudin_at_gmail.com> wrote:
>
> peter,
>
> for the oltp users, they didn't give me an alert.
> but i'm testing a report as exactly so many joins, even the latest
> result comes from the result set not from table directly,,,, something
> like
>
> select * from (select .... from a,b where ... ( select .....))....
>
> tested on 9i it tooks only 30min, but on 10g rac, till 2hours and never
> end.. :(
>
> could you point me which v$ ?
>
>
> regards
> ujang
>
>
> On 6/12/07, Peter McLarty <p.mclarty_at_cqu.edu.au> wrote:
> > Hi Ujang
> >
> > I have seen this behaviour. It happens going from 9 to 10 at any level.
> > In the case I had was due to things doing joins whose performance
> > changed due to the effects of the optimiser.
> >
> > You will have to take each case by itself and analyse it. I would
> > consult with your business users for their worst performer and isolate
> > the cause of that and then work back from there removing the most
> > expensive issues
> >
> > It is likely as you take it apart you may fix a number of issues at the
> > same time
> >
> > Look out for joins to v$ views as these performed badly
> >
> > Cheers
> >
> > Peter
> >
> >
> > -----Original Message-----
> > From: Ujang Jaenudin [mailto:ujang.jaenudin_at_gmail.com]
> > Sent: Tuesday, 12 June 2007 03:14 PM
> > To: oracle-l_at_freelists.org
> > Subject: 10g vs 9i
> >
> > all,
> >
> > recently i have a dev machine 10g on itanium ia-64 another linux is
> > itanium ia-64 with 9i
> >
> > the performance on 10g (10.2.0.3) is dropped for almost 1/3 from the 9i,
> > if someone has the same experiences, pls share....
> >
> > all these parameters doesn't help either...
> > alter system set optimizer_features_enable='9.2.0' scope=both; alter
> > system set "_optimizer_cost_based_transformation" =on scope=both; alter
> > system set "_gby_hash_aggregation_enabled" = TRUE scope=both;
> >
> >
> > --
> > regards
> > ujang
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> regards
> ujang
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Regards,

Limin Guo.

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