Home » RDBMS Server » Performance Tuning » When is execution plan for a package determined (merged)
When is execution plan for a package determined (merged) [message #441010] Thu, 28 January 2010 06:03 Go to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi,

I have a package that get's compiled. After this happens an index is created on a table which the package queries. The index doesn't seem to be used by the package, but when I execute the statement outside of the package it uses the index. This is leading me to think that the execution plan for the package is determined and locked when the package is compiled. Can anyone confirm/deny this?
Thanks
Re: When is execution plan for a package determined (merged) [message #441014 is a reply to message #441010] Thu, 28 January 2010 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the plan is determined as parse time.
Now it depends on your version and package (static or dynamic query).

Regards
Michel
Re: When is execution plan for a package determined (merged) [message #441017 is a reply to message #441010] Thu, 28 January 2010 06:14 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi Michel

10.2.0.4
Static query within the procedure.

Thanks for replying so quickly.

F
Re: When is execution plan for a package determined (merged) [message #441031 is a reply to message #441017] Thu, 28 January 2010 06:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The plan is determined the first time the procedure executes the sql statement.

Regards
Michel
Re: When is execution plan for a package determined (merged) [message #441057 is a reply to message #441010] Thu, 28 January 2010 07:48 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi,

Any chance you could provide a link for this? I would have thought the procedure would use the index even if the index was created after the package.

Thanks
F
Re: When is execution plan for a package determined (merged) [message #441059 is a reply to message #441010] Thu, 28 January 2010 07:54 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
When the package is created is irrelevant. What matters is when it is run. When it's run an execution plan is determined and stored in the sga along with the sql statement. As long as the sql statement remains in the sga the same execution plan is used whenever that sql is run, unless something happens to invalidate the excution plan - like changes to objects queried by the plan. I'm not sure but I think index creation would do that.
How are you determining whether or not the package uses the index?
I suspect the optimiser is seeing the index but is deciding not to use it.
Can you give us some details of the query/index and explain plans?
Re: When is execution plan for a package determined (merged) [message #441062 is a reply to message #441010] Thu, 28 January 2010 08:11 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks for the reply. I'm unable to produce the explain plans at this time - probably tomo am. If you could point me to Oracle documentation for this behaviour that would be great.

Thanks
F
Re: When is execution plan for a package determined (merged) [message #441067 is a reply to message #441062] Thu, 28 January 2010 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is surely explained in Database Concepts

Regards
Michel
Re: When is execution plan for a package determined (merged) [message #441074 is a reply to message #441067] Thu, 28 January 2010 09:09 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi Michel,

I taken a look at the link you provided and cannot find the behaviour explained. I've looked up links to execution plan in the index but no luck.
Thanks
F
Re: When is execution plan for a package determined (merged) [message #441077 is a reply to message #441062] Thu, 28 January 2010 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I'm unable to produce the explain plans at this time - probably tomo am. If you could point me to Oracle documentation for this behaviour that would be great.

One way is to do as below

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> -- now invoke procedure here, then CUT everything & PASTE results back here
Re: When is execution plan for a package determined (merged) [message #441078 is a reply to message #441010] Thu, 28 January 2010 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
this bit covers most of it.

[Updated on: Thu, 28 January 2010 09:27]

Report message to a moderator

Re: When is execution plan for a package determined (merged) [message #441080 is a reply to message #441010] Thu, 28 January 2010 09:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You might find this asktom thread useful as well: http://asktom.oracle.com/pls/asktom/f?p=100:11:990263010705004::::P11_QUESTION_ID:2588723819082
Re: When is execution plan for a package determined (merged) [message #441088 is a reply to message #441077] Thu, 28 January 2010 11:17 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
BlackSwan wrote on Thu, 28 January 2010 15:24
> I'm unable to produce the explain plans at this time - probably tomo am. If you could point me to Oracle documentation for this behaviour that would be great.

One way is to do as below

SQL> SET AUTOTRACE TRACEONLY EXPLAIN
SQL> -- now invoke procedure here, then CUT everything & PASTE results back here


I should have explained - the procedure is in production and can only be run with the nightly batch. I will try and turn on tracing for the next run to capture this.

Re: the links provided, these are helpful in a general sense but I cannot find anywhere a sentence which says when the execution plan for a procedure is determined. I would have thought cookiemonster is correct saying it depends when it's run rather than when the package was compiled

F
Re: When is execution plan for a package determined (merged) [message #441090 is a reply to message #441088] Thu, 28 January 2010 11:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I will try and turn on tracing for the next run to capture this.

Alternatively do instead


ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the SQL code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

EXPLAIN PLAN will be contained in trace_results.txt file
Re: When is execution plan for a package determined (merged) [message #441091 is a reply to message #441090] Thu, 28 January 2010 11:22 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
BlackSwan wrote on Thu, 28 January 2010 17:21
>I will try and turn on tracing for the next run to capture this.

Alternatively do instead


ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the SQL code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here

EXPLAIN PLAN will be contained in trace_results.txt file



The statement causing problems within the procedure is a DELETE so can't run this outside the batch for obvious reasons! Thanks
Re: When is execution plan for a package determined (merged) [message #441092 is a reply to message #441091] Thu, 28 January 2010 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>The statement causing problems within the procedure is a DELETE so can't run this outside the batch for obvious reasons!

It might be helpful if you posted the complete & actual SQL.

Is there a sub-SELECT involved?

Re: When is execution plan for a package determined (merged) [message #441093 is a reply to message #441088] Thu, 28 January 2010 11:43 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
firefly wrote on Thu, 28 January 2010 17:17
Re: the links provided, these are helpful in a general sense but I cannot find anywhere a sentence which says when the execution plan for a procedure is determined. I would have thought cookiemonster is correct saying it depends when it's run rather than when the package was compiled

F


Let's sort out terminology here. Procedure's do not have execution plans. They're PL/SQL and execution plans belong to SQL statements.
Each individual sql statement in a procedure will have it's own execution plan.
Also bare in mind that if you have 2 different procedures within the same schema with identical sql statements, then those 2 statements will share the same execution plan.

The reason you're struggling to find anything about procedures specifically in the docs is because for this sql in procedures isn't treated any differently to sql in a java app, or a proC program, or in sqlplus - the execution plan is determined when the sql is first parsed in all cases. And to clarify compiling a procedure doesn't parse the sql statements it just syntax checks them.
Re: When is execution plan for a package determined (merged) [message #441165 is a reply to message #441093] Fri, 29 January 2010 02:38 Go to previous message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
cookiemonster wrote on Thu, 28 January 2010 17:43
firefly wrote on Thu, 28 January 2010 17:17
Re: the links provided, these are helpful in a general sense but I cannot find anywhere a sentence which says when the execution plan for a procedure is determined. I would have thought cookiemonster is correct saying it depends when it's run rather than when the package was compiled

F


Let's sort out terminology here. Procedure's do not have execution plans. They're PL/SQL and execution plans belong to SQL statements.
Each individual sql statement in a procedure will have it's own execution plan.
Also bare in mind that if you have 2 different procedures within the same schema with identical sql statements, then those 2 statements will share the same execution plan.

The reason you're struggling to find anything about procedures specifically in the docs is because for this sql in procedures isn't treated any differently to sql in a java app, or a proC program, or in sqlplus - the execution plan is determined when the sql is first parsed in all cases. And to clarify compiling a procedure doesn't parse the sql statements it just syntax checks them.


Thanks for that. Clear to me know!
Previous Topic: Rarely used columns
Next Topic: Bitmap Join Index for DW
Goto Forum:
  


Current Time: Sun Dec 11 02:18:29 CST 2016

Total time taken to generate the page: 0.08602 seconds