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

I believe your original email stated that you "...run it through sql plus with autotrace..." and "...it returned in less than 10s...".

If I'm not mistaken (and I may be) but when you run it through SQLPlus with autotrace, that executes it with tracing hence my point about tracing giving different execution plans and the blog entry from Tom Kyte.

Again, I may be mistaken about the setting of autotrace in sqlplus.

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 7:52 PM
To: oracle-l_at_freelists.org
Subject: Re: Huge difference between sqlplus and sqldeveloper - sorting in memory vs disk

Thanks Greg and Chris for your inputs. sql_trace is not the tool in this case, I cannot trace a session that running > 10 hours. The stage db which I do test has pga%6MB and our production is 9GB  
I broke the 77 subquery blocks in the WITH claus with 77 create global temporary statement. Then I only run the main sql in sqlplus and sqldeveloper, again sqlplus session return in minutes and sqldev never. Found  the join order is different: -- main sql : ---
 
select
....

FROM   TECH_PER_DAY a
JOIN   SOIFInstJPDGoal b
ON     a.team = b.team
AND    (a.m_date between b.from_date AND (b.to_date - 1)) LEFT JOIN   SOIFInstJPDGoalOR b1 ON     a.person= b1.person AND    (a.m_date between b1.from_date AND b1.to_date) JOIN   SOIFMtcJPDGoal c ON     a.team = c.team AND    (a.m_date between c.from_date AND (c.to_date - 1)) LEFT JOIN   SOIFMtcJPDGoalOR c1 ON     a.person= c1.person AND    (a.m_date between c1.from_date AND c1.to_date) JOIN   CoreVInstJPDGoal d ON     a.team = d.team AND    (a.m_date between d.from_date AND (d.to_date - 1)) ...

• 72 |.            HASH JOIN                                        |                          |     1 |   255 |   940   (9)| 00:00:12 |
• 73 |.             HASH JOIN                                       |                          |     1 |   211 |   929   (9)| 00:00:12 |
• 74 |.              HASH JOIN                                      |                          |     6 |  1002 |   917   (9)| 00:00:12 |
• 75 |.               HASH JOIN                                     |                          |   375 | 46125 |   905   (9)| 00:00:11 |   76 |.                TABLE ACCESS FULL                            | SOIFMTCJPDGOAL           |  7998 |   343K|    12   (0)| 00:00:01 |
• 77 |.                HASH JOIN                                    |                          | 23269 |  1795K|   892   (9)| 00:00:11 |   78 |.                 TABLE ACCESS FULL                           | SOIFINSTJPDGOAL          |  7998 |   343K|    11   (0)| 00:00:01 |   79 |.                 TABLE ACCESS FULL                           | TECH_PER_DAY             |  1444K|    48M|   825   (3)| 00:00:10 |   80 |.               TABLE ACCESS FULL                             | COREVINSTJPDGOAL         |  7998 |   343K|    11   (0)| 00:00:01 |   81 |.              TABLE ACCESS FULL                              | COREVMTCJPDGOAL          |  7998 |   343K|    11   (0)| 00:00:01 |   82 |.             TABLE ACCESS FULL                               | ISHINSTJPDGOAL           |  7998 |   343K|    11   (0)| 00:00:01 |   83 |.            TABLE ACCESS FULL                                | ISHMTCJPDGOAL            |  7998 |   343K|    12   (0)| 00:00:01 |   84 |.           TABLE ACCESS FULL                                 | SOIFICODEGOAL            |  7998 |   343K|    11   (0)| 00:00:01 |   85 |.          TABLE ACCESS FULL                                  | SOIFREPEATGOAL           |  7998 |   343K|    11   (0)| 00:00:01 |  

The problem is  using /*+leading(a), ordered/ or /*+leading(a b), ordered / does not make sqldev work, it still uses above join order. And advice?

From: Denis <denis.sun_at_yahoo.com>
To: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Monday, November 14, 2011 4:19 PM
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

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




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