Thanks

Thanks guys.. I appreciate it. Is it me or has it been a while since Tom has been on the group?

Pretty well cleared up now,
thanks again.

On Fri, 18 May 2001 19:46:14 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> 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.
Received on Mon May 21 2001 - 10:09:53 CDT