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