[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等待事件.不过整个网络很慢的情况下,也许需要,不过没有对照很难比较是否是网络出现问题.