本文共 6223 字，大约阅读时间需要 20 分钟。
[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 Production2.普通函数:
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 SALES7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING 7788 SCOTT 20 RESEARCH 7839 KING 10 ACCOUNTING 7844 TURNER 30 SALES7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES 7902 FORD 20 RESEARCH 7934 MILLER 10 ACCOUNTING14 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--//正好一致.
转载地址：http://scjpx.baihongyu.com/