[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.

--//按照%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.最後注意收尾.

--//取消修改設定為預設值.略.

[20190306]共享服務模式與SDU.txt

相關文章