[20181106]12c sqlplus rowprefetch参数3.txt

--//12cR2 可以改变缺省rowprefetch参数.11g之前缺省是1.通过一些测试说明问题.

1.环境:

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

SCOTT@test01p> 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 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;

/

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

RETURN NUMBER

AS

BEGIN

sys.DBMS_LOCK.sleep (seconds);

RETURN seconds;

END;

/

2.建立测试脚本:

--//确定转储文件略。

SCOTT@test01p> @pp

TRACEFILE

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

D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_4696.trc

--//建立测试脚本:

--//aa.txt

set timing on

set arraysize &1

set rowprefetch &2

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

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

--//分别执行如下bb.txt:

@ aa.txt 5 1

@ aa.txt 5 2

@ aa.txt 5 3

@ aa.txt 5 4

@ aa.txt 5 5

@ aa.txt 5 6

@ aa.txt 5 7

@ aa.txt 5 8

@ aa.txt 5 9

@ aa.txt 5 10

@ aa.txt 5 11

@ aa.txt 5 12

@ aa.txt 5 13

@ aa.txt 5 14

@ aa.txt 5 15

--//我使用----分开。

d:\>grep "plh=1498225739" D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_4696.trc | grep "FETCH"

FETCH #161315584:c=0,e=1009200,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1498225739,tim=6757881731

FETCH #161315584:c=0,e=1999313,p=0,cr=5,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6759887687

FETCH #161315584:c=15600,e=3003068,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6762891906

FETCH #161315584:c=0,e=3001084,p=0,cr=10,cu=0,mis=0,r=3,dep=0,og=1,plh=1498225739,tim=6765893526

----

FETCH #161689480:c=0,e=1998000,p=0,cr=8,cu=0,mis=0,r=2,dep=0,og=1,plh=1498225739,tim=6767908274

FETCH #161689480:c=15600,e=3002196,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6770913580

FETCH #161689480:c=0,e=2998619,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6773912782

FETCH #161689480:c=0,e=1998639,p=0,cr=8,cu=0,mis=0,r=2,dep=0,og=1,plh=1498225739,tim=6775911957

----

FETCH #161689480:c=0,e=1999140,p=0,cr=8,cu=0,mis=0,r=3,dep=0,og=1,plh=1498225739,tim=6777920935

FETCH #161689480:c=15600,e=3001413,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6780926027

FETCH #161689480:c=0,e=2998129,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6783924786

FETCH #161689480:c=0,e=999625,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1498225739,tim=6784925189

----

FETCH #161689480:c=0,e=1998143,p=0,cr=8,cu=0,mis=0,r=4,dep=0,og=1,plh=1498225739,tim=6786930245

FETCH #161689480:c=0,e=3000067,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6789933340

FETCH #161689480:c=0,e=2997676,p=0,cr=7,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6792931596

FETCH #161689480:c=0,e=29,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=1498225739,tim=6792932166

----

FETCH #33162208:c=0,e=2002008,p=0,cr=8,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6794943158

FETCH #33162208:c=0,e=3012994,p=0,cr=10,cu=0,mis=0,r=9,dep=0,og=1,plh=1498225739,tim=6797959770

----

FETCH #33162208:c=0,e=2000316,p=0,cr=8,cu=0,mis=0,r=6,dep=0,og=1,plh=1498225739,tim=6799977070

FETCH #33162208:c=0,e=2998486,p=0,cr=10,cu=0,mis=0,r=8,dep=0,og=1,plh=1498225739,tim=6802978947

----

FETCH #33162208:c=15601,e=3004012,p=0,cr=10,cu=0,mis=0,r=7,dep=0,og=1,plh=1498225739,tim=6806009715

FETCH #33162208:c=0,e=2998099,p=0,cr=10,cu=0,mis=0,r=7,dep=0,og=1,plh=1498225739,tim=6809011284

----

FETCH #33162208:c=0,e=2998413,p=0,cr=10,cu=0,mis=0,r=8,dep=0,og=1,plh=1498225739,tim=6812022392

FETCH #33162208:c=0,e=2997879,p=0,cr=10,cu=0,mis=0,r=6,dep=0,og=1,plh=1498225739,tim=6815024740

----

FETCH #33162208:c=0,e=2999262,p=0,cr=10,cu=0,mis=0,r=9,dep=0,og=1,plh=1498225739,tim=6818046984

FETCH #33162208:c=0,e=2997424,p=0,cr=10,cu=0,mis=0,r=5,dep=0,og=1,plh=1498225739,tim=6821047792

----

FETCH #33162208:c=0,e=2999506,p=0,cr=10,cu=0,mis=0,r=10,dep=0,og=1,plh=1498225739,tim=6824057849

FETCH #33162208:c=0,e=2999211,p=0,cr=10,cu=0,mis=0,r=4,dep=0,og=1,plh=1498225739,tim=6827061343

----

FETCH #33162208:c=15600,e=3031951,p=0,cr=10,cu=0,mis=0,r=11,dep=0,og=1,plh=1498225739,tim=6830128155

FETCH #33162208:c=0,e=2997636,p=0,cr=10,cu=0,mis=0,r=3,dep=0,og=1,plh=1498225739,tim=6833129328

----

FETCH #33162208:c=0,e=3012728,p=0,cr=10,cu=0,mis=0,r=12,dep=0,og=1,plh=1498225739,tim=6836158511

FETCH #33162208:c=0,e=1999216,p=0,cr=8,cu=0,mis=0,r=2,dep=0,og=1,plh=1498225739,tim=6838161474

----

FETCH #33162208:c=0,e=3009873,p=0,cr=10,cu=0,mis=0,r=13,dep=0,og=1,plh=1498225739,tim=6841178435

FETCH #33162208:c=0,e=999882,p=0,cr=6,cu=0,mis=0,r=1,dep=0,og=1,plh=1498225739,tim=6842181712

----

FETCH #33162208:c=0,e=3000846,p=0,cr=10,cu=0,mis=0,r=14,dep=0,og=1,plh=1498225739,tim=6845211650

FETCH #33162208:c=0,e=33,p=0,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=1498225739,tim=6845215320

----

FETCH #30590696:c=0,e=2999696,p=0,cr=13,cu=0,mis=0,r=14,dep=0,og=1,plh=1498225739,tim=7067691416

--//d:\>grep "plh=1498225739" D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_4696.trc | grep "FETCH" | cut -d"=" -f8 | cut -d"," -f1

3.将统计信息做一个表格:

arraysize rowprefetch 显示行数顺序执行时间 fetch的顺序

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

5 1 5,5,4 00:00:09.03 1 5 5 3

5 2 5,5,4 00:00:10.00 2 5 5 2

5 3 5,5,4 00:00:09.01 3 5 5 1

5 4 5,9 00:00:08.01 4 5 5 0

5 5 5,9 00:00:05.00 5 9

5 6 5,9 00:00:05.01 6 8

5 7 5,9 00:00:06.00 7 7

5 8 5,9 00:00:06.00 8 6

5 9 5,9 00:00:06.00 9 5

5 10 10,4 00:00:06.00 10 4

5 11 10,4 00:00:06.00 11 3

5 12 10,4 00:00:05.00 12 2

5 13 10,4 00:00:04.00 13 1

5 14 14 00:00:03.01 14 0

5 15 14 00:00:03.01 14

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

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

--//1.fetch 第1次数量与参数rowprefetch相关,当然如果返回记录小于记录数量.

--//2.显示行数一般都是array的倍数(与rowprefetch也有关).

--//3.问题在于arraysize=5,rowprefetch=4时,显示行数顺序5,9.不好解析..

--//这东西写出来很难表达,大家自己理解吧.

--//arraysize=5,rowprefetch=4时,为什么显示行数顺序是5,9而不是5,5,4.

--//我感觉第2次fetch是连续fetch2次.如果第3次fetch等于arraysize,仅仅第2次fetch的5条条进入输出缓存(我的理解).

--//如果如果第3次fetch小于arraysize,也就是fetch没有剩余记录了,显示全部输出.

arraysize rowprefetch 显示行数顺序执行时间 fetch的顺序

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

3 6 6,8 00:00:05.16 6,8

--//不好理解放弃!!

ITPub博客

[20181106]12c sqlplus rowprefetch参数3.txt

最新文章