Different DBMS brands vary in the way they actually implement stored procedures.
In several brands, the stored procedure text is stored within the database and is interpreted when the procedure is executed. This has the advantage of creating a very flexible stored procedure language, but it creates significant runtime overhead for complex stored procedures. The DBMS must read the statements that make up the stored procedure at runtime, parse and analyze them, and determine what to do on the fly.
Because of the overhead in the interpreted approach, some DBMS brands compile stored procedures into an intermediate form that is much more efficient to execute. Compilation may be automatic when the stored procedure is created, or the DBMS may provide the ability for the user to request stored procedure compilation. The disadvantage of compiled stored procedures is that the exact technique used to carry out the stored procedure is fixed when the procedure is compiled. Suppose, for example, that a stored procedure is created and compiled soon after a database is first created, and later some useful indexes are defined on the data. The compiled queries in the stored procedure won’t take advantage of these indexes, and as a result, they may run much more slowly than if they were recompiled.
To deal with stale compiled procedures, some DBMS brands automatically mark any compiled procedures that may be affected by subsequent database changes as being in need of recompilation. The next time the procedure is called, the DBMS notices the mark and recompiles the procedure before executing it. Normally, this approach provides the best of both worlds—the performance benefits of precompilation while keeping the compiled procedure up to date. Its disadvantage is that it can yield unpredictable stored procedure execution times. When no recompile is necessary, the stored procedure may execute quickly; when a recompile is activated, it may produce a significant delay; and in most cases, the recompile delay is much longer than the disadvantage of using the old compiled version.
To determine the stored procedure compilation capabilities of a particular DBMS, you can examine its CREATE PROCEDURE and EXECUTE PROCEDURE statement options or look for other procedure management statements such as ALTER PROCEDURE
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.