FAQ
First, the disclaimer, someone made a very bad coding
decision! Now the question is how to get out of the
mess.

We have about 2,000 scripts that do not have ORDER BY
clauses anywhere in them. In 9i all of the queries
returned the rows in the correct order. More a lucky
accident than anything else.

In 10g the optimizer has changed and the results are
returned unordered. Since we are weeks away from go
live of a major project there is no time to edit and
test all of the scripts. Management is now wanting us
to go live on 9i rather than 10g. Something the DBA
team is really resisting but may be necessary.

I came across a _newsort_enabled parameter which did
not change the result set in 10g. Is anyone aware of
a parameter that can be set in 10g that will give the
9i behavior other than setting the
optimizer_features_enabled to 9.2? If we do that we
might as well be on 9i.

Pete Barnett



Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
http://autos.yahoo.com/carfinder/

Search Discussions

  • Goran bogdanovic at Aug 24, 2007 at 2:21 pm
    http://jonathanlewis.wordpress.com/2007/06/03/sorting/

    you may set it for a session too:

    alter session set "_newsort_enabled"=false;
  • Ghassan Salem at Aug 24, 2007 at 3:03 pm
    It's not the new sort that doing this, it's the 'hash group by'
    try disabling it using
    alter ... set "_gby_hash_aggregation_enabled"=false;
    On 8/24/07, goran bogdanovic wrote:

    http://jonathanlewis.wordpress.com/2007/06/03/sorting/

    you may set it for a session too:

    alter session set "_newsort_enabled"=false;
    --
    http://www.freelists.org/webpage/oracle-l
  • Peter Barnett at Aug 24, 2007 at 5:48 pm
    We already have that set for another problem and still
    have the unordered sort issue.

    This is really a coding issue but we dno't have time
    to fix it.

    Ghassan Salem wrote:
    It's not the new sort that doing this, it's the
    'hash group by'
    try disabling it using
    alter ... set "_gby_hash_aggregation_enabled"=false;


    On 8/24/07, goran bogdanovic
    wrote:
    http://jonathanlewis.wordpress.com/2007/06/03/sorting/
    you may set it for a session too:

    alter session set "_newsort_enabled"=false;
    Pete Barnett



    Yahoo! oneSearch: Finally, mobile search
    that gives answers, not web links.
    http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC

    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Aug 28, 2007 at 3:57 pm
    First, I'm a bit surprised that disabling hash aggregation didn't force sort
    aggregation which should produce an ordered set of groups as a side effect
    (not to be confused with valid SQL and specifically warned against being
    relied on by Oracle since at least November 1988.) I would check the plan
    generated and v$parameter to be certain the setting is being effectively set
    simultaneously with getting an unordered result set.

    Did I get it correct from the original post that these all involve group by
    statements?

    If verifying the plan and parameter setting yields no joy, then it seems
    likely that a sed, awk or perl script to pass across your script library and
    generate a ORDER BY that matches the GROUP BY is not too much of a stretch.
    Since you have over 1000 scripts it seems likely this is less error prone
    than attempting hand repairs.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Barnett
    Sent: Friday, August 24, 2007 1:49 PM
    To: Oracle-l
    Subject: Re: 10g ORDER BY Performance

    We already have that set for another problem and still
    have the unordered sort issue.

    This is really a coding issue but we dno't have time
    to fix it.

    Ghassan Salem wrote:
    It's not the new sort that doing this, it's the
    'hash group by'
    try disabling it using
    alter ... set "_gby_hash_aggregation_enabled"=false;


    On 8/24/07, goran bogdanovic
    wrote:
    http://jonathanlewis.wordpress.com/2007/06/03/sorting/
    you may set it for a session too:

    alter session set "_newsort_enabled"=false;
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark W. Farnham at Aug 28, 2007 at 4:07 pm
    Sorry. I see from the original post this is NOT specifically about GROUP BY
    without sorts. For the set of scripts that do have GROUP BY, either the
    setting or the previously suggested automated script edit should work.

    For scripts with neither a group by nor an order by it is difficult to
    create a solution without making presumptions about the desired order and
    the order the data was originally inserted into the tables as well as any
    intervening deletes followed by inserts as well as updates involving the
    columns involved in the former coincidental ordering of the result sets.

    Now possibly you could get back to sloppy acceptability and previous
    behavior by setting both those parameters previously referenced in the
    thread as well as manually setting your hash area parameter to an absurdly
    small size. But otherwise, see Tanel's note.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Barnett
    Sent: Friday, August 24, 2007 1:49 PM
    To: Oracle-l
    Subject: Re: 10g ORDER BY Performance

    We already have that set for another problem and still
    have the unordered sort issue.

    This is really a coding issue but we dno't have time
    to fix it.

    Ghassan Salem wrote:

    --
    http://www.freelists.org/webpage/oracle-l
  • Goran bogdanovic at Aug 28, 2007 at 11:55 am
    the parameter "_gby_hash_aggregation_enabled" is related to case where
    'group by' in query (without 'order by' clause) was returning ordered data
    in 9i using one method and in 10g is using another...the group by clause in
    10g is enhanced to allow a hash algorithm to be used instead of sorting
    algorithm to group the data.
    On 8/24/07, Ghassan Salem wrote:

    It's not the new sort that doing this, it's the 'hash group by'
    try disabling it using
    alter ... set "_gby_hash_aggregation_enabled"=false;

    On 8/24/07, goran bogdanovic wrote:

    http://jonathanlewis.wordpress.com/2007/06/03/sorting/

    you may set it for a session too:

    alter session set "_newsort_enabled"=false;
    --
    http://www.freelists.org/webpage/oracle-l
  • Amit poddar at Aug 24, 2007 at 4:08 pm
    do all these queries have group by ?

    Oracle has introduced a new grouping algorithm visible in explain plan
    as HASH GROUP BY instead of SORT GROUP BY
    which can change the ordering in aggregate queries.

    If thats the case then you can try disabling this new algorithm by

    setting parameter
    _gby_hash_aggregation_enabled to false

    amit

    Peter Barnett wrote:
    First, the disclaimer, someone made a very bad coding
    decision! Now the question is how to get out of the
    mess.

    We have about 2,000 scripts that do not have ORDER BY
    clauses anywhere in them. In 9i all of the queries
    returned the rows in the correct order. More a lucky
    accident than anything else.

    In 10g the optimizer has changed and the results are
    returned unordered. Since we are weeks away from go
    live of a major project there is no time to edit and
    test all of the scripts. Management is now wanting us
    to go live on 9i rather than 10g. Something the DBA
    team is really resisting but may be necessary.

    I came across a _newsort_enabled parameter which did
    not change the result set in 10g. Is anyone aware of
    a parameter that can be set in 10g that will give the
    9i behavior other than setting the
    optimizer_features_enabled to 9.2? If we do that we
    might as well be on 9i.


    Pete Barnett



    ____________________________________________________________________________________
    Choose the right car based on your needs. Check out Yahoo! Autos new Car Finder tool.
    http://autos.yahoo.com/carfinder/
    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Aug 24, 2007 at 4:25 pm
    Hi Pete,

    wow! haven't seen such mess before!

    If the scripts have been tested on 9i then go live with 9i (although you
    will definitely have some surprises, when someone enables parallelism,
    oracle starts favouring range scan on another index etc etc etc etc).

    I would freeze any changes to db schema, optimizer & system stats close to
    absolute zero.

    otherwise if 10g is inevitable, set the optimizer_features_enabled to 9i and
    freeze all stats again.

    anyway, if you're the one who has to support this application I think it's
    wise to start looking for a new job;)

    good luck!

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Barnett
    Sent: Friday, August 24, 2007 21:48
    To: Oracle-l
    Subject: 10g ORDER BY Performance

    First, the disclaimer, someone made a very bad coding
    decision! Now the question is how to get out of the mess.

    We have about 2,000 scripts that do not have ORDER BY clauses
    anywhere in them. In 9i all of the queries returned the rows
    in the correct order. More a lucky accident than anything else.

    In 10g the optimizer has changed and the results are returned
    unordered. Since we are weeks away from go live of a major
    project there is no time to edit and test all of the scripts.
    Management is now wanting us to go live on 9i rather than
    10g. Something the DBA team is really resisting but may be necessary.

    I came across a _newsort_enabled parameter which did not
    change the result set in 10g. Is anyone aware of a parameter
    that can be set in 10g that will give the 9i behavior other
    than setting the optimizer_features_enabled to 9.2? If we do
    that we might as well be on 9i.


    Pete Barnett



    ______________________________________________________________
    ______________________
    Choose the right car based on your needs. Check out Yahoo!
    Autos new Car Finder tool.
    http://autos.yahoo.com/carfinder/
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Allen, Brandon at Aug 24, 2007 at 5:07 pm
    I've seen this a several times before - once on a large scale just a few
    weeks ago. I think the problem stems from a common misconception among
    developers (and probably some DBAs) that they can improve performance by
    omitting the ORDER BY clause and avoiding an unnecssary sort operation
    as long as they write their query to take advantage of an index that
    will return rows in the desired order. What they don't understand is
    that Oracle (and all other modern RDBMSs, I hope) is smart enough to
    recognize that rows are being returned from an index and it will
    automatically skip the sort operation on its own if it's not necessary.
    They also fail to recognize that the CBO could change the execution plan
    in the future for any number of reasons and then their data will not be
    sorted since they left off the ORDER BY. I'm not sure where these
    misunderstandings stem from, but I suspect they come from some old
    inaccurate book or white paper, another RDBMS, or maybe even earlier
    versions of Oracle on the RBO when the plan was much less volatile.

    Regards,
    Brandon

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Tanel Poder

    wow! haven't seen such mess before!
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Barnett

    First, the disclaimer, someone made a very bad coding decision! Now
    the question is how to get out of the mess.

    We have about 2,000 scripts that do not have ORDER BY clauses anywhere
    in them. In 9i all of the queries returned the rows in the correct
    order. More a lucky accident than anything else.
    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.
  • Kerber, Andrew W. at Aug 24, 2007 at 7:03 pm
    I think it is a futile effort. What happens when you go live, and you
    have to update a record or two? There is now way the data will continue
    come back in the desired order unless you add order bys.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Barnett
    Sent: Friday, August 24, 2007 8:48 AM
    To: Oracle-l
    Subject: 10g ORDER BY Performance

    First, the disclaimer, someone made a very bad coding
    decision! Now the question is how to get out of the
    mess.

    We have about 2,000 scripts that do not have ORDER BY
    clauses anywhere in them. In 9i all of the queries
    returned the rows in the correct order. More a lucky
    accident than anything else.

    In 10g the optimizer has changed and the results are
    returned unordered. Since we are weeks away from go
    live of a major project there is no time to edit and
    test all of the scripts. Management is now wanting us
    to go live on 9i rather than 10g. Something the DBA
    team is really resisting but may be necessary.

    I came across a _newsort_enabled parameter which did
    not change the result set in 10g. Is anyone aware of
    a parameter that can be set in 10g that will give the
    9i behavior other than setting the
    optimizer_features_enabled to 9.2? If we do that we
    might as well be on 9i.

    Pete Barnett



    Choose the right car based on your needs. Check out Yahoo! Autos new
    Car Finder tool.
    http://autos.yahoo.com/carfinder/
    --
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 24, '07 at 1:48p
activeAug 28, '07 at 4:07p
posts11
users8
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase