Re: temporary datafiles not being used

Thanks for the response. I posted something to Metalink last week and got no replies.

They are locally managed with uniform allocation setup in a temporary tablespace. It's Oracle version 8.1.7.4 running on a Sun E3500 with Sun OS 5.8.

I seem to have a lot of waits on direct file write to the temporary files. So I was wondering if the file setup could be an issue.

My best bet is probably to tune my sort and hash areas and look at my disk setup. Currently I have a 5 disk stripe and and a 6 disk stripe.  Currently all my temp files are on the same 5 disk stripe. I could put one on the other stripe or create one across all 11.

"Howard J. Rogers" <howardjr_at_dizwell.com> wrote in message news:<4161d289$0$23896$afc38c87_at_news.optusnet.com.au>...
> 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 Tue Oct 05 2004 - 16:05:10 CDT