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