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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 18 May 2001 19:46:14 +0100
Message-ID: <990211925.5143.0.nnrp-07.9e984b29@news.demon.co.uk>

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;

>

Received on Fri May 18 2001 - 13:46:14 CDT