Re: Stored Procedure Parsing
Date: 1996/08/01
Message-ID: <4tr711$dol_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <31FFDC5E.7550_at_thomtech.com>, ashok kapur <akapur_at_thomtech.com> writes:
|> Barry Johnson wrote:
|> >
|> > I've recently been told that there is a bug within
|> > the Oracle RDBMS which contridicts claims made by
|> > Oracle. I've been told that no matter what, Oracle
|> > parses stored procedure code, each and every time.
|> > This came from a very reliable source. I was told
|> > the Oracle consultant found the problem at a
|> > customer account in Houston and there corporate
|> > office refused to correct the problem. Is someone
|> > confussed or has anyone experianced similar
|> > processing.
|> >
|> > TIA
|> > -Barry
|>
|>
|> My understanding is that a stored procedure is parsed when it is brought
|> into the SGA. If the stored procedure is in the SGA and parsed, it is NOT
|> parsed again, just like any other SQL stmt in the SGA.
|>
|> I can understand why it is parsed, when it is brought up in memory
|> initially: object structures may have changed, object permissions may
|> have changed, etc. Instead of re-compiling ALL stored procedure whenever
|> any DDL is issued, I think it makes sense to parse a stored procedure
|> whenever it is brought into memory.
The procedure is parsed only *once*, when it is created the first time, unless a dependent object is changed. The parsed code (called p-code) is stored, and the first time it is called the p-code is read into the SGA. It doesn't have to be parsed again.
Oracle maintains a dependency graph, so if you do DDL on a table which a procedure references, the procedure is marked as invalid. The next time it is called, it will be parsed again, and the status changed to valid. This is the only case where a procedure could be parsed multiple times.
|>
|>
|> --
|> Ashok Kapur (akapur_at_thomtech.com)
|> Thomson Technology Consulting Group
|> 1375 Piccard Drive, Suite 250
|> Rockville, MD 20850
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Thu Aug 01 1996 - 00:00:00 CEST