Bug 6471770 – OERI [32695] [hash aggregation can’t be done] from Hash GROUP BY

Oct 9

Posted 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.

Regards,
AlesK

Advertisement

Posted on 09.10.2008, in Oracle and tagged . Bookmark the permalink. 1 Comment.

  1. alesk | 13.10.2008 at 00:00

    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.