[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条,如此循环,取出全部记录.
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条,如此循环,取出全部记录.
