[20190622]收集SQLNet Message From Client資訊.txt

[20190622]收集SQLNet Message From Client資訊.txt

--//我曾經在連結提到http://blog.itpub.net/267265/viewspace-2144051/=>[20170824]SQL/Net message from client與網路丟包模擬
--//.txt,出現網路緩慢或者丟包的情況,應該關注的是SQL*Net message from client.而SQL*Net message to client應該不考慮.
--//注：網路測試最好使用如下連結指令碼，http://blog.itpub.net/267265/viewspace-2218147/=>[20181031]模擬網路問題.txt，前面
--//的連結可能出現很難控制的情況。

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> select * from v$event_name where lower(name) like lower('%&&1%');
EVENT#   EVENT_ID NAME                         PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS DISPLAY_NAME                CON_ID
------ ---------- ---------------------------- ---------- ---------- ---------- ------------- ----------- ---------- --------------------------- ------
   414 2067390145 SQL*Net message to client    driver id  #bytes                   2000153315           7 Network    SQL*Net message to client   0
   415 3655533736 SQL*Net message to dblink    driver id  #bytes                   2000153315           7 Network    SQL*Net message to dblink   0
   418 1421975091 SQL*Net message from client  driver id  #bytes                   2723168908           6 Idle       SQL*Net message from client 0
   420 4093028837 SQL*Net message from dblink  driver id  #bytes                   2000153315           7 Network    SQL*Net message from dblink 0

--//這樣就意味一個問題，當網路出現問題時必須收集SQL*Net message from client這個空閒等待事件。
--//實際上修改引數"_ash_sample_all"=true;就可以實現。測試如下：

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
         0

2.測試:
SYS@test> @ hide _ash_sample_all
NAME            DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
--------------- ------------------------------------------------------------------ ------------- ------------- ------------
_ash_sample_all To enable or disable sampling every connected session including on TRUE          FALSE         FALSE
                es waiting for idle waits

--//設定為true,idle waits事件也會收集.

SCOTT@test01p> alter system  set "_ash_sample_all"=true scope=memory;
alter system  set "_ash_sample_all"=true scope=memory
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SYS@test> alter system  set "_ash_sample_all"=true scope=memory;
System altered.

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
        25
--//可以發現已經收集到了'SQL*Net message from client'.

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
        87

SCOTT@test01p> host sleep 4

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
        93

3.還原:

SYS@test> alter system set "_ash_sample_all"=false scope=memory;
System altered.

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
       124

SCOTT@test01p> host sleep 4

SCOTT@test01p> select count(*) from   v$active_session_history  where EVENT='SQL*Net message from client';
  COUNT(*)
----------
       124

--//一般工作需求很少要求收集idle等待事件.不過整個網路很慢的情況下,也許需要,不過沒有對照很難比較是否是網路出現問題.