RE: 10g vs 9i

Hi Ujang

We had a a problem with users joining on the lock table v$lock. Seeing the frame of what you are looking at can you send a trace from the report query on 10G and the explain plan and it would be good to compare to the same from 9i

This will assist in the diagnosis

Have you run statisitics yourself not relied on the dbms_stats_gather job.

Also do you have statistics carried over from the 9i in the 10G database. You might want to generate a entire fresh statistics set.

I would look at returning all parameters to default 10G unless you can clearly see that one creates a performance improvement in some way. Ie set back optimizer_features_enable to 10.2

With the trace and explain plan the point of the slowness can be idenfied and then the proper action can be taken

Cheers    

-----Original Message-----
From: Ujang Jaenudin [mailto:ujang.jaenudin_at_gmail.com] Sent: Tuesday, 12 June 2007 03:48 PM
To: Peter McLarty
Cc: oracle-l_at_freelists.org
Subject: Re: 10g vs 9i

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