[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的測試.放棄探究.