Sorry, Forgot to mention Oracle 11.2.0.3 on Linux x86. On Thu, Nov 6, 2014 at 2:15 PM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote: > 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 >