[20180918]等待事件SQL/Net more data from client.txt

[20180918]等待事件SQL/Net more data from client.txt

--//很少關注SQL/Net more data from client,最近正好看一份awr報表比較靠前.
--//網上google看看.

SQL/Net more data from client As observed in AWR, network  wait is because of the the shadow process has received part
of a call from the client process (for example, SQL*Plus, Pro*C, and JDBC) in the first network package and is waiting
for more data for the call to be complete. Examples are large SQL or PL/SQL block and insert statements with large
amounts of data.

--//從提示可以看出就是sql語句寫的太長是一個原因,自己驗證看看.

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

SCOTT@test01p> @ ev_name "SQL*Net more data from client"
EVENT#   EVENT_ID NAME                          PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME                  CON_ID
------ ---------- ----------------------------- ---------- ---------- ---------- ------------- ----------- ---------- ----------------------------- ----------
   376 3530226808 SQL*Net more data from client driver id  #bytes                   2000153315           7 Network    SQL*Net more data from client 0

2.建立測試指令碼:
$ cat d:\tmp\text.txt
select /*+ dssdjfsdfsdfdsfsdfdsfjkdhsjkfhdskjfhdskjfhsdfkdfjh
11111...
......
111111111111111111111
*/ sysdate from dual;

D:\tmp>ls -l test.txt
ls -l test.txt
-rw-rw-rw-   1 user     group     3233179 Sep 18 21:34 test.txt
--//大小3M.

3.測試:
SCOTT@test01p> @ 10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.

SCOTT@test01p> @ d:\tmp\test.txt
SYSDATE
-------------------
2018-09-18 21:36:57

SCOTT@test01p> @ 10046off
Session altered.

--//檢查轉儲檔案:
*** 2018-09-18 21:36:52.789
*** SESSION ID:(86.1153) 2018-09-18 21:36:52.789
*** CLIENT ID:() 2018-09-18 21:36:52.789
*** SERVICE NAME:(test01p) 2018-09-18 21:36:52.789
*** MODULE NAME:(SQL*Plus) 2018-09-18 21:36:52.789
*** ACTION NAME:() 2018-09-18 21:36:52.789
*** CONTAINER ID:(3) 2018-09-18 21:36:52.789
 
WAIT #357727064: nam='SQL*Net message to client' ela= 7 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6457218928

*** 2018-09-18 21:36:57.617
WAIT #357727064: nam='SQL*Net message from client' ela= 4828299 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=6462066625
CLOSE #357727064:c=0,e=9,dep=0,type=1,tim=6462066791
WAIT #0: nam='SQL*Net more data from client' ela= 11473 driver id=1413697536 #bytes=3223974 p3=0 obj#=-1 tim=6462079887
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

*** 2018-09-18 21:36:57.881
=====================
PARSING IN CURSOR #358097392 len=3231828 dep=0 uid=109 oct=3 lid=109 tim=6462329389 hv=111498884 ad='7ff2425c088' sqlid='gcff5kc3aapn4'
select /*+ dssdjfsdfsdfdsfsdfdsfjkdhsjkfhdskjfhdskjfhsdfkdfjh
....

--//注意看下劃線內容,可以發現#bytes=3223974,大小與檔案test.txt相當,也驗證了sql語句太長傳輸到服務端導致這樣的等待事件.
--//相關連結:
http://blog.itpub.net/267265/viewspace-2199981/ => [20180813]sqlplus arraysize設定與SDU.txt
http://blog.itpub.net/267265/viewspace-2144051/ => 0824SQL*Net message from client 丟包模擬
http://blog.itpub.net/267265/viewspace-2130032/ => [20161208]SQL*Net message from client

--//我在連結提到當網路異常時,應該特別關注SQL*Net message from client idle等待事件.而不是SQL*Net message to client.
--//另外SQL*Net message事件的包都很小,僅僅#bytes=1 (包括SQL*Net message from dblink的等待事件).