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

From: Denis <denis.sun_at_yahoo.com>
Date: Mon, 14 Nov 2011 13:19:04 -0800 (PST)
Message-ID: <1321305544.61397.YahooMailNeo_at_web161804.mail.bf1.yahoo.com>

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

Received on Mon Nov 14 2011 - 15:19:04 CST