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 SUSPENDWatch Unix, you will see suspended session going down on $>top
3. To resume session execute from sqlplus
oradebug RESUME
No comments:
Post a Comment