Re: execution plan different inside stored procedure?

You don't say, but what version of Oracle? Is cursor_sharing turned on? Are the literals replaced with bind variables? All of the answers will help determine what is happening.

Mike
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<C6Ya9.206023$me6.27773_at_sccrnsc01>...
> I am not sure but I think that in a stored procedure the optimizer tries for
> all rows instead of first rows.
> Jim
> "Todd" <thperry_at_duke-energy.com> wrote in message
> news:5f8b006d.0208271354.1c01aa06_at_posting.google.com...
> > The following query generates the following execution plan in
> > Toad/SQLPlus/etc..
> >
> > SELECT /*+ LEADING(query_result) */TRAN_SCHEDULE_DETAILS.*
> > FROM
> > PROFILE,
> > TRAN_SCHEDULE_DETAILS,
> > AB_TRAN,
> > QUERY_RESULT
> > WHERE
> > PROFILE.start_date < '01-SEP-2002' AND
> > PROFILE.end_date > '01-JUL-2002' AND
> > QUERY_RESULT.unique_id = 3526317 AND
> > AB_TRAN.toolset = 33 AND
> > PROFILE.param_seq_num = 0 AND
> > AB_TRAN.ins_num = PROFILE.ins_num AND
> > AB_TRAN.tran_num = QUERY_RESULT.QUERY_RESULT AND
> > AB_TRAN.tran_num = TRAN_SCHEDULE_DETAILS.tran_num AND
> > PROFILE.profile_seq_num = TRAN_SCHEDULE_DETAILS.profile_seq_num
> >
> > execution plan per TKPROF is
> > Rows Row Source Operation
> > ------- ---------------------------------------------------
> > 172 NESTED LOOPS
> > 44 NESTED LOOPS
> > 2 NESTED LOOPS
> > 2 INDEX RANGE SCAN (object id 162054)
> > 2 TABLE ACCESS BY INDEX ROWID AB_TRAN
> > 2 INDEX UNIQUE SCAN (object id 127624)
> > 44 TABLE ACCESS BY INDEX ROWID PROFILE
> > 45 INDEX RANGE SCAN (object id 127528)
> > 172 TABLE ACCESS BY INDEX ROWID TRAN_SCHEDULE_DETAILS
> > 215 INDEX RANGE SCAN (object id 128776)
> >
> >
> > the same statement when included in a stored procedure but as a
> > returned cursor generates this plan
> > Rows Row Source Operation
> > ------- ---------------------------------------------------
> > 172 NESTED LOOPS
> > 44 NESTED LOOPS
> > 228590 MERGE JOIN CARTESIAN
> > 2 INDEX RANGE SCAN (object id 162054)
> > 228590 SORT JOIN
> > 228589 TABLE ACCESS BY INDEX ROWID PROFILE
> > 231271 INDEX RANGE SCAN (object id 127529)
> > 228632 TABLE ACCESS BY INDEX ROWID AB_TRAN
> > 457178 INDEX UNIQUE SCAN (object id 127624)
> > 172 TABLE ACCESS BY INDEX ROWID TRAN_SCHEDULE_DETAILS
> > 215 INDEX RANGE SCAN (object id 128776)
> >
> > Why is this?
> >
> > Thanks,
> > -TP
Received on Wed Aug 28 2002 - 15:51:26 CEST