Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk

Hi,
 
a sql with the structure:
WITH
t1 As 
( select   ..
),
WITH
t2 As
( select ...
),
...
WITH
t77 As
(
 select ...
)
select ...
from t1 join t2 join .. t77l
 
 
I run it through sql plus with autotrace, I got it returned in less than 10s 
with the statistics as shown below:

Statistics
----------------------------------------------------------
        182  recursive calls
       8105  db block gets
     141705  consistent gets
          0  physical reads
      45960  redo size
       2784  bytes sent via SQL*Net to client
      60901  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         76  sorts (memory)
          0  sorts (disk)
          0  rows processed

However when I run it with sql developer ( our clients use sql developer), it 
runs forever:. Checking what the session is doing while it is executing in 
sqldevloper, I got something like
 
--- look at temp space usage by the session
SID_SERIAL      USERNAME        OSUSER     SPID       MODULE             
PROGRAM                 MB_USED TABLESPACE   SORT_OPS
--------------- --------------- ---------- ---------- ------------------ 
-------------------- ---------- ---------- ----------
72,50674        V8xxxx9         V8xxxx9    23219      SQL Developer      SQL 
Developer               313 TEMP               79

--- long ops
  SID USERNAME        OPERATION        START TIME         PCT ELASPSED_MIN  
REMAIN_MIN
----- --------------- ---------------- ---------------- ----- ------------ 
-----------
   72 V8xxxx9         Hash Join        11/14 15:33:40    49.4            
0           0
 
--- session wait event
 
v8xxxx9@ABCSTGDB> @sw 72
old  34:    sid IN (&1)
new  34:    sid IN (72)
    SID STATE   EVENT                                SEQ# SEC_IN_WAIT         
P1         P2 P1TRANSL
------- ------- ------------------------------ ---------- ----------- 
---------- ---------- -------------------------
     72 WORKING On CPU / runqueue                   49444           0       
1253    1095484
v8xxxx9@ABCSTGDB> @sw 72
old  34:    sid IN (&1)
new  34:    sid IN (72)
    SID STATE   EVENT                                SEQ# SEC_IN_WAIT         
P1         P2 P1TRANSL
------- ------- ------------------------------ ---------- ----------- 
---------- ---------- -------------------------
     72 WAITING direct path write temp              49932           0       
1253     716265
 
 
Comparing execution plan from sqlplus and from sqldevloper is impossible by 
visual checking as it has 1000+ line. (anyone know a tool to compare execution 
plan based on sql_id? )
 
Tried run it in TOAD,same as in sqldeveloper. Anyone can suggest how to further 
troubleshooting?
 
BTW Oracle 11.2.0.1.0  PGA%6MB in stage db , but problem is same with 
production db PGA=9G
 
- Denis
--
http://www.freelists.org/webpage/oracle-l

Other related posts: