If you read the train of comments you now have a sample of code you can experiment with to learn more about the pros and cons of hash aggregation.

I’m not surprised that a DBA that’s set a (hidden) parameter is scared to remove it on a production system. But you could try persuading them to test a few queries with the ‘use_hash_aggregation’ hint.

Are you sure that people who “add an order by” are doing so to disable the feature – if so then there’s no guarantee that it will work. They’d be better off (temporarily) using the ‘no_use_hash_aggregation’ hint. Most people who add an “order by” do so because they forgot that a “group by” doesn’t imply an “order by”.

If you think there is a patch for hash aggregation, I suggest you get one of your doubting DBAs to raise an SR for it.

Don’t get me wrong. There are several blogs explaining how “hash group by” behave differently than “sort group by”, but I have been trying to search & read a lot to get the positive benchmark proof for hash group by (during the past 2 years), that single line in ORACLE 10gR2 white paper is the only official statement I can use so far. If I just post my question by referring a statement from some personal blog, you might not pay attention to the topic, right?

There are blogs talking about the problems for “hash group by”, even with a general statement saying that “hash group by” is a good feature, but I have not yet found any dedicated article to explain if/why “group hash by” is faster & useful.

I kept wondering if I miss-used “group hash by” or there’s a bug fix for it that I was not ware of. I have encountered 3 DW RAC 20TB+ (2 x 10g and 1 x 11g), “group hash by” are disabled in all 4 implementations by DBA, and I can’t convince them to enable it.

Some people add “order by” to their query to avoid “hash group by”, some people disable the feature at session level, some people continue running the SQL without noticing any difference… I hope to leverage the popularity of your blog to ask: is there anyone out there who has strong proof that “hash group by” is really good? what patch is required?

Yes, you are correct, my blog entry is more about scientific approach than disabling “Group by hash aggregation”. It seems to me that, in some corner cases, HASH GROUP BY feature has a bug (10.2.0.3 and 10.2.0.4), due to which memory is allocated and de-allocated excessively leading to longer run time. Of course, this is not going to show up in sqltrace or any other conventional tools, only a pstack loop pointed to the issue. My intention was to introduce those tools to approach the problem scientifically. I remember seeing a similar bug in MOS, but that bug was specific to queries involving FILTER step with GROUP BY operation in a subquery branch (and I can’t find the bug anymore). My client didn’t prefer to follow up the issue with support all the way, we resolved the issue with a profile and closed the problem.

In my blog entry, I also said “Note that, I am not saying hash group by feature is bad. Rather, we seem to have encountered an unfortunate bug which caused performance issues at this client. I think, Hash Grouping is a good feature as the efficiency of grouping operations can be improved if you have ample amount of memory. That’s the reason why we disabled this feature at the statement level, NOT at the instance level.” in line with you.

Why don’t you try reading things carefully and thinking about them, rather than looking at one line that seems to confirm you prejudices ?

The white paper gives you one example of a query where the 10.2 hash aggregations operates much more efficiently than the 10.1 sort aggregation. You are insisting that this means hash aggregation must always be faster than sort aggregation in all cases and versions. Don’t forget that the paper also points out that the 10.2 simple sort algorithm is faster than the 10.1 simple sorting algorithm – might this also be relevant to sort aggregation in later releases ?

Your results in show figures for sort and hash aggregration with degree 4, 8, and 16. At degree 8 the performance is significantly better than at degree 4 OR 16 – and the hash aggregation is 25% faster than the sort aggregation at that degree. (It’s a few percent worse at degree 4 and a few percent better at degree 16). Shouldn’t the drop from 8 minutes to 5:30 at degree eight give you reason to wonder where the time is going ? I note also that the plan suggests you are aggregating 288 Million rows down to 71M – as I pointed out in my comment on the other thread where you raised your point ( https://jonathanlewis.wordpress.com/2010/11/19/quiz-night-9/#comment-37904 ), the relative performance of sort and hash aggregation is dependent on the data pattern and level of aggregation – and the relatively small reduction of volume in your example offers the hash aggregation little room for improvement over the sort aggregate.

You might point out to your DBAs that the link on orainternals (which is about 10g, not 11g) has the conclusion that the feature is too good to miss in general, so they only disable it for specific statements.

]]>By: goldenorbithttps://jonathanlewis.wordpress.com/2008/12/21/group-by/#comment-38013
Thu, 02 Dec 2010 02:09:43 +0000http://jonathanlewis.wordpress.com/?p=830#comment-38013Thanks for the reply, Greg. The reason that I posted the question here to leverage Jonathan’s popularity to get more feedback.

Since HASH GROUP BY was introduced, it has at least 2 bugs reported: wrong result 4604970 and excessive temp space usage 7716219 (don’t remember the links).

As you can see from the old twp-dss-performance-10gr2-0705-128049.pdf, ORACLE did claim that hash aggregation should bring major performance improvement in 10gR2. But even the above bugs should have been patched by now, my DBA still insist disabling hash group by, and I still don’t have a benchmark proof to convince them that hash group by is way faster than sort group by, so this feature can be enabled.