[20170506]fetch sqlplus arraysize.txt

[20170506]fetch sqlplus arraysize.txt

http://blog.itpub.net/267265/viewspace-2138042/

--//前一陣子寫的,如果設定arrarsize=3,可以看到3條3條輸出.
--//裡面提到4秒,是因為第一次提取1條,然後在提取3條,輸出3條,在輸出快取保留1條,如此迴圈.
--//實際上使用10046跟蹤也能觀察到這種現象.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> set arraysize 3

SCOTT@test01p> @ 10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@test01p> select * from emp ;
EMPNO ENAME  JOB        MGR HIREDATE             SAL COMM DEPTNO
----- ------ --------- ---- ------------------- ---- ---- ------
 7369 SMITH  CLERK     7902 1980-12-17 00:00:00  800          20
 7499 ALLEN  SALESMAN  7698 1981-02-20 00:00:00 1600  300     30
 7521 WARD   SALESMAN  7698 1981-02-22 00:00:00 1250  500     30
 7566 JONES  MANAGER   7839 1981-04-02 00:00:00 2975          20
 7654 MARTIN SALESMAN  7698 1981-09-28 00:00:00 1250 1400     30
 7698 BLAKE  MANAGER   7839 1981-05-01 00:00:00 2850          30
 7782 CLARK  MANAGER   7839 1981-06-09 00:00:00 2450          10
 7788 SCOTT  ANALYST   7566 1987-04-19 00:00:00 3000          20
 7839 KING   PRESIDENT      1981-11-17 00:00:00 5000          10
 7844 TURNER SALESMAN  7698 1981-09-08 00:00:00 1500    0     30
 7876 ADAMS  CLERK     7788 1987-05-23 00:00:00 1100          20
 7900 JAMES  CLERK     7698 1981-12-03 00:00:00  950          30
 7902 FORD   ANALYST   7566 1981-12-03 00:00:00 3000          20
 7934 MILLER CLERK     7782 1982-01-23 00:00:00 1300          10
14 rows selected.

SCOTT@test01p> @ 10046off
Session altered.

2.觀察跟蹤檔案:
PARSING IN CURSOR #357077368 len=18 dep=0 uid=109 oct=3 lid=109 tim=8967176038 hv=862079893 ad='7ff1cb55268' sqlid='g5wp7pwtq4kwp'
select * from emp
END OF STMT
PARSE #357077368:c=109201,e=305055,p=21,cr=608,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=8967176037
EXEC #357077368:c=0,e=113,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=8967176252
WAIT #357077368: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=275 tim=8967176337
WAIT #357077368: nam='Disk file operations I/O' ela= 125 FileOperation=2 fileno=9 filetype=2 obj#=92287 tim=8967176534
WAIT #357077368: nam='db file sequential read' ela= 10710 file#=9 block#=146 blocks=1 obj#=92287 tim=8967187272
WAIT #357077368: nam='db file scattered read' ela= 610 file#=9 block#=147 blocks=5 obj#=92287 tim=8967188102
FETCH #357077368:c=0,e=11810,p=6,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=8967188191
WAIT #357077368: nam='SQL*Net message from client' ela= 350 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967188631
WAIT #357077368: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967188693
FETCH #357077368:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967188721
WAIT #357077368: nam='SQL*Net message from client' ela= 248 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189004
WAIT #357077368: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189047
FETCH #357077368:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189075
WAIT #357077368: nam='SQL*Net message from client' ela= 170 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189277
WAIT #357077368: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189331
FETCH #357077368:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189357
WAIT #357077368: nam='SQL*Net message from client' ela= 170 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189566
WAIT #357077368: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189617
FETCH #357077368:c=0,e=37,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189644
WAIT #357077368: nam='SQL*Net message from client' ela= 169 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189853
WAIT #357077368: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8967189899
FETCH #357077368:c=0,e=41,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=8967189933
STAT #357077368 id=1 cnt=14 pid=0 pos=1 obj=92287 op='TABLE ACCESS FULL EMP (cr=12 pr=6 pw=0 time=11800 us cost=3 size=532 card=14)'
*** 2017-05-06 20:16:17.450
WAIT #357077368: nam='SQL*Net message from client' ela= 6502696 driver id=1413697536 #bytes=1 p3=0 obj#=92287 tim=8973692722
CLOSE #357077368:c=0,e=20,dep=0,type=0,tim=8973692991
=====================

D:\temp>grep FETCH aa.txt
FETCH #357077368:c=0,e=11810,p=6,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=8967188191
FETCH #357077368:c=0,e=41,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967188721
FETCH #357077368:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189075
FETCH #357077368:c=0,e=36,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189357
FETCH #357077368:c=0,e=37,p=0,cr=1,cu=0,mis=0,r=3,dep=0,og=1,plh=3956160932,tim=8967189644
FETCH #357077368:c=0,e=41,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,plh=3956160932,tim=8967189933

--//注意看每行r=N的內容1,3,3,3,3,1.從這裡也可以看出fetch的數量變化.
--//總之,先取1條,再取ayysize條,輸出arraysize條,再輸出快取中保留1條,如此迴圈,取出全部記錄.