[20180813]sqlplus arraysize设置与SDU.txt

[20180813]sqlplus arraysize设置与SDU.txt --//别人问的问题如何设置arraysize大小,实际上这个没有统一标准,设置太小,全表扫描操作逻辑读很增加. --//我个人认为:正常标准数据块8K,一般最大容纳的记录数是736(实际上仅仅734),设置很大完全没有必要. --//因为跨块逻辑读就会增加.arraysize设置很大逻辑读不会减少,我个人设置200,而且设置很大,会导致 --//SDU填满,这样会出现大量等待事件: SQL*net more data to client. --//另外我以前在toad 与PLSQLDev下测试fetch与sqlplus不同: --//链接:http://blog.itpub.net/267265/viewspace-2152038/=>[20180320]toad环境中一次fetch等于多少.txt --//大致总结一下: --//1.sqlplus可以这么理解先提取1行,不输出.然后提取N行,输出N行,保留1行在输出缓存.如此循环.这里N=arraysie. --//2.toad先提取1001行,然后输出1000行,然后提取1000行,输出1000行,如此循环. --//3.PLSQLDev先提取100行,然后输出100行,然后提取100行,输出100行,如此循环. 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0 SCOTT@test01p> create table t as select * from all_objects order by  DBMS_RANDOM.random; Table created. 2.测试: --//缺省SDU=8192. SCOTT@test01p> show arraysize arraysize 200 --//测试前先执行select * from t;减少一些递归操作. @ 10046on 12 select * from t; @ 10046off --//检查转储文件内容: *** 2018-08-13 21:12:11.398 WAIT #174817080: nam='SQL*Net message from client' ela= 2051025 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431244692 CLOSE #174817080:c=0,e=35,dep=0,type=1,tim=2431245161 ===================== PARSING IN CURSOR #174802008 len=15 dep=0 uid=109 oct=3 lid=109 tim=2431249623 hv=1134051363 ad='7ff23997360' sqlid='89km4qj1thh13' select * from t END OF STMT PARSE #174802008:c=15600,e=4307,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=2431249619 EXEC #174802008:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=2431279382 WAIT #174802008: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431279560 WAIT #174802008: nam='direct path read' ela= 15529 file number=9 first dba=2963 block cnt=13 obj#=107848 tim=2431295627 --//竟然走direct path read.关系不大.感觉是取样,忘记分析表了. FETCH #174802008:c=0,e=16298,p=28,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1601196873,tim=2431295959 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WAIT #174802008: nam='SQL*Net message from client' ela= 10577 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431306712 WAIT #174802008: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431307122 WAIT #174802008: nam='SQL*Net more data to client' ela= 191 driver id=1413697536 #bytes=8103 p3=0 obj#=107848 tim=2431307681 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& WAIT #174802008: nam='SQL*Net more data to client' ela= 173 driver id=1413697536 #bytes=8102 p3=0 obj#=107848 tim=2431308229 FETCH #174802008:c=0,e=1298,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431308363 WAIT #174802008: nam='SQL*Net message from client' ela= 35316 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431343824 WAIT #174802008: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431344242 WAIT #174802008: nam='SQL*Net more data to client' ela= 195 driver id=1413697536 #bytes=8109 p3=0 obj#=107848 tim=2431344678 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& WAIT #174802008: nam='SQL*Net more data to client' ela= 222 driver id=1413697536 #bytes=8116 p3=0 obj#=107848 tim=2431345133 &&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& FETCH #174802008:c=0,e=1144,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431345210 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ WAIT #174802008: nam='SQL*Net message from client' ela= 19881 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431365158 WAIT #174802008: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=2431365499 WAIT #174802008: nam='SQL*Net more data to client' ela= 539 driver id=1413697536 #bytes=8107 p3=0 obj#=107848 tim=2431366434 WAIT #174802008: nam='SQL*Net more data to client' ela= 491 driver id=1413697536 #bytes=8095 p3=0 obj#=107848 tim=2431367675 FETCH #174802008:c=0,e=2554,p=0,cr=5,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=2431367926 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ --//我设置arraysize=200,重新分析表可以发现发现平均行长115.这样 200*115 = 23000,超过SDU=8192限制,fetch=200需要3次 --//SQL*Net more data to client等待事件. SCOTT@test01p> @ ev_name 'SQL*Net more data to client' old   1: select * from v$event_name where lower(name) like lower('%&&1%') new   1: select * from v$event_name where lower(name) like lower('%SQL*Net more data to client%') EVENT#   EVENT_ID NAME                        PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME                CON_ID ------ ---------- --------------------------- ---------- ---------- ---------- ------------- ----------- ---------- --------------------------- -------    373  554161347 SQL*Net more data to client driver id  #bytes                   2000153315           7 Network    SQL*Net more data to client 0 --//P2参数是字节: 可以发现大约都是81XX. --//ela =  191+195+222 = 608 (单位微妙) --//这样导致buffer pin时间增加,对数据库影响并不是很大.因为读读模式是共享的.即使是写也影响不大. --//如果设置arraysize更大,在一次fetch中遇到SQL*Net more data to client等待事件次数会越多. D:\app\oracle\diag\rdbms\test\test\trace>grep "SQL\*Net more data to client" test_ora_5288.trc |wc 898   13470  111416 3.测试: --//修改sqlnet.ora DEFAULT_SDU_SIZE=32768 --//重复前面测试: @ 10046on 12 select * from t; @ 10046off --//检查转储文件内容: ===================== PARSING IN CURSOR #183256856 len=15 dep=0 uid=109 oct=3 lid=109 tim=3516832814 hv=1134051363 ad='7ff23997360' sqlid='89km4qj1thh13' select * from t END OF STMT PARSE #183256856:c=0,e=4934,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1601196873,tim=3516832811 EXEC #183256856:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1601196873,tim=3516833461 WAIT #183256856: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=3516833727 WAIT #183256856: nam='Disk file operations I/O' ela= 601 FileOperation=2 fileno=9 filetype=2 obj#=107848 tim=3516834941 WAIT #183256856: nam='direct path read' ela= 20202 file number=9 first dba=2963 block cnt=13 obj#=107848 tim=3516855802 FETCH #183256856:c=0,e=22124,p=28,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=1601196873,tim=3516856006 WAIT #183256856: nam='SQL*Net message from client' ela= 4794 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516860893 WAIT #183256856: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516861016 FETCH #183256856:c=0,e=328,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516861319 WAIT #183256856: nam='SQL*Net message from client' ela= 25029 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516886434 WAIT #183256856: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516886906 FETCH #183256856:c=0,e=310,p=0,cr=4,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516887167 WAIT #183256856: nam='SQL*Net message from client' ela= 44761 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516932043 WAIT #183256856: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=107848 tim=3516932438 FETCH #183256856:c=15600,e=246,p=0,cr=5,cu=0,mis=0,r=200,dep=0,og=1,plh=1601196873,tim=3516932653 --//在一次fetch=200中,SDU=32768,不再遇到SDU填满的情况,也就是不再出现SQL*Net more data to client等待事件 D:\app\oracle\diag\rdbms\test\test\trace>grep "SQL\*Net more data to client" test_ora_6856.trc |wc 0       0       0 --//可以发现没有出现SQL*Net more data to client等待事件.可以发现设置合理设置就ok.arraysize=200仅仅是个人的经验值. --//大多数应用不会修改client端的SDU大小.这个参数要协商,以客户端,服务器最小值作为标准. --//如果3层应用,建议还是修改中间服务器,服务器我个人建议SDU=32768. --//另外设置arraysize很大,一定程度减少SQL*Net roundtrips to/from client. SCOTT@test01p> set autot traceonly SCOTT@test01p> set array 200 SCOTT@test01p> select * from t; 89888 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      | 89888 |     9M|   421   (1)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T    | 89888 |     9M|   421   (1)| 00:00:01 | -------------------------------------------------------------------------- Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        1957  consistent gets        1511  physical reads           0  redo size     7754897  bytes sent via SQL*Net to client        5483  bytes received via SQL*Net from client         451  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       89888  rows processed SCOTT@test01p> set array 400 SCOTT@test01p> select * from t; 89888 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      | 89888 |     9M|   421   (1)| 00:00:01 | |   1 |  TABLE ACCESS FULL| T    | 89888 |     9M|   421   (1)| 00:00:01 | -------------------------------------------------------------------------- Statistics ----------------------------------------------------------           0  recursive calls           0  db block gets        1736  consistent gets        1511  physical reads           0  redo size     7711922  bytes sent via SQL*Net to client        3008  bytes received via SQL*Net from client         226  SQL*Net roundtrips to/from client           0  sorts (memory)           0  sorts (disk)       89888  rows processed --//SQL*Net roundtrips to/from client 从451->226.但是总bytes sent via SQL*Net to client是基本不变的. --//在vage的<oracle揭秘> 有很好的说明,arraysize很大,也不会过度消耗pga,因为填满SDU就传输,只不过一直 --//持有buffer bin,但是对数据库影响不大. --//设置DEFAULT_SDU_SIZE=32768,会不会导致用户uga或者pga消耗,我自己也没注意观察. 总结: 1.我个人建议设置DEFAULT_SDU_SIZE=32768. 2.预提取大小设置200足以.