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

还是那个hash group by算法的问题,日志文件中出现以下记录:

*** ACTION NAME:(SQL 窗口 - 新建) 2010-09-03 14:27:54.594
*** MODULE NAME:(PL/SQL Developer) 2010-09-03 14:27:54.594
*** SERVICE NAME:(HQYDB1) 2010-09-03 14:27:54.594
*** SESSION ID:(3205.17923) 2010-09-03 14:27:54.594
*** 2010-09-03 14:27:54.594
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [32695], [hash aggregation can't be done], [], [], [], [], [], []
Current SQL statement for this session:
create table zou_201008_cell_id as
select /* g_all_cdr02,60 */
calling_num mobile_number,
lac,
lpad(cell_id,5,'0') cell_id,
count(*) c,
sum(call_duration) call_duration,
sum(decode(record_type,'00',1,0)*call_duration) moc_call_duration,
sum(decode(record_type,'01',1,0)*call_duration) mtc_call_duarion
from  g_all_cdr02
where substr(calling_num,1,7) in (select mobile_prefix from zou_mobile_prefix)
group by
calling_num ,
lac,
lpad(cell_id,5,'0')
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              100000000 ? 11055A9A0 ?
ksedmp+0290          bl       ksedst               104A54870 ?
ksfdmp+0018          bl       03F30204
kgeriv+0108          bl       _ptrgl
kgesiv+0080          bl       kgeriv               1050BE654 ? 1050BE604 ?
                                                   0000027E5 ? 080000000 ?
                                                   07FFFFFFF ?
ksesic1+0060         bl       kgesiv               43300000FFFF5310 ?
                                                   4530000000000000 ?
                                                   000000071 ? 000000001 ?
                                                   000000000 ?
qeshPartitionBuildH  bl       01F9CA24
D+04bc
qeshGBYOpenScan2+02  bl       qeshPartitionBuildH  0000027E5 ? 1105C06C0 ?
34                            D
qeshGBYOpenScan+001  bl       qeshGBYOpenScan2     FFFFFFFFFFF5740 ? 11055A938 ?
8                                                  000000000 ? 000000010 ?
qerghFetch+05e8      bl       qeshGBYOpenScan      000001000 ?
rwsfcd+0054          bl       _ptrgl
qerltFetch+036c      bl       03F2EB1C
ctcdrv+4160          bl       01F9C898
opiexe+2884          bl       ctcdrv               100000001 ? 100000001 ?
                                                   110467F30 ?
opiosq0+19f0         bl       opiexe               FFFFFFFFFFF8B50 ?
                                                   2824422142420820 ?
                                                   FFFFFFFFFFF8C10 ?
kpooprx+0168         bl       opiosq0              300000000 ? 000000000 ?
                                                   000000000 ? A4000000000000 ?
kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB464 ?
                                                   FFFFFFFFFFFB068 ?
                                                   1BF000001BF ? 100000001 ?
                                                   000000000 ? A40000000000A4 ?
                                                   000000000 ? 1103878F8 ?
opiodr+0ae0          bl       _ptrgl
ttcpip+1020          bl       _ptrgl
opitsk+1124          bl       01F9F2A0
opiino+0990          bl       opitsk               000000000 ? 000000000 ?
opiodr+0ae0          bl       _ptrgl
opidrv+0484          bl       01F9E0E8
sou2o+0090           bl       opidrv               3C02DC1BBC ? 44065F000 ?
                                                   FFFFFFFFFFFF3A0 ?
opimai_real+01bc     bl       01F9B9F4
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?

--------------------- Binary Stack Dump ---------------------

这次是因为应用人员不了解alter session的作用域,在PL/SQL Developer工具中的不同窗口(也就是不在同一会话中)中执行了”alter session set “_gby_hash_aggregation_enabled” = false;”和涉及group by操作的SQL,并导致了unpublished bug:6471770被触发。
我们比较容易地workaround绕过这个Bug:


/* 在会话级别设置优化参数_gby_hash_aggregation_enabled */

alter session set "_gby_hash_aggregation_enabled" = false;

/* 或者在语句中加入NO_USE_HASH_AGGREGATION的 hint */

select  /*+ NO_USE_HASH_AGGREGATION */ ....

以上提及的unpublished bug:6471770据称在10.2.0.5,11.1.0.7,11.2.0.1版本中被修正了。

Comments

  1. admin says

    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

    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

    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

     

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号