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

From: Denis <denis.sun_at_yahoo.com>
Date: Mon, 14 Nov 2011 17:51:46 -0800 (PST)
Message-ID: <1321321906.65114.YahooMailNeo_at_web161801.mail.bf1.yahoo.com>

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

...
--- sql plus join order ---
|  77 |.                 TABLE ACCESS FULL                           | TECH_PER_DAY             |     1 |    35 |     2
|  78 |.                 TABLE ACCESS FULL                           | SOIFINSTJPDGOAL          |     1 |    44 |     2
|  80 |.               TABLE ACCESS FULL                             | SOIFMTCJPDGOAL           |     1 |    44 |     2
|  81 |.              TABLE ACCESS FULL                              | SOIFMTCJPDGOALOR         |     1 |    44 |     2
|  82 |.             TABLE ACCESS FULL                               | COREVINSTJPDGOAL         |     1 |    44 |     2
|  83 |.            TABLE ACCESS FULL                                | COREVINSTJPDGOALOR       |     1 |    44 |     2
|  84 |.           TABLE ACCESS FULL                                 | COREVMTCJPDGOAL          |     1 |    44 |     2
|  85 |.          TABLE ACCESS FULL                                  | COREVMTCJPDGOALOR        |     1 |    44 |     2
|  86 |.         TABLE ACCESS FULL                                   | ISHINSTJPDGOAL           |     1 |    44 |     2
|  87 |.        TABLE ACCESS FULL                                    | ISHINSTJPDGOALOR         |     1 |   
--- sqldev join order --

• 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

Received on Mon Nov 14 2011 - 19:51:46 CST