Re: procedures

Michael L. Mason a crit dans le message <3638E35C.3496_at_osf1.gmu.edu>...
>Question:
>
>When Oracle gets a request for a procedure from a user process, how
 does
>it obtain it? Does it read it from disk? Or, if the procedure is
>already pinned in memory, can it somehow replicate it from memory, and
>create another copy?

Oracle internally stores the PL/SQL source of the procedure or function or trigger, and you can query it within the USER SOURCE system view (note that the view is already sorted, so you don't need to select line numbers to perform an ORDER BY:

SELECT text FROM user source WHERE name='MY PROCEDURE';

This procedure code is then tokenised and compiled within the Oracle SYSTEM tablespace, with all object names, column names and variables resolved. If there are errors, this tokenization is not stored, but the user source is kept in USER SOURCE, and compile errors are inserted within the USER ERRORS system view, and you can query all compile errors by:

SELECT * FROM user errors;

Sometimes, errors are coming from unknown objects which were not present at the time of procedure or trigger definition, but that were loaded later. You don't need to store the procedure again, you just need to recompile it, using:

ALTER PROCEDURE my procedure COMPILE;

and then check errors (SHOW ERR in SQL*Plus, or SELECT * FROM USER ERRORS). Then at run-time, the first procedure invocation loads the compiled code of the procedure into the shared SGA memory (when the client parses the procedure invocation). Parameters are checked against the compiled procedure definition. When the client executes the procedure, Oracle creates an execution plan for all requests; this uses the optimizer which solves object statistics to produce the best execution plan. This execution plan is also kept in the SGA. If your SGA does not know the table statistics, and if you have a very small SGA, this may be a lengthy process requiring many internal requests.

Because the SGA is a sparse ressource for concurrent processes, sometimes the procedure compiled code and the execution plans are dropped out by other successive requests. If you have a large SGA, you can avoid much time loss because the compiled code and the execution plan will still be in memory. If you change any object definition with DDL statements, the compiled code and the execution plan are removed, and the procedure is marked as 'INVALID'. Further requests using that procedure will try to recompile the procedure automatically. Received on Fri Oct 30 1998 - 00:00:00 CST