Tuesday, February 5, 2019

Suspend sessions and release CPU resources

Summary 
Lets say that 50 users are currently active to the database and running heavy queries and the CPU utilization is 100%. Suddenly a new request is coming with a maximum priority. The new request must run and finish at the expected time as soon as possible, but with the load right currently is impossible because of all the other 50 active users. What do you do if you don't want to kill the other sessions? 

One possible solution 
You suspend the others sessions and release CPU resources. 

How to suspend one session. 
1. Find the SPID of the session that you want to “pause” with the query below
SELECT DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE-LOGON_TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
v$session.SID, v$session.SERIAL#, v$process.SPID spid, v$session.process CLPRID,
v$session.USERNAME, STATUS, OSUSER, MACHINE, v$session.PROGRAM, MODULE, action, SQL_HASH_VALUE,
'alter system kill session ' || '''' || v$session.SID || ', ' || v$session.SERIAL# || '''' || ' immediate;' kill_sql
FROM v$session, v$process
WHERE v$session.paddr = v$process.addr
--AND v$process.spid = 23832
--and v$session.process = '26432'
--AND v$session.status = 'INACTIVE'
--AND v$session.username LIKE '%KAPARELIS SPYROS%'
--AND v$session.SID = 4392
--and v$session.sid in (select sid from v$session where SADDR in (select session_addr from v$sort_usage)) --(v$temp_usage)
--and v$session.osuser like 'oracle%'
--and osuser='uidea'
--AND v$session.module LIKE '%qot%'
--and v$session.machine like '%PLHROFORIK92%'
--AND v$session.program LIKE '%QMN%'
--AND v$session.action LIKE 'FRM%'
--and action like '%FRM%OTE%'
ORDER BY logon_time ASC;
2. From sqlplus connect as sysdba
oradebug setorapid SPID
oradebug SUSPEND
Watch Unix, you will see suspended session going down on $>top 
3. To resume session execute from sqlplus
oradebug RESUME

No comments:

Post a Comment