Skip to Main Content
  • Questions

  • Calculating resource usage by database users

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 12, 2010 - 12:20 am UTC

Last updated: October 25, 2010 - 8:51 am UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

We want to calculate the resources used by the database users based on their userid. For example, at a given time what amount of cpu, memory or disk i/o is consumed by a database user on the database server. Some of our databases are shared by different applications and we want to identify how many resources each application is using. I think that v$session_time_model or v$sesstat can be used to get the CPU and memory usage details. I have a couple of questions regarding the same.

1. The cpu usage shown by the above views is in milliseconds. How can I convert it to percentage of total cpu at a time?

2. Is there any table or view in OEM grid control which stores this information for all the databases? It would be easy to query the view and get the information at one time rather than querying each database separately. Also since v$ views are dynamic, it would be nice if some table stores the usage history.

Many thanks.

and Tom said...

1) you cannot, you have a cumulative number (V$ style information) and a quantum in time (your concept of "right now". In practical terms - you cannot tell what % an individual is using of any resource at a *point* in time because points in time are infinitely small.

what you CAN do is

a) take a snapshot of data (copy v$ information)
b) wait some DURATION of time
c) take another snapshot of data

and then you can subtract (c) from (a) and report back that during this INTERVAL of time (that is your point in time), this guy used X units of CPU - we had N units of CPU available to us (N = number of cpus * interval of time in your units) - therefore he used Y% of the cpu (X/N*100)

The active session history (ASH) data can be used to get this information as well.


2) what information? The v$ tables have the cumulative values, the DBA_HIST views have point in time copies of these counters historically. You can use them (dba_hist views) instead of snapshotting your own data - but the interval is typically "60 minutes" and hence your duration would be 60 minutes at least.


Rating

  (1 rating)

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

Comments

John, October 18, 2010 - 7:01 am UTC

"but the interval is typically "60 minutes" and hence your duration would be 60 minutes at least."

1. Could you explain your above statement?
2. Can't we sum up _delta columns on dba_hist_sqlstat to get resource usage by user?




Tom Kyte
October 25, 2010 - 8:51 am UTC

1) they were asking for "point in time" data - at a given time what amount of cpu, memory or disk i/o is consumed by a database user on the database server.

I was telling them that "you cannot get that, you have to measure the amount used in some quantum of time - an interval, a duration".

There is no such concept as "how much cpu is Bob using RIGHT NOW".
There is a concept of "how much cpu did Bob using in the last second".

2) that would be for a duration, an interval - which is what I was saying they would have to use. Yes.
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.