> In Oracle, stored procedures are compiled when you create them (i.e. run
> the CREATE PROCEDURE/FUNCTION/PACKAGE/TRIGGER statement). The first time
> you call an Oracle Stored Procedure it is loaded into Oracle's memory
> (shared pool of the SGA). The stored procedure will stay there until it is
> paged out (to make room for other stored procedures). Once the procedure
> has been loaded into memory, it will execute quickly and the copy in memory
> will be used for subsequent calls. There are some things that can be done
> by the DBA to keep stored procedures in memory (pinning).
>
> In Oracle, execution plans are usually associated with SQL statements not
> with Stored Procedures.
Regardless of what terminology is commonly used, the principles
that Adam Churvis explained so eloquently apply to all databases
that use cost-based optimization. (Allthough database vendors are
working on technology that might change that in the future, e.g.
http://www.almaden.ibm.com/software/dm/SMART/LeoVLDB01.pdf).
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
