Why does TEMP tablespace consume so much read/write time?

[Ted Buis]

I am not familiar whether or not Oracle's TEMP tablespace is in a separate disk directory, but if it is and you are not doing RAW disk access, then there is the possibility to mount a temp file system with faster (more aggressive) options which will help write performance. This can be done by the system administrator using the 'sam' utility.

[Sanjay Kumar Suri]

Dave, Have a look at the topic "Tuning Sorts" in the following link:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76992/ch20_io.htm#3689

sks

[Yogeeraj_1 · Accepted solution]

hi,

Why not change it?
drop tablespace temp
create TEMPORARY TABLESPACE TEMP tempfile .....

By using a true TEMPORARY tablespace -- you will allocate an extent ONCE and then keep it. These extents will be managed not via the data dictionary but in memory. This means the expensive recursive sql we do to allocate an extent and then free it is removed. This can dramatically increase performance if you use lots of temp space frequently.

and then use:
no raid or raid 0 for temporary datafiles (used with temporary tablespaces).

no raid/raid 0 is sufficient. If you lose these, who cares? You want speed on these, not reliability. If a disk fails, drop and recreate temp elsewhere.

Normally, you should try to dedicate specific devices to
o online redo
o archive
o temp

Because you would not want temp (direct writes, direct reads) with you online redo log (direct writes, point of serialization, want speed).

read also:
http://www.jlcomp.demon.co.uk/sort_usage.html

hope this helps!

regards
Yogeeraj

[Dave Chamberlin]

Thanks for all the replies - I think I will try the locally managed solution.