Re: temporary datafiles not being used

Michael Jernigan wrote:

> I have a temporary tablespace with 4 data files of 1GB each. The 1st
> and 4th appear to be used. There is no activity on the 2nd and 3rd
> files. Is that normal? There is a lot of contention on the files
> that are being used. To reduce the contention would it be better to
> have smaller files, but more of them?

It's a bit difficult to answer, because there is so little detail here. An Oracle version, perhaps? An operating system? These all might affect the answer. So too would knowing how the temporary tablespace was created... is it locally managed? A tempfile tablespace? Or a datafile one? Autoallocate, uniform sized?

To answer your last question: no, it would not help to have more, smaller files, *unless* each was housed on a separate hard disk. The ideal with temporary tablespace is to stripe it over as many spindles as possible. If all you are going to do is to use the same disk hardware as you have now, but muck around with the number/size of the files, you'll be wasting your time.

My best guess, and it's only a guess, is that extents are not being round-robin'ed across all files in the temporary tablespace because it is LMT autoallocate. But in the absence of any hard information, I am probably wrong. *If* that was so, however, then it would suggest that your temporary tablespace is too big (because Oracle has evidently felt no need yet to move on to files 2 and 3).

You could therefore, perhaps, re-create the tablespace with just the total size of files 1 and 4. If you utilise more hard disks to spread that reduced size across, then that would certainly help ease the contention issue, too.

But size and contention are two different things.

Regards
HJR Received on Sun Oct 03 2004 - 17:42:19 CDT