DBMS_PROFILER and dynamic sql
- From: Kenneth Naim <kennethnaim@xxxxxxxxx>
- To: oracle-l-freelists <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 6 Nov 2014 14:15:17 -0500
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
Other related posts:
