[20180922]等待事件SQLNet more data from client 4.txt

来源：这里教程网时间：2026-03-03 12:01:55 作者：

[20180922]等待事件SQLNet more data from client 4.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.测试建立脚本: --//建立脚本大小8192,命名8192.txt(注意文件格式是linux格式),脚本很长不在贴出. 3.建立测试脚本: --//先执行如下: CREATE TABLE tt AS SELECT sysdate d,0 c,P2,TIME_SINCE_LAST_WAIT_MICRO FROM V$SESSION_WAIT_HISTORY WHERE sid = 1 AND event = 'SQL*Net more data FROM client'; D:\temp\test> cat init.sql variable i number ; exec :i := 8192; column sid new_value v_sid select sid from v$mystat where rownum=1; set verify off set head off D:\temp\test> cat loop.sql @@8192.txt insert inot tt select sysdate,:i,P2,TIME_SINCE_LAST_WAIT_MICRO from V$SESSION_WAIT_HISTORY where sid=&v_sid and event='SQL*Net more data from client'; host sed -i -e "3s/^.//g" 8192.txt exec :i := :i - 1; --//注:8192.txt 第3行最好长一些.至少包括394个字符. D:\temp\test> cat loop1.sql @@loop.sql @@loop.sql @@loop.sql @@loop.sql .... .... .... @@loop.sql --//写394行. D:\temp\test> wc loop1.sql 394 394 4728 loop1.sql 4.测试结果如下: @ init.sql SCOTT@test01p> @ init.sql PL/SQL procedure successfully completed. SID ---------- 166 SCOTT@test01p> @ loop1.sql ... 2018-09-22 21:23:16 1 row created. PL/SQL procedure successfully completed. 2018-09-22 21:23:16 0 rows created. PL/SQL procedure successfully completed. 2018-09-22 21:23:16 0 rows created. PL/SQL procedure successfully completed. --//仅仅最后2行没有遇到这个等待事件. 5.继续分析: SCOTT@test01p> select * from tt where c > 8188 order by c desc; D C P2 TIME_SINCE_LAST_WAIT_MICRO ------------------- ---------- ---------- -------------------------- 2018-09-22 21:23:03 8192 336 79 2018-09-22 21:23:03 8191 335 126 2018-09-22 21:23:03 8190 334 78 2018-09-22 21:23:03 8189 333 73 --//可以发现该版本没有11.2.0.4 for linux的情况,这里的P2是正确的,估计linux下应该是bug. --//而且这里的测试就没有linux下遇到的问题. --//sql语句长度减少,P2也随之减少. --//注:实际上查看包还是无法猜到P2=336从那里来的.不再探究. SELECT * FROM (SELECT c, p2, LEAD (p2) OVER (ORDER BY c DESC) p2x FROM tt) WHERE p2 <> p2x + 1; C P2 P2X ---------- ---------- ---------- 7857 1 4 7853 1 52 --//而在7857,7853处出现反复. SCOTT@test01p> select * from tt where c between 7850 and 7860 order by c desc; D C P2 TIME_SINCE_LAST_WAIT_MICRO ------------------- ---------- ---------- -------------------------- 2018-09-22 21:23:14 7860 4 101 2018-09-22 21:23:14 7859 3 72 2018-09-22 21:23:14 7858 2 95 2018-09-22 21:23:14 7857 1 85 =>这里出现反复 2018-09-22 21:23:14 7856 4 62 2018-09-22 21:23:14 7855 3 80 2018-09-22 21:23:14 7854 2 81 2018-09-22 21:23:14 7853 1 88 2018-09-22 21:23:14 7852 52 78 =>这里出现反复 2018-09-22 21:23:14 7851 51 78 2018-09-22 21:23:14 7850 50 79 11 rows selected. SCOTT@test01p> select * from tt where c in (select min(C) from tt); D C P2 TIME_SINCE_LAST_WAIT_MICRO ------------------- ---------- ---------- -------------------------- 2018-09-22 21:23:16 7801 1 185 --//也就是sql语句长度<=7800(如果不包括最后分号,换行,就是7798),不再出现SQL*Net more data from client等待事件. --//为什么出现反复,不清楚,要使用分析数据包工具分析看看. --//比如如果文件大小7852,执行显示P2=52,这个52如何得来的.无法确定. SCOTT@127.0.0.1:1521/test01p> select P2 from V$SESSION_WAIT_HISTORY where event='SQL*Net more data from client'; P2 ---------- 52 --//我查看跟踪的包,这个不像前面linux的测试.放弃探究.