Can DPA track or report on the amount of time taken by a single execution of a certain query in Oracle?

rharland2012

Hi there!

I'm a new user of DPA - we were fortunate to get greenlit to bring it into our environment to give more visibility to our Oracle backend performance.

I had a question from one of our developers today, and I don't have an answer....so I'm throwing it out to thwack.

Subject line sums it up, really - Can DPA show how long a certain execution of a query takes?

One additional small question as well. My dev also asked if DPA prunes data (that is, excludes recording/capture of certain queries), or does it truly capture all queries performed against the DB?

Thanks for any info!

Find more posts tagged with

oracle query

dpa

Accepted answers

mandevil

Great questions.

First, DPA does not track the time of just one execution unless it is only executed once. This comes down to our methodology and the overhead associated with doing that type of analysis. We track wait times and its associated metadata (user, client machine, plan, etc.) every second. However, we only gather statistics (including executions) every 10 minutes. Because of this, if you have 100 executions within those 10 minutes, we can tell you an avg time for execution, but not how long it took any one of them. Gathering stats more often increases overhead on the monitored instance and we strive to not cause performance impacts while helping you resolve performance issues. Kind of a balancing act.

Second, we do exclude background processes, idle waits, etc. Having said that, we collect all non-excluded ACTIVE sessions and what they are running every second by default. So when we poll your monitored instance, it's akin to running select from v$session where status='ACTIVE' kind of thing.

Hope that helps.

All comments

mandevil

Great questions.

Hope that helps.

rharland2012

Perfect - thank you very much!

rharland2012

I have a followup question regarding this, and was hoping to get some clarification if possible.

So let's say a session goes from active to inactive. At that point, does DPA stop collecting or storing info about that session because the status changed? The behavior of the particular app that talks to our Oracle DBs marks sessions inactive after some amount of time, and I'm curious as to what happens with regard to monitoring those sessions.

Thank you for any info!

mandevil

When sessions go inactive, they will be excluded from our poll for that second. If they become active again, we'll "see" them again.

Most apps use connection pooling (or should). Let's say the app spins up 20 connections in a pool. Of those 20 connections, at any given moment, it may be using say 9 of them for SQL requests to the DB. We'll see those 9. We don't care about the other 11 as they aren't waiting on the database (their just inactive).

HTH.

rharland2012

Thank you!