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

christianbilien

12 years ago

Advertisements

Calling DDL from PL/SQL can be done using either oftwo 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:

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.EXECUTEfrom the main program.