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.

First, we'll turn on a level 1 SQL trace.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.

Now we can run our NDS example.

SQL> set timing on
 
SQL> DECLARE
  2     n NUMBER;
  3  BEGIN
  4     FOR i IN 1 .. 100000 LOOP
  5        EXECUTE IMMEDIATE 'SELECT 0 AS nds_eg FROM dual' INTO n;
  6     END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.19

Now we can repeat the exercise but using DBMS_SQL.

SQL> DECLARE
  2     c BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
  3     n NUMBER;
  4     x NUMBER;
  5  BEGIN
  6     DBMS_SQL.PARSE(c, 'SELECT 0 AS dbmssql_eg FROM dual', DBMS_SQL.NATIVE);
  7     DBMS_SQL.DEFINE_COLUMN(c, 1, n);
  8     FOR i IN 1 .. 100000 LOOP
  9        x := DBMS_SQL.EXECUTE(c);
 10        x := DBMS_SQL.FETCH_ROWS(c);
 11        DBMS_SQL.COLUMN_VALUE(c, 1, n);
 12     END LOOP;
 13  END;
 14  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:13.05

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.

*********************************************************************************
SELECT 0 AS nds_eg
FROM
 dual

 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100000      1.87       1.66          0          0          0           0
Fetch   100000      0.78       0.70          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001      2.65       2.37          0          0          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 23     (recursive depth: 1)

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.

*********************************************************************************
SELECT 0 AS dbmssql_eg
FROM
 dual

 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute 100000      1.54       1.72          0          0          0           0
Fetch   100000      0.88       0.74          0          0          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200001      2.43       2.46          0          0          0      100000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 23     (recursive depth: 1)
********************************************************************************

acknowledgements

I first learned of this optimisation from material by Tom Kyte, either from a presentation or a thread on Ask Tom. To my knowledge, it is not documented in the online manuals.

source code

The source code for the examples in this article can be downloaded from here.

Adrian Billington, June 2004

oracle-developer.net