Improve dynamic SQL performance with binds
One reason is that it's riskier because the SQL statements aren't parsed until runtime, so it's more difficult to catch simple syntax errors. Also, many attempts at dynamic SQL run into performance problems, and the complexity of simply writing dynamic SQL give a negative impression on dynamic SQL.
Listing A compares two alternatives to insert a range of numbers into a table. In my tests, the second PL/SQL block takes about six times as long as the first block. However, this isn't really a fair comparison because it violates one of the golden rules of dynamic SQL: Never concatenate when you can bind. The constant concatenation means that the 10,000 independent SQL statements need to be parsed, resolved, and executed. The native PL/SQL insert statement in the first example actually generates a recursive SQL statement--'insert into mytestvalues(:1)'--and binds the values on each iteration.
Listing B offers a better comparison with dynamic SQL. This code runs about twice as slow as the first block. That's three times as fast as the second example. However, the dynamic SQL is still at a disadvantage. The execute immediate still does its parse, but it uses SQL sharing to resolve the statement.
It would be ideal if PL/SQL would allow us to create the cursor during OPEN, and then have some new command that allowed us to EXECUTE <cursor> USING …. Even in Oracle 10g, there is no such command. I've been watching native dynamic SQL for a long time, and I still find that it's not as powerful as DBMS_SQL in these cases.
The PL/SQL user's guide warns that the performance of DBMS_SQL isn't as good as EXECUTE IMMEDIATE, but let's try it anyway. Listing C suffers the same problem as the second example--it uses concatenation. It also turns out that the PL/SQL manual is correct in this case. Example 4 in Listing C took seven times as long as the first example. It's true that DBMS_SQL is slower than EXECUTE IMMEDIATE, but EXECUTE IMMEDIATE still isn't as flexible as DBMS_SQL. Example 5 in Listing C is a pleasant surprise--it finished in about the same time as the first example. It's going through nearly the same process as the first example; it's obviously more difficult to code and more error prone but, for many tasks, dynamic SQL may be necessary.
As a further comparison, the equivalent Java stored procedure, (which is always dynamic) takes about the same time as the original PL/SQL and the DBMS_SQL with binding. View Listing D.
Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.