How to monitor temp tablespace usage?

At times you may have a scenario where you may want to monitor temp space usage in a running session. So using the following PL/SQL code loops which inserts every interval on temp usage for a specific sid (session id).

create table monitor_usage (
RUN_DATE DATE,
BLOCKS NUMBER(38),
BYTES NUMBER(38),
APP VARCHAR2(10)
);

declare
cursor l_cur IS SELECT sysdate, b.blocks, b.blocks*d.value bytes
FROM v$session a, (select session_addr, sum(blocks) blocks from v$tempseg_usage group by session_addr) b, v$sqlarea c,
(select value from v$parameter where name=’db_block_size’) d
WHERE a.saddr = b.session_addr
and a.sid = &sid
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*d.value > 1024;
l_rec l_cur%rowtype;
app varchar2(2) := ‘&1’;
interval integer := 10;
begin
while true loop
open l_cur;
fetch l_cur into l_rec;
If l_cur%found THEN
insert into monitor_usage values (l_rec.sysdate, l_rec.blocks, l_rec.bytes, app);
commit;
end if;
DBMS_LOCK.sleep(seconds => interval);
close l_cur;
end loop;
end;
/

Example:

SQL> alter session set nls_Date_format=’DD-MON-YYY

Session altered.

SQL> select * from monitor_usage where app = ‘R1’
RUN_DATE BLOCKS BYTES APP
——————– ———- ———- ——-
15-JUN-2010 10:21:21 1792 14680064 R1
15-JUN-2010 10:21:32 8576 70254592 R1

15-JUN-2010 10:27:52 119808 981467136 R1
15-JUN-2010 10:28:04 122240 1001390080 R1
15-JUN-2010 10:28:15 124672 1021313024 R1

15-JUN-2010 10:29:59 33920 277872640 R1
15-JUN-2010 10:30:10 34048 278921216 R1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s