Bad timing with nested sql select

Hello folks!

I have a really bad performance with a nested sql selection. The select is structurated like:

	SELECT ...                    /* 3th select */
	FROM   ...
	WHERE  ... IN
		SELECT ...            /* 2nd select */
		FROM   ...
		WHERE  ... IN
			SELECT ...    /* 1st select */
			FROM   ...
			WHERE  ...

The complete nested selection require 37.2400 seconds.

The 1st selection alone require:       1.0600 seconds,
the 2nd, giving the IN values:         0.2000 seconds,
and the 3th with IN values:            0.2400 seconds,

with a total of supposed               1.5000 seconds,

Why will my nested selection lost all this time? I think the problem is not in rollback segments since this is just a select, not DML (insert/update/delete) right? And I also don't think the problem can be in the index ts. So, the problem may be with the temporary tablespace, where the result of differents select are stored during the nested selection... right?

This is the tablespace I use as temporary:

NAME INIT_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE STATUS
---- -------- -------- ------- ------- ------------ ------
TEMP 16384 51200 1 999 20 ONLINE The TEMP tablespace as a Free Space Fragmentation Index FSFI = 23.8094802 What else? Thanks for any help,
Stefano
--

______________________|    Stefano Unternaehrer    |__________________________
\  Mecasoft SA        |         Oracle DBA         | fax:    +41 93 335 507  /
 \ 6600 Muralto       |     and  PL/SQL, Pro*C     | voice:  +41 93 337 444 /
  \                   |     C, XWindow & Motif     |                       /
  /                   |     Software Developer     | email:                \
 / Switzerland Europe |____________________________|  dba,unter_at_mecasoft.ch \
/________________________)                      (____________________________\