RE: 10g vs 9i

Ujang

First thing is why is this happening.

This event is possibly due to sorting how do your SGA sizes compare are you using Automatic SGA Management and is their anything restricting your memory sort space. If you have free memory then I would certainly look at increasing the SGA size to reduce disk sorts. Once again not good unless you can identify why

Mark Bobak posted this SQL recently on this list to identify what is doing the sorts, but I think it is pretty obvious what is doing it in your case.

 select vsn.name, vss.value
   from v$sesstat vss,

        v$statname vsn

  where vsn.name like '%sort%'
    and vss.statistic# = vsn.statistic#
    and vss.sid in(select vs.sid
                     from v$session vs,
                          v$sort_usage vsu
                   where vs.saddr=vsu.session_addr);

As I said using a trace and explain plan a much clearer picture will emerge and the we can redesign the SQL or determine places for you to apply indexes to stop these problems. I suspect remove or reduce the source of your sorts and you will improve the performance.

Cheers

Peter

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

peter,

ofcourse the cost in 10g is bad.
and i have tried change optimizer feature, but doesn't help either.

i'm built this dev db from export 9i and import to 10g without statistics carry on.

query v$session_wait found "direct path write temp", but sure the temp file size is same as 9i.. even just now i added a tempfile ... but still got the problem.

regards
ujang

On 6/12/07, Peter McLarty <p.mclarty_at_cqu.edu.au> wrote:
> 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
>

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