[20181108]12c sqlplus rowprefetch引數4.txt

--//12cR2 可以改變預設rowprefetch引數.11g之前預設是1.透過一些測試說明問題.

--//前幾天做的測試有點亂,連結http://blog.itpub.net/267265/viewspace-2219004/.

--//重新梳理看看.

1.環境:

SCOTT@78> @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

--//資料庫11.2.0.4.但是我使用12c sqlplus做為客戶端,rowprefetch引數可以設定,測試一樣有效.

SCOTT@78>create table t as select rownum id1,1 id2 from dual connect by level<=23;

Table created.

SCOTT@78> show rowprefetch

rowprefetch 1

--//可以發現預設就是1.11g之前無法設定.

--//另外rowprefetch不能設定為0.

SCOTT@78> set rowprefetch 0

SP2-0267: rowprefetch option 0 out of range (1 through 32767)

grant EXECUTE ON dbms_lock to scott;

CREATE OR REPLACE FUNCTION SCOTT.sleep (seconds IN NUMBER)

RETURN NUMBER

AS

BEGIN

sys.DBMS_LOCK.sleep (seconds);

RETURN seconds;

END;

/

CREATE OR REPLACE FUNCTION get_dept (p_deptno dept.deptno%TYPE)

RETURN dept.dname%TYPE

DETERMINISTIC

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;

/

2.建立測試指令碼:

R:> cat aa.txt

set timing on

set arraysize &1

set rowprefetch &2

alter session set events '10046 trace name context forever, level 12';

select rownum ,t.*,sleep(id2) n10,&&1 arraysize ,&&2 rowprefetch from t;

--select rownum ,emp.*,get_dept(deptno) c10,&&1 arraysize ,&&2 rowprefetch from emp;

alter session set events '10046 trace name context off';

set timing off

--//執行指令碼時,第1個參數列示arraysize,第2個參數列示rowprefetch.

3.測試:

SCOTT@78> @ aa.txt 5 4

ROWNUM ID1 ID2 N10 ARRAYSIZE ROWPREFETCH

---------- ---------- ---------- ---- ---------- -----------

1 1 1 1 5 4

2 2 1 1 5 4

3 3 1 1 5 4

4 4 1 1 5 4

5 5 1 1 5 4

6 6 1 1 5 4

7 7 1 1 5 4

8 8 1 1 5 4

9 9 1 1 5 4

10 10 1 1 5 4

11 11 1 1 5 4

12 12 1 1 5 4

13 13 1 1 5 4

14 14 1 1 5 4

15 15 1 1 5 4

16 16 1 1 5 4

17 17 1 1 5 4

18 18 1 1 5 4

19 19 1 1 5 4

20 20 1 1 5 4

21 21 1 1 5 4

22 22 1 1 5 4

23 23 1 1 5 4

23 rows selected.

FETCH #140079390128648:c=1000,e=4004469,p=0,cr=3,cu=0,mis=0,r=4,dep=0,og=1,plh=2402761124,tim=1541647376650608

FETCH #140079390128648:c=1000,e=5004986,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=1541647381656601

FETCH #140079390128648:c=999,e=5005185,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=1541647386662564

FETCH #140079390128648:c=1000,e=5005145,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=1541647391668598

FETCH #140079390128648:c=1000,e=4004133,p=0,cr=1,cu=0,mis=0,r=4,dep=0,og=1,plh=2402761124,tim=1541647395673629

--//fetch 4,5,5,5,4. 而顯示行數5,5,5,8.

--//你可以發現在輸出前等待至少9秒(我使用手機秒錶測試).也就是在輸出前已經fetch 9條記錄.

--//可以理解為先fetch=4(rowprefetch=4),不足arraysize=5,再fetch=5.必須等fetch完成了(這時已經過了9秒),才會顯示輸出5條,

--//剩餘4條在輸出快取(不足),再fetch=5,顯示輸出5條.剩餘4條在輸出快取.再fetch=5,顯示輸出5條,剩餘4條在輸出快取,這時再

--//fetch=4僅僅剩下4條記錄,這時已經fetch完成,快取的8條一起輸出,不再按照5條輸出.

SCOTT@78> @ aa.txt 5 9

...

--//結果不再貼出略.

FETCH #140079394727376:c=2000,e=9009422,p=0,cr=3,cu=0,mis=0,r=9,dep=0,og=1,plh=2402761124,tim=1541649678068572

FETCH #140079394727376:c=1000,e=10009690,p=0,cr=1,cu=0,mis=0,r=10,dep=0,og=1,plh=2402761124,tim=1541649688079548

FETCH #140079394727376:c=0,e=4003868,p=0,cr=1,cu=0,mis=0,r=4,dep=0,og=1,plh=2402761124,tim=1541649692084551

--//fetch 9,10,4. 而顯示行數5,10,8.

--//你可以發現第2次fetch不再等於arraysize,而是2*arraysize.

SCOTT@78> @ aa.txt 6 11

..

--//結果不再貼出略.

FETCH #140079389953128:c=2000,e=11010343,p=0,cr=3,cu=0,mis=0,r=11,dep=0,og=1,plh=2402761124,tim=1541660682428524

FETCH #140079389953128:c=2000,e=12011616,p=0,cr=1,cu=0,mis=0,r=12,dep=0,og=1,plh=2402761124,tim=1541660694441553

FETCH #140079389953128:c=0,e=14,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2402761124,tim=1541660694442817

--//fetch 11,12,1. 而顯示行數6,17

--//你可以發現第2次fetch不再等於arraysize,而是2*arraysize.

--//注:最後1次fetch r=0,很快,所以看到第2次顯示17,實際上顯示行數還是6,12,5.

SCOTT@78> @ aa.txt 3 7

..

--//結果不再貼出略.

FETCH #140079389953128:c=2000,e=7006724,p=0,cr=3,cu=0,mis=0,r=7,dep=0,og=1,plh=2402761124,tim=1541660899414555

FETCH #140079389953128:c=2000,e=9008694,p=0,cr=1,cu=0,mis=0,r=9,dep=0,og=1,plh=2402761124,tim=1541660908424586

FETCH #140079389953128:c=999,e=7006930,p=0,cr=1,cu=0,mis=0,r=7,dep=0,og=1,plh=2402761124,tim=1541660915432531

--//fetch 7,9,7. 而顯示行數6,9,8

--//你可以發現第2次fetch不再等於arraysize,而是3*arraysize.

SCOTT@78> @ aa.txt 2 7

...

--//結果不再貼出略.

FETCH #140079390530944:c=1000,e=7007159,p=0,cr=3,cu=0,mis=0,r=7,dep=0,og=1,plh=2402761124,tim=1541661275532542

FETCH #140079390530944:c=1000,e=8007723,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=1541661283541636

FETCH #140079390530944:c=2000,e=8007970,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=1541661291550622

FETCH #140079390530944:c=0,e=19,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2402761124,tim=1541661291551655

--//fetch 7,8,8,0. 而顯示行數6,8,9.

--//你可以發現第2次fetch不再等於arraysize,而是4*arraysize.

--//再次出現最後一次fetch r=0的情況,實際上顯示行數是6,8,8,1.

--//這樣可以得到一個規律:

--//你可以發現第2次fetch不再等於arraysize,而是N*arraysize.第2次fetch引數應該是 ceil(rowprefetch/arraysize)*arraysize.

3.小結:

--//可以看出一些規律:

--//1.fetch 第1次數量與引數rowprefetch相關,當然必須小於返回記錄的數量.

--//2.fetch 第X次數量(X>=2)與引數arraysize的倍數N有關. N=ceil(rowprefetch/arraysize)

--//3.fetch 最後一次應該等於剩餘記錄.不會大於ceil(rowprefetch/arraysize)*arraysize.

--//4.fetch 最後一次有可能是0.

--//5.顯示行數第1次 floor(rowprefetch/arraysize)*arraysize.

--//6.顯示行數第X次(X>=2) ceil(rowprefetch/arraysize)*arraysize.

--//7.顯示行數最後一次等於剩餘在快取的行數.

--//這樣連結中http://blog.itpub.net/267265/viewspace-2219004/.看到的情況就很好解析了.

--//實際上這其中細節不重要,你可以理解改變12c下改變sqlplus的rowprefetch引數,有可能隱含改變了fetch方式.

--//第1次fetch = rowpefetch, 第2次 fetch 等於 ceil(rowprefetch/arraysize)*arraysize就足夠了.

--//最後再分析前面測試我遇到的一個問題.實際上某種巧合.最後一次fetch r=0導致的情況.

set arraysize 5

set rowprefetch 4

@ 10046on 12

select rownum ,emp.*,get_dept(deptno) c10,5 arraysize ,4 rowprefetch from emp;

@ 10046off

FETCH #140079390567504:c=1000,e=2002160,p=0,cr=10,cu=0,mis=0,r=4,dep=0,og=1,plh=1498225739,tim=1541661929029851

FETCH #140079390567504:c=1000,e=3002851,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=1541661932033872

FETCH #140079390567504:c=2000,e=3002868,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=1541661935037842

FETCH #140079390567504:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1498225739,tim=1541661935038827

--//fetch 4,5,5,0 而顯示行數5,9.我當時的疑問是為什麼顯示行數不是5,5,4.

--//為什麼最後9行全部一次顯示出來.實際上問題在第4次fetch=0,第4次fetch就很快,這樣看到的顯示行數就是5,9.

--//實際上就是5,5,4.

[20181108]12c sqlplus rowprefetch引數4.txt

相關文章