cpu usage by v$views

[mybear]

Can you use any v$views to see the cpu usage a of a user?

[roobaron]

Have a look at the doco (Reference section)

for a session/user use v$sesstat
for system use v$sysstat

BTW needs the parameter timed_statistics = true

Have Fun

[Shestakov]

And addition you should st OS_TIMED_STATISTICS = n
where n-seconds interval for collect OS statictict include Processors utilization.

[julian]

I have never heard of OS_TIMED_STATISTICS. Is there really such a parameter?

[Shestakov]

I'm sory - this parameter: TIMED_OS_STATISTICS

It specifies the interval (in seconds) at which Oracle collects operating system statistics when a request is made from the client to the server or when a request completes.

On dedicated servers, Oracle collects operating system statistics at user logon and after each subsequent client invocation through the OCI into the Oracle server as a remote procedure call message.

On shared servers, Oracle collects statistics when client calls to Oracle are processed.

Information collect in following statistics:

OS User time used
OS System time used
OS Maximum resident set size
OS Integral shared text size
OS Integral unshared data size
OS Integral unshared stack size
OS Page reclaims
OS Page faults
OS Swaps
OS Block input operations
OS Block output operations
OS Socket messages sent
OS Socket messages received
OS Signals received
OS Voluntary context switches
OS Involuntary context switches

-------------------------------------------------
PS:
Usually I used this parameter when me need:

OS Page reclaims
OS Page faults
OS Swaps
OS Block input operations
OS Block output operations

or
OS User time used
OS System time used
(if we had nondedicated Oracle server or multiply Oracle instances on 1 box)
-------------------------------------------------





[Edited by Shestakov on 10-29-2001 at 12:54 PM]

[mybear]

Thanks for all the info but that does not answer my question.
In other words, how do I use v$session,v$sysstat and v$sesstat to
find out how nuch CPU a particular user is using.

[roobaron]

You look at the columns in the table and write a query to join v$sesstat to v$session via the column SID (and v$statname to resolve the statistic# to a name)

Here is an example

select b.username,b.sid,c.name,a.value
from v$sesstat a, v$session b, v$statname c
where b.sid = a.sid
and a.statistic# = c.statistic#
and value > &min
and b.username like '&user'
order by b.username,c.name

To find out which users are currently logged in and active you need to look at v$session, or the above SQL could include a condition on status.

Sorry for being vague... i believe people learn better thru discovery by themselves rather than being spoonfed the complete answer

Have Fun

[ngwh]

not true...u help them by throwing them some light and depends on wat light u
throw :P

[clinton]

I thought we are supposed to share our knowledge on this board. No one person knows everything.You share ideas.

sorry Mybear, I wish I know the answer to this