[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等待事件.不过整个网络很慢的情况下,也许需要,不过没有对照很难比较是否是网络出现问题.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2648449/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2648449/