ITPub博客

首页 > 数据库 > Oracle > 临时表空间过度使用故障解决

临时表空间过度使用故障解决

原创 Oracle 作者:monkeybron 时间:2016-01-06 11:11:10 0 删除 编辑

临时表问题是个dba 永久的话题:
 从9i 到11g,也看出oracle对临时表不断改善。
 根本还是在sql上的排序操作:
 故障现象:


查询发现应用占用90g临时表空间,总100g


杀会话:

SQL> alter system kill session '418,5285';


System altered.


SQL> select * from (
  2  Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as    Space,se.sql_id,tablespace,segtype,substr(sql_text,1,25) sqltext
  3  from v$sort_usage su,v$parameter p,v$session se,v$sql s where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
  4  order by  space desc) where rownum<=5
  5  ;
/


USERNAME          SID    EXTENTS      SPACE SQL_ID        TABLESPACE SEGTYPE   SQLTEXT
---------- ---------- ---------- ---------- ------------- ---------- --------- ------------------------------
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxx SE


SQL> 
USERNAME          SID    EXTENTS      SPACE SQL_ID        TABLESPACE SEGTYPE   SQLTEXT
---------- ---------- ---------- ---------- ------------- ---------- --------- ------------------------------
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxxx SE
xxxx             1265          1    1048576               TEMP       LOB_DATA  UPDATE xxxx SE
xxxx            1265          1    1048576               TEMP       LOB_DATA  UPDATE xxxx SE
xxxx            1265          1    1048576               TEMP       LOB_DATA  UPDATE xxxx SE


查询该历史sql:



select sql_id,EVENT,PROGRAM,MACHINE,TEMP_SPACE_ALLOCATED from DBA_HIST_ACTIVE_SESS_HISTORY where MACHINE='HQ-A-VPLYS0105' and SESSION_ID=418 and SAMPLE_TIME 
between to_timestamp('2016-01-06 09:00:00','yyyy-mm-dd hh24:mi:ss') and  to_timestamp('2016-01-06 09:30:00','yyyy-mm-dd hh24:mi:ss')  order by SAMPLE_TIME desc ,TEMP_SPACE_ALLOCATED desc ;




711a32rb2cn09 JDBC Thin Client 99438559232
036yt6k6cfkyx db file parallel read JDBC Thin Client 99438559232
036yt6k6cfkyx db file parallel read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file parallel read JDBC Thin Client 99438559232
036yt6k6cfkyx gc cr multi block request JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx JDBC Thin Client 99438559232
036yt6k6cfkyx db file parallel read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
036yt6k6cfkyx db file parallel read JDBC Thin Client 99438559232
036yt6k6cfkyx db file sequential read JDBC Thin Client 99438559232
gjdzq7qg9ktng JDBC Thin Client 99438559232
gjdzq7qg9ktng JDBC Thin Client HQ-A-VPLYS0105 99438559232





  1* select sql_text from dba_hist_sqltext where sql_id='gjdzq7qg9ktng'
SQL> /





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30182853/viewspace-1972401/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-03-30

  • 博文量
    70
  • 访问量
    80241