Skip to Main Content
  • Questions

  • Script to monitor long running queries

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Srinivas.

Asked: October 03, 2019 - 8:56 am UTC

Last updated: October 04, 2019 - 12:37 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi TOM,

I have been following your posts since the beginning of my career (6 years). I like your way of giving solutions to critical problems / queries.

I am struggling to get a good script/command to monitor the long running queries in Oracle. In few views, i see the elapsed_time column, but i fee those are the cumulative values of all the executions of that SQL_ID. I will only get the average elapsed time if i do any maths on that.

However, i am looking for the pure response time of a query since it is fired from the client machine, and the response is reached to client. (Network time + parsing time + CPU/IO/Wait time...)

My customer is asking for the split for each of these components (network time , parsing time, actual execution time in DB, actual wait time etc) for each and every query that is triggered from his application. Help me with this please..

Oracle version: 12.1.0.2


and Connor said...

My customer is asking for...

My first question to the customer would be "why?", as in, what is the business intent behind this question? For example, you could run a database in trace mode 24 hours a day and have the exact metrics for every single execution of every query, but in reality, what is the benefit of that (besides having hundreds of gigabytes of trace data :-))

If they are looking for some *testing* or *indicative* data, then yes, running the application in trace mode for (say) an hour or two is a valid approach to get some good data, but wanting that *permanently* doesn't really serve any purpose.

If, on the other hand, they want to know about *outliers*, ie, when things go wrong, then active session history (ASH) is probably your best bet here, in conjunction with the AWR report facilities we provided.

John Beresniewicz did an excellent presentation on all the ways you can exploit ASH here

https://www.oracle.com/technetwork/database/manageability/con9577-ash-deep-dive-oow2013-2031468.pdf



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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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