Using dynamic sql inside Oracle stored procedure

Question

Assuming I have the following procedure

CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type, 
                     table1_val IN TABLE1.table1_value%type) 
AS 
  SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2';
BEGIN
  --.....
  --1 : 
    EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id;
  --2 : 
    UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id;

END;

Beside the style/readability, is it any performance penalty for using dynamic query (1) compared to (2) in such cases (I mean when it's absolutely avoidable) ?

Thank you.

Answer 1

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required.

As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the database.

Answer 2

For simple SQL statements, such as the one you're describing, there is indeed an overall hit on performance in an Oracle DB. Since the DBMS must soft parse the sql each time (which also takes up a little bit of time), it then needs to write to SGA and might end up taking away resources from already parsed execution plans from static queries, since the SGA is not infinite. If you're running a lot of statements in dynamic SQL within procedures, you'll experience deterioration of performance over time. Keep in mind that catching errors caused by dynamic SQL is a lot trickier than errors raised by standard SQL statements.

I'd recommend only resorting to dynamic SQL if you're doing complex administrative work with db objects, or if your query differs greatly with input to procedures. Even in those cases you'd be better off with if / else statements dictating your queries.