Re: temporary datafiles not being used

On 5 Oct 2004 14:05:10 -0700, mjern92_at_yahoo.com (Michael Jernigan) wrote:

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

Your best bet, is to first look at the queries doing direct sorts. Join v$session w/ v$session_wait for status='ACTIVE', many many times, and grap the SQL that constantly sorts from sql_hash_value. Then see if it can be tuned.

Next look at sort_area_size, next look at IO. If this is some warehouse app, soring to disk might not be avoidable, in transactino processing, should be minimal, IMHO.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Wed Oct 06 2004 - 10:48:48 CDT