title: Performance of Dynamic SQL with Bind vs Normal SQL within Package
body: <p>In an Oracle 10g environment, I have a statement that needs to be executed several million times based on the results of a cursor. For flexibility, the current code has the statement as a constant in the package body. Here is a simplified version of the code. Please keep in mind this is all within the same package:</p>
  
  <pre><code>c_Stmt  CONSTANT VARCHAR2(128) := 'DELETE FROM t WHERE fldA = :b1';
  
  ...
  
  PROCEDURE p1(vSomeNumber NUMBER(10)) IS
  BEGIN
    EXECUTE IMMEDIATE c_Stmt USING vSomeNumber;
  END;
  
  ...
  
  FOR i IN 1 .. 9999999
    LOOP
       p1(i);
    END LOOP;
  </code></pre>
  
  <p>Since the dynamic SQL already uses bind variables, is there any performance advantage to rewriting this code to replace the dynamic SQL with a regular DML statement like this:</p>
  
  <pre><code>PROCEDURE p1(vSomeNumber NUMBER(10)) IS
  BEGIN
    /* EXECUTE IMMEDIATE c_Stmt USING vSomeNumber; */
    DELETE FROM t WHERE fldA = vSomeNumber;
  END;
  </code></pre>
  
  <p>I think the impact might be on the number of parses, but it's unclear to me if that's still a concern in 10g.</p>
  
  <p>Thank you for your answers.</p>