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

来源：这里教程网时间：2026-03-03 12:11:01 作者：

[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 --//不好理解放弃!!