SQL query

When DB load high, we can try to find out the current situation by these queries:

--Top queries in last 5 min

SELECT * FROM
(select parsing_schema_name as users, sql_id, sql_text
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) "CPU Time"
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed Time"
FROM v$sqlarea
WHERE LAST_ACTIVE_TIME BETWEEN (sysdate - 5/1440) AND sysdate
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY "CPU Time" DESC) x
WHERE rownum <=5;


--Top waiting session

SELECT sess.sid, sess.serial#, sess.username, sess.machine, SUM(ash.wait_time + ash.time_waited) wait_time
FROM v$active_session_history ash, v$session sess
WHERE ash.session_id = sess.sid
AND ash.sample_time BETWEEN (sysdate - 15/1440) AND sysdate
GROUP BY sess.sid, sess.serial#, sess.username, sess.machine
ORDER BY 4 DESC;

--Kill session

alter system kill session 'sid,serial#' ;

--Kill multiple session

SELECT 'alter system kill session '''|| a.sid||','||a.serial# ||''';' ses_serial

FROM v$session a,v$process b

WHERE a.username IS NOT NULL AND a.paddr=b.addr AND a.type='USER'

AND a.logon_time> = sysdate- 1/48 --last 30 min

AND a.username = 'PROD7'

AND a.osuser ='sqa'

;