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

Turning on tracing affects how the optimizer performs in some cases - 
especially with bind variables.

http://tkyte.blogspot.com/2007/09/tuning-with-sqltracetrue.html

From Tom:  " The fact that when you set sql_trace=true, you set up a new 
'parse' environment (made up term).  You have changed your session in such a 
way that you will not share any existing SQL that was not parsed with sql_trace 
enabled.  So, it is highly likely that you will either hard parse a new version 
of the query - or use some existing child cursor that is different from the one 
you would use with sql_trace disabled."

If it were me, I'd setup a trace environment (level 12) and run it through 
tkprof and examine the output file.  There are other ways to accomplish the 
same thing - I'm partial to tkprof however.


Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205

"Quality is never an accident; it is always the result of intelligent effort."
-- John Ruskin (English Writer 1819-1900)

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and 
may also be privileged. If you are not the named recipient, please notify the 
sender immediately and delete the contents of this message without disclosing 
the contents to anyone, using them for any purpose, or storing or copying the 
information on any medium.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Denis
Sent: Monday, November 14, 2011 3:19 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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




--
http://www.freelists.org/webpage/oracle-l

Other related posts: