ORA-1652 临时表空间满了导致新会话数据不能入库诊断案例
ORA-1652 临时表空间满了导致新会话数据不能入库诊断案例
1.联通现场状态
时间：2012年8月17日
数据库版本：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
操作系统平台：HP-UX
告警日志： more alert_mdsoss.log
2.定位问题
报错现象：
Fri Aug 17 13:37:39 EAT 2012
ORA-1652: unable to extend temp segment by 128 in tablespace MDSTEMP 显示不能扩展临时段，说明临时表空间已经被使用满了，空间不够。
说明：从metalink上官方解释，没有更多的空闲区分给这个临时段了，可以给表空间添加数据文件的方式来解决此问题，表面上是这样，我们更加的深入了解，是什么原因导致的临时段没有空间了呢，我们都知道临时段是记录排序和数据迁移的，现在深层次问题不是空间不够，过一会再执行sql可能就不报错了。是sql语句不够优化。因为当sql在批量DML操作的时候，会突发性占用大量临时空间排序，就会报临时段不够用，新数据此时不能入库！过一会空间释放后又可以入库了，要想解决此问题就需要sql优化。
The below is from metalink:
Error: ORA-1652
Text: unable to extend temp segment by %s in tablespace %s
------- -----------------------------------------------------------------------
Cause: Failed to allocate an extent for temp segment in tablespace.
Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated or create the object in another
tablespace.
select * from gnwebbrw12081720; 此时是有数据的，说明空间已经释放了
colfile_name for a35
selectfile_name,file_id,bytes/1024/1024,status,autoextensible TABLESPACE_NAME from DBA_TEMP_FILES;
FILE_NAME FILE_ID BYTES/1024/1024 STATUS TAB
----------------------------------- ---------- --------------- --------- ---
/oradata/mdsoss/temp01.dbf 1 24671 AVAILABLE YES
/oradata/mdsoss/mdstmp.dbf 2 20000 AVAILABLE NO MDSTEMP 不是自动扩展，如果是就没有上述问题了，但我们不建议使用数据文件自动扩展功能，不容易监控。看 24G + 20G 空间是没有问题的，一般都是sql写的不够好导致不必要排序。
3.解决方案
（1）重启实例，7*24 重启实例 smon进程可以释放sort段，但我们的库是不能down的
（2）增加数据文件，我的空间很紧张，不可以
（3）配置合理sort_area大小 已经配置完毕了，现PGA 4G sort_area_size 208M
（4）sql optimization 最佳方案
（5）总结哪些操作会导致临时表空间暴涨呢
什么操作在使用temp
- 索引创建或重建.
- ORDER BY or GROUP BY
- DISTINCT 操作.
- UNION & INTERSECT & MINUS
- Sort-Merge joins.
- Analyze 操作
- 有些异常将会引起temp暴涨
当处理以上操作时候呢，dba需要加倍关注temp使用情况？我们现在来看看谁使用这些临时段。
（5）临时表空间使用情况
select tablespace_name,current_users,total_blocks,used_blocks,free_blocks from v$sort_segment;
TABLESPACE_NAME CURRENT_USERS TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
------------------- ------------- ------------ ----------- -----------
TEMP 1 3157760 128 3157632
MDSTEMP 24 2559872 2337152 222720 已经使用了92%
（6）谁在使用这些sort段
select username,session_addr,sqladdr,sqlhash from v$sort_usage;
USERNAME SESSION_ADDR SQLADDR SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS C0000008483ECFB8 C0000008512150B8 3342809064
SABOCOUSR C00000084B405E50 C00000033F867510 141205382
MDSOSS C00000084740E988 C0000008508AB1C0 409467952
MDSOSS C0000008483DE390 C00000033B8914F0 2951877480
MDSOSS C00000084A404460 C0000003404007A0 2584373469
MDSOSS C0000008483F5088 C00000033FA63E18 2245874020
MDSOSS C0000008483FFC48 C00000084D5B5F98 3000467390
MDSOSS C0000008483F5088 C00000033FA63E18 2245874020
MDSOSS C000000852404A60 C00000084DD6F598 1491833069
MDSOSS C0000008483EBA40 C00000084DE28990 1530468420
MDSOSS C0000008483FD158 C00000084E13A648 3459409074
MDSOSS C000000852404A60 C00000084DD6F598 1491833069
MDSOSS C000000852404A60 C00000084DD6F598 1491833069
MDSOSS C000000852404A60 C00000084DD6F598 1491833069
MDSOSS C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS C0000008474068B8 C00000084CCC7790 2982079417
MDSOSS C0000008494501E0 C00000085098B400 3836056470
MDSOSS C00000084B3E5B10 C00000084DA9B990 719858768
MDSOSS C0000008523F3348 C00000084D96F830 2434343698
MDSOSS C00000084740D410 C0000008174B3540 2103182003
USERNAME SESSION_ADDR SQLADDR SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS C00000084A3FD908 C00000084C944888 3846639713
MDSOSS C0000008523D5AF8 C00000084DC6BB30 3158920754
MDSOSS C0000008483FA668 C00000032FCDF3D8 1691040305
MDSOSS C00000084943BFD8 C00000084CE4CA70 2036150049
MDSOSS C00000084A4170F0 C00000084D75FF68 2058192145
MDSOSS C0000008483CF768 C00000084DC096B8 3375505524
MDSOSS C000000849452CD0 C00000031E5A32F8 346930689
MDSOSS C000000849433F08 C000000340106C48 2344782277
MDSOSS C0000008484067A0 C000000344414D88 4097260861
MDSOSS C00000084B3C57D0 C000000850BF8528 3690121153
MDSOSS C00000084740D410 C0000008174B3540 2103182003
MDSOSS C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS C0000008483EBA40 C00000084DE28990 1530468420
MDSOSS C0000008483FD158 C00000084E13A648 3459409074
MDSOSS C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS C0000008523F73B0 C00000034046B670 3944536657
MDSOSS C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS C0000008473E3A88 C00000084F13BD98 1450489357
MDSOSS C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS C0000008473E2510 C0000008518A3DA0 2446627624
USERNAME SESSION_ADDR SQLADDR SQLHASH
------------------------------ ---------------- ---------------- ----------
MDSOSS C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS C0000008523DDBC8 C000000850AE75E0 2349095001
MDSOSS C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS C0000008473E2510 C0000008518A3DA0 2446627624
MDSOSS C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS C00000084941A720 C00000084F2EB838 1495689429
MDSOSS C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS C00000084941A720 C00000084F2EB838 1495689429
MDSOSS C00000084941A720 C00000084F2EB838 1495689429
MDSOSS C00000084B3CC328 C00000084D2185C8 497018778
MDSOSS C00000084941A720 C00000084F2EB838 1495689429
MDSOSS C00000084941A720 C00000084F2EB838 1495689429
MDSOSS C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS C00000084A3F98A0 C00000030871D7E0 1117602678
MDSOSS C00000084A3F8328 C000000851A28740 87302580
MDSOSS C00000084A3F8328 C000000851A28740 87302580
MDSOSS C00000084A3F8328 C000000851A28740 87302580
61 rows selected
此时我们知道MDSOSS用户的sql是导致临时段爆满的罪魁祸首
|
评论暂时关闭