hash aggregation can't be done

在执行hash group by 查询时,经常报not free slot伴随ORA-32695错误, 这是由于11.1.0.7中存在bug6471770 ,是由于大表hash join group by时超出了自动内存管理器分配的内存的限制.

[@more@]

在执行hash group by 查询时,经常报not free slot伴随ORA-32695错误, 这是由于11.1.0.7中存在bug6471770

https://support.oracle.com/CSP/ui/flash.html#tab=KBHome(page=KBHome&id=()),(page=KBNavigator&id=(bmDocTitle=Bug%206471770%20-%20ora-32690/OERI%20%5B32695%5D%20%5Bhash%20aggregation%20can't%20be%20done%5D%20from%20Hash%20GROUP%20BY&viewingMode=1143&from=BOOKMARK&bmDocID=6471770.8&bmDocType=PATCH&bmDocDsrc=DOCUMENT))

Description

Group-By queries can fail with ora-32690 or
  ORA-600 [32695] [hash aggregation can't be done] 
when operating on a large volume of data if hash group by 
aggregation is used.

Also, it is possible for hash group-bys to over allocate memory beyond 
the limit impose by the auto-memory manager.

Workaround
 Disable Hash group-by by setting
  "_gby_hash_aggregation_enabled" to FALSE,
  or by using a NO_USE_HASH_AGGREGATION hint

oracle声称在下面版本中会fix,但我们为11.1.0.7打了2009.10月的PSU

升到11.1.0.7.1之后,这个问题仍然存在.

10.2.0.3 Patch 23 on Windows Platforms 10.2.0.4 Patch 5 on Windows Platforms 10.2.0.5 (Server Patch Set) 11.1.0.7 (Server Patch Set) 11.2 (Future Release)

用推荐的方法可以解决问题,禁用hash group by之后,会用缺省的sort by,问题是sort by要求排序段,在某些场合会降低查询性能; 什么时候适合用hash group by,什么时候用sort by呢?

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/94384/viewspace-1028628/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/94384/viewspace-1028628/