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 > -- > //www.freelists.org/webpage/oracle-l > > > -- regards ujang -- //www.freelists.org/webpage/oracle-l
-- Regards, Limin Guo.