Bug 6471770 – OERI  [hash aggregation can’t be done] from Hash GROUP BY
Today we hit another bug related to Oracle hash group by aggregation. I said another because we already had serious problem with hash group by in 10.2.0.1, as I noted here.
At the moment we’re using 10.2.0.3 Patch 12 and bug is fixed in 10.2.0.5 (and 11.1.07). User encountered bug while processing large dataset with SAS as a front end (extensively utilizing Oracle Analytical functions), on top of that it was during the period of time when server was already under heavy workload.
We’ll try to:
- repeat the error ORA-600 on the same data set with the same tool (SAS) but during off-peak hours. I have a feeling that heavy workload is somehow part of the problem.
- use hint NO_USE_HASH_AGGREGATION within all potential queries. (Con: we’ll see how well will this query work without group by hash feature.)
- disable gby at session level by setting “_gby_hash_aggregation_enabled” to FALSE
I’m certainly not fond to use the same “workaround” as last time by disabling group by hash at instance level, because this time it’s not a “hidden” error and it seems it’s rather sporadic (the same query run well on the same dataset and RDBMS version in the past).
Reference: Metalink Note 6471770.8.
the workaround we used in SAS
For the time being the workaround we deployed is a hint /*+ no_use_hash_aggregation */ in problematic query.
Which brings us to the main topic of this comment: SAS default SQL statement handling. By default SAS silently removes any comment section (/* */), including hints from SQL text before it sends query to the Oracle server. Until now we were not aware of this.ortunately, our main SAS developer found a connection string parameter (PRESERVE_COMMENTS) that prevents hint removal. The syntax is documented here.