RE: _gby_hash_aggregation_enabled=false

Well I was going to say we had set it and have not experienced any problems.  

However, we're already on 10.2.0.3. We were actually experiencing a performance issue with it and when I saw there was a propensity for wrong results, I went ahead and set it.    

Chris Taylor

Sr. Oracle DBA

Ingram Barge Company

Nashville, TN 37205

Office: 615-517-3355

Cell: 615-354-4799

Email: chris.taylor_at_ingrambarge.com

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Allen, Brandon Sent: Thursday, November 29, 2007 4:10 PM To: oracle-l_at_freelists.org
Subject: _gby_hash_aggregation_enabled=false  

Has anyone else tried setting this hidden parameter and found it didn't disable the HASH GROUP BY feature? It's suggested in Metalink 387958.1 as a workaround to the "wrong results" bug (4604970) with the new HASH GROUP BY feature, so I just set it as follows:

SQL> alter system set "_gby_hash_aggregation_enabled"=false;

System altered.

Then I ran a 10046 trace on a query known to have the HASH GROUP BY operation in its explain plan:

SQL> alter session set sql_trace=true;

Session altered.

SQL> @dts1
. . .
2310 rows selected.

But, in the trace file, it still shows that HASH GROUP BY is being used:

STAT #1 id=2 cnt=952 pid=1 pos=1 obj=0 op='HASH GROUP BY (cr=2395 pr=0 pw=0 time=49601 us)'

Next, I tried setting optimizer_features_enable="10.1.0" as recommended in the same Metalink article and that worked as expected - the trace file showed SORT GROUP BY instead of the HASH GROUP BY.

I've already got an SR open, so we'll see what they say but I'm curious if others have encountered the same thing. I don't want to set optimizer_features_enable to 10.1.0 at the system level so if the hidden parameter doesn't really work, then I'll have to install patchset 10.2.0.3 instead.  

Thanks,

Brandon  

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.

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