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@xxxxxxxxx> 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@xxxxxxxxxx> 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@xxxxxxxxx]
> Sent: Tuesday, 12 June 2007 03:14 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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.

Other related posts: