数据库相关情况探讨请教

显示全部楼层 · 2007-5-10 01:02:02

数据库频繁出现如下问题，应是因为大量排序的原因，该如何解决这个问题？
Sun Nov 06 23:38:44 GMT+08:00 2011ORA-1652: unable to extend temp segment by 128 in tablespace
TEMP
Sun Nov 06 23:41:38 GMT+08:00 2011ORA-1652: unable to extend temp segment by 128 in tablespace
TEMP
Sun Nov 06 23:45:58 GMT+08:00 2011ORA-1652: unable to extend temp segment by 128 in tablespace
TEMP
想请教：该如何查出哪些语句占用最大的排序，这个是否会导致前台应用断开连接
注：
1. 数据库是ORACLE10G FOR AIX 6.1 的 RAC双机
2. 两台主机的物理内存32G，数据库两节点分别SGA为20G,PGA为3G
3. 当前临时表空间的大小是30G，是非自动扩展模式的。
4. 前台经常断开连接，监听日志经常出现如下信息：
TNS-01184: Listener rejected registration or update of service handler "DEDICATED"
TNS-01185: Registration attempted from a remote node

我有想过把PGA设置为8G，SGA设置为16G
有想过将将临时表空间设置为临时表空间组。多加几个临时表空间进去。
但是这个估计是治标不治本。关键还是我提的这个问题。
“该如何查出哪些语句占用最大的排序，这个是否会导致前台应用断开连接”
其实通过AWR报表，已经发现了很多ORDER BY 相关的SQL语句，在优化了。
大家有啥好主意帮忙一起想想，谢谢！

千问 · 2007-5-10 01:02:02

本身内存够用，pga可以再增大些。
最好的办法就是对应用就行修改，减少排序的使用。

千问 · 2007-5-10 01:02:02

删除老的临时表空间，创建一个新临时表空间要求是可以自己扩展，将新改成默认
就可以了

千问 · 2007-5-10 01:02:02

本帖最后由 fjliangrq 于 2011-11-8 07:09 编辑
看了一下AWR数据库，觉的非常奇怪。明明2个节点在6日都有unable to extend temp segment by 128 in tablespace
TEMP
的提示，我分别进两个节点看AWR，是从4-7日的AWR报表，含了6号
发现 IN-MEMORY-SORT 100%
sorts (memory)=18,732,289
sorts (rows) 80,022,319,449
但是sort(disk)却是0
两个节点情况类似。
是否可以说明，系统中虽然排序动作非常多，3天有排序800亿行。但是却都是在内存中完成的，耗去临时表空间不足的语句非排序，是另有其他语句？

千问 · 2007-5-10 01:02:02

临时表空间是无法回缩的，一般只在实例启动时smon进行回收，一般采取新建一个临时表空间然后改成默认！

千问 · 2007-5-10 01:02:02

删除该表空间，重建一个，观察几天

千问 · 2007-5-10 01:02:02

本帖最后由 fjliangrq 于 2011-11-8 15:30 编辑
我已经增加了3个临时表空间，并且整合到一个临时表空间组里去了。现在这个问题应该可以暂缓了。
不过我的临时表空间不足不是排序引发的，还有啥可能呢？
而且我怀疑真正的问题是ORACLE BUG导致的前台断连

千问 · 2007-5-10 01:02:02

想从根本上解决这个问题的话，还是需要对SQL进行优化。避免使用大量的排序或HASH空间。

千问 · 2007-5-10 01:02:02

能不能贴出AWR看看

千问 · 2007-5-10 01:02:02

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace,b.contents, b.blocks,b.extents, c.sql_text,a.status
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;