Question: I am getting a slow GROUP BY
performance in Oracle 10g and I believe that it is related to a hash
aggregation step in my SQL execution plan.
What is this new hash aggregation plan, and how do I fix this
slow performance?
Answer:
The hash aggregation plan is an 10g enhancement that improves GROUP
BY performance when enough PGA exists to allow hash operations to
replace traditional sorting.
Your slow GROUP BY performance is one of the
10g upgrade issues, and Oracle has released Bug 4604970 to
address issues with the brand new hash aggregation plan in 10g.
Oracle suggests setting the hidden parameter
_gby_hash_aggregation_enabled=false to
disable hash aggregation. This bug should be fixed in Oracle 11g and
beyond, and hash aggregation should improve performance of GROUP BY
operations where enough RAM exists in the PGA to allow a hash
operation.
