When tuning an application that is under heavy load, one of the issues is that some queries perform very well most of the time, but occassionally run very long and create application problems. These queries don’t look suspicious from OEM or the AWR reports, but they cause unexplained blips in the application.
If you’re looking for cases where a query runs for a second or more, a good place to look is v$active_session_history.
A useful query is:
SELECT ROUND((last_sample_date-first_sample_date)*24*60*60) seconds,
first_sample_date,
last_sample_date,
username,
program,
module,
action,
session_id,
inst_id,
(SELECT sql_fulltext
FROM v$sql s
WHERE ROWNUM = 1
AND s.sql_id = full_query.sql_id) AS sql_fulltext
FROM
(
SELECT inst_id,
MIN(sample_date) OVER (PARTITION BY sql_exec_id, session_id, inst_id, sql_id) first_sample_date,
MAX(sample_date) OVER (PARTITION BY sql_exec_id, session_id, inst_id, sql_id) last_sample_date,
sample_date,
user_id,
session_id,
sql_id,
sql_exec_id,
username,
program,
module,
action
FROM (
SELECT inst_id,
sample_id,
TO_DATE(TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') sample_date,
h.user_id,
h.session_id,
sql_id,
sql_exec_id,
u.username,
program,
module,
action
FROM v$active_session_history h
dba_users u
WHERE sample_time > SYSDATE - (0.25/24) --last 15 minutes
AND u.user_id = h.user_id
AND h.sql_exec_id IS NOT NULL
AND h.sql_id IS NOT NULL
)
) full_query
WHERE sample_date = first_sample_date
AND ROUND((last_sample_date-first_sample_date)*24*60*60) >= 1
ORDER BY first_sample_date, username
/