共计 5775 个字符，预计需要花费 15 分钟才能阅读完成。
Oracle arraysize 的研究是怎样的，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
SYS@proc desc aaa;
Name Null? Type
—————————————– ——– —————————-
ID1 NUMBER(38)
ID2 NUMBER(38)
ID3 NUMBER(38)
ID4 NUMBER(38)
SYS@proc select * from aaa;
ID1 ID2 ID3 ID4
———- ———- ———- ———-
1 1 1 1
1 1 1 0
SYS@proc select * from aaa1;
ID1 ID2 ID3 ID4
———- ———- ———- ———-
1 1 1 0
1 1 1 1
SYS@proc select * from aaa where id1/id2=1 and id3/id4=1;
ERROR:
ORA-01476: divisor is equal to zero
no rows selected
SYS@proc set arraysize 1
SYS@proc /
ID1 ID2 ID3 ID4
———- ———- ———- ———-
1 1 1 1
ERROR:
ORA-01476: divisor is equal to zero
SYS@proc select * from aaa1 where id1/id2=1 and id3/id4=1;
select * from aaa1 where id1/id2=1 and id3/id4=1
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
SYS@proc drop table aaa2 purge;
Table dropped.
SYS@proc create table aaa2 (id1 int,id2 int,id3 int,id4 int,flag int);
Table created.
SYS@proc insert into aaa2 values(1,1,1,1,1);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,2);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,3);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,4);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,5);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,6);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,7);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,8);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,9);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,1,10);
1 row created.
SYS@proc insert into aaa2 values(1,1,1,0,0);
1 row created.
SYS@proc commit;
Commit complete.
SYS@proc analyze table aaa2 compute statistics;
Table analyzed.
SYS@proc set arraysize 1
SYS@proc select * from aaa2 where id1/id2=1 and id3/id4=1;
ID1 ID2 ID3 ID4 FLAG
———- ———- ———- ———- ———-
1 1 1 1 1
1 1 1 1 2
1 1 1 1 3
1 1 1 1 4
1 1 1 1 5
1 1 1 1 6
1 1 1 1 7
1 1 1 1 8
1 1 1 1 9
ERROR:
ORA-01476: divisor is equal to zero
9 rows selected.
– 从大量结果上看，arraysize 为 1 或者 2 是一样的。这里为 9 预见。
SYS@proc select * from aaa2;
ID1 ID2 ID3 ID4 FLAG
———- ———- ———- ———- ———-
1 1 1 1 1
1 1 1 1 2
1 1 1 1 3
1 1 1 1 4
1 1 1 1 5
1 1 1 1 6
1 1 1 1 7
1 1 1 1 8
1 1 1 1 9
1 1 1 1 10
1 1 1 0 0
11 rows selected.
SYS@proc
SYS@proc set arraysize 2
SYS@proc select * from aaa2 where id1/id2=1 and id3/id4=1;
ID1 ID2 ID3 ID4 FLAG
———- ———- ———- ———- ———-
1 1 1 1 1
1 1 1 1 2
1 1 1 1 3
1 1 1 1 4
1 1 1 1 5
1 1 1 1 6
1 1 1 1 7
1 1 1 1 8
ERROR:
ORA-01476: divisor is equal to zero
8 rows selected.
语句对应的 10046，可以看出是返回了 9 行，但是从上边看是 8 行，很奇怪。
PARSING IN CURSOR #140496887317072 len=48 dep=0 uid=0 oct=3 lid=0 tim=1514130832420098 hv=3007681721 ad= 812bd000 sqlid= 7cfwyuytnb55t
select * from aaa2 where id1/id2=1 and id3/id4=1
END OF STMT
PARSE #140496887317072:c=0,e=1221,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420093
EXEC #140496887317072:c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2576342259,tim=1514130832420198
WAIT #140496887317072: nam= SQL*Net message to client ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420238
FETCH #140496887317072:c=0,e=64,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832420331
WAIT #140496887317072: nam= SQL*Net message from client ela= 476 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420842
WAIT #140496887317072: nam= SQL*Net message to client ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832420907
FETCH #140496887317072:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832420924
WAIT #140496887317072: nam= SQL*Net message from client ela= 146 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421092
WAIT #140496887317072: nam= SQL*Net message to client ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421137
FETCH #140496887317072:c=0,e=28,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421151
WAIT #140496887317072: nam= SQL*Net message from client ela= 66 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421237
WAIT #140496887317072: nam= SQL*Net message to client ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421277
FETCH #140496887317072:c=0,e=26,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421290
WAIT #140496887317072: nam= SQL*Net message from client ela= 60 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421369
WAIT #140496887317072: nam= SQL*Net message to client ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421407
FETCH #140496887317072:c=0,e=25,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2576342259,tim=1514130832421420
WAIT #140496887317072: nam= SQL*Net message from client ela= 410 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421848
WAIT #140496887317072: nam= SQL*Net message to client ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832421907
FETCH #140496887317072:c=0,e=63,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2576342259,tim=1514130832421956
STAT #140496887317072 id=1 cnt=10 pid=0 pos=1 obj=88977 op= TABLE ACCESS FULL AAA2 (cr=7 pr=0 pw=0 time=94 us cost=2 size=10 card=1)
WAIT #140496887317072: nam= SQL*Net break/reset to client ela= 28 driver id=1650815232 break?=1 p3=0 obj#=-1 tim=1514130832422110
WAIT #140496887317072: nam= SQL*Net break/reset to client ela= 120 driver id=1650815232 break?=0 p3=0 obj#=-1 tim=1514130832422252
WAIT #140496887317072: nam= SQL*Net message from client ela= 595 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1514130832422889
CLOSE #140496887317072:c=0,e=14,dep=0,type=0,tim=1514130832422981
=====================
所以其实 arraysize 是 1 还是 2，还是存在区别的。不过从 10046 上看却是没多大区别，从全表扫描或者其他能够正常返回结果的情况下，值为 1 和 2 是完全一样的。
但是实际上无论 arraysize 的值是多少，默认第一行单独会直接发送反馈给用户的，所以应该是不用设置的。
后边研究的逻辑读也有点问题，在 12C 里边差别更大。
看完上述内容，你们掌握 Oracle arraysize 的研究是怎样的的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！
