[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的等待事件).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2214441/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2214441/