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.
*/
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