[20180321]toad下execute as script的fetch大小.txt
1.环境：
SCOTT@book> @ &r/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
2.普通函数:
CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)
RETURN dept.dname%TYPE
IS
l_dname dept.dname%TYPE;
BEGIN
DBMS_LOCK.sleep (1);
SELECT dname
INTO l_dname
FROM dept
WHERE deptno = p_deptno;
RETURN l_dname;
END;
/
3.测试一:
--//如果我设置array=5,在sqlplus下执行:
set array 5
select empno, ename, deptno, get_dept(deptno) c20 from emp;
SCOTT@book> set array 5
SCOTT@book> select empno, ename, deptno, get_dept(deptno) c20 from emp;
EMPNO ENAME DEPTNO C20
---------- ---------- ---------- --------------------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTING
14 rows selected.
Elapsed: 00:00:09.01
--//按照sqlplus的fetch的情况:
20 1
30 30 20 30 30 2
10 20 10 30 20 3
30 20 10 3
--//共9秒.
4.测试二:
--//如果在toad下选择execute as script:
--//注我前面的测试是array=15.与sqlplus下的设置无关.应该是缺省设置.
set timing on
set array 5
set pagesize 9999
select empno, ename, deptno, get_dept(deptno) c20 from emp;
--//按照昨天toad下的测试,fetch第一次设置array的数量+1,后面才是fetch的array的数量.
20 30 30 20 30 30 2
10 20 10 30 20 3
30 20 10 3
--//而实际上执行时间是
14 rows selected.
Elapsed: 00:00:03.03
--//从跟踪文件也可以发现:
=====================
PARSING IN CURSOR #139775580095920 len=58 dep=0 uid=83 oct=3 lid=83 tim=1521616861157255 hv=3016795818 ad='7b8ed5d8' sqlid='8f7cqdqtx19pa'
select empno, ename, deptno, get_dept(deptno) c20 from emp
END OF STMT
PARSE #139775580095920:c=3999,e=4008,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3956160932,tim=1521616861157253
EXEC #139775580095920:c=0,e=26,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3956160932,tim=1521616861157380
WAIT #139775580095920: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=407 tim=1521616861157444
*** 2018-03-21 15:21:02.158
WAIT #139775580095920: nam='PL/SQL lock timer' ela= 1000296 duration=0 p2=0 p3=0 obj#=407 tim=1521616862158061
=====================
PARSING IN CURSOR #139775573855912 len=42 dep=1 uid=83 oct=3 lid=83 tim=1521616862158511 hv=328320763 ad='7b8bb058' sqlid='56r5sd49t3jrv'
SELECT DNAME FROM DEPT WHERE DEPTNO = :B1
END OF STMT
PARSE #139775573855912:c=0,e=313,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1521616862158509
BINDS #139775573855912:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f20096e4fc8 bln=22 avl=02 flg=05
value=20
EXEC #139775573855912:c=1000,e=1163,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=2852011669,tim=1521616862159773
FETCH #139775573855912:c=0,e=24,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=2852011669,tim=1521616862159875
STAT #139775573855912 id=1 cnt=1 pid=0 pos=1 obj=87106 op='TABLE ACCESS BY INDEX ROWID DEPT (cr=2 pr=0 pw=0 time=20 us cost=1 size=13 card=1)'
STAT #139775573855912 id=2 cnt=1 pid=1 pos=1 obj=87107 op='INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=13 us cost=0 size=0 card=1)'
CLOSE #139775573855912:c=0,e=2,dep=1,type=3,tim=1521616862160024
*** 2018-03-21 15:21:03.161
WAIT #139775580095920: nam='PL/SQL lock timer' ela= 1000970 duration=0 p2=0 p3=0 obj#=407 tim=1521616863161065
BINDS #139775573855912:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f20098ca1b0 bln=22 avl=02 flg=05
value=30
EXEC #139775573855912:c=0,e=123,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2852011669,tim=1521616863161320
FETCH #139775573855912:c=0,e=20,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=2852011669,tim=1521616863161392
CLOSE #139775573855912:c=0,e=2,dep=1,type=3,tim=1521616863161456
*** 2018-03-21 15:21:04.162
WAIT #139775580095920: nam='PL/SQL lock timer' ela= 1000540 duration=0 p2=0 p3=0 obj#=407 tim=1521616864162061
BINDS #139775573855912:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7f20098ca1b0 bln=22 avl=02 flg=05
value=10
EXEC #139775573855912:c=0,e=360,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2852011669,tim=1521616864162524
FETCH #139775573855912:c=0,e=22,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=1,plh=2852011669,tim=1521616864162609
CLOSE #139775573855912:c=0,e=3,dep=1,type=3,tim=1521616864162674
FETCH #139775580095920:c=4000,e=3005256,p=0,cr=12,cu=0,mis=0,r=14,dep=0,og=1,plh=3956160932,tim=1521616864162741
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
STAT #139775580095920 id=1 cnt=14 pid=0 pos=1 obj=87108 op='TABLE ACCESS FULL EMP (cr=6 pr=0 pw=0 time=75 us cost=3 size=182 card=14)'
WAIT #139775580095920: nam='SQL*Net message from client' ela= 142367 driver id=1413697536 #bytes=1 p3=0 obj#=407 tim=1521616864305223
CLOSE #139775580095920:c=0,e=12,dep=0,type=0,tim=1521616864305315
=====================
--//注意看下划线实际上提取的r=14,也就是跟前面的array设置无关.toad下execute as script的fetch到底大小如何?
5.建立大一点的表测试:
create table t as select * from all_objects order by DBMS_RANDOM.random;
create index i_t_object_id on t(object_id);
--//分析略.
set timing on
set array 5
set pagesize 0
select * from t;
--//跟踪并且保存fetch的结果:
Fetch # CPU Time Elapsed Time Physical Reads Consistent Reads Current Reads Rows Processed Time Completed
1 0.002000s 0.001442s 20 10 0 501
2 0.001999s 0.002474s 23 15 0 1000
3 0.002000s 0.002569s 15 15 0 1000
4 0.003000s 0.002510s 15 16 0 1000
5 0.002000s 0.002341s 15 15 0 1000
6 0.002000s 0.002479s 15 15 0 1000
7 0.003000s 0.002377s 7 15 0 1000
8 0.001999s 0.002390s 8 16 0 1000
9 0.003000s 0.003188s 252 15 0 1000
....
81 0.002999s 0.002334s 0 15 0 1000
82 0.002000s 0.002361s 0 15 0 1000
83 0.001999s 0.002258s 0 16 0 1000
84 0.001999s 0.002358s 0 15 0 1000
85 0.002000s 0.002373s 0 15 0 1000
86 0.001000s 0.000863s 0 5 0 260
--//可以发现测试跟昨天的不同.先fetch 501,很奇怪的数值.然后才是1000,最后余下的260.
--//1000*84+501+260 = 84761
SCOTT@book> select count(*) from t;
COUNT(*)
----------
84761
--//正好一致.
