[20180511]PLSQL与fetchsize.txt
--//测试看看PLSQL中cursor的fetchsize:
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> show array
arraysize 200
SCOTT@book> create table t as select * from all_objects;
Table created.
SCOTT@book> select count(*) from t;
COUNT(*)
----------
84781
--//分析表略.
2.测试1:
--//建立脚本test2.sql
declare
cursor c_sql is select OBJECT_ID from t;
type t_sql is table of c_sql%ROWTYPE index by pls_integer;
v_sql t_sql;
v_x number;
begin
for i in c_sql loop
v_x:=v_x+i.OBJECT_ID ;
end loop;
end;
/
2.测试:
SCOTT@book> @ test2.sql
PL/SQL procedure successfully completed.
--//先执行一次避免一些递归.
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> @ test2.sql
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046off
Session altered.
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10413.trc
FETCH #139921268357584:c=1000,e=580,p=20,cr=4,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567077606
FETCH #139921268357584:c=0,e=65,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567077744
FETCH #139921268357584:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567077860
FETCH #139921268357584:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567077973
FETCH #139921268357584:c=0,e=64,p=0,cr=3,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567078099
FETCH #139921268357584:c=1000,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567078213
...
FETCH #139921268357584:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567179481
FETCH #139921268357584:c=0,e=62,p=0,cr=3,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567179593
FETCH #139921268357584:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567179704
FETCH #139921268357584:c=0,e=61,p=0,cr=3,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567179815
FETCH #139921268357584:c=0,e=61,p=0,cr=2,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567179932
FETCH #139921268357584:c=0,e=72,p=0,cr=3,cu=0,mis=0,r=100,dep=1,og=1,plh=1601196873,tim=1526006567180055
FETCH #139921268357584:c=0,e=73,p=0,cr=2,cu=0,mis=0,r=81,dep=1,og=1,plh=1601196873,tim=1526006567180181
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10413.trc|wc
848 1696 89261
--//847*100+81 = 84781
--//可以发现每次fetch都是100.不受set array参数的控制.
3.测试使用bulk collect into的情况:
$ cat test3.sql
declare
cursor c_sql is select OBJECT_ID from t;
type t_sql is table of c_sql%ROWTYPE index by pls_integer;
v_sql t_sql;
v_x number;
begin
open c_sql;
loop
fetch c_sql bulk collect into v_sql limit &1;
exit when c_sql%NOTFOUND;
for i in v_sql.first..v_sql.last loop
v_x:=v_x+v_sql(i).object_id;
end loop;
end loop;
close c_sql;
end;
/
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> @ test3.sql 400
old 9: fetch c_sql bulk collect into v_sql limit &1;
new 9: fetch c_sql bulk collect into v_sql limit 400;
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046off
Session altered.
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10488.trc | head
FETCH #139680790747072:c=999,e=827,p=20,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897362160
FETCH #139680790747072:c=0,e=229,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897362549
FETCH #139680790747072:c=1000,e=299,p=8,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897362939
FETCH #139680790747072:c=0,e=374,p=15,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897363403
FETCH #139680790747072:c=0,e=220,p=0,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897363707
FETCH #139680790747072:c=0,e=324,p=15,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897364110
FETCH #139680790747072:c=0,e=221,p=0,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897364410
FETCH #139680790747072:c=0,e=218,p=0,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897364706
FETCH #139680790747072:c=0,e=326,p=15,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897365116
FETCH #139680790747072:c=0,e=220,p=0,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897365415
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10488.trc | tail
FETCH #139680790747072:c=0,e=220,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897429478
FETCH #139680790747072:c=0,e=219,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897429776
FETCH #139680790747072:c=1000,e=230,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897430083
FETCH #139680790747072:c=0,e=220,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897430383
FETCH #139680790747072:c=0,e=217,p=0,cr=6,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897430684
FETCH #139680790747072:c=1000,e=219,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897430980
FETCH #139680790747072:c=0,e=221,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897431290
FETCH #139680790747072:c=0,e=220,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897431594
FETCH #139680790747072:c=999,e=221,p=0,cr=7,cu=0,mis=0,r=400,dep=1,og=1,plh=1601196873,tim=1526006897431894
FETCH #139680790747072:c=0,e=247,p=0,cr=7,cu=0,mis=0,r=381,dep=1,og=1,plh=1601196873,tim=1526006897432218
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10488.trc | wc
212 424 22635
--//受参数的限制.
--//211*400+381 = 84781
4.测试使用bulk collect into的情况(不加limit的情况):
$ cat test4.sql
declare
cursor c_sql is select OBJECT_ID from t;
type t_sql is table of c_sql%ROWTYPE index by pls_integer;
v_sql t_sql;
v_x number;
begin
open c_sql;
loop
fetch c_sql bulk collect into v_sql ;
exit when c_sql%NOTFOUND;
for i in v_sql.first..v_sql.last loop
v_x:=v_x+v_sql(i).object_id;
end loop;
end loop;
close c_sql;
end;
/
SCOTT@book> @ test4.sql
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046on 12
Session altered.
SCOTT@book> @ test4.sql
PL/SQL procedure successfully completed.
SCOTT@book> @ &r/10046off
Session altered.
$ grep FETCH /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_10539.trc | head
FETCH #140346985642920:c=80988,e=82954,p=1211,cr=1213,cu=0,mis=0,r=84781,dep=1,og=1,plh=1601196873,tim=1526007080754390
--//一次完成.
