文章目录
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
- 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