1 问题描述
一个性能poor的sql,上去就把temp段用完了,告警日志报错 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2 temp 用完可能会导致的问题
临时表空间被资源中的多个会话共享 ,当临时表空间被填充满时,任何尝试获得更多的临时表空间的用户将会得到“ORA-1652: unable to extend temp segment”错误。
换个说法,所有需要临时段的操作都会有问题,最常见的就是 排序操作,以下是oracle可能要用到temp段的操作 【参考 oracle文档 19047.1 】
(1) A SORT Used for a SELECT or for DML/DDL(2) CREATE INDEX
(3) CREATE PK CONSTRAINT
(4) ENABLE CONSTRAINT
(5) CREATE TABLE New tables start out as TEMPORARY segments.
Eg: If MINEXTENTS is > 1 or you issue CREATE table as SELECT.
(6) Accessing a GLOBAL TEMPORARY TABLE When you access a global temporary table a TEMP segment is instantiated to hold the temporary data.
3 如何找到导致问题的sql语句
alter日中中告警信息 ORA-1652: unable to extend temp segment by 128 in tablespace TEMP ORA-1652: unable to extend temp segment by 128 in tablespace TEMP) 会有告警的时间 ,可以找出具体的时间,根据这个时间点前后,到表 DBA_HIST_ACTIVE_SESS_HISTORY 中找处理执行sql的等待事件,然后看到了事件“direct path write temp”再根据这个事件定位sql
点击(此处)折叠或打开
- SELECT EVENT,
- SQL_ID,
- SAMPLE_TIME,
- MACHINE
- FROM DBA_HIST_ACTIVE_SESS_HISTORY
- WHERE SAMPLE_TIME >= TO_DATE (‘201501211328′, ‘YYYYMMDDHH24MI’)
- AND SAMPLE_TIME <= TO_DATE (‘201501211331′, ‘YYYYMMDDHH24MI’)
- and event =’direct path write temp’
4 和临时段相关的视图
select * from v$tempseg_usage5 调优问题sql,解决问题
剩下的问题就是调优有问题的sql,保证该问题不再出现。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/617982/viewspace-1410739/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/617982/viewspace-1410739/