Oracle Performance and Engineering

Dynamic SQL is a query which is not known fully when you're writing the code. In other words, it will be fully known only at runtime. In the words of Secretary of Defense Donald Rumsfeld, this could be termed "known unknowns". We use it in many cases where

There are two ways Dynamic SQL can be written -

(a) Using the "DBMS_SQL" API
(b) Using the keywords "EXECUTE IMMEDIATE" (Oracle calls this 'Native Dynamic SQL')

While using DBMS_SQL package, you need to OPEN a cursor assigned for that 'dynamic' SQL, bind it if you can, execute it and close the cursor when you're done.

In many of these cases, you'll also iterate through multiple values that will be fed to the Dyanamic SQL (possibly). I've seen many cases where the programmer just 'integrates' all the DBMS_SQL syntax within the loop. Thus, even though one single DBMS_SQL.OPEN_CURSOR call would suffice, since called within LOOP - one separate cursor is opened for each iteration of the loop.

The result?

(a) Much worse performance (demonstrated below)
(b) Your program could actually die throwing out "ORA-1000 : Max Open Cursors Exceeded" error if the number of iterations is near the value of "open_cursors" and other sessions are active too!

A testcase -

The more generic code I see most of the time -

create table t(x number, y date, z varchar2(255));

create or replace procedure p1
as
l_cursor int;
l_status int;
begin
for i in 1 .. 10000
loop
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse( l_cursor,
'insert into t (x,y,z)
values (:x,:y,:z)',
dbms_sql.native );
dbms_sql.bind_variable( l_cursor, ':x', i );
dbms_sql.bind_variable( l_cursor, ':y', sysdate+i );
dbms_sql.bind_variable( l_cursor, ':z',
'this is row ' || i, 255 );
l_status := dbms_sql.execute( l_cursor );
dbms_sql.close_cursor(l_cursor);
end loop;
end;
/


Notice, the open_cursor, parse and close_cursor calls have been made *within* the Loop. Thereby executing them 9999 extra times for a 10,000 row insert. They could easily be placed outside the loop. The tuned code then looks like -

create or replace procedure p2
as
l_cursor int default dbms_sql.open_cursor;
l_status int;
begin
dbms_sql.parse( l_cursor,
'insert into t (x,y,z)
values (:x,:y,:z)',
dbms_sql.native );

for i in 1 .. 10000
loop
dbms_sql.bind_variable( l_cursor, ':x', i );
dbms_sql.bind_variable( l_cursor, ':y', sysdate+i );
dbms_sql.bind_variable( l_cursor, ':z',
'this is row ' || i, 255 );
l_status := dbms_sql.execute( l_cursor );
end loop;

dbms_sql.close_cursor(l_cursor);
end;
/


I've used two different sets of runs. First to insert 100 rows and second, to insert 10,000 rows. I've also set plsql_optimize_level to 0 (so that Oracle does not try to tune the PL/SQL code at all!).

Here's the result - the time is obtained by "set timing on" on SQL*Plus.

P1 - (open-parse-close calls within the LOOP)

Run 1 - 0.64 second
Run 2 - 0.20 second

P2 - (open-parse-close calls only ONCE outside the LOOP)

Run 1 - 5.88 second
Run 2 - 1.99 second

However, for a quick but effective comparison between two Database side code, I like to use runstats.

Using that the difference found between two approaches can be spread out over more stuff (like, latching!) -

Difference between P1 and P2 for run2 (inserting 10,000 records) --

Run1 ran in 3775 hsecs
Run2 ran in 2544 hsecs
run 1 ran in 148.39% of the time

Name Run1 Run2 Diff
STAT...Elapsed Time 3,778 2,546 -1,232
LATCH.active checkpoint queue 5,305 611 -4,694
LATCH.redo writing 5,417 695 -4,722
STAT...undo change vector size 739,464 733,440 -6,024
LATCH.messages 10,893 1,424 -9,469
STAT...session cursor cache hi 10,032 26 -10,006
STAT...parse count (total) 10,064 52 -10,012
STAT...opened cursors cumulati 10,065 49 -10,016
STAT...consistent gets 10,345 292 -10,053
STAT...consistent gets from ca 10,345 292 -10,053
STAT...session logical reads 21,754 11,581 -10,173
LATCH.checkpoint queue latch 21,952 2,856 -19,096
STAT...redo size 2,881,572 2,861,076 -20,496
STAT...recursive calls 70,941 40,639 -30,302
LATCH.cache buffers chains 88,471 57,053 -31,418
LATCH.object queue header oper 44,356 2,234 -42,122
LATCH.session allocation 49,204 536 -48,668
LATCH.library cache lock 103,449 139 -103,310
LATCH.shared pool 144,004 40,463 -103,541
LATCH.row cache objects 129,869 752 -129,117
LATCH.library cache pin 243,979 80,460 -163,519
LATCH.library cache 317,594 80,644 -236,950

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,169,318 270,530 -898,788 432.23%

Look at the differences between P1 and P2 -- not only P2 runs much faster, but since P2 does 1 Parse call as opposed to 10,000 done by P1 -- the difference in latch resources is ENORMOUS.

That's (high latching) the real cost of re-parsing.

If we trace the runs for both P1 and P2 here's what we get --

For p1 --

insert into t (x,y,z)
values (:x,:y,:z)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10000 0.29 0.30 0 0 0 0
Execute 10000 1.00 0.90 0 10037 10503 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20000 1.29 1.20 0 10037 10503 10000

* Notice the 9999 extra PARSE calls for P1


For p2 --

insert into t (x,y,z)
values (:x,:y,:z)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 10000 0.63 0.55 0 48 10520 10000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 10001 0.64 0.55 0 48 10520 10000

Though I don't like to put things under "Best practices" document - there're always exceptions, but this can probably be a good candidate to implement there.

The statement in there would look something like -

"If you are LOOPing over a dynamic SQL and are using DBMS_SQL for that -- keep the OPEN_CURSOR, PARSE and CLOSE_CURSOR out of the LOOP."