TEMP Tablespace spiked to 99.9%

[Ratzie]

we currently monitor the tablespaces and recieved a warning that TEMP jumped from 45% to 99.9%

I can not find anything that would show what caused the spike. I understand that the TEMP dataspace "grows" but I have never had it almost cack out!

I tried looking at the V$SORT_USAGE and V$SORT_SEGMENT but it returns nothing.

I would like to have a script that if TEMP reaches a set %, it will run some tests to see what process and user is using the temp tablespace. Is there a way.

It just seems I can not come up with any data that I can use.

Any ideas?

[Roland_17]

Hi,
at dictionary managed tablespaces change pctincrease temporary from e.g. 1 to 2 and back again.
This will force the smon-process to cleanup the temp-tablespace.

[twang · Accepted solution]

To find out what are the SQL statements causing current disk sorts
select sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where a.sqladdr = b.address and
a.sqlhash = b.hash_value and
a.session_addr = c.saddr
order by sid

[Eric Antunes]

Or another way to see what session is doing this heavy sorting:

select s.username "User Name",
s.osuser "OS User",
s.status "Status",
lockwait "Lock Wait",
s.program "Program",
s.machine "Machine",
s.logon_time "Connect Time",
p.program "P Program",
si.physical_reads "Physical Reads",
si.block_gets "Block Gets",
si.consistent_gets "Consistent Gets",
si.block_changes "Block Changes",
si.consistent_changes "Consistent Changes",
s.process "Process",
p.spid, p.pid, s.serial#, si.sid
from sys.v_$sess_io si, sys.v_$session s, sys.v_$process p
where s.username is not null and
si.sid(+)=s.sid
and p.addr(+)=s.paddr
order by status,si.consistent_gets+si.block_gets desc

Best Regards,

Eric Antunes

[Fred Ruffet]

TEMP tablespace near 100% can be something rather normal. You may have a difference in activity that makes this change. If there were really a problem, users would recieve message such as "Unable to allocate ... In TEMP tablespace".

Usage of TEMP and UNDO shouldn't be monitored on their usage PCT. This doesn't have a lot meaning.

Some question :
. Are you using temporary tablespace ? (or tablespace temporary)
. Are you using tempfile ?

Regards,

Fred

[Jean-Luc Oudart]

Hi,

as mention in another thread regarding Temporary segment, refer to Metalink note #102339.1

Regards
Jean-Luc