This statistics represents the total time spent in database calls and is an indicator of the total instance workload.

It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions).

i.e. DB TIME is time spent either actively working or actively waiting

What this effectively means is that TM_DELTA_CPU_TIME and TM_DELTA_DB_TIME have no context without TM_DELTA_TIME.

So when looking at TM_DELTA_CPU_TIME and TM_DELTA_DB_TIME, all other sample times/periods are pretty much irrelevant apart from TM_DELTA_TIME – the latter is the only value that gives context to the other two.

TM_DELTA_DB_TIME is time spent actively working or actively waiting, some of this might be time spent on CPU – TM_DELTA_CPU_TIME – the rest will be time spent actively waiting on some non-idle wait events plus some overheads no doubt.

And TM_DELTA_DB_TIME should never exceed TM_DELTA_TIME, right?

The difference between TM_DELTA_TIME and TM_DELTA_DB_TIME should be idle time.

Sounds like something that might be quite useful particularly to counterbalance some of the pitfalls of looking at sampled data?

How might we investigate this further?

Shall we start with a plsql loop that should do nothing but sit on CPU for a short while?

As expected, we see from TM_DELTA_DB_TIME that we are mainly spending 100% of our time actively working or actively waiting.

But, because we’ve had to do some IO, the proportion of that time which is CPU has reduced and the difference between TM_DELTA_DB_TIME and TM_DELTA_CPU_TIME should be roughly the time spent actively waiting during that TM_DELTA_TIME.

But in this output immediately above, do we start to see some of the limitations in this data?

So, the good news would be that the values from the buffer are not just copied down but seem to be an aggregation, presumably of the sampled rows. I’m not sure because some of the numbers add up, some don’t.

But the big, bad news is that there are similar accounting bugs – note the values in the first two rows for TM_DELTA_DB_TIME.

It should not be possible for TM_DELTA_DB_TIME to be greater than TM_DELTA_TIME.

As a result, this data seems occasionally unreliable and this unreliability undermines it’s usefulness.

Unless of course someone else has another explanation?

Share this:

Like this:

Related

6 Responses to Thoughts on TM_DELTA_TIME

I have tried to use these three columns and find excessively large TM_DELTA_CPU_TIME appearing in both V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY (naturally following from the source). This is in 11.2.0.2 A search on support.oracle.com doesn’t yield any useful notes.

just to give some more thoughts on this: All these columns added in 11.2 are actually used by Real-Time SQL Monitoring to populate the “Metrics” tab in the “EM/Active” (flash-based) version of the report.

There is an interesting query run by the DBMS_SQLTUNE.BUILD_STASH_XML function as part of generating the final XML embedded into the HTML document representing the document.

This uses all these newly added fields to come up with some bucket-based aggregation of that data which in turn seems to be used by the function PL/SQL code to generate the corresponding XML data the “Metrics” tab content is based on.

The query also seems to filter out those “outliers” that you’ve identified.

Note that my XPLAN_ASH tool in “experimental” mode also tries to analyze that data (the I/O related part) but it looks like that at least the I/O figures are not really corresponding to the actual amount of I/O – quite often they seem to fall short of the actual amount.

I haven’t investigated if a common cause for this can be found, but I have test cases where I can see that the I/O related graphs in the “Metrics” tab show the same inconsistency, so it doesn’t look a problem of my code handling the ASH data.

If I am doing the math correctly, it looks like there 985 seconds in your first session record in ASH at 15:05. This is about 16 minutes. Your previous record was 2:45PM, or about 20 minutes prior. I am wondering if you didn’t do something else in your session at about 2:49PM, and then nothing for 16 minutes. Could there be a bug where the time is recorded since the previous measurement? I know that defeats the entire purpose of tracking “active” sessions, but the math at least makes me ask the question.