[20180510]sqlplus array 和 opifch2.txt-优快云博客

本文通过实际案例展示了如何在SQLPlus中调整arraysize参数来优化数据检索过程。揭示了首次检索与后续批次检索的不同行为，并针对特殊设置进行了验证。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[20180510]sqlplus array 和 opifch2.txt

--//我以前通过10046跟踪发现fetchsize的大小,一般在sqlplus下第1个总是1条,然后是arraysize大小.
--//也可以通过别的方式观察,通过例子说明问题.
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> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
94 31 3282 DEDICATED 3283 31 15 alter system kill session '94,31' immediate;

SCOTT@book> set arraysize 3
SCOTT@book> select count(*) from emp;
COUNT(*)
----------
14

2./打开另外终端回话,执行:
8]$ gdb -p 3283
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-45.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Attaching to process 3283
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle...(no debugging symbols found)...done.
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libodm11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libodm11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libcell11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libcell11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxp11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxp11.so
Reading symbols from /lib64/librt.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/librt.so.1
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnnz11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnnz11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libclsra11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libclsra11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libdbcfg11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libdbcfg11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libhasgen11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libhasgen11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libskgxn2.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocr11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocr11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrb11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrb11.so
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrutl11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libocrutl11.so
Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib64/libnuma.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libnuma.so.1
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
Reading symbols from /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so...(no debugging symbols found)...done.
Loaded symbols for /u01/app/oracle/product/11.2.0.4/dbhome_1/lib/libnque11.so
0x000000379a00da70 in __read_nocancel () from /lib64/libpthread.so.0

--//输入如下:
(gdb) b opifch2
Breakpoint 1 at 0x95b4cac
(gdb) command 1
Type commands for when breakpoint 1 is hit, one per line.
End with a line saying just "end".
>p/d $rcx
>c
>end
(gdb) set pagination off
(gdb) c
Continuing.

2.测试:
SCOTT@book> select * from emp;
...
SCOTT@book> select * from emp;
SCOTT@book> select * from emp;

--//观察gdb会话:(注从第3次观察,因为前面的执行存在递归)
Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$21 = 140735563509568

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$22 = 3

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$23 = 3

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$24 = 3

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$25 = 3

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$26 = 3
--//第一次总是140735563509568.后面都是3次.

SCOTT@book> set arraysize 5
SCOTT@book> select * from emp;

--//观察gdb会话:
Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$27 = 140735563509568

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$28 = 5

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$29 = 5

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$30 = 5