[20180925]等待事件SQLNet more data from client 6.txt

[20180925]等待事件SQLNet more data from client 6.txt

--//前幾天測試分析等待事件SQLNet more data from client,今天測試改變檔案大小後,檢視檢視V$SESSION_WAIT_HISTORY看到P2的變化.
--//測試使用儲存過程的情況:

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

--//sqlnet.ora檔案設定DEFAULT_SDU_SIZE=8192

2.測試建立指令碼:

$ cat sleep2.sql
CREATE OR REPLACE procedure sleep2 (seconds IN NUMBER)
is
d_date date;
BEGIN
select /*+
123z567890123z567890123z567890123z567890123z567890123z567890123
...
a123z567890123z567890123z567890123z567890123z567890123z5
*/
sysdate into d_date from dual;
END;
/

--//裡面的sql語句足夠長.注意一定帶加號,不然註解在呼叫儲存過程時會過濾掉.
--//參考連結:
--//[20180925]等待事件SQLNet more data from client 5.txt
--//

3.測試:
--//session 1:
SCOTT@test01p> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID                     PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- -------------------- ------- ---------- ---------------------------------------------
         8         41 356:5176                 DEDICATED 624                       52         13 alter system kill session '8,41' immediate;

--//session 2:
SYS@test> select * from V$SESSION_WAIT_HISTORY where sid=8 and event='SQL*Net more data from client';
no rows selected

--//session 1:
SCOTT@test01p> @sleep2
Procedure created.

--//session 2:
SYS@test> select * from V$SESSION_WAIT_HISTORY where sid=8 and event='SQL*Net more data from client';
       SID       SEQ#     EVENT# EVENT                          P1TEXT             P1 P2TEXT             P2 P3TEXT             P3  WAIT_TIME WAIT_TIME_MICRO TIME_SINCE_LAST_WAIT_MICRO     CON_ID
---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- -------------------------- ----------
         8          3        376 SQL*Net more data from client  driver id  1413697536 #bytes           1892                     0          0              43                        264          3

--//建立儲存過程,透過client傳輸指令碼.P2=1892,這個是正常的情況.

--//sesson 1:
SCOTT@test01p> exec sleep2(10);
PL/SQL procedure successfully completed.

SCOTT@test01p> exec sleep2(11);
PL/SQL procedure successfully completed.

SCOTT@test01p> exec sleep2(12);
PL/SQL procedure successfully completed.

SCOTT@test01p> exec sleep2(14);
PL/SQL procedure successfully completed.

--//session 2:
SYS@test> select * from V$SESSION_WAIT_HISTORY where sid=8 and event='SQL*Net more data from client';
no rows selected

--//可以發現儲存過程中的超長sql語句不受影響.