Oracle Blog

Saturday, September 5, 2015

Who is using most temp space on Database

11:52 AM Posted by Yunus SIMSEK No comments
Day to day DBA work, I am seeing more and more temp space related issues, here are the some simple queries that can tell you who is eating up temp spaces, and usage of the temp spaces. 

How do you find who using temp space
SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, 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;
Get 10 sessions with largest temp usage
column proginfo format a40
column LASTCALLET format a35
column TABLESPACE format a25
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;


Percentage of TEMP Space usage
For dictionary managed temporary tablespace :
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='tbsp_name') s, (select sum(blocks) total_blocks from dba_data_files where tablespace_name='tbsp_name') f;

For locally  managed temporary tablespace :
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used" from (select sum(used_blocks) tot_used_blocks from v$sort_segment where tablespace_name='TEMP') s, (select sum(blocks) total_blocks from dba_temp_files where tablespace_name='TEMP') f;

0 comments:

Post a Comment