oracle-developer.net

native dynamic sql optimisation in 10g

Native Dynamic SQL (NDS) was introduced in Oracle 8i; supported by the new EXECUTE IMMEDIATE command and the existing OPEN FOR syntax. The new syntax made dynamic SQL and PL/SQL considerably easier to code than the low-level APIs of the existing DBMS_SQL. As such, NDS has probably become one of the most used (and definitely abused) new features of recent releases.

For one-off dynamic SQL tasks, NDS is incredibly simple to use. For row-based cursor processing, however, it was (prior to 10g) inefficient and considerably slower than static or DBMS_SQL cursor processing. Prior to 10g, NDS statements would be "soft-parsed" prior to every execute, such that dynamic SQL statements with multiple-executions would perform far better using DBMS_SQL. In 10g, Oracle has optimised this additional parsing away, as we'll see in this article.

a simple example

In the following example, we'll execute a simple "SELECT INTO" repeatedly, using both Native Dynamic SQL and DBMS_SQL. We'll trace the session and use tkprof to report the parsing. In addition, we'll time both blocks with a "wall-clock". Remember the premise to this is that in previous versions of Oracle, the NDS method would take considerably longer to run and use more resources due to excessive soft-parsing.

We can see that in 10g, the DBMS_SQL method has taken slightly longer to run by the wall-clock. This is by no means a conclusive or scientific test, however, but is purely illustrative. But in the very least we can see that NDS loops are no longer as inefficient as they were in previous versions of Oracle. The reason, as stated previously, is in the parse optimisation. We can see this by examining the tkprof outputs for the trace file we created.

Here we can see the optimisation in evidence. Oracle has parsed the statement once and executed it 100,000 times, in the same way that it would with either a static or DBMS_SQL statement. For completeness, we'll take a look at the DBMS_SQL version and see the same pattern.