[20190306]共享服务模式与SDU.txt
[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.环境:
SCOTT@book> @ 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:\app\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora
DEFAULT_SDU_SIZE=65535
2.测试1:
--//首先测试专用模式:
R:\>sqlplus scott/book@192.168.100.78:1521/book:DEDICATED
SCOTT@192.168.100.78: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,使用专用连接.
SCOTT@192.168.100.78:1521/book:DEDICATED> @ e1.txt 67
no rows selected
SCOTT@192.168.100.78:1521/book:DEDICATED> @e2.txt
SYSDATE
-------------------
2019-03-06 16:51:42
SCOTT@192.168.100.78: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/book@192.168.100.78:1521/book:shared
SCOTT@192.168.100.78: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
SCOTT@192.168.100.78:1521/book:shared> @ e1.txt 261
no rows selected
SCOTT@192.168.100.78:1521/book:shared> @ e2.txt
SYSDATE
-------------------
2019-03-06 16:55:46
SCOTT@192.168.100.78: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字节,重复测试看看:
SCOTT@192.168.100.78:1521/book:shared> @ e1.txt 261
no rows selected
SCOTT@192.168.100.78:1521/book:shared> @ e2.txt
SYSDATE
-------------------
2019-03-06 16:58:13
SCOTT@192.168.100.78: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/book@192.168.100.78:1521/book:shared <<EOF > /dev/null 2>&1 &
select sysdate from dual ;
host sleep 30
quit;
EOF
done
SYS@book> 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
SYS@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';
NAME POOL BYTES
-------------------- ------------ ----------
NETWORK BUFFER shared pool 33292408
--//33292408/1024/1024 = 31.75M
SYS@book> 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,pool,bytes from v$sgastat where name like '%NETWORK%';,记下bytes数量
3.执行$ seq 100 | xargs -I{} -P 100 bash -c 'sqlplus -s scott/book@192.168.100.78:1521/book:shared <<< "host sleep 20" '
4.查询select name,pool,bytes from v$sgastat where name like '%NETWORK%';,记下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测试情况不同:
SYS@test> @ &r/ver1
PORT_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 - 64bi
SDU 开始NETWORK BUFFER 结束NETWORK BUFFER 差值 差值/100/SDU
-------------------------------------------------------------------------------------------------------------------------
10000 139824 12025456 12025456-139824 = 11885632 11885632/100/10000 = 11.88563200000000000000
20000 139824 14025456 14025456-139824 = 13885632 13885632/100/20000 = 6.94281600000000000000
30000 139824 16025424 16025424-139824 = 15885600 15885600/100/30000 = 5.29520000000000000000
32767 139824 16579040 16579040-139824 = 16439216 16439216/100/32767 = 5.01700369273964659566
40000 139824 16579024 16579024-139824 = 16439200 16439200/32767/100 = 5.01699880977813043610
-------------------------------------------------------------------------------------------------------------------------
--//可以看出10g SDU最大是32767.
--//后面的差值/100/SDU很大时才接近5个SDU.
5.最后注意收尾.
--//取消修改设置为缺省值.略.
