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

[Dave Chamberlin]

Hello,
I am running Oracle 8.1.7 with a dictionary managed TEMP tablespace (3 datafiles). I am looking at utlbstat/utlestat data on datafile reads/writes. The TEMP ts accounts for < 1% of all blocks read, but uses 35% of all physical read time. Does anyone know why that would be? TEMP also accounts for 55% of all block writes, and 39% of all phys write time. Is there anything that can be tuned to make TEMP more efficient?
Thanks

[Steven E. Protter]

Kind of depends how you use it, how much i/o its going to consume.

I would suggest seeing if the data tablespace is fragmented, and cleaning that up with oracle procedures.

I'd take a look at what is in those tablespaces and what your product set and developers are doing on those tablespaces.

TEMP can be used for anything, thats the problem. Maybe stuff that belongs in a permanent tablespace is in there.

SEP

[Hein van den Heuvel]

A little bit of handwaving here: The main reason for TEMP tablespace is for SORT and JOIN data that does not fit, or is not allowed to fit, in memory.

The reason for DATA read IO is that you have not (yet) touched the data, or it was touched, now lives in a filesystem cache, but was purged out of the SGA.

The main DATA IOs are likely to be fast single page reads, where TEMP often performs multy-block IO possibly invokign striping code.

So the temp IOs are large and come from disk, and the data IOs are smaller and might come from caches. If that is the case, it suggest that you should give more memory to the SGA, less to the dbc.

Is DATA on RAID-1 storage (two disks to read from for any block) and TEMP on RAID-1 (just one disk)

To help explain more precisely we would probably need to know some absolute numbers from Oracle, and a quick overview of the storage. Does temp and data live on the same or similar disks? LVM? files? LVM striping? Stripe-size? COntroller? Controller striping?
Attach details in text file with a future reply if needed!

Btw 1... the Oracle number had better be 'significant'
- to have a real impact on your system.
- for oracle to measure accurately. If have seen many 'oddly high' io response times for low io-rate oracle files.

Btw 2... If you have a chance to make a (test) migration to 9i, you may find that TEMp processing significantly improved between 'pga_aggregate_target' and 'locally managed' concepts.

Hope this helps some,
Hein.

[Sanjay Kumar Suri]

Can you please share/increase the values for the following initialization parameters defined in init.ora file:

sort_area_size
sort_area_retained_size

Check the output of the following SQL:
select mem.value/(disk.value + mem.value)
"In-memory Sort Ratio"
from v$sysstat mem, v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';

One need to ensure that 95% of all sorts happen in memory.

sks

[Hein van den Heuvel]

>> One need to ensure that 95% of all sorts happen in memory.

Ah... you gotta love 'rules of thumbs'.

No, you do NOT need to ensure the 95% of all sorts fit in memory.
You just need to ensure that the the cost of sorting is acceptable to the application end usage as well as acceptable in the total system resource usage impacts.

Admittedlly this may well be achieved by having all 95% of the sorts go to disk, or is may be 50% or it may be 99.5%.
It all depends! ... on the total number of sorts, the average size of all sorts the average size of the large sorts, the memry available, the disk config available and so on.

grins,
Hein.

[Jean-Luc Oudart]

Hi Dave,

1st I would probably have a look at statpack report to see the impact on the TEMP tablespace and sort.

also read MEtalink note 102339.1

If the TEMP tablespace is dictionary managed this will have an impact too.

Regards
Jean-Luc

[Dave Chamberlin]

Thanks for the replies. My sort_area_size is 1M, the sort_area_retained_size is 0, the result of the query showing in-memory sort ratio is .9999 . I just find it odd that it takes several times longer to read or write a block of TEMP data than to read or write a block of DATA data. A block is a block isn't it? The datafiles live on the same kind of disk, both are dictionary managed etc. One question does come to mind here though - if few actual reads/writes are occurring - would it help to reduce the initial/next sizes? Currently in TEMP these are 1M, so would a smaller size consume less disk IO?

[Fred Ruffet]

Oracle consider as good having inital set at 1M, next 1M and pct_increase to 0. This prevent fragmentation.

Operations on Data is not the same as on Temp tablespace. Data is use to read or write blocks to disk. Temp is used during queries to sort, join etc. So in a multi-pass query, it may have need to push things in temp and read them back later. These can not be compared.

Changing initial will need to recreate the TEMP tablespace.

Other things are involved in Temp perfs. For example, are disks your temp is on mirrored or striped ?

Regards,

Fred

[Dave Chamberlin]

It looks like the best option for improving things is to better distrubute the TEMP ts datafiles. Thanks. The metalink note was useful.

[Jean-Luc Oudart]

Hi Dave,

We have the Database on LVM stripped on 8 disks, the SAN backend is RAID0+1.

Using stripping definitely reduces Qlen on disks

Regards
Jean-Luc

[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.