1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 10:06 AM by sb92075 RSS

    V$sessions

    yxes2013
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          any & all issues regarding posted SQL MUST be directed to Catfive Lander.
                          • 10. Re: V$sessions
                            yxes2013
                            cont...
                            confidential
                            Edited by: yxes2013 on 21.1.2013 19:15
                            • 11. Re: V$sessions
                              yxes2013
                              Cont3...
                              confidential
                              • 12. Re: V$sessions
                                yxes2013
                                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
                                  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
                                    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