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

[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的测试.放弃探究.

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

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