监控SQL对临时表空间的使用

select to_number(decode(sid, 65535, null, sid)) sid,
       operation_type operation,trunc(expected_size/1024) esize,
       trunc(actual_mem_used/1024) mem, trunc(max_mem_used/1024) "max mem",
       number_passes pass, trunc(tempseg_size/1024) tsize
  from v$sql_workarea_active
order by 1,2;

select a.username,
       a.sid,
       a.serial#,
       a.username,
       b.blocks * 8 size_kb,
       c.sql_id,
       c.sql_text
  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;

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

转载于:http://blog.itpub.net/29843794/viewspace-1328328/