RE: PL/SQL Automatic BULK

From: Buechi Martin <Martin.Buechi_at_avaloq.com>
Date: Fri, 18 Mar 2005 08:40:07 +0100
Message-ID: <5735FA5BE1D07E44ADA5560214020D1907013B@WCLUSTER2.sys.net>

Bruce,

>>Can you tell if bulkification is performed without running the p/sql.

>If PLSQL_OPTIMIZE_LEVEL is set to 2 the feature is activated.

Furthermore, plsql_debug must be false.

K.B1D5.AVALOQ> set serveroutput on size 100000 K.B1D5.AVALOQ> create table t(x number);

Table created.

K.B1D5.AVALOQ> insert into t select 1 from all_objects where rownum < 10000;

9999 rows created.

K.B1D5.AVALOQ> show parameter plsql_optimize_level

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
plsql_optimize_level                 integer     2

K.B1D5.AVALOQ> alter session set plsql_debug=3Dfalse;

Session altered.

K.B1D5.AVALOQ> create or replace procedure prefetch(

  2    i_x                        pls_integer :=3D 1

  3 )
  4 as

  5    l_cnt                      pls_integer :=3D 0;
  6    l_x                        number;

  7 begin
  8 savepoint sp;
  9 update t
 10 set x =3D x + 1
 11 where rownum < 1000;
 12 for c in (select * from t where x =3D i_x) loop

 13      l_x :=3D c.x;
 14      l_cnt :=3D l_cnt + 1;
 15      rollback to sp;

 16 end loop;
 17 exception
 18 when others then
 19 dbms_output.put_line('Prefetched: ' || l_cnt);  20 end;
 21 /

Procedure created.

K.B1D5.AVALOQ> exec prefetch
Prefetched: 100

PL/SQL procedure successfully completed.

K.B1D5.AVALOQ> alter session set plsql_debug=3Dtrue;

Session altered.

K.B1D5.AVALOQ> alter procedure prefetch compile;

Procedure altered.

K.B1D5.AVALOQ> exec prefetch
Prefetched: 1

PL/SQL procedure successfully completed.

As pointed out by Chris, the implicit bulkification only works for implicit cursors:

K.B1D5.AVALOQ> -- No automatic prefetching for explicit cursors K.B1D5.AVALOQ> alter session set plsql_debug=3Dfalse;

Session altered.

K.B1D5.AVALOQ> set serveroutput on
K.B1D5.AVALOQ> declare

  2    l_cur                      sys_refcursor;
  3    l_cnt                      pls_integer :=3D 0;
  4    l_t                        t%rowtype;

  5 begin
  6 savepoint sp;
  7 update t set x =3D x + 1 where rownum < 1000;   8 open l_cur for 'select * from t';   9 loop

 10      fetch l_cur into l_t;
 11      exit when l_cur%notfound;
 12      l_cnt :=3D l_cnt + 1;
 13      if l_cnt =3D 1 then
 14        rollback to sp;
 15      end if;

 16 end loop;
 17 exception
 18 when others then

 19      dbms_output.put_line('Fetched: ' || l_cnt);
 20      raise;

 21 end;
 22 /
Fetched: 1
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 20

Regards,

Martin

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 18 2005 - 02:44:12 CST