Query to Detect Sessions Using up a Lot of Database Resources

 
A common issue among databases are sessions/users that overuse database resources which causes database performance degredation. This can be averted through the use of Oracle’s Resource Manager but it is still a good idea to be prepared.

Detect session queries using too much memory or cpu. Change the order by to cpu_time for cpu and sharable_mem for memory. You can choose to show only active sessions by removing the outer join (+) on vsess.sql_id.

select username,
sid,
serial#,
machine,
vsql.sql_id,
sharable_mem,
runtime_mem,
cpu_time,
executions,
first_load_time,
buffer_gets,
rows_processed,
sql_text
from v$sqlarea vsql,
v$session vsess
where vsess.sql_id (+) = vsql.sql_id
order by sharable_mem desc;

Sessions using too much temporary tablespace (sorting).
10g version (using gv$ or v$sort_usage) 9i you must use gv$ or v$TEMPSEG_USAGE:
SELECT srt.inst_id,
TRUNC((sysdate - sess.logon_time)*1440,2) as "Session Duration (mins)",
TRUNC((srt.blocks * tblsp.block_size / 1024 / 1024 ),2) AS "TEMP USAGE IN MEGABYTES",
srt.sqladdr AS "ACTIVE SQL ADDRESS",
srt.username,
sess.status,
sess.sid,
sess.serial#,
sess.process,
sess.module,
sess.machine,
sess.program,
area.sql_text AS "ACTIVE SQL TEXT"
FROM GV$SORT_USAGE srt,
DBA_TABLESPACES tblsp,
GV$SESSION sess,
GV$SQLAREA area
WHERE srt.tablespace = tblsp.tablespace_name
AND srt.session_addr = sess.saddr
AND sess.sql_id = area.sql_id (+)
AND srt.inst_id = sess.inst_id
ORDER BY 3 desc;

Detect sessions using too much undo space:
SELECT s.username,
t.xidusn,
t.ubafil,
t.ubablk,
t.used_ublk
FROM v$session s,
v$transaction t
WHERE s.saddr=t.ses_addr;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart