long running session and sql*net message from client wait event

1.About check_mk metric:ORA_{SID}_Long_Running_Session

This is a custom monitoring metric on check_mk platform,
the threshold is defined in /usr/lib/check_mk_oracle/MAIN/conf/perf_{SID}_sessionlong.json
Note: here it will show as ORA_PCIDEC_Long_Running_Session event in our check_mk platform

2.solution:

2.1 check whether there are long session in system.

SELECT SE.SID,SE.SERIAL#,to_char(logon_time,'YYYY-MM-DD HH24:MI:SS') LOGON_TIME,SE.STATUS,SE.OSUSER,SE.MACHINE,SE.PROGRAM,SE.BLOCKING_SESSION, 
SE.SQL_ID,SE.PREV_SQL_ID ,SE.EVENT,SE.P1TEXT,SE.P1,SE.P2TEXT,SE.P2,SE.P3TEXT,SE.P3,SE.SECONDS_IN_WAIT 
FROM SYS.V_$SESSION  SE , SYS.v_$session_wait SW 
        WHERE SE.SID=SW.SID  AND SE.STATUS='ACTIVE'   AND SE.USERNAME NOT IN ('SYS','SYSMAN','DBSNMP') 
        AND (SE.LAST_CALL_ET/86400) > 1 
        AND ( TO_CHAR(SYSDATE,'YYYYMMDD') <> TO_CHAR(SE.LOGON_TIME,'YYYYMMDD') OR SYSDATE-LOGON_TIME >= 24/24 );           

Output 1:

Acoording to the value returned by second_in_wait column in previous figure
get sql text according to the sql_id column in the previous figure, it shows the session already executed 173062 second
note the wait event column in previous figure, a SQL*Net message from client wait event appear,In general, sqlnet message from client is the “idle wait event experienced by the server while waiting for the client to tell it to do something”.
for example - say the client

at 12:00:00.0000 submits “select * from dual”
spends 30 seconds pondering the results
at 12:00:30.0000 submits “select sysdate from dual”
you will see some 30 seconds of “sql net message from client” wait.

If you have an application that should CONSTANTLY be doing work in the database (eg: a batch process) and you see high “sqlnet message from client”- that is time spent in the client outside the database - and it could be a problem in that case (the client is spending a lot of time doing something OUTSIDE of the database)

1.get sql according to value retuned by sql_id column in the previous figure

SELECT * FROM V$SQLAREA WHERE SQL_ID='490j3btzfrh0x'

Output 2:

2.tell the detail along with the above query results to developer, together disscuss and analysis
3.if confirm that you can terminate the long session, you can issue the following commend in sqlplus or sql developer tool

alter system kill session 'sid,serial#';
sid and serial will be from the value returend by sid and serial# column in the previous output 1
here will be:
alter system kill session '537,24661';

in general, the sql can execute successfully and immediate terminate the session you expected, but it is also possible to return follows:

31. 00000 -  "session marked for kill"
*Cause:    The session specified in an ALTER SYSTEM KILL SESSION command
           cannot be killed immediately (because it is rolling back or blocked
           on a network operation), but it has been marked for kill.  This
           means it will be killed as soon as possible after its current
           uninterruptable operation is done.

you also can confirm the state

select * from v$session where sid=537 and serial#=24661

output shows to only mark the session state to killed, session itself not disappear

if you still hope to terminate the session immediately, you can implement by os level:
1.first to get spid from v$process

select spid from v$process where addr in (select paddr  from v$session where sid=537 and serial#=24661)
spid
1558

2. issue command in os shell to kill the spid

kill -9 1558

wait for a moment to check session again, it will disappear from v$session view

select * from v$session where sid=537 and serial#=24661