| When is execution plan for a package determined (merged) [message #441010] |
Thu, 28 January 2010 06:03  |
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 #441059 is a reply to message #441010] |
Thu, 28 January 2010 07:54  |
cookiemonster
Messages: 13958
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 #441088 is a reply to message #441077] |
Thu, 28 January 2010 11:17 |
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 #441091 is a reply to message #441090] |
Thu, 28 January 2010 11:22 |
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 #441093 is a reply to message #441088] |
Thu, 28 January 2010 11:43 |
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
firefly wrote on Thu, 28 January 2010 17:17Re: 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 |
firefly
Messages: 53
Registered: March 2009
Location: Europe
|
Member |
|
|
cookiemonster wrote on Thu, 28 January 2010 17:43firefly wrote on Thu, 28 January 2010 17:17Re: 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!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
