最近遇到一个问题，每天数据库的临时表空间都会被占满。
alter日志里面报临时段不能扩展的警告。
这个数据库压力并不大，以前没有发生过该问题，通过awr检查，发现有一条SQL语句执行时间比较长，而且有较大的排序，再查看该语句的执行计划，发现有hash join并且有hash group。这个表并不大，感觉似乎是一个bug。通过support.oracle.com进行查找，发现是这个bug 7716219 。具体如下：
Bug 7716219 HASH GROUP BY can use excessive TEMP space
This note gives a brief overview of bug 7716219.
The content was last updated on: 28-APR-2010
Click here for details of each of the sections below.
Affects:Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2
Versions confirmed as being affected 11.1.0.7
Platforms affected Generic (all / most platforms affected)
Fixed:This issue is fixed in 10.2.0.5 (Server Patch Set)
11.1.0.7.1 (Patch Set Update)
11.2.0.1 (Base Release)
Symptoms: Related To:
Storage Space Usage Affected Hash Join
_GBY_HASH_AGGREGATION_ENABLED
Description
HASH GROUP BY can use excessive TEMP space.
Workaround
Disable hash group by
eg: Set "_gby_hash_aggregation_enabled" = false
HOOKS PARAMETER:_GBY_HASH_AGGREGATION_ENABLED LIKELYAFFECTS XAFFECTS_A201 AFFECTS=10.2.0.1 XAFFECTS_A202 AFFECTS=10.2.0.2 XAFFECTS_A203 AFFECTS=10.2.0.3 XAFFECTS_A204 AFFECTS=10.2.0.4 XAFFECTS_A2041 AFFECTS=10.2.0.4.1 Patch Set Update XAFFECTS_A2042 AFFECTS=10.2.0.4.2 Patch Set Update XAFFECTS_A2043 AFFECTS=10.2.0.4.3 Patch Set Update XAFFECTS_A2044 AFFECTS=10.2.0.4.4 Patch Set Update XAFFECTS_A2045 AFFECTS=10.2.0.4.5 Patch Set Update XAFFECTS_B106 AFFECTS=11.1.0.6 XAFFECTS_B107 AFFECTS=11.1.0.7 XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_HASH TAG_SPACEUSE FIXED_A205 FIXED_B1071 FIXED_B201
阅读(2104) | 评论(0) | 转发(0) |
