Skip to Main Content
  • Questions
  • View that can be used to get last SQL elapsed time

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 06, 2019 - 3:38 am UTC

Last updated: February 07, 2019 - 2:11 am UTC

Version: 12.2

Viewed 1000+ times

You Asked

Hello Gentlemen,

We currently don't have OEM tool. Is there a data dictionary view that can be queried that will provide the last elapsed time for a particular query that has an elapsed time of less than 5 seconds? v$sql view has a column called ELAPSED_TIME but that is cumulative. v$sql_monitor view would be perfect, but it only tracks those that have taken more than 5 seconds.

Thank you for your help.

John

and Connor said...

Probably your best bet would be v$active_session_history. You get a row every second, so something like:

select sql_id, sql_exec_id, count(*)
from v$active_session_history
group by sql_id, sql_exec_id

gives you execution times to the granularity of a second. Really fast SQL's might not show up at all, or might show up as a blip (so they are *not* really 1 second long) but it should be a good place to start.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Perfect!

John Cantu, February 06, 2019 - 4:22 pm UTC

Thank you once again, Conner! That worked perfectly.

One thing though, why are do some records have NULL for SQL_EXEC_ID? I see that the records span from date the database started to current time.




Connor McDonald
February 07, 2019 - 2:11 am UTC

I'm not sure why. But if you're looking to spruce up your query, a friend Kyle tackled this problem a while back

http://datavirtualizer.com/finding-the-slowest-sql-execution-of-the-same-query/

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.