• [20200824]12c sqlplus rowprefetch arraysize 显示行数量的关系.txt


    [20200824]12c sqlplus rowprefetch arraysize 显示行数量的关系.txt

    --//以前写的:
    [20181108]12c sqlplus rowprefetch参数4.txt => http://blog.itpub.net/267265/viewspace-2219260/
    [20181109]12c sqlplus rowprefetch参数5.txt => http://blog.itpub.net/267265/viewspace-2219334/

    --//别人问测试一些细节问题,说真的当时测试完成就再没关注这个问题,这种问题对于实际应用根本不重要.前台应用不会使用
    --//sqlplus.

    1.fetch规律:

    --//我当时测试总结一些规律:
    --//1.fetch 第1次数量等于rowprefetch.当然必须小于返回记录的数量.
    --//2.fetch 第X次数量(X>=2)与参数arraysize的倍数N有关. N=floor(rowprefetch/arraysize+1), 等于N*arraysize.
    --//3.fetch 最后一次应该等于剩余记录.不会大于floor(rowprefetch/arraysize+1)*arraysize.
    --//4.fetch 最后一次有可能是0.
    --//也就是fetch的顺序:
    rowprefetch,(floor(rowprefetch/arraysize)+1)*arraysize,(floor(rowprefetch/arraysize)+1)*arraysize,...,剩下的记录.
    --//注:我当时测试时忽略了rowprefetch=arraysize的情况.使用floor代替ceil才是正确的.

    2.显示行数量规律:

    --//而显示记录时看到的情况并不对应fetch的记录数量.以前的分析有误.仅仅需要记住几点点,我自己的总结:
    --//1.显示输出行数 第1次 floor(rowprefetch/arraysize)*arraysize.
    --//注:.如果rowprefetch < arraysize,第1次fetch后,不足arraysize数量.不会马上输出,而是等待下一个fetch完成,再输出.

    --//2.显示输出行数 第Y次(Y>=2) floor((前次剩下的记录+本次fetch的记录)/arraysize)*arraysize.
    --//注:因为前次剩下的记录小于arraysize,这样显示输出行数=floor(rowprefetch/arraysize+1)*arraysize.

    --//3.显示输出行数 最后1次比较特殊,是全部输出.判断这个依据是最后fetch的数量<(floor(rowprefetch/arraysize)+1)*arraysize,
    --//  表示已没有记录需要fetch.
    --//通过下面例子说明:

    3.再做一个测试说明问题:
    SYS@test> @ ver1
    PORT_STRING                    VERSION        BANNER                                                                               CON_ID
    ------------------------------ -------------- -------------------------------------------------------------------------------- ----------
    IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

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

    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;
    /

    $ 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;
    alter session set events '10046 trace name context off';
    set timing off
    quit

    $ cat ~/bin/ts.awk
    #! /bin/bash
    awk '{ print strftime("[%Y-%m-%d %H:%M:%S]"), $0 }'

    $ sqlplus -s -l scott/btbtms@test01p @ aa.txt 2 7 | ~/bin/ts.awk
    [2020-08-24 20:31:25]
    [2020-08-24 20:31:25] Session altered.
    [2020-08-24 20:31:25]
    [2020-08-24 20:31:25] Elapsed: 00:00:00.01
    [2020-08-24 20:31:25] old   1: select rownum,t.*,sleep(id2) n10,&&1 arraysize ,&&2 rowprefetch from t
    [2020-08-24 20:31:25] new   1: select rownum,t.*,sleep(id2) n10,2 arraysize ,7 rowprefetch from t
    [2020-08-24 20:31:32]
    [2020-08-24 20:31:32] ROWNUM  ID1  ID2  N10  ARRAYSIZE ROWPREFETCH
    [2020-08-24 20:31:32] ------ ---- ---- ---- ---------- -----------
    [2020-08-24 20:31:32]      1    1    1    1          2           7
    [2020-08-24 20:31:32]      2    2    1    1          2           7
    [2020-08-24 20:31:32]      3    3    1    1          2           7
    [2020-08-24 20:31:32]      4    4    1    1          2           7
    [2020-08-24 20:31:32]      5    5    1    1          2           7
    [2020-08-24 20:31:32]      6    6    1    1          2           7
    [2020-08-24 20:31:40]      7    7    1    1          2           7
    [2020-08-24 20:31:40]      8    8    1    1          2           7
    [2020-08-24 20:31:40]      9    9    1    1          2           7
    [2020-08-24 20:31:40]     10   10    1    1          2           7
    [2020-08-24 20:31:40]     11   11    1    1          2           7
    [2020-08-24 20:31:40]     12   12    1    1          2           7
    [2020-08-24 20:31:40]     13   13    1    1          2           7
    [2020-08-24 20:31:40]     14   14    1    1          2           7
    [2020-08-24 20:31:48]     15   15    1    1          2           7
    [2020-08-24 20:31:48]     16   16    1    1          2           7
    [2020-08-24 20:31:48]     17   17    1    1          2           7
    [2020-08-24 20:31:48]     18   18    1    1          2           7
    [2020-08-24 20:31:48]     19   19    1    1          2           7
    [2020-08-24 20:31:48]     20   20    1    1          2           7
    [2020-08-24 20:31:48]     21   21    1    1          2           7
    [2020-08-24 20:31:48]     22   22    1    1          2           7
    [2020-08-24 20:31:48]     23   23    1    1          2           7
    [2020-08-24 20:31:48]
    [2020-08-24 20:31:48] 23 rows selected.
    [2020-08-24 20:31:48]
    [2020-08-24 20:31:48] Elapsed: 00:00:23.03
    [2020-08-24 20:31:48]
    [2020-08-24 20:31:48] Session altered.
    [2020-08-24 20:31:48]
    [2020-08-24 20:31:48] Elapsed: 00:00:00.00
    --//注意看前面的时间戳,可以发现输出时间间隔7,8,8秒.显示输出行数6,8,9.

    $ grep "FETCH" test_ora_7992.trc | grep  "#451109600"
    FETCH #451109600:c=0,e=6994268,p=0,cr=7,cu=2,mis=0,r=7,dep=0,og=1,plh=2402761124,tim=1748148741
    FETCH #451109600:c=0,e=7993084,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=1756146829
    FETCH #451109600:c=0,e=7992326,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=1764140167
    FETCH #451109600:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2402761124,tim=1764141275
    --//注意看r=??,fetch 7,8,8,0. 而显示输出行数6,8,9.
    --//你可以发现第2次fetch不再等于arraysize,而是4*arraysize=8.
    --//最后出现最后一次fetch r=0的情况,实际上显示行数是6,8,8,1,为什么看上去是显示9,因为最后1次fetch是0,消耗时间很小.
    --//这样感觉上输出9,这也是对方感觉困惑的主要原因.

    --//可以这样理解: arraysize = 2,rowprefetch=7
    --//第1次fetch 7,而arraysize=2,这样显示输出floor(rowprefetch/arraysize)*arraysize=floor(7/2)*2=6.剩下1条.
    --//第2次fetch 8,显示输出floor((8+1)/2)*2=8,还是剩下1条.
    --//第3次fetch 8,显示输出floor((8+1)/2)*2=8,还是剩下1条.
    --//第4次fetch 0,全部输出,剩下的1行.由于fetch等于0,这次会很快.
    --//实际显示输出的是:6,8,8,1.

    --//如果最后1次不是fetch=0,看到的情况如下:
    $ sqlplus -s -l scott/btbtms@test01p @ aa.txt 5 4 | ~/bin/ts.awk
    [2020-08-24 21:04:12]
    [2020-08-24 21:04:12] Session altered.
    [2020-08-24 21:04:12]
    [2020-08-24 21:04:12] Elapsed: 00:00:00.00
    [2020-08-24 21:04:12] old   1: select rownum,t.*,sleep(id2) n10,&&1 arraysize ,&&2 rowprefetch from t
    [2020-08-24 21:04:12] new   1: select rownum,t.*,sleep(id2) n10,5 arraysize ,4 rowprefetch from t
    [2020-08-24 21:04:21]
    [2020-08-24 21:04:21] ROWNUM  ID1  ID2  N10  ARRAYSIZE ROWPREFETCH
    [2020-08-24 21:04:21] ------ ---- ---- ---- ---------- -----------
    [2020-08-24 21:04:21]      1    1    1    1          5           4
    [2020-08-24 21:04:21]      2    2    1    1          5           4
    [2020-08-24 21:04:21]      3    3    1    1          5           4
    [2020-08-24 21:04:21]      4    4    1    1          5           4
    [2020-08-24 21:04:21]      5    5    1    1          5           4
    [2020-08-24 21:04:26]      6    6    1    1          5           4
    [2020-08-24 21:04:26]      7    7    1    1          5           4
    [2020-08-24 21:04:26]      8    8    1    1          5           4
    [2020-08-24 21:04:26]      9    9    1    1          5           4
    [2020-08-24 21:04:26]     10   10    1    1          5           4
    [2020-08-24 21:04:31]     11   11    1    1          5           4
    [2020-08-24 21:04:31]     12   12    1    1          5           4
    [2020-08-24 21:04:31]     13   13    1    1          5           4
    [2020-08-24 21:04:31]     14   14    1    1          5           4
    [2020-08-24 21:04:31]     15   15    1    1          5           4
    [2020-08-24 21:04:35]     16   16    1    1          5           4
    [2020-08-24 21:04:35]     17   17    1    1          5           4
    [2020-08-24 21:04:35]     18   18    1    1          5           4
    [2020-08-24 21:04:35]     19   19    1    1          5           4
    [2020-08-24 21:04:35]     20   20    1    1          5           4
    [2020-08-24 21:04:35]     21   21    1    1          5           4
    [2020-08-24 21:04:35]     22   22    1    1          5           4
    [2020-08-24 21:04:35]     23   23    1    1          5           4
    [2020-08-24 21:04:35]
    [2020-08-24 21:04:35] 23 rows selected.
    [2020-08-24 21:04:35]
    [2020-08-24 21:04:35] Elapsed: 00:00:23.20
    [2020-08-24 21:04:35]
    [2020-08-24 21:04:35] Session altered.
    [2020-08-24 21:04:35]
    [2020-08-24 21:04:35] Elapsed: 00:00:00.00
    --//注意看前面的时间戳,可以发现输出时间间隔9,5,5,4秒.
    --//显示行数:5,5,5,8.

    $ grep "FETCH" test_ora_7712.trc | grep "#201226544"
    FETCH #201226544:c=78000,e=4070787,p=0,cr=467,cu=2,mis=0,r=4,dep=0,og=1,plh=2402761124,tim=3712159663
    FETCH #201226544:c=0,e=4995743,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=3717161694
    FETCH #201226544:c=0,e=4995417,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=3722158913
    FETCH #201226544:c=0,e=4995747,p=0,cr=1,cu=0,mis=0,r=5,dep=0,og=1,plh=2402761124,tim=3727156131
    FETCH #201226544:c=0,e=3996706,p=0,cr=1,cu=0,mis=0,r=4,dep=0,og=1,plh=2402761124,tim=3731154321
    --//可以这样理解: arraysize = 5,rowprefetch=4
    --//第1次fetch 4,而arraysize=5,不足arraisize数量不会输出.
    --//第2次fetch 5,floor((5+4)/5)*5=5,输出5行,还剩下4行.这也是为什么第1个时间间隔是9秒的原因.
    --//第3次fetch 5,floor((5+4)/5)*5=5,输出5行,还剩下4行.
    --//第4次fetch 4,需要4秒完成fetch对应前面最后的时间间隔是4秒,全部输出 4+4=8.

    --//再做1个特殊情况,ROWPREFETCH正好整除ARRAYSIZE的情况:
    $ sqlplus -s -l scott/btbtms@test01p @ aa.txt 2 6 | ~/bin/ts.awk
    [2020-08-24 21:11:58]
    [2020-08-24 21:11:58] Session altered.
    [2020-08-24 21:11:58]
    [2020-08-24 21:11:58] Elapsed: 00:00:00.00
    [2020-08-24 21:11:58] old   1: select rownum,t.*,sleep(id2) n10,&&1 arraysize ,&&2 rowprefetch from t
    [2020-08-24 21:11:58] new   1: select rownum,t.*,sleep(id2) n10,2 arraysize ,6 rowprefetch from t
    [2020-08-24 21:12:04]
    [2020-08-24 21:12:04] ROWNUM  ID1  ID2  N10  ARRAYSIZE ROWPREFETCH
    [2020-08-24 21:12:04] ------ ---- ---- ---- ---------- -----------
    [2020-08-24 21:12:04]      1    1    1    1          2           6
    [2020-08-24 21:12:04]      2    2    1    1          2           6
    [2020-08-24 21:12:04]      3    3    1    1          2           6
    [2020-08-24 21:12:04]      4    4    1    1          2           6
    [2020-08-24 21:12:04]      5    5    1    1          2           6
    [2020-08-24 21:12:04]      6    6    1    1          2           6
    [2020-08-24 21:12:12]      7    7    1    1          2           6
    [2020-08-24 21:12:12]      8    8    1    1          2           6
    [2020-08-24 21:12:12]      9    9    1    1          2           6
    [2020-08-24 21:12:12]     10   10    1    1          2           6
    [2020-08-24 21:12:12]     11   11    1    1          2           6
    [2020-08-24 21:12:12]     12   12    1    1          2           6
    [2020-08-24 21:12:12]     13   13    1    1          2           6
    [2020-08-24 21:12:12]     14   14    1    1          2           6
    [2020-08-24 21:12:20]     15   15    1    1          2           6
    [2020-08-24 21:12:20]     16   16    1    1          2           6
    [2020-08-24 21:12:20]     17   17    1    1          2           6
    [2020-08-24 21:12:20]     18   18    1    1          2           6
    [2020-08-24 21:12:20]     19   19    1    1          2           6
    [2020-08-24 21:12:20]     20   20    1    1          2           6
    [2020-08-24 21:12:20]     21   21    1    1          2           6
    [2020-08-24 21:12:20]     22   22    1    1          2           6
    [2020-08-24 21:12:21]     23   23    1    1          2           6
    [2020-08-24 21:12:21]
    [2020-08-24 21:12:21] 23 rows selected.
    [2020-08-24 21:12:21]
    [2020-08-24 21:12:21] Elapsed: 00:00:23.03
    [2020-08-24 21:12:21]
    [2020-08-24 21:12:21] Session altered.
    [2020-08-24 21:12:21]
    [2020-08-24 21:12:21] Elapsed: 00:00:00.00
    --//时间间隔:6,8,8,1
    --//显示行数:6,8,8,1.

    $ grep "FETCH" test_ora_4344.trc | grep "#201597384"
    FETCH #201597384:c=0,e=6007379,p=0,cr=56,cu=2,mis=0,r=6,dep=0,og=1,plh=2402761124,tim=4180072163
    FETCH #201597384:c=0,e=7998394,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=4188072679
    FETCH #201597384:c=0,e=8000802,p=0,cr=1,cu=0,mis=0,r=8,dep=0,og=1,plh=2402761124,tim=4196074146
    FETCH #201597384:c=0,e=999593,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2402761124,tim=4197074434
    --//fetch 6,8,8,1.

    --//可以这样理解: arraysize = 2,rowprefetch=6
    --//第1次fetch 6,而arraysize=2, floor(rowprefetch/arraysize)*arraysize=floor(6/2)*2=6.显示输出6行.剩下0条.
    --//第2次fetch 8,(floor(8+0)/2)*2=8,显示输出8行.剩下0条.
    --//第3次fetch 8,(floor(8+0)/2)*2=8,显示输出8行.剩下0条.
    --//第4次fetch 1 ,需要1秒完成fetch对应前面最后的时间间隔是1秒,全部输出1.

    --//你可以使用下面的sleept函数替换脚本aa.txt里面的sleep函数.
    CREATE OR REPLACE FUNCTION SCOTT.sleepT (seconds IN NUMBER)
       RETURN timestamp
    AS
    BEGIN
       sys.DBMS_LOCK.sleep (seconds);
       RETURN SYSTIMESTAMP-1/86400;
    END;
    /

    $ cat ba.txt
    set timing on
    set arraysize &1
    set rowprefetch &2
    alter session set events '10046 trace name context forever, level 12';
    select rownum,t.*,sleept(id2) n10,&&1 arraysize ,&&2 rowprefetch from t;
    alter session set events '10046 trace name context off';
    set timing off
    quit

    $ sqlplus -s -l scott/btbtms@test01p @ ba.txt 7 7 | ~/bin/ts.awk
    [2020-08-25 20:22:00]
    [2020-08-25 20:22:00] Session altered.
    [2020-08-25 20:22:00]
    [2020-08-25 20:22:00] Elapsed: 00:00:00.00
    [2020-08-25 20:22:00] old   1: select rownum,t.*,sleept(id2) n10,&&1 arraysize ,&&2 rowprefetch from t
    [2020-08-25 20:22:00] new   1: select rownum,t.*,sleept(id2) n10,7 arraysize ,7 rowprefetch from t
    [2020-08-25 20:22:07]
    [2020-08-25 20:22:07] ROWNUM  ID1  ID2 N10                           ARRAYSIZE ROWPREFETCH
    [2020-08-25 20:22:07] ------ ---- ---- ----------------------------- --------- -----------
    [2020-08-25 20:22:07]      1    1    1 2020-08-25 20:22:00.000000000         7           7
    [2020-08-25 20:22:07]      2    2    1 2020-08-25 20:22:01.000000000         7           7
    [2020-08-25 20:22:07]      3    3    1 2020-08-25 20:22:02.000000000         7           7
    [2020-08-25 20:22:07]      4    4    1 2020-08-25 20:22:03.000000000         7           7
    [2020-08-25 20:22:07]      5    5    1 2020-08-25 20:22:04.000000000         7           7
    [2020-08-25 20:22:07]      6    6    1 2020-08-25 20:22:05.000000000         7           7
    [2020-08-25 20:22:07]      7    7    1 2020-08-25 20:22:06.000000000         7           7
    [2020-08-25 20:22:21]      8    8    1 2020-08-25 20:22:07.000000000         7           7
    [2020-08-25 20:22:21]      9    9    1 2020-08-25 20:22:08.000000000         7           7
    [2020-08-25 20:22:21]     10   10    1 2020-08-25 20:22:09.000000000         7           7
    [2020-08-25 20:22:21]     11   11    1 2020-08-25 20:22:10.000000000         7           7
    [2020-08-25 20:22:21]     12   12    1 2020-08-25 20:22:11.000000000         7           7
    [2020-08-25 20:22:21]     13   13    1 2020-08-25 20:22:12.000000000         7           7
    [2020-08-25 20:22:21]     14   14    1 2020-08-25 20:22:13.000000000         7           7
    [2020-08-25 20:22:21]     15   15    1 2020-08-25 20:22:14.000000000         7           7
    [2020-08-25 20:22:21]     16   16    1 2020-08-25 20:22:15.000000000         7           7
    [2020-08-25 20:22:21]     17   17    1 2020-08-25 20:22:16.000000000         7           7
    [2020-08-25 20:22:21]     18   18    1 2020-08-25 20:22:17.000000000         7           7
    [2020-08-25 20:22:21]     19   19    1 2020-08-25 20:22:18.000000000         7           7
    [2020-08-25 20:22:21]     20   20    1 2020-08-25 20:22:19.000000000         7           7
    [2020-08-25 20:22:21]     21   21    1 2020-08-25 20:22:20.000000000         7           7
    [2020-08-25 20:22:23]     22   22    1 2020-08-25 20:22:21.000000000         7           7
    [2020-08-25 20:22:23]     23   23    1 2020-08-25 20:22:22.000000000         7           7
    [2020-08-25 20:22:23]
    [2020-08-25 20:22:23] 23 rows selected.
    [2020-08-25 20:22:23]
    [2020-08-25 20:22:23] Elapsed: 00:00:23.00
    [2020-08-25 20:22:23]
    [2020-08-25 20:22:23] Session altered.
    [2020-08-25 20:22:23]
    [2020-08-25 20:22:23] Elapsed: 00:00:00.00
    --//时间间隔:7,14,2
    --//显示行数:7,14,2.

    $ grep "FETCH" test_ora_4444.trc | grep 45247739
    FETCH #452477392:c=15600,e=7003344,p=0,cr=30,cu=2,mis=0,r=7,dep=0,og=1,plh=2402761124,tim=2850476765
    FETCH #452477392:c=0,e=13988663,p=0,cr=1,cu=0,mis=0,r=14,dep=0,og=1,plh=2402761124,tim=2864469452
    FETCH #452477392:c=0,e=1999838,p=0,cr=1,cu=0,mis=0,r=2,dep=0,og=1,plh=2402761124,tim=2866471508
    --//不再展开分析.

    4.总结:
    --//实际上这其中细节不重要,你可以理解改变12c下改变sqlplus的rowprefetch参数,有可能隐含改变了fetch方式与数量.
    --//第1次fetch = rowpefetch, 第2次 fetch 等于 (floor(rowprefetch/arraysize)+1)*arraysize.
    --//个人建议还是不要设置rowprefetch >= arraysize的情况.因为这样改变fetch的模式.

    --//显示输出 : floor(rowprefetch/arraysize)*arraysize,floor(rowprefetch/arraysize+1)*arraysize, ...,剩下的记录.
    --//注:fetch在前,输出在后.
    --//   如果rowprefetch < arraysize,第1次fetch后,不足arraysize数量.不会马上输出,而是等待下一个fetch完成,再输出.

    5.补充:
    --//另外我还找到一个链接,结论跟我的测试一样,表述的方式不同罢了.
    --//https://blog.dbi-services.com/arraysize-or-rowprefetch-in-sqlplus/的测试:

    We can see 3 things here:
    - The first FETCH (from the internal OCI execute) contains always the number of rows as defined in the ROWPREFETCH
      setting
    - The second FETCH (and all subsequent fetches) contains a multiple of the ARRAYSIZE setting rows. The following code
      fragment should show the logic:

    2nd_Fetch_Rows = if ROWPREFETCH <ARRAYSIZE
                     then ARRAYSIZE
                     else (TRUNC(ROWPREFETCH/ARRAYSIZE)+1)*ARRAYSIZE

    - If a fetch does not detect the end of the data in the cursor then an additional fetch is necessary. In 3 cases above a
      last fetch fetched 0 rows.

  • 相关阅读:
    poj2386 Lake Counting
    poj 1852 Ants
    Luogu P2419 [USACO08JAN]牛大赛Cow Contest
    Luogu P2336 [SCOI2012]喵星球上的点名
    Luogu P2463 [SDOI2008]Sandy的卡片
    Luogu P2852 [USACO06DEC]牛奶模式Milk Patterns
    Luogu P4248 [AHOI2013]差异
    【NOI2008】志愿者招募
    Luogu P2743 [USACO5.1]乐曲主题Musical Themes
    P3723 [AH2017/HNOI2017]礼物
  • 原文地址:https://www.cnblogs.com/lfree/p/13561908.html
Copyright © 2020-2023  润新知