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

<a href="http://blog.51cto.com/maclean/1277548#">?</a>

<code>*** </code><code>ACTION</code> <code>NAME</code><code>:(SQL 視窗 - 建立) 2010-09-03 14:27:54.594</code>

<code>*** MODULE </code><code>NAME</code><code>:(PL/SQL Developer) 2010-09-03 14:27:54.594</code>

<code>*** SERVICE </code><code>NAME</code><code>:(HQYDB1) 2010-09-03 14:27:54.594</code>

<code>*** SESSION ID:(3205.17923) 2010-09-03 14:27:54.594</code>

<code>*** 2010-09-03 14:27:54.594</code>

<code>ksedmp: internal </code><code>or</code> <code>fatal error</code>

<code>ORA-00600: internal error code, arguments: [32695], [hash aggregation can</code><code>'t be done], [], [], [], [], [], []</code>

<code>Current SQL statement for this session:</code>

<code>create table zou_201008_cell_id as</code>

<code>select /* g_all_cdr02,60 */</code>

<code>calling_num mobile_number,</code>

<code>lac,</code>

<code>lpad(cell_id,5,'</code><code>0</code><code>') cell_id,</code>

<code>count(*) c,</code>

<code>sum(call_duration) call_duration,</code>

<code>sum(decode(record_type,'</code><code>00</code><code>',1,0)*call_duration) moc_call_duration,</code>

<code>sum(decode(record_type,'</code><code>01</code><code>',1,0)*call_duration) mtc_call_duarion</code>

<code>from  g_all_cdr02</code>

<code>where substr(calling_num,1,7) in (select mobile_prefix from zou_mobile_prefix)</code>

<code>group by</code>

<code>calling_num ,</code>

<code>lpad(cell_id,5,'</code><code>0')</code>

<code>----- Call Stack Trace -----</code>

<code>calling              call     entry                argument </code><code>values</code> <code>in</code> <code>hex</code>

<code>location             type     point                (? means dubious value)</code>

<code>-------------------- -------- -------------------- ----------------------------</code>

<code>ksedst+001c          bl       ksedst1              100000000 ? 11055A9A0 ?</code>

<code>ksedmp+0290          bl       ksedst               104A54870 ?</code>

<code>ksfdmp+0018          bl       03F30204</code>

<code>kgeriv+0108          bl       _ptrgl</code>

<code>kgesiv+0080          bl       kgeriv               1050BE654 ? 1050BE604 ?</code>

<code>                                                   </code><code>0000027E5 ? 080000000 ?</code>

<code>                                                   </code><code>07FFFFFFF ?</code>

<code>ksesic1+0060         bl       kgesiv               43300000FFFF5310 ?</code>

<code>                                                   </code><code>4530000000000000 ?</code>

<code>                                                   </code><code>000000071 ? 000000001 ?</code>

<code>                                                   </code><code>000000000 ?</code>

<code>qeshPartitionBuildH  bl       01F9CA24</code>

<code>D+04bc</code>

<code>qeshGBYOpenScan2+02  bl       qeshPartitionBuildH  0000027E5 ? 1105C06C0 ?</code>

<code>34                            D</code>

<code>qeshGBYOpenScan+001  bl       qeshGBYOpenScan2     FFFFFFFFFFF5740 ? 11055A938 ?</code>

<code>8                                                  000000000 ? 000000010 ?</code>

<code>qerghFetch+05e8      bl       qeshGBYOpenScan      000001000 ?</code>

<code>rwsfcd+0054          bl       _ptrgl</code>

<code>qerltFetch+036c      bl       03F2EB1C</code>

<code>ctcdrv+4160          bl       01F9C898</code>

<code>opiexe+2884          bl       ctcdrv               100000001 ? 100000001 ?</code>

<code>                                                   </code><code>110467F30 ?</code>

<code>opiosq0+19f0         bl       opiexe               FFFFFFFFFFF8B50 ?</code>

<code>                                                   </code><code>2824422142420820 ?</code>

<code>                                                   </code><code>FFFFFFFFFFF8C10 ?</code>

<code>kpooprx+0168         bl       opiosq0              300000000 ? 000000000 ?</code>

<code>                                                   </code><code>000000000 ? A4000000000000 ?</code>

<code>kpoal8+0400          bl       kpooprx              FFFFFFFFFFFB464 ?</code>

<code>                                                   </code><code>FFFFFFFFFFFB068 ?</code>

<code>                                                   </code><code>1BF000001BF ? 100000001 ?</code>

<code>                                                   </code><code>000000000 ? A40000000000A4 ?</code>

<code>                                                   </code><code>000000000 ? 1103878F8 ?</code>

<code>opiodr+0ae0          bl       _ptrgl</code>

<code>ttcpip+1020          bl       _ptrgl</code>

<code>opitsk+1124          bl       01F9F2A0</code>

<code>opiino+0990          bl       opitsk               000000000 ? 000000000 ?</code>

<code>opidrv+0484          bl       01F9E0E8</code>

<code>sou2o+0090           bl       opidrv               3C02DC1BBC ? 44065F000 ?</code>

<code>                                                   </code><code>FFFFFFFFFFFF3A0 ?</code>

<code>opimai_real+01bc     bl       01F9B9F4</code>

<code>main+0098            bl       opimai_real          000000000 ? 000000000 ?</code>

<code>__start+0098         bl       main                 000000000 ? 000000000 ?</code>

<code>--------------------- Binary Stack Dump ---------------------</code>

這次是因為應用人員不了解alter session的作用域，在PL/SQL Developer工具中的不同視窗(也就是不在同一會話中)中執行了"alter session set "_gby_hash_aggregation_enabled" = false;"和涉及group by操作的SQL，并導緻了unpublished bug:6471770被觸發。 我們比較容易地workaround繞過這個Bug:

<code>/* 在會話級别設定優化參數_gby_hash_aggregation_enabled */</code>

<code>alter</code> <code>session </code><code>set</code> <code>"_gby_hash_aggregation_enabled"</code> <code>= </code><code>false</code><code>;</code>

<code>/* 或者在語句中加入NO_USE_HASH_AGGREGATION的 hint */</code>

<code>select</code>  <code>/*+ NO_USE_HASH_AGGREGATION */ ....</code>

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

本文轉自maclean_007 51CTO部落格，原文連結:http://blog.51cto.com/maclean/1277548