Christian Bilien’s Oracle performance and tuning blog

February 26, 2007

Calling DDL from PL/SQL: you can’t avoid paying the parsing price

Filed under: Oracle — christianbilien @ 1:41 pm

Calling DDL from PL/SQL can be done using either of two options (if you find a third one, drop me a comment): you can call the DBMS_SQL package (available for a long time), or the newer “EXECUTE IMMEDIATE”. Let’s assume that you want to rewrite this piece of code:

begin

for i in 1..10000

loop

select order_detail_od_id_seq.nextval into ood_id from dual;

…. some action into an array indexed by i….

end loop;

end;

into:

begin

for i in 1..batch_size

loop

…. some action into an array indexed by i ….

end loop;

end;

execute immediate ‘alter sequence od_id_seq_test increment by 10000’ ;

This is much faster (the execute immediate is about 500 times faster that doing 10000 select .. .nextval…), but still not enough. The execute immediate takes about 27ms on a SPARC IV 1.3Ghz, of which 26ms is parse time. I would have liked to cut this down to a few ms. My idea was to prepare the SQL call using DBMS_SQL.PARSE, and then call DBMS_SQL.EXECUTE from the main program.

create or replace procedure testseq1(cur out number) as

BEGIN

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur,’alter sequence od_id_seq_test increment by 10000′, DBMS_SQL.NATIVE);

END;

create or replace procedure testseq2(cur in number) as

rc integer;

begin

rc := DBMS_SQL.EXECUTE(cur);

end;

 

alter session set events ‘10046 trace name context forever, level 12’;

declare cur integer;

begin

testseq1(cur);

—- testseq2(cur); Test for the time being

END;

This is where it gets interesting. Here is an excerpt of the trace:

alter session set events ‘10046 trace name context forever, level 12’

PARSING IN CURSOR #1 len=67 dep=0 uid=0 oct=47 lid=0 tim=14667711607899 hv=2605459378 ad=’e4653ed8′

declare cur integer;

begin

testseq1(cur);

—- testseq2(cur);

END;

alter sequence od_id_seq_test increment by 10000

select increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags from seq$ where obj#=:1

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9, flags=:10 where obj#=:1

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9 …

In other words, I am NOT calling DBMS_SQL.EXECUTE, but DBMS_SQL.PARSE did execute the call. Even funnier, a subsequent call to DBMS_SQL.EXECUTE does not do anything!

Metalink note 1008453.6 indeed says that the DBMS_SQL.PARSE will not only parse, but also execute the DDL.

So until now, I have to live with an “execute immediate (DDL)”!

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: