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

From: Taylor, Chris David <ChrisDavid.Taylor_at_ingrambarge.com>
Date: Mon, 14 Nov 2011 15:57:16 -0600
Message-ID: <C5533BD628A9524496D63801704AE56D6A334C3CC2_at_SPOBMEXC14.adprod.directory>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Denis Sent: Monday, November 14, 2011 3:19 PM
To: oracle-l_at_freelists.org
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_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

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

Received on Mon Nov 14 2011 - 15:57:16 CST