DBMS_PROFILER and dynamic sql

From: Kenneth Naim <kennethnaim_at_gmail.com>
Date: Thu, 6 Nov 2014 14:15:17 -0500
Message-ID: <CANQD+sJobr_Q8VPNqzP8dndYhY49hJtWQthEUwhYMKrrd9fHkw_at_mail.gmail.com>



I am profiling a seeded piece of oracle code that explicitly open a cursor for a dynamic (execute immediate) merge statement that concatenates a literal value, then commits each statement. The loop runs 12k times and based upon the logging that is built into the code the loop took an hour. Looking at the dbms_profiler report it took 2 minutes for the whole procedure.

I am customizing this code to use bind variables and will likely rewrite the merge statement to avoid the loop so my question is does the dbms_profiler intentionally or legitimately ignore dynamic sql. Or is it possible that it is ignoreing the 12k unique dynamic statements?

I tried searching MOS and google, and no answers yet. I will update the list with any findings after the rewrite.

simplified version of the code

cursor c is select distinct item_id from table 1; begin

v_sql := merge into table2 using (select * from table1 where item_id=' v_sql2:=') on (....) when matched then update ... when not matched the insert ...;

open rec for c
loop
 exit when rec%notfound;
execute immediate v_sql||rec.item_id||v_sql2; commit;
end loop;

end;

Thank you,
Ken

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 06 2014 - 20:15:17 CET

Original text of this message