Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 14568 invoked from network); 29 Nov 2007 16:57:12 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 29 Nov 2007 16:57:11 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6A2C87C9D58; Thu, 29 Nov 2007 17:57:11 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07972-10; Thu, 29 Nov 2007 17:57:11 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D8BE97CA7F9; Thu, 29 Nov 2007 17:57:10 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 29 Nov 2007 17:10:15 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 127717CABE0 for ; Thu, 29 Nov 2007 17:10:15 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31993-04 for ; Thu, 29 Nov 2007 17:10:14 -0500 (EST) Received: from NT15.oneneck.corp (dot092host.oneneck.net [63.226.42.92]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E0D1E7CAA49 for ; Thu, 29 Nov 2007 17:10:09 -0500 (EST) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C832D4.9794A8C4" Subject: _gby_hash_aggregation_enabled=false Date: Thu, 29 Nov 2007 15:10:03 -0700 Message-ID: <04DDF147ED3A0D42B48A48A18D574C450987F2E1@NT15.oneneck.corp> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: _gby_hash_aggregation_enabled=false Priority: normal From: "Allen, Brandon" To: X-archive-position: 3601 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Brandon.Allen@OneNeck.com Precedence: normal Reply-to: Brandon.Allen@OneNeck.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------_=_NextPart_001_01C832D4.9794A8C4 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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"=3Dfalse; 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=3Dtrue; 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=3D2 cnt=3D952 pid=3D1 pos=3D1 obj=3D0 op=3D'HASH GROUP BY = (cr=3D2395 pr=3D0 pw=3D0 time=3D49601 us)' Next, I tried setting optimizer_features_enable=3D"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. =20 Thanks, Brandon =20 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. ------_=_NextPart_001_01C832D4.9794A8C4 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

SQL> = alter system=20 set "_gby_hash_aggregation_enabled"=3Dfalse;

System=20 altered.


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

SQL> alter session set = sql_trace=3Dtrue;

Session=20 altered.

SQL> @dts1
. . .
2310 rows=20 selected.


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

STAT #1=20 id=3D2 cnt=3D952 pid=3D1 pos=3D1 obj=3D0 op=3D'HASH GROUP BY (cr=3D2395 = pr=3D0 pw=3D0 time=3D49601=20 us)'

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

I've already got an SR = open, so we'll=20 see what they say but I'm curious if others have encountered the = same=20 thing.  I don't want to set optimizer_features_enable to = 10.1.0 at the=20 system level so if the hidden parameter doesn't really work, then = I'll have=20 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.

 ------_=_NextPart_001_01C832D4.9794A8C4-- -- http://www.freelists.org/webpage/oracle-l