Oracle Database Discussions

Hello,

we are using Oracle 9.2.0.7, Solaris 10 and VxFS (DSS worlkoad). Currently we are trying to optimize some long-time queries - they are written using parallel queries. We compared two of them: one is running for about 20 minutes, the other is running for 10 hours and ... nothing happens (in fact it is killed by database). In the first query our developer is not using temporary tablespace (TTS), in the second - she is using TTS for some interim results. While investigating the problem I saw that when the first query was running iostat shown close to 200MB/s of reads/writes (FC connection to SAN) while during second query the bandwidth dropped down to 20-30 MB/s ! The main difference between the two queries is the TTS. She suspected that the problem might be related to the tablespace and changed the second query to use some temporary tables outside TTS. We ran the second query again and the bandwidth increased to close to 200MB/s and time of running dropped down to about 30 minutes ! It means that there are some performance probems with using TTS. Could you please advice how can we tune TTS ? Is it possible in general ?

Regards
przemol