|
[20190306]共享服务模式与SDU.txt
--//一些文档提到共享服务模式,服务端SDU=65535,测试验证看看.
--//链接:https://blogs.sap.com/2013/02/07/oracle-sqlnet-researching-setting-session-data-unit-sdu-size-and-how-it-can-go-wrong/
Official Oracle 11g R2 documentation
Under typical database configuration,Oracle Net encapsulates data into buffers the size of the session data unit (SDU)
before sending the data across the network. Oracle Net sends each buffer when it is filled,flushed,or when an
application tries to read data. Adjusting the size of the SDU buffers relative to the amount of data provided to Oracle
Net to send at any one time can improve performance,network utilization,and memory consumption. When large amounts of
data are being transmitted,increasing the SDU size can improve performance and network throughput.
The amount of data provided to Oracle Net to send at any one time is referred to as the message size. Oracle Net assumes
by default that the message size will normally vary between 0 and 8192 bytes,and infrequently,be larger than 8192
bytes. If this assumption is true,then most of the time,the data is sent using one SDU buffer.
The SDU size can range from 512 bytes to 65535 bytes. The default SDU for the client and a dedicated server is 8192
bytes. The default SDU for a shared server is 65535 bytes.
The actual SDU size used is negotiated between the client and the server at connect time and is the smaller of the
client and server values. Configuring an SDU size different from the default requires configuring the SDU on both the
client and server computers,unless you are using shared servers. For shared servers,only the client value must be
changed because the shared server defaults to the maximum value.
--//注意理解最后一段: For shared servers,only the client value must be changed because the shared server defaults to
--//the maximum value.也就是在共享服务器模式下服务端SDU设置65535,以client端要设置为准,我开始理解错误.
--//实际上服务端应该也是可以改变的,修改dispatchers参数.
1.环境:
[email?protected]> @ ver1
PORT_STRING??????????????????? VERSION??????? BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx??????????? 11.2.0.4.0???? Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--//在client建立sql脚本e2.txt:
R:&;cat e1.txt
select sysdate,P2,TIME_SINCE_LAST_WAIT_MICRO from V$SESSION_WAIT_HISTORY where sid=&&1 and event=‘SQL*Net more data from client‘;
R:&; cat e2.txt
select /*
0014567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
0024567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
...snip...
5994567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
6004567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
*/ sysdate
from dual;
R:&;ls -l? e2.txt
-rw-rw-rw-?? 1 user???? group?????? 61235 Mar? 6 16:46 e2.txt
--//说明服务端监听配置以及sqlnet.ora采用缺省配置SDU.
--//修改client端sqlnet.ora加入DEFAULT_SDU_SIZE=65535
R:&;grep -i sdu E:apporacleproduct12.2.0dbhome_1networkadminsqlnet.ora
DEFAULT_SDU_SIZE=65535
2.测试1:
--//首先测试专用模式:
R:&;sqlplus scott/[email?protected]:1521/book:DEDICATED
[email?protected]:1521/book:DEDICATED> @ spid
?SID??? SERIAL# PROCESS????????????????? SERVER??? SPID? PID? P_SERIAL# C50
---- ---------- ------------------------ --------- ----- --- ---------- --------------------------------------------
? 67???????? 73 12224:12764????????????? DEDICATED 13418? 29???????? 30 alter system kill session ‘67,73‘ immediate;
--//sid=67,使用专用连接.
[email?protected]:1521/book:DEDICATED> @ e1.txt? 67
no rows selected
[email?protected]:1521/book:DEDICATED> @e2.txt
SYSDATE
-------------------
2019-03-06 16:51:42
[email?protected]:1521/book:DEDICATED> @ e1.txt? 67
SYSDATE???????????????????? P2 TIME_SINCE_LAST_WAIT_MICRO
------------------- ---------- --------------------------
2019-03-06 16:51:48???????? 64????????????????????????? 5
2019-03-06 16:51:48??????? 229????????????????????????? 5
2019-03-06 16:51:48??????? 138????????????????????????? 7
--//你可以发现会话出现SQL*Net more data from client等待事件,而且出现3次(注V$SESSION_WAIT_HISTORY视图记录遇到的最后10个等
--//待事件),这是因为sql语句超长,而我客户端设置DEFAULT_SDU_SIZE=65535,但是服务端SDU缺省是8192,这样协调选择最小的SDU值.
3.测试2:
--//测试共享模式:
R:&;sqlplus scott/[email?protected]:1521/book:shared
[email?protected]:1521/book:shared> @ spid
?SID??? SERIAL# PROCESS????????????????? SERVER??? SPID? PID? P_SERIAL# C50
---- ---------- ------------------------ --------- ----- --- ---------- ---------------------------------------------
?261???????? 31 12988:8108?????????????? SHARED??? 59542? 20????????? 1 alter system kill session ‘261,31‘ immediate;
--//sid=261
[email?protected]:1521/book:shared> @ e1.txt 261
no rows selected
[email?protected]:1521/book:shared> @ e2.txt
SYSDATE
-------------------
2019-03-06 16:55:46
[email?protected]:1521/book:shared> @ e1.txt 261
no rows selected
--//可以发现在采用共享服务连接模式,执行脚本大小61K,并没有出现SQL*Net more data from client等待事件.说明这种情况下SDU确实
--//设置很大.
R:&;ls -l e2.txt
-rw-rw-rw-?? 1 user???? group?????? 67355 Mar? 6 16:57 e2.txt
--//增加e2.txt 大小大于65535字节,重复测试看看:
[email?protected]:1521/book:shared> @ e1.txt 261
no rows selected
[email?protected]:1521/book:shared> @ e2.txt
SYSDATE
-------------------
2019-03-06 16:58:13
[email?protected]:1521/book:shared> @ e1.txt 261
SYSDATE???????????????????? P2 TIME_SINCE_LAST_WAIT_MICRO
------------------- ---------- --------------------------
2019-03-06 16:58:15???????? 31??????????????????????? 126
--//当脚本大小变大时,出现‘SQL*Net more data from client‘等待事件.
4.疑问:
--//自己觉得好奇的是oracle为什么这样设置,这样设置有什么好处.如果设置很大,使用共享模式消耗NETWORK BUFFER不是很大吗?
--//共享池不是要设置很大吗?
--//参考链接:http://blog.itpub.net/267265/viewspace-2214856/.
--//重新测试:
--//修改服务端的sqlnet.ora,DEFAULT_SDU_SIZE=65535
$ grep -i sdu /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/sqlnet.ora
DEFAULT_SDU_SIZE=65535
#DEFAULT_SDU_SIZE=32767
$ cat /home/oracle/xxx430/testh/b.sh
#!/bin/bash
for i in $(seq 100)
do
nohup?? sqlplus -s scott/[email?protected]:1521/book:shared <<EOF > /dev/null 2>&1 &
select sysdate from dual ;
host sleep 30
quit;
EOF
done
[email?protected]> select name,pool,bytes from v$sgastat where name like ‘%NETWORK%‘;
NAME???????????????? POOL????????????? BYTES
-------------------- ------------ ----------
NETWORK BUFFER?????? shared pool?????? 73808
$ . /home/oracle/xxx430/testh/b.sh
[email?protected]> select name,bytes from v$sgastat where name like ‘%NETWORK%‘;
NAME???????????????? POOL????????????? BYTES
-------------------- ------------ ----------
NETWORK BUFFER?????? shared pool??? 33292408
--//33292408/1024/1024 = 31.75M
[email?protected]> select (33292408-73808)/100 from dual;
(33292408-73808)/100
--------------------
????????????? 332186
--//332186/1024= 324.4K.每个连接消耗324K. 332186/65535 = 5.06883344777599755855,5个SDU???
--//另外我在10g下做了测试10g下好像sdu最大32767.
--//好奇心测试看看SDU不同的情况下NETWORK BUFFER的消耗,方法如下:
1.修改SDU值
2.查询select name,bytes from v$sgastat where name like ‘%NETWORK%‘;,记下bytes数量
3.执行$ seq 100 | xargs -I{} -P 100 bash -c ‘sqlplus -s scott/[email?protected]:1521/book:shared <<< "host sleep 20" ‘
4.查询select name,记下bytes数量
5.重复测试
--//画一个表格:
SDU??? 开始NETWORK BUFFER?? 结束NETWORK BUFFER? 差值????????????????????????? 差值/100/SDU
-------------------------------------------------------------------------------------------------------------------------
10000? 3344376?????????????? 8399608??????????? 8399608-3344376 = 5055232???? 5055232/100/10000 = 5.05523200000000000000
20000? 3344376?????????????? 13399584 13399584-3344376 = 10055208??? 10055208/100/20000 = 5.02760400000000000000
30000? 3344376?????????????? 18399728????????? 18399728-3344376 = 15055352??? 15055352/100/30000 = 5.01845066666666666666
40000? 3344376?????????????? 23399600????????? 23399600-3344376 = 20055224??? 20055224/100/40000 = 5.01380600000000000000
50000? 3344376?????????????? 28399784????????? 28399784-3344376 = 25055408??? 25055408/100/50000 = 5.01108160000000000000
60000? 3344376?????????????? 33400168????????? 33400168-3344376 = 30055792??? 30055792 /100/60000 = 5.00929866666666666666
65535? 3344376?????????????? 36167632????????? 36167632-3344376 = 32823256??? 32823256/100/65535 = 5.00850782024872205691
70000? 3344376?????????????? 36167760????????? 36167760-3344376 = 32823384??? 32823384/100/5 = 65646.76800000000000000000
---------------------------------------------------------------------------------------------------------------------------
--//注:最后SUD设置70000,实际上协调2者SDU=65535.
--//按照http://www.askmaclean.com/archives/%E5%85%B1%E4%BA%AB%E6%B1%A0%E4%B8%AD%E7%9A%84network-buffer.html的理解:
为什么共享服务器模式下会用到共享池中的NETWORK BUFFER，而独享服务器模式下没有呢？因为在独享服务器模式下每个会话所分配的三
个SDU是从PGA中获取的；当使用共享服务器模式时会话与服务进程形成一对多的映射关系，这三个SDU 的NETWORK BUFFER同UGA一样转移
到了SGA中。
--//可不可这样理解实际上不是3个SDU,而是5个呢?不知道我的理解是否正确.不过我在10g测试情况不同:
[email?protected]> @ &r/ver1PORT_STRING??????????????????? VERSION??????? BANNER------------------------------ -------------- ----------------------------------------------------------------x86_64/Linux 2.4.xx??????????? 10.2.0.4.0???? Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64biSDU??? 开始NETWORK BUFFER?? 结束NETWORK BUFFER? 差值????????????????????????? 差值/100/SDU-------------------------------------------------------------------------------------------------------------------------10000? 139824?????????????? 12025456??????????? 12025456-139824 = 11885632??? 11885632/100/10000 = 11.8856320000000000000020000? 139824?????????????? 14025456??????????? 14025456-139824 = 13885632??? 13885632/100/20000 = 6.9428160000000000000030000? 139824?????????????? 16025424??????????? 16025424-139824 = 15885600??? 15885600/100/30000 = 5.2952000000000000000032767? 139824?????????????? 16579040??????????? 16579040-139824 = 16439216??? 16439216/100/32767 = 5.0170036927396465956640000? 139824?????????????? 16579024??????????? 16579024-139824 = 16439200??? 16439200/32767/100 = 5.01699880977813043610---------------------------------------------------------------------------------------------------------------------------//可以看出10g SDU最大是32767.--//后面的差值/100/SDU很大时才接近5个SDU.5.最后注意收尾.--//取消修改设置为缺省值.略.
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
