Search code examples
oraclesybase

What is the equivalent oracle code for CREATE PROCEDURE <Proc1> WITH RECOMPILE which is available in Sybase ASE/SQL Server

This is what I see from Sybase ASE documentation:
"It instructs Adaptive Server not to save a plan for this procedure. A new plan is created each time the procedure is executed."
So this means ASE does plan the stored procedure code in order to execute it, but what about oracle?
Does Oracle saves plan at stored procedure level?

Solution

  • Oracle does not save plans at the stored procedure level.

    Oracle stores SQL plan information in a memory structure called the shared pool, which is part of the System Global Area (SGA). Plans will be modified and created when necessary, based on when the queries are executed. With the default settings, the plans will automatically adjust based on things like data and bind value changes. You don't need to worry about plans changing or not changing when compiling objects.