This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 8:06 AM by sb92075 RSS

V$sessions

yxes2013 Newbie
Currently Being Moderated
Hi All,

I have this output select of V$session. How can I join this to other v$ performance table so that I will be able to get the cpu, i/o, and, memory usage?
confidential
Thanks a lot,
  • 1. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    CPU can be measured via top as shown below
    [oracle@localhost ~]$ top
    
    top - 17:24:09 up 5 days,  7:12,  3 users,  load average: 0.16, 0.12, 0.09
    Tasks: 182 total,   1 running, 181 sleeping,   0 stopped,   0 zombie
    Cpu(s):  1.5%us,  2.7%sy,  0.0%ni, 95.2%id,  0.3%wa,  0.0%hi,  0.3%si,  0.0%st
    Mem:    909120k total,   826888k used,    82232k free,    48428k buffers
    Swap:  1735012k total,   328320k used,  1406692k free,   393776k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND            
    19194 oracle    15   0  2420  972  704 R  2.0  0.1   0:00.02 top                
        1 root      15   0  2156  484  460 S  0.0  0.1   0:11.88 init               
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/0        
        3 root      34  19     0    0    0 S  0.0  0.0   0:02.50 ksoftirqd/0        
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0         
        5 root      10  -5     0    0    0 S  0.0  0.0   1:08.67 events/0     
    RAM an be measured via vmstat as shown below
    [oracle@localhost ~]$ vmstat 6 10
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
     r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
     0  0 328320  83528  48684 394288    1    2    13    42   65   28  1  3 95  0  0
     0  0 328320  83428  48700 394284    0    0     1    43 1029 1097  1  3 96  0  0
     2  0 328320  83296  48716 394300    0    0     1    56  995 1073  3  7 90  0  0
     1  0 328320  83304  48732 394316    0    0     7    79 1006 1098  6 14 79  0  0
     0  0 328320  83296  48748 394316    0    0     0    30 1018 1081  1  3 96  0  0
     0  0 328320  87272  48764 394316    0    0     0    19  998 1068  1  3 96  0  0
     0  0 328320  87264  48772 394316    0    0     1    62 1011 1078  1  2 96  1  0
     0  0 328320  87272  48796 394312    0    0     0    49 1002 1064  1  2 96  0  0
     0  0 328320  87140  48804 394320    0    0     0    25  998 1052  2  4 93  0  0
     0  0 328320  87272  48820 394328    0    0     0    29 1002 1078  1  2 96  1  0
  • 2. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Thanks sb,

    But how can I know which process in the V$session contrubuted the 100% cpu in my "top"?
    top - 10:07:52 up 23:32,  2 users,  load average: 5.60, 6.30, 5.63
    Tasks: 428 total,   2 running, 426 sleeping,   0 stopped,   0 zombie
    Cpu(s):  6.9%us,  1.8%sy,  0.0%ni, 83.6%id,  7.8%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:  12079104k total, 12023624k used,    55480k free,    74924k buffers
    Swap:  6104692k total,   193968k used,  5910724k free, 10699172k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     3177 oraprod   25   0 1683m 293m 290m R 100.5  2.5  10:18.76 oracle
     2867 oraprod   18   0 1691m 302m 293m D  3.0  2.6   0:57.44 oracle
     3040 oraprod   18   0 1681m 224m 222m D  1.0  1.9   0:13.06 oracle
     2775 oraprod   18   0 1791m 439m 339m D  0.3  3.7   3:04.67 oracle
     3410 root      15   0  2584 1288  836 S  0.3  0.0   0:00.19 top
     3608 oraprod   16   0 1692m 181m 172m S  0.3  1.5   0:01.82 oracle
        1 root      15   0  2184  680  580 S  0.0  0.0   0:00.31 init
        2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/0
        3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0
        4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
        5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/1
        6 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/1
        7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
        8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/2
        9 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/2
       10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
       11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/3
    
    
    
    [oraprod@oel5 ~]$ vmstat 6 10
    procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
     r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
     2  2 193932  67648  75324 10696360    0    0   667   103   35    1  3  0 92  4  0
     1  3 193932  68684  75304 10695152    0    0  4300   813 1142 1377  6  3 83  8  0
     1  3 193932  62148  75328 10701752    0    0  3663   523  934 1188  6  3 84  7  0
     1  3 193932  68840  75380 10694736    0    0  3839   471 2416 2236  7  3 81 10  0
     2  3 193932  65116  75356 10699036    0    0  2941   459  787 1071  6  2 88  4  0
     2  4 193932  63460  75468 10722068    0    0  3831   400 1050 1364  7  2 83  9  0
     1  3 193932  63660  75376 10715528    0    0  3526   566 1025 1174  6  2 84  8  0
     1  4 193932  65324  75516 10712116    0    0  3988   402 1209 1446  7  2 82  8  0
     1  5 193932  70232  75472 10705408    0    0  3507   391  901 1203  6  2 83  8  0
     1  4 193932  63700  75688 10715468    0    0  3849   664  921 1281  6  2 83  8  0
    Is it possible to join by PPID?

    Thanks

    Edited by: yxes2013 on 21.1.2013 17:37

    Edited by: yxes2013 on 21.1.2013 17:39
  • 3. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    yxes2013 wrote:
    Thanks sb,

    But how can I know which process in the V$session contrubuted the 100% cpu in my "top"?
    top - 10:07:52 up 23:32,  2 users,  load average: 5.60, 6.30, 5.63
    Tasks: 428 total,   2 running, 426 sleeping,   0 stopped,   0 zombie
    Cpu(s):  6.9%us,  1.8%sy,  0.0%ni, 83.6%id,  7.8%wa,  0.0%hi,  0.0%si,  0.0%st
    Mem:  12079104k total, 12023624k used,    55480k free,    74924k buffers
    Swap:  6104692k total,   193968k used,  5910724k free, 10699172k cached
    
    PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
    3177 oraprod   25   0 1683m 293m 290m R 100.5  2.5  10:18.76 oracle
    single process PID=3177

    How many core/CPU in this system?

    Please NOTE that top reports "83.6%id" Cpu(s) total are 83+% IDLE!
  • 4. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Its a 12core, right now its seem only 1 core being used, because it is still 10am, usually the users get performance issues in the afternoon.
    I am observing first the behaviour of the system when its performing ok upto its performing worst. The users complaining the the system is getting
    worst everyday. I want to dive inside the server and feel its soul and heartbeats.

    So to start with, I continuously run and check

    1. Top
    2. Vmstat
    3. V$session


    Any addition to the above? Is V$process helpful to check?


    Thanks
  • 5. Re: V$sessions
    Catfive Lander Explorer
    Currently Being Moderated
    At it's simplest, try:
    SELECT *
     FROM (SELECT S.SID, P.SPID, S.AUDSID, USERENV('SESSIONID') AS SESSIONID, 0 AS ACTIVITY, S.SERIAL#, S.SADDR, PX.QCSID, PX.QCSERIAL#, S.USERNAME, S.TYPE, S.COMMAND, S.STATUS, 
           SW.STATE, SW.EVENT, SW.WAIT_TIME, SW.SECONDS_IN_WAIT, S.SERVER, S.LOGON_TIME, S.LAST_CALL_ET,  S.SCHEMANAME, S.OSUSER, S.MACHINE, S.TERMINAL, S.PROGRAM, S.MODULE, S.ACTION, 
            S.CLIENT_INFO, S.SQL_ADDRESS, S.SQL_HASH_VALUE, S.PREV_SQL_ADDR, S.PREV_HASH_VALUE, SLO.SOFAR, SLO.TOTALWORK, OWNSESSION.SID OWNSID
     FROM V$SESSION S, V$PX_SESSION PX, V$SESSION_WAIT SW, V$PROCESS P, (SELECT *
     FROM V$SESSION_LONGOPS
     WHERE TIME_REMAINING <> 0) SLO, (SELECT SID
     FROM V$MYSTAT
     WHERE ROWNUM = 1) OWNSESSION
     WHERE S.SID = SW.SID(+) AND S.PADDR=P.ADDR AND (S.SID = SLO.SID(+) AND S.SERIAL# = SLO.SERIAL#(+)) AND (S.SID = PX.SID(+) AND S.SERIAL# = PX.SERIAL#(+)));
    That will get you sessions, what they're currently doing, how much CPU and memory they're consuming, what they're waiting on, and so on.
  • 6. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Hi Cat,

    SELECT *
    FROM (SELECT S.SID, P.SPID, S.AUDSID, USERENV('SESSIONID') AS SESSIONID, 0 AS ACTIVITY, S.SERIAL#, S.SADDR, PX.QCSID, PX.QCSERIAL#, S.USERNAME, S.TYPE, S.COMMAND, S.STATUS,
    SW.STATE, SW.EVENT, SW.WAIT_TIME, SW.SECONDS_IN_WAIT, S.SERVER, S.LOGON_TIME, S.LAST_CALL_ET, S.SCHEMANAME, S.OSUSER, S.MACHINE, S.TERMINAL, S.PROGRAM, S.MODULE, S.ACTION,
         S.CLIENT_INFO, S.SQL_ADDRESS, S.SQL_HASH_VALUE, S.PREV_SQL_ADDR, S.PREV_HASH_VALUE, SLO.SOFAR, SLO.TOTALWORK, OWNSESSION.SID OWNSID
    FROM V$SESSION S, V$PX_SESSION PX, V$SESSION_WAIT SW, V$PROCESS P, (SELECT *
    FROM V$SESSION_LONGOPS
    WHERE TIME_REMAINING 0) SLO, (SELECT SID
    FROM V$MYSTAT
    WHERE ROWNUM = 1) OWNSESSION
    WHERE S.SID = SW.SID(+) AND S.PADDR=P.ADDR AND (S.SID = SLO.SID(+) AND S.SERIAL# = SLO.SERIAL#(+)) AND (S.SID = PX.SID(+) AND S.SERIAL# = PX.SERIAL#(+)));

    ORA-00920: invalid relational operator
    00920. 00000 - "invalid relational operator"
    *Cause:   
    *Action:
    Error at Line: 7 Column: 24

    What is > TIME_REMAINING 0)

    Thanks
  • 7. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    SELECT * 
    FROM   (SELECT S.sid, 
                   P.spid, 
                   S.audsid, 
                   Userenv('SESSIONID') AS SESSIONID, 
                   0                    AS ACTIVITY, 
                   S.serial#, 
                   S.saddr, 
                   PX.qcsid, 
                   PX.qcserial#, 
                   S.username, 
                   S.TYPE, 
                   S.command, 
                   S.status, 
                   SW.state, 
                   SW.event, 
                   SW.wait_time, 
                   SW.seconds_in_wait, 
                   S.server, 
                   S.logon_time, 
                   S.last_call_et, 
                   S.schemaname, 
                   S.osuser, 
                   S.machine, 
                   S.terminal, 
                   S.program, 
                   S.MODULE, 
                   S.action, 
                   S.client_info, 
                   S.sql_address, 
                   S.sql_hash_value, 
                   S.prev_sql_addr, 
                   S.prev_hash_value, 
                   SLO.sofar, 
                   SLO.totalwork, 
                   OWNSESSION.sid       OWNSID 
            FROM   v$session S, 
                   v$px_session PX, 
                   v$session_wait SW, 
                   v$process P, 
                   (SELECT * 
                    FROM   v$session_longops 
                    WHERE  time_remaining != 0) SLO, 
                   (SELECT sid 
                    FROM   v$mystat 
                    WHERE  ROWNUM = 1) OWNSESSION 
            WHERE  S.sid = SW.sid(+) 
                   AND S.paddr = P.addr 
                   AND ( S.sid = SLO.sid(+) 
                         AND S.serial# = SLO.serial#(+) ) 
                   AND ( S.sid = PX.sid(+) 
                         AND S.serial# = PX.serial#(+) ))
    /
  • 8. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Thanks :)

    This is the output: its very long :( can we minimize the columns?
    confidential
    Edited by: yxes2013 on 21.1.2013 19:15
  • 9. Re: V$sessions
    sb92075 Guru
    Currently Being Moderated
    any & all issues regarding posted SQL MUST be directed to Catfive Lander.
  • 10. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    cont...
    confidential
    Edited by: yxes2013 on 21.1.2013 19:15
  • 11. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Cont3...
    confidential
  • 12. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    Hi sb,
    any & all issues regarding posted SQL MUST be directed to Catfive Lander
    I can see that there are lots of inactive, Can I kill all these inactive sessions? Do they eat resources?
    Can I monitor only the active ones?

    Thanks

    Edited by: yxes2013 on 21.1.2013 18:41
  • 13. Re: V$sessions
    Catfive Lander Explorer
    Currently Being Moderated
    The code I posted had the correct character in there: I expect the forum software "parsed" it into oblivion.

    It was supposed to be "where time_remaining>0": you're interested in sessions doing long-running operations which have still got some way to run before they finish.

    Not equal to zero (!=0) works just as well.
  • 14. Re: V$sessions
    Catfive Lander Explorer
    Currently Being Moderated
    Please. There's answering a question and then there's spoon-feeding!

    It's just a piece of SQL! You can stick a 'where rownum < ...' clause on to reduce the number of rows returned if you like. You can choose which columns to display, obviously, by simply editing the first SELECT clause. You could filter out the inactive sessions by adding a suitable where clause. Do what you like with it: it's now your query.

    The hard part is knowing what tables to fetch which columns from, and how to join them. That, I've shown you. Everything else is down to you and your choice of customization.

    +Incidentally, if I were you, I'd go back to your previous posts and heavily edit them. There was no point posting all of it in the first place, but more importantly, by doing so, you've exposed an awful lot of information that it is probably best the outside world shouldn't know about (file paths, hostnames, names of forms being used and so on).+
1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points