Re: DBMS_PROFILER and dynamic sql
Date: Thu, 6 Nov 2014 14:16:16 -0500
Message-ID: <CANQD+sKffYVdkyBNRhzSFL5Vgm5wQ4Mvk5LCB_1KzRYDQT--_w_at_mail.gmail.com>
Sorry,
Forgot to mention Oracle 11.2.0.3 on Linux x86.
On Thu, Nov 6, 2014 at 2:15 PM, Kenneth Naim <kennethnaim_at_gmail.com> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 06 2014 - 20:16:16 CET