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)”!
Christian Bilien's Oracle performance and tuning blog 