[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语句不受影响.

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

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