ORA-00600:[32695], [hash aggregation can't be done]错误一例

Comments

1. admin says

   2010/09/03 at 16:18

   ORA-600 [32695] [hash aggregation can’t be done]

   Applies to:

   Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.1.0.6 – Release: 10.2 to 11.1
   Information in this document applies to any platform.

   Symptoms

   When running a statement that involves a GROUP BY operation, the following error is raised:

   ORA-00600: internal error code, arguments: [32695], [hash aggregation can’t be done], [], [],

   The call stack looks similar to:

   … qeshPartitionBuildHD qeshGBYOpenScan2 qeshGBYOpenScan qerghFetch qervwFetch …

   and the query plan for the SQL statement shows a HASH GROUP BY, eg. :

   ——————————————
   | Id | Operation                         |
   ——————————————
   | 0 | INSERT STATEMENT                   |
   | 1 | PX COORDINATOR                     |
   | 2 | PX SEND QC (RANDOM)                |
   | 3 | HASH GROUP BY                      |
   …

   A second case where this would occur could be with a failing query that has no GROUP BY, but has a Select Distinct. The Plan table indicates a HASH UNIQUE instead of HASH GROUP BY.

   Cause

   This is likely to be a case of unpublished bug:6471770 – see eg. note:6471770.8
   – fixed in 10.2.0.5, 11.1.0.7, and 11.2

   A similar problem is reported in:
   bug:5893340  ORA-600 [32695], [HASH AGGREGATION CAN’T BE DONE]
   – fixed in 10.2.0.4, 11.1.0.6

   Solution

   Solutions are as follows:

   1)
   Disable HASH GROUP BY operations by setting the parameter _gby_hash_aggregation_enabled to FALSE, ie.:

   SQL> alter session set "_gby_hash_aggregation_enabled" = false;

    or

   SQL> alter system set "_gby_hash_aggregation_enabled" = false scope=spfile;

   A hard parse to the statement needs to be performed, preferably to flush the Shared Pool after setting this workaround and then re-run the statement.

   2)
   Disable HASH GROUP BY operations by using the hint NO_USE_HASH_AGGREGATION:

   SQL> select /*+ NO_USE_HASH_AGGREGATION */ …

   3)
   Apply patch:6471770 if available for the relevant platform/version

   For Windows, the patch is included in:
   10.2.0.3 patch 23 and later – see note:342443.1
   10.2.0.4 patch 5 and later – see note:342443.1

   Please note that the patch is crucial to resolve the Second Case of the Symptoms section above, the workarounds will not resolve the issue.

   References

   NOTE:6471770.8 – Bug 6471770 – ora-32690/OERI [32695] [hash aggregation can’t be done] from Hash GROUP BY

   回复
2. admin says

   2010/09/03 at 16:20

   ORA-04030 in QERGH hash-agg,kllcqas:kllsltba

   Applies to:

   Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.1.0.6
   This problem can occur on any platform.

   Symptoms

   Running a resource consuming process (e.g. a huge CTAS) fails with ORA-04030: out of process memory when trying to allocate 115212 bytes (QERGH hash-agg,kllcqas:kllsltba) ORA-12801: error signaled in parallel query server P005 …

   OS, kernel and Oracle settings are all OK, and do not explain the ORA-4030

   Plenty of free memory available at time of error, and process ulimit limitations are not exceeded.

   Not able to determine that the process runing the CTAS is consuming huge allocations for PGA memory.

   Cause

   This problem is due to to (Unpublished) Bug 6471770.

   The scenario can be due to large data sets that cause allocated hash group-bys to allocate memory beyond internal limis for the auto-memory manager of PGA.

   Solution

   Resolve the ORA-4030 by setting parameter _gby_hash_aggregation_enabled = false

   SQL> alter system set “_gby_hash_aggregation_enabled”=false scope=both;

   or use NO_USE_HASH_AGGREGATION hint within the sql statement.

   Fixed in the 10.2.0.5 patchset and 11.1.0.7 patchset.

    

   回复
3. admin says

   2010/09/03 at 16:22

   Query crash with ORA-32690 — Bug 6471770.

   Applies to:

   Oracle Server – Enterprise Edition – Version: 10.2.0.3
   This problem can occur on any platform.

   Symptoms

   On 10.2.0.3 in Production:
   When attempting to  run the query below

   SQL> INSERT /*+ append */
   2 INTO med_clm_sas_&extractId
   3 SELECT /*+ use_hash(a,c) */
   4 distinct
   5 a.claim_pk AS med_clm_id,
   6 ims_pat_id,
   7 claim_dt AS min_from_dt,
   8 bounded_svc_to_dt AS max_to_dt,
   9 a.perf_ims_prov_id AS perf_prov_id,
   10 source_pay_grp1 AS pay1_grp_cd,
   11 source_pay_grp2 AS pay2_grp_cd,
   12 source_pay_dtl1 AS pay1_dtl_cd,
   13 source_pay_dtl2 AS pay2_dtl_cd,
   14 svc_ims_prov_id AS svc_prov_id,
   15 svc_prov_zip,
   16 payer1_claim_zip AS pay1_clm_zip,
   17 payer2_claim_zip AS pay2_clm_zip,
   18 lab_chg,
   19 charged_amt AS tot_clm_chrg,
   20 service AS service_cd,
   21 pos, tos, units,
   22 line_chg AS charged_amt,
   23 diagnosis AS icd9_cd,
   24 rank AS claim_med_diag_rnk_nbr,
   25 idb,
   26 ims_specialty AS perf_prov_spec
   27 FROM &&extractId._mx_claim_diag a
   28 INNER JOIN provider.provider c on (a.perf_ims_prov_id=c.ims_prov_id)
   29 WHERE provider_type = ‘I’;
   old 2: INTO med_clm_sas_&extractId
   new 2: INTO med_clm_sas_az_pain0909
   old 27: FROM &&extractId._mx_claim_diag a
   new 27: FROM az_pain0909_mx_claim_diag a

   the following error occurs:

   ERROR at line 2:
   ORA-32690: Hash Table Infrastructure ran out of memory

   Cause

   This seems to be Bug 6471770.

   – Known bug on database version 10.2.0.3.
   – The workaround is resolving the issue.

   Solution

   The bug is fixed in 11.2 release.

   So our recommended solution always to be on the latest patchset or even 11.1.0.6 where there is one off backport for several platforms .

   – The bug also has a one off on top of 10.2.0.4, if you are not able to be on top of 10.2.0.4 for the time being, so our option now is to resolve the issue on top of 10.2.0.3

   – No one off on top of 10.2.0.3 for Solaris platform however the good news that there is a merge Patch 6907160 MERGE LABEL REQUEST ON TOP OF 10.2.0.3 FOR BUGS 6471770 6471515, available to Solaris platform.

   – There are also the available workarounds

   Disable Hash group-by by setting
   “_gby_hash_aggregation_enabled” to FALSE,
   or by using a NO_USE_HASH_AGGREGATION hint

    

   回复