Migração de 9i para 10g

[vieriParticipante]

Deêm uma lida nessa discussão antes de decidir qual decisão tomar.

Como tenho desenvolvedores a disposição eu mandaria alterar,
caso fosse um programa fechado e sem contrato de suporte e
sem problemas críticos de performance eu arriscaria alterar esse parâmetro a acompanharia o alert e a performance da base para ver se houve mudanças!

Salvo os trechos mais interessantes:

Re: Is it recomended to set _gby_hash_aggregation_enabled =FALSE
by Tim… » Wed Nov 14, 2007 4:12 pm

Hi.

As a general rule, you should not touch the parameters beginning with a “_” unless specifically told to do so by Oracle support.

If this gets you past your current issue, fine, but you really need to refactor your application so that you can run without this setting. You should consider this a temporary fix. Things like your record ordering issue are down to bad programming. The documentation clearly states that row order cannot be guaranteed unless you have an ORDER BY clause. The fact that some operations may currently perform an implicit ORDER BY does not mean you don’t have to add one yourself. The differences between 9i and 10g with the GROUP BY prove this.

Cheers

Tim…

Hi,
How are you Wasif?
Distinct and Group By clauses are not supposed to sort the result set. It was only the execution plan which oracvle 8i/9i optimizer has been using for group by and distinct which returned rows in sorte order and if some developer trusted group by OR distinct for sorting and didn’t use order by, its not Oracle fault (Oracle corporation says this).
I think the parameter you are talking about will give you behaviour of distinct same as it was in 9i and it will start returning the result in sorted order and using which you will be safing yourself from making many changes in the Application and it could also work for you but, you will be using efficient execution plan which oracle 10g uses.
As far as i know, the default value for this mentioned parameter is TRUE and i guess this is also the recommended value)

Regards