Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    50

    Unanswered: How to find cpu usage by each user session

    Hi,
    How can I find the amount of CPU being used by each session.

    Ishan.

  2. #2
    Join Date
    Jan 2004
    Location
    Scottsdale, AZ
    Posts
    106
    Review Microstate Response Time Performance Profiling (MRPP), which can be found at http://www.orapub.com/cgi/genesis.cgi?p1=sub&p2=abs147.
    You will need to register to download it, which is free to register.
    MRPP won't explicitly answer your question on how do you ascertain the cpu usage of a user, but it will show you that the time spent is inaccurate.
    "Take Control!
    Make The Choice!
    Accept The Consequences!
    Deal With The Results! "

  3. #3
    Join Date
    Mar 2004
    Location
    Champaign, Illinois USA
    Posts
    8
    It does matter what platform you are talking about....What Operating System is Oracle installed on? (Unix,Windows,Linux, other..?)

  4. #4
    Join Date
    Mar 2004
    Posts
    50
    Originally posted by saundman2000
    It does matter what platform you are talking about....What Operating System is Oracle installed on? (Unix,Windows,Linux, other..?)
    I am using windows NT.

  5. #5
    Join Date
    Mar 2004
    Location
    Champaign, Illinois USA
    Posts
    8
    Ah Windows... First, you have to realize that CPU information is not stored in the database and so you can't simply run a query that returns cpu information. So this means that you have to tie information from the OS (in this case "Task Manager") to the session information from Oracle.

    And now the downside to Windows in this regard: Windows works with services and Oracle operates a single, multi-threaded process and so individual session information cannot be discerned from "Task Manager"

    So, you are out of luck my friend unless you have a 3rd party tool (most of the ones I know of cost $$) that can get this information in a Windows environment. One tool I know of that can do this is Quest's "Spotlight". I think "Oracle Enterprise Manager" also does a fine job on this and it is FREE!!! (if you have The Enterprise Edition of Oracle)

    Good Luck

  6. #6
    Join Date
    Feb 2004
    Location
    Riyadh
    Posts
    24
    Hello,
    Check the script i am attaching.

    HTH,
    Sudhakar
    Attached Files Attached Files

  7. #7
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273

    Re: How to find cpu usage by each user session

    Originally posted by ishanbansal
    Hi,
    How can I find the amount of CPU being used by each session.

    Ishan.
    PHP Code:
    SELECT se.usernamess.sidROUND (value/100"CPU Usage"
    FROM v$session sev$sesstat ssv$statname st
    WHERE ss
    .statistic# = st.statistic#
       
    AND name LIKE  '%CPU used by this session%'
       
    AND se.sid ss.SID 
       
    AND se.username IS NOT NULL
      ORDER BY value DESC

    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  8. #8
    Join Date
    Mar 2004
    Posts
    50
    Hi

    In both the sudhakar's and bhavin's script ; the CPU usage is coming as 0 for all the sessions.
    Am I missing something ?

    Thnx

    Ishan

  9. #9
    Join Date
    Feb 2004
    Location
    Riyadh
    Posts
    24
    What are the schema's you are looking at -- if you can specify it we can look at it from that point of view, because when i run that script i get the cpu usage as well as Disk I/O for almost all the sessions.

    Sudhakar

  10. #10
    Join Date
    Mar 2004
    Posts
    50
    Originally posted by sudhakarpv5
    What are the schema's you are looking at -- if you can specify it we can look at it from that point of view, because when i run that script i get the cpu usage as well as Disk I/O for almost all the sessions.

    Sudhakar
    Hi Sudhakar,

    I am logging in as 'system' and want to know about cpu usage by all the sessions that are open on that database , irrespective of the schema .

    I am able to get Disk I/O with your script, but CPU usage is coming as zero for all the sessions.

    Thanks
    Ishan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •