Re: Execution plan in stored procedures

A copy of this was sent to "Adrian Bowen" <adrian_at_raptor.win-uk.net> (if that email address didn't require changing) On Tue, 22 Jun 1999 06:22:04 +0100, you wrote:

>Thanks for the info. I was misled by (or have misinterpreted) the comments
>made on page 234 of Guy Harrison's "Oracle SQL High-Performance Tuning"
>book, viz.
>
>'Stored procedures and packages are held within the database in a compiled
>form. This means that all the SQL within the stored procedure has already
>been parsed. When the SQL statement is executed, there is no need to check
>the statement for syntax, object references or to determine the execution
>plan.'
>

that statement is partially correct. the first sentence is 100% correct. the second is sort of correct (the statement is parsed by plsql but not to execute -- its parsed so plsql can rewrite the query, we don't keep the parsed query around). The 3'rd statement is 100% incorrect.

• plsql is stored in a pcode format in the database (the procedure code is stored 'compilled')
• the sql has been parsed once by the plsql compiler and rewritten. for example:

  declare

     x number := 5;
  begin

     for y in ( select * from emp where empno = x ) loop ....

will result in a query like "select * from emp where empno = :b0" being stored. plsql parses each and every query and finds 'bind' variables and replaces them with real bind variables. It also validates that the EMP table is accesible and sets up a dependency between this procedure and the emp table (so that modifications to the emp table result in this procedure becoming invalid). Adding a column to EMP will invalidate this procedure but adding an INDEX to emp will not (since we don't store the plan it doesn't need to).

• when the sql statement is executed, it is sent to the database kernel as any other query would be. It (the rewritten query) is parsed, compiled, optimized, etc at that time. If the query is submitted for the second time in the same sesson -- plsql will have cached the cursor for us and just reuses it. If the query is submitted in another session, the shared sql area will kick in and make use of the plan that is already there.

>Regards
>
>Adrian
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jun 22 1999 - 08:35:54 CDT