Thanks for the question, Nitin.
Asked: August 05, 2017 - 2:34 pm UTC
Last updated: August 06, 2017 - 6:08 pm UTC
Version: 12.1.0.2
Viewed 1000+ times
You Asked
Hi Tom,
In our OLTP database, business requirement is response should be sent within 5 secs, anything more than that is considered a delay. We have fixed plans for all the sqls in the journey and they should not take more than 0.5 secs ideally. But daily strange thing happens and 5-10 requests get delayed, it happens only with span of 1mins or 30 sec and then back to normal.
So we need to view or tool which can find all such sql which took more than 5 secs. V$sql_monitor only captures cpu and io based queries taking more than a second.
Any option for this at least we need to track schema level.
Thanks,
Nitin
and Connor said...
Some options
1) v$active_session_history
select sql_id, sql_exec_id, count(*)
from v$active_session_history
group by sql_id, sql_exec_id
having count(*) >= 5
because we take a sample each second, so the count loosely equates to elapsed time.
2) v$sql
select sql_id, elapsed_time, executions
from v$sql
where elapsed_time / nullif(executions,0) / 1000000 > 5
These are not guaranteed precise because we're averaging and/or sampling, but it definitely enough to pick candidates for investigation
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment