[20180321]toad下execute as script的fetch

[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

--//正好一致.