[20180510]sqlplus array 和 opifch2.txt

[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 <>
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:
<>.
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

3.測試特殊情況:
set arraysize 1
--//我以前提到實際上最小2.看看測試的情況:
select * from emp;

--//觀察gdb會話:
Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$31 = 140735563509568

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$32 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$33 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$34 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$35 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$36 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$37 = 2

Breakpoint 1, 0x00000000095b4cac in opifch2 ()
$38 = 2

--//可以發現即使設定最小1,實際上還是2.