Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 27638 invoked from network); 30 Nov 2007 13:01:59 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by 69.64.49.119 with SMTP; 30 Nov 2007 13:01:59 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E5D4F7CB775; Fri, 30 Nov 2007 14:01:59 -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 12591-02; Fri, 30 Nov 2007 14:01:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 573777CB729; Fri, 30 Nov 2007 14:01:59 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 30 Nov 2007 13:15:03 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CEB8D7CB9C5 for ; Fri, 30 Nov 2007 13:15:03 -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 06760-08 for ; Fri, 30 Nov 2007 13:15:03 -0500 (EST) Received: from outbound2-sin-R.bigfish.com (outbound-sin.frontbridge.com [207.46.51.80]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1A2827CB9AC for ; Fri, 30 Nov 2007 13:15:01 -0500 (EST) Received: from outbound2-sin.bigfish.com (localhost.localdomain [127.0.0.1]) by outbound2-sin-R.bigfish.com (Postfix) with ESMTP id 07FB212C9EC6; Fri, 30 Nov 2007 18:08:20 +0000 (UTC) Received: from mail197-sin-R.bigfish.com (unknown [10.3.252.3]) by outbound2-sin.bigfish.com (Postfix) with ESMTP id E4134B8005B; Fri, 30 Nov 2007 18:08:19 +0000 (UTC) Received: from mail197-sin (localhost.localdomain [127.0.0.1]) by mail197-sin-R.bigfish.com (Postfix) with ESMTP id 24B2E390253; Fri, 30 Nov 2007 18:07:47 +0000 (UTC) X-BigFish: VP X-MS-Exchange-Organization-Antispam-Report: OrigIP: 12.23.250.36;Service: EHS Received: by mail197-sin (MessageSwitch) id 119644606786676_1973; Fri, 30 Nov 2007 18:07:47 +0000 (UCT) Received: from spobmexc02.adprod.directory (smtp.ingrambarge.com [12.23.250.36]) by mail197-sin.bigfish.com (Postfix) with ESMTP id 7D0FD3B8072; Fri, 30 Nov 2007 18:07:46 +0000 (UTC) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: =?ISO-8859-1?Q?==3Fiso-8859-1=3FQ=3FRE:=5F=5Fgby=5Fhash=5Faggregation=5Fenabled=FAlse=3FDate:?= Fri, 30 Nov 2007 12:09:10 -0600 Message-ID: <17E4CDE8F84DC44A992E8C00767402E0860D53@spobmexc02.adprod.directory> In-Reply-To: <17E4CDE8F84DC44A992E8C00767402E0860D52@spobmexc02.adprod.directory> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: =?iso-8859-1?Q?_gby_hash_aggregation_enabledúlse?Thread-Index: Acgze7RIY8hmUot4Tc+EWJQdDItMQQAADrYg References: <20071130080514.65DFC7CB329@turing.freelists.org> <01e201c83377$6c66a450$0200a8c0@Primary> <17E4CDE8F84DC44A992E8C00767402E0860D52@spobmexc02.adprod.directory> From: "Taylor, Chris David" To: "Taylor, Chris David" , , X-archive-position: 3639 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: Chris.Taylor@ingrambarge.com Precedence: normal Reply-to: Chris.Taylor@ingrambarge.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 Date: Fri, 30 Nov 2007 14:01:59 -0500 (EST) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char FA hex) in message header 'Thread-Topic': Thread-Topic: ...by_hash_aggregation_enabled\372lse?Thread-Inde... Ok, now that's really weird. In one of my DBs it does in fact reverse, but in our main Psoft database it does not. Chris Taylor Sr. Oracle DBA Ingram Barge Company Nashville, TN 37205 Office: 615-517-3355 Cell: 615-354-4799 Email: chris.taylor@ingrambarge.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Taylor, Chris David Sent: Friday, November 30, 2007 12:05 PM To: jonathan@jlcomp.demon.co.uk; oracle-l@freelists.org Subject: RE: _gby_hash_aggregation_enabledúlse Jonathan, Something I pointed out to Brandon in a separate email, is that the behavior doesn't seem to reverse when you set that parameter back to true. Queries continue to use SORT (GROUP BY)... I would be curious if you see the same thing... Chris Taylor Sr. Oracle DBA Ingram Barge Company Nashville, TN 37205 Office: 615-517-3355 Cell: 615-354-4799 Email: chris.taylor@ingrambarge.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Jonathan Lewis Sent: Friday, November 30, 2007 11:36 AM To: oracle-l@freelists.org Subject: Re: _gby_hash_aggregation_enabled=false I've just tried the following on 10.2.0.1 select owner, count(*) from dba_objects group by owner; Autotrace produced a plan with a hash group by. Then I did alter session set "_gby_hash_aggregation_enabled" = false; and repeated the query - the plan switched to a sort group by (Your sample query from v$sql_plan defaulted to a sort group by anyway, which is why I had to find another query). A possible explanation for your observation - if I ran the test using SQL_TRACE rather than autotrace, the first cursor was not invalidated when I changed the setting for the parameter. Try repeating your test with a comment in the text to make the before and after versions of the query look different. You might also try the /*+ no_use_hash_aggregation */ hint. Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- > Subject: _gby_hash_aggregation_enabled=false > Date: Thu, 29 Nov 2007 15:10:03 -0700 > From: "Allen, Brandon" > > 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; > -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l