在执行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
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 hintoracle声称在下面版本中会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/