[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足以.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2199981/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
