Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
2  
"Lock up the SGA"? –  David Aldridge May 1 '13 at 9:14
    
Perhaps badly worded, I just talking about memory usage in general. Edited –  druzin May 1 '13 at 12:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.