Re: Probably Simple Question - Execute immediate - confused by sytax

Jonathan Lewis wrote:
>
> You beat me to it -
> Just to add a little icing on the cake, though, I
> use the following two fragments of code to
> demonstrate (a) the difference in costs and (b)
> the impact due to concurrency
>
> procedure bound(i_table_no in number default 1, i_ct in number default 500)
> is
> m_spare varchar2(6);
> m_padding varchar2(100);
> begin
> for i in 1..i_ct loop
> execute immediate
> 'select spare,padding from parse_table_' || i_table_no || '
> wher
>
> e id = :v1'
> into m_spare, m_padding
> using i;
> end loop;
> end;
>
> procedure parse_once_dynamic(
> i_table_no in number default 1,
> i_ct in number default 500
> ) is
>
> m_cursor integer;
> m_rows_processed number;
> m_spare varchar2(6);
> m_padding varchar2(100);
>
> begin
>
> if (dbms_sql.is_open(m_cursor)) then
> null;
> else
> m_cursor := dbms_sql.open_cursor;
> dbms_sql.parse(
> m_cursor,
> 'select spare, padding from parse_table_' ||
> i_table_no ||' where id = :v1',
> dbms_sql.native
> );
> dbms_sql.define_column(m_cursor,1,m_spare,6);
> dbms_sql.define_column(m_cursor,2,m_padding,100);
> end if;
>
> for i in 1..i_ct loop
>
> dbms_sql.bind_variable(m_cursor, ':v1', i);
> m_rows_processed := dbms_sql.execute(m_cursor);
>
> if dbms_sql.fetch_rows(m_cursor)>0 then
> dbms_sql.column_value(m_cursor, 1, m_spare);
> dbms_sql.column_value(m_cursor, 2, m_padding);
> end if;
>
> end loop;
>
> dbms_sql.close_cursor(m_cursor);
>
> exception
> when others then
> dbms_sql.close_cursor(m_cursor);
> raise;
> end;
>
> There is also the issue of ease of writing etc.. which
> the execute immediate wins hands-down.
>
> Basically the code selects two columns from a
> row identified by the unique key. All data is buffered.
>
> Timings:
> NDS 9.3 seconds
> dbms_sql 10.53 seconds
>
> Looking good for dbms_sql - the difference is the other
> way round if only a single column is fetched.
>
> Latching costs:
> NDS: 1.1M library cache, 40,000 shared pool
> dbms_sql 40,000 library cache
>
> Library cache access:
> NDS 20,000 gets, 160,000 pins on SQL area
> 80,000 pins on table/proc
> dbms_sql 20,000 pins on SQL area.
>
> Now, if you run two copies of the code on a twin CPU machine,
> the side-effect of all that extra latching gives you the following
> side effects.
>
> Timings (for each copy):
> NDS goes up from 9.3 seconds to 11.1 seconds
> dbms_sql goes up from 10.53 seconds to 10.67 seconds
>
> If you are going to write code that uses a dynamic SQL
> method to re-use small simple code fragments very
> frequently, then dbms_sql scales much more effectively
> because whilst it tends to use a little more CPU, it uses far
> less latching, and therefore serialises activity much less.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Thomas Kyte wrote in message ...
> >
> >
> >beg to differ. There are things native dynamic sql (NDS) does faster,
 things it
> >does slower. If you execute a statement dynamically once or twice, NDS is
> >faster. If you execute the same statement with different inputs many
 time --
> >dbms_sql is not only faster but much more scalable.
> >
> >the problem with NDS is that a look like:
> >
> > for i in 1 .. 100 loop
> > execute immediate 'insert into ' || tname || ' values ( :x )' using i;
> > end loop;
> >

As well as being the only way to have an 'upper limit' on how much you 'bulk collect'...

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"