Brandon I don't remember the name of the hint off hands, but AFAIK there is one that lets you set the optimizer features enabled per statement. Stefan On Nov 29, 2007 11:10 PM, Allen, Brandon <Brandon.Allen@xxxxxxxxxxx> wrote: > 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.3instead. > > > > 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. > -- ========================= Stefan P Knecht Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxx http://www.trivadis.com OCP 9i/10g SCSA SCNA =========================