FAQ
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;

Search Discussions

  • Peter McLarty at Jun 12, 2007 at 5:34 am
    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
    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

    --
    http://www.freelists.org/webpage/oracle-l
  • Ujang Jaenudin at Jun 12, 2007 at 5:48 am
    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 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
    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
  • Peter McLarty at Jun 12, 2007 at 6:08 am
    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
    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 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
    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
  • Limin Guo at Jun 12, 2007 at 3:27 pm
    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 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 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
    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.

    --
    http://www.freelists.org/webpage/oracle-l
  • Brady, Mark at Jun 12, 2007 at 5:48 pm
    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
    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 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 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
    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
  • Kerber, Andrew W. at Jun 12, 2007 at 12:36 pm
    You might try this:

    Alter system set optimizer_features_enable=8.1.7

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter McLarty
    Sent: Tuesday, June 12, 2007 12:34 AM
    To: ujang.jaenudin_at_gmail.com; oracle-l_at_freelists.org
    Subject: RE: 10g vs 9i

    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
    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

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

    NOTICE: This electronic mail message and any attached files are confidential. The information is exclusively for the use of the individual or entity intended as the recipient. If you are not the intended recipient, any use, copying, printing, reviewing, retention, disclosure, distribution or forwarding of the message or any attached file is not authorized and is strictly prohibited. If you have received this electronic mail message in error, please advise the sender by reply electronic mail immediately and permanently delete the original transmission, any attachments and any copies of this message from your computer system. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Peter McLarty at Jun 12, 2007 at 7:00 am
    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
    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 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
    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 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
    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
  • Marco Gralike at Jun 12, 2007 at 8:27 am
    The following strange issue happens...

    I got a HPUX 11.23 machine.

    When I connect remote via SQLPlus (SQLNET) to this database, I can
    select stuff etc.

    A compile action of a trigger hangs the SQLPlus session (no locks, no
    traces, no waiting seen from the database)
    The same action is doing fine (compiling the trigger) on the machine
    itself via direct (using ORACLE_SID) or SQLNET...

    It looks like there is some kind of firewall / name resolution thingy
    going on.

    As far as I (not HPUX specialist) can see the firewall is disabled and
    bastille (which apparently is default nowadays) is hopefully not into
    effect.

    Does someone has any pointers ??? Would appriciate it

    THX

    Marco

    amisux08:[MTSO]:/oracle/10.2> lsnrctl status

    LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 12-JUN-2007
    10:23:32

    Copyright (c) 1991, 2005, Oracle. All rights reserved.

    Connecting to
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amisux08)(PORT=1521)))
    STATUS of the LISTENER

    Alias LISTENER
    Version TNSLSNR for HPUX: Version 10.2.0.2.0 -
    Production
    Start Date 12-JUN-2007 07:23:06
    Uptime 0 days 3 hr. 0 min. 26 sec
    Trace Level off
    Security ON: Password or Local OS Authentication
    SNMP OFF
    Listener Parameter File /oracle/network/admin/listener.ora
    Listener Log File /oracle/network/log/server/listener.log

    Listening Endpoints Summary...


    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux08.amis.local)(PORT=1521
    )))
    Services Summary...
    Service "MTSO.amis.nl" has 1 instance(s).
    Instance "MTSO", status READY, has 1 handler(s) for this service...
    Service "MTSO_XPT.amis.nl" has 1 instance(s).
    Instance "MTSO", status READY, has 1 handler(s) for this service...
    Service "MTSTXDB.amis.nl" has 1 instance(s).

    tnsnames entry

    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=10.252.252.8)(Po
    rt=1521)))(CONNECT_DATA=(SERVICE_NAME=mtso.amis.nl)(SRVR=DEDICATED)))
  • Marco Gralike at Jun 12, 2007 at 1:36 pm
    Solved...

    Apparently it was a switch (network) problem... The machine has been
    hooked up, since yesterday, with a new network cable on a different
    switch. When using the old switch with the old network cable everything
    works OK. When using the new network connection on the different switch
    "large" data statements (like creating "big" PL/SQL objects) the session
    hangs for a long period of time (no return data, timeperiod >= 30 min).
    Select statements work in both situations as normal (at least not really
    noticable difference)...

    GrZ



    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Marco Gralike
    Sent: dinsdag 12 juni 2007 10:27
    To: oracle-l_at_freelists.org
    Subject: Strange problem

    The following strange issue happens...

    I got a HPUX 11.23 machine.

    When I connect remote via SQLPlus (SQLNET) to this database, I can
    select stuff etc.

    A compile action of a trigger hangs the SQLPlus session (no locks, no
    traces, no waiting seen from the database) The same action is doing fine
    (compiling the trigger) on the machine itself via direct (using
    ORACLE_SID) or SQLNET...

    It looks like there is some kind of firewall / name resolution thingy
    going on.

    As far as I (not HPUX specialist) can see the firewall is disabled and
    bastille (which apparently is default nowadays) is hopefully not into
    effect.

    Does someone has any pointers ??? Would appriciate it

    THX

    Marco

    amisux08:[MTSO]:/oracle/10.2> lsnrctl status

    LSNRCTL for HPUX: Version 10.2.0.2.0 - Production on 12-JUN-2007
    10:23:32

    Copyright (c) 1991, 2005, Oracle. All rights reserved.

    Connecting to
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=amisux08)(PORT=1521)))
    STATUS of the LISTENER

    Alias LISTENER
    Version TNSLSNR for HPUX: Version 10.2.0.2.0 -
    Production
    Start Date 12-JUN-2007 07:23:06
    Uptime 0 days 3 hr. 0 min. 26 sec
    Trace Level off
    Security ON: Password or Local OS Authentication
    SNMP OFF
    Listener Parameter File /oracle/network/admin/listener.ora
    Listener Log File /oracle/network/log/server/listener.log

    Listening Endpoints Summary...


    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=amisux08.amis.local)(PORT=1521
    )))
    Services Summary...
    Service "MTSO.amis.nl" has 1 instance(s).
    Instance "MTSO", status READY, has 1 handler(s) for this service...
    Service "MTSO_XPT.amis.nl" has 1 instance(s).
    Instance "MTSO", status READY, has 1 handler(s) for this service...
    Service "MTSTXDB.amis.nl" has 1 instance(s).

    tnsnames entry

    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=10.252.252.8)(Po
    rt=1521)))(CONNECT_DATA=(SERVICE_NAME=mtso.amis.nl)(SRVR=DEDICATED)))

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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 12, '07 at 5:13a
activeJun 12, '07 at 5:48p
posts10
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase