[20180511]PLSQL與fetchsize.txt

lfree發表於2018-05-11
SQL

[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

--//一次完成.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2154326/，如需轉載，請註明出處，否則將追究法律責任。

相關文章