[20180926]共享池中的NETWORK BUFFER.txt

[20180926]共享池中的NETWORK BUFFER.txt

--//最近幾天一直在探究SQL*Net more data from client 相關等待事件,發現SDU相關,自己也網上探究一些帖子,找到劉公的一個帖子.

--//連結:%e5%85%b1%e4%ba%ab%e6%b1%a0%e4%b8%ad%e7%9a%84network-buffer.html

--//雖然大多少場合使用dedicated server模式，而如果採用共享伺服器模式,NETWORK BUFFER將被大量使用。MOS文件[741523.1]敘述了

--//NETWORK BUFFER的主要用途:

On 10.2, after upgrading from 9iR2, the following error occurs:

ORA-07445: exception encountered: core dump [] [] [] [] [] []

plus

Dispatcher Trace file contains an ORA-4031 Diagnostic trace, with:

Allocation request for: NETWORK BUFFER

…followed by…

found dead dispatcher 'D000', pid = (12, 1)

The amount of memory used by NETWORK BUFFERs in the shared pool has significantly grown between 9.2 and 10.2.  The

side-effect is to run-out of Shared Pool memory (reporting an ORA-4031), when a large number of sessions are connecting

to the server (in the order of 1000's).

While a session is being established, we allocate 3 buffers each of 32k in size.  After the session is established, we

use the 3 SDU-sized buffers, however we do not deallocate the 3x32k buffer we allocated initially.

This issue has been logged in unpublished Bug 5410481.

Additionally, there is  Bug 6907529.

NS buffers are allocated based on the SDU specified by the user. The negotiated SDU could be considerably lower. The

difference between these two is wasted.

For example, the dispatcher specifies an SDU of 32k. Clients, by default, use an SDU of 8k. The remaining 24k is never

used.

Issue in Bug 6907529 is fixed in 11.2.

Bug 5410481 is fixed in 10.2.0.3.

As a workaround to 5410481, the ADDRESS part of DISPATCHERS parameter can be used to specify a smaller SDU size.

For example:

DISPATCHERS="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp))(SDU=8192))"

To implement the change;

connect to the database as SYSDBA

alter system set dispatchers='(address=(protocol=tcp)(host=IP-Address)(sdu=8192))(dispatchers=DispatcherCount)' scope=spfile;

re-start the database

--//當然這個bug在11.2.0.4下已經不復存在.但是在共享伺服器模式下,NETWORK BUFFER將被大量使用,我還第一次知道,我決定測試看看:

1.環境:

--//上班在11g的環境重複測試看看.

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

SCOTT@book> show parameter dispatchers

NAME            TYPE     VALUE

--------------- -------- -------------------------------------

dispatchers     string   (PROTOCOL=TCP) (SERVICE=book,bookXDB)

max_dispatchers integer

SCOTT@book> show parameter shared_servers

NAME               TYPE     VALUE

------------------ -------- -----

max_shared_servers integer  1

shared_servers     integer  1

SCOTT@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                 POOL              BYTES

-------------------- ------------ ----------

NETWORK BUFFER       shared pool       65576

2.建立測試指令碼:

$ cat 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 60

quit;

EOF

done

3.測試:

SCOTT@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                 POOL              BYTES

-------------------- ------------ ----------

NETWORK BUFFER       shared pool      477176

$ source b.sh

SCOTT@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                 POOL              BYTES

-------------------- ------------ ----------

NETWORK BUFFER       shared pool     4628744

--//4628744-477176 = 4151568

--//4151568/100/1024 = 40.54kb

4.繼續測試,修改SDU=32768.

--//修改sqlnet.ora加入,安裝劉工文章介紹,最大32767.

DEFAULT_SDU_SIZE=32768 

SCOTT@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                 POOL              BYTES

-------------------- ------------ ----------

NETWORK BUFFER       shared pool      436016

$ source b.sh

SCOTT@book> select name,pool,bytes from v$sgastat where name like '%NETWORK%';

NAME                  POOL              BYTES

--------------------- ------------ ----------

NETWORK BUFFER        shared pool    16916464

--//16916464-436016 = 16480448

--//16480448/100/1024  = 160.94k

5.思考:

--//%e5%85%b1%e4%ba%ab%e6%b1%a0%e4%b8%ad%e7%9a%84network-buffer.html

你可能會問SDU是什麼？Oracle NET快取的資料以SDU為基本單位，SDU即 session data unit，一般預設為8192 bytes。當這些資料單元

被寫滿，或被client讀取時，他們將被傳遞給Oracle Network層(oracle network layer)。譬如Data Guard環境中redo傳輸的每個Chunk

往往要大於8192 bytes，那麼預設的SDU就不太適用。當有大量重做資料要傳輸到standby庫時，增大SDU buffer的大小可以改善Oracle的

網路效能。你可以很方便的透過修改sqlnet.ora配置檔案來修改SDU,如在該檔案內加入以下條目:

DEFAULT_SDU_SIZE=32767 /*修改全域性預設SDU到32k*/

當然你也可以在tnsnames.ora中定義服務別名時個別指定SDU，下文我們會用到。

如上文所述在版本10.2.0.3以前當會話建立時，Oracle會以dispatchers引數定義的SDU為單位，分配3個單位的NETWORK  BUFFER，而實際

上client端可能並未指定和dispatchers一致的SDU，若dispatchers中定義的SDU為32k，而client使用預設的8k SDU,則一個會話可能要浪

費3*32-3*8=72k的NETWORK BUFFER。

為什麼共享伺服器模式下會用到共享池中的NETWORK BUFFER，而獨享伺服器模式下沒有呢？因為在獨享伺服器模式下每個會話所分配的三

個SDU是從PGA中獲取的；當使用共享伺服器模式時會話與服務程式形成一對多的對映關係，這三個SDU 的NETWORK BUFFER同UGA一樣轉移

到了SGA中。

--//這樣如果預設設定很大,不管共享還是專用模式,導致記憶體消耗增加.

--//共享伺服器模式消耗在共享池,如果大量連線使用這個模式,消耗也很大.而且這樣配置要求共享池要設定大一些.

--//專用伺服器模式消耗在PGA.也會導致記憶體的消耗.