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@xxxxxxxxx> To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 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 -- //www.freelists.org/webpage/oracle-l