RE: 10g vs 9i

There is also a Horrible bug along these lines that actually causes queries to occasionally yield incorrect results.  

https://metalink.oracle.com/metalink/plsql/f?p=130:4:4310688952095264987 ::::p4_database_id,p4_docid,p4_show_header,p4_show_help,p4_black_frame,p 4_font:Bug,4604970,1,0,1,helvetica  

which is avoided by set "_gby_hash_aggregation_enabled" to FALSE.  

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Limin Guo Sent: Tuesday, June 12, 2007 11:27 AM
To: ujang.jaenudin_at_gmail.com
Cc: Peter McLarty; oracle-l_at_freelists.org Subject: Re: 10g vs 9i  

Ujang,

I had similar problem when upgrading application from 9.2.0.7 to 10.2.0.2 last 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. 



>>> This e-mail and any attachments are confidential, may contain legal,

professional or other privileged information, and are intended solely for the
addressee.  If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2

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