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

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Tue, 15 Nov 2011 20:21:38 +0800
Message-ID: <CAM_ddu_u=haVm2XBhEvaC_hJ0qQd-pjuLkq_A2R5emDFWSbRKQ_at_mail.gmail.com>

Denis,
I guess it's because there is different optimizer setting cause different execution plan. you can check this by passing the dbms_xplay.display_cursor with outline as format parameter, and see if any different setting by the opt_param.

select * from table(dbms_xplan.display_cursor(null,null,'outline')); Outline Data

---

  /*+

      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
*      OPT_PARAM('_complex_view_merging' 'false')*
      ALL_ROWS
      OUTLINE_LEAF(_at_"SEL$1")
      FULL(_at_"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA

  */

Of cause, the definitive approach is to enable 10053 trace in both tool and compare the two trace to identify the difference . but it will take great effort to drill down such detail

alter session set events '10053 trace name context forever, level 1'; explain plan for <your sql>;
alter session set events '10053 trace name context off';

--

Regards
Sidney Chen

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 15 2011 - 06:21:38 CST