 | | | PL/SQL Automatic BULK | PL/SQL Automatic BULK 2005-03-18 - By Buechi Martin
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 (See ORA-01002.ora-code.com): fetch out of sequence
ORA-06512 (See ORA-06512.ora-code.com): at line 20
Regards,
Martin
--
http://www.freelists.org/webpage/oracle-l
|
|
|