1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 10:06 AM by sb92075 Go to original post RSS
      • 15. Re: V$sessions
        yxes2013
        Can you help please ....which of the above process is having performance issue? Am I right that I should select the "ACTIVE" ones?
        • 16. Re: V$sessions
          Catfive Lander
          I'm sorry, I can't diagnose your performance problems remotely. You initially asked how to get CPU and memory information per session, and I showed you a query that would do that. How you interpret that data then comes down to lots of experience and an understanding of how your systems and applications are supposed to behave.

          I will say, however, not to worry about inactive sessions and that, yes, concentrate on active ones. There is very little overhead involved in being connected to the instance but not doing anything (provided the total number of sessions recorded isn't in any danger of exceeding your SESSIONS parameter setting).
          • 17. Re: V$sessions
            yxes2013
            Thanks,

            Right now I the performance is still okay says the users, but they said it will start to hang in the afternoon.

            So can you suggest other manual monitoring tools?

            I am doing the following to check the performance issues.

            1. Run "top" and get the PIDs of processes with high cpu usage.
            2. vmstat 5 10 > to check if there are i/o contentions
            3. Run long-ops query. > connect/link the process to the PID in #1
            4. Run statspack

            Anymore I need to check? I just want to give output to the Oracle Support to have it analyzed.

            Thanks

            Edited by: yxes2013 on 21.1.2013 19:44
            • 18. Re: V$sessions
              Catfive Lander
              Well, that query I gave you is an instantaneous snapshot of who is doing what on the database right now. I don't know any better way of running just one thing at the very moment a user complains things are slow to see what exactly is happening on the database. Usually, when things slow down, you'll find sessions are waiting on locks or latches, and that query will spell out when that's true, and what latches/waits they're waiting on.

              I would look at things like sar and top only once I've eliminated anything obvious from the database (you can only monitor so many things at the one instant!)

              Statspack is good for 'period monitoring' (what happened on the database between time 1 and time 2). And you'll probably have to do that eventually. But if you can nail down what's going wrong by an instantaneous view of what's happening now when a complaint is received, that saves a lot of hunting around.
              • 19. Re: V$sessions
                yxes2013
                Thanks cat,

                Again pardon for me being redundant. Given the PID in #1 , how can I get the corresponding long-ops session? Is there a PID column in the long-ops query?
                I only see "SPID" how can I link it to PID?


                Edited by: yxes2013 on 21.1.2013 19:59
                • 20. Re: V$sessions
                  Catfive Lander
                  PID is an OS value, no?

                  You're not going to see anything useful by doing what you propose, I think: every process you see in a tool like 'top' is going to list Oracle as the owner (because even if I am making a server process, or DBWR or LGWR do huge amounts of work, it's the oracle user which owns those processes).

                  On the more specific one of querying SESSION_LONGOPS, you'll join it to V$SESSION (which has username and so on) as shown in that query I gave you: via SID and SERIAL#. An outer join will be needed, of course, because there will be lots of sessions and only some of them will be doing long operations.

                  If you then join the PADDR in V$SESSION with the ADDR in V$PROCESS, you'll be able to go from username in one to the process ID in the other. Or the other way around, I suppose.

                  So: V$SESSION => V$SESSION_LONGOPS on sid=sid and serial#=serial#
                  AND V$SESSION => V$PROCESS on paddr=addr

                  Make sense?

                  Something like this, I think:
                  select s.sid, s.serial#, p.program, p.spid, s.username, w.event, s.last_call_et, w.seconds_in_wait, q.sql_text, 
                  l.sofar/l.totalwork*100 PCT_COMPLETE
                  from v$sql q, v$session s, v$session_wait w, v$process p, (select * from v$session_longops where time_remaining>0) l
                  where q.sql_id (+) = s.sql_id
                  and q.address (+) = s.sql_address
                  and q.child_number (+) = s.sql_child_number
                  and s.sid = w.sid
                  and s.paddr = p.addr
                  and (s.sid=l.sid(+) and s.serial# = l.serial#(+))
                  and s.status = 'ACTIVE'
                  and s.type != 'BACKGROUND'
                  order by s.last_call_et desc; 
                  • 21. Re: V$sessions
                    sb92075
                    yxes2013 wrote:
                    Thanks cat,

                    Again pardon for me being redundant. Given the PID in #1 , how can I get the corresponding long-ops session? Is there a PID column in the long-ops query?
                    I only see "SPID" how can I link it to PID?


                    Edited by: yxes2013 on 21.1.2013 19:59
                      1* select program, username from v$session where process = '20858'
                    SQL> /
                    
                    PROGRAM                                          USERNAME
                    ------------------------------------------------ ------------------------------
                    sqlplus@localhost.localdomain (TNS V1-V3)        SYS
                    
                    SQL> !ps -ef | grep sqlplus
                    oracle   20858  2937  0 17:56 pts/1    00:00:00 sqlplus   as sysdba
                    oracle   29013 20858  0 20:30 pts/1    00:00:00 /bin/bash -c ps -ef | grep sqlplus
                    • 22. Re: V$sessions
                      baskar.l
                      Hi,

                      Check this out oratop - utility for near real-time monitoring of databases, RAC and Single Instance[Article ID 1500864.1]

                      Baskar.l
                      • 23. Re: V$sessions
                        Catfive Lander
                        Bit of a shame if you're running on AIX, HP or Solaris, no?

                        (Oratop runs on Linux only, in other words ... and that's why I'd stick to using SQL where possible. That runs on any platform that has a database!)
                        • 24. Re: V$sessions
                          Catfive Lander
                          Slightly tidied up version of that second query:
                          col username format a10
                          col program format a30
                          col even format a20
                          col process format a7
                          
                          select s.sid, s.serial#, substr(p.program,1,20) program, p.spid process, s.username, substr(w.event,1,20) event, s.last_call_et, w.seconds_in_wait, substr(q.sql_text,1,30) sql_text,
                          l.sofar/l.totalwork*100 PCT_COMPLETE
                          from v$sql q, v$session s, v$session_wait w, v$process p, (select * from v$session_longops where time_remaining>0) l
                          where q.sql_id (+) = s.sql_id
                          and q.address (+) = s.sql_address
                          and q.child_number (+) = s.sql_child_number
                          and s.sid = w.sid
                          and s.paddr = p.addr
                          and (s.sid=l.sid(+) and s.serial# = l.serial#(+))
                          and s.status = 'ACTIVE'
                          and s.type != 'BACKGROUND'
                          order by s.last_call_et desc;
                          • 25. Re: V$sessions
                            baskar.l
                            If needed sql then you can use
                            -- by Jeremy Schneider, Pythian
                            clear breaks
                            col sql_text format a40
                            col wait_class format a20
                            col event format a40
                            col top_obj_pct format a11
                            col top_object format a30
                            break on sql_id on sql_text skip 1
                            
                            with master as (
                            select /*+ materialize */ hhh.sql_id,
                                   trunc(100*count(*)/hhh.total_sess) percentage,
                                   hhh.wait_class, 
                                   hhh.event,
                                   hhh.top_obj,
                                   decode(hhh.top_obj,0,null,-1,null,trunc(100*hhh.total_top_obj/hhh.total_sess)) top_obj_pct,
                                   count(*) total_samples,
                                   hhh.topn
                            from (
                              select hh.*,
                                     first_value(hh.current_obj#) over (partition by hh.sql_id, hh.event order by hh.total_obj desc nulls last) top_obj,
                                     first_value(hh.total_obj) over (partition by hh.sql_id, hh.event order by hh.total_obj desc nulls last) total_top_obj,
                                     dense_rank() over (partition by hh.sql_id order by hh.total_ev desc) topn_ev,
                                     dense_rank() over (order by total_sess desc) topn
                              from (
                                select h.sql_id, case when h.session_state='WAITING' then h.wait_class else 'CPU' end wait_class, 
                                       case when h.session_state='WAITING' then h.event else 'CPU' end event, h.current_obj#, 
                                       count(*) over (partition by h.sql_id) total_sess, 
                                       count(*) over (partition by h.sql_id, case when h.session_state='WAITING' then h.event else 'CPU' end) total_ev, 
                                       case when h.current_obj#>1 then count(*) over (partition by h.sql_id, h.current_obj#) else -1 end total_obj
                                from dba_hist_active_sess_history h
                                where h.instance_number=1
                                  --and h.session_state='WAITING'
                                  and h.sql_id is not null
                                  and h.sample_time between '&1' and '&2'
                              ) hh
                            ) hhh 
                            where 1=1
                              and topn<=14
                            --  and topn_ev<=5
                            having 100*count(*)/hhh.total_sess>10  -- this wait event accounts for more than 10% of this SQL statement
                            group by hhh.sql_id, hhh.wait_class, hhh.event, hhh.total_sess, hhh.top_obj, hhh.total_top_obj, hhh.topn_ev, hhh.topn
                            )
                            select m.sql_id,
                                   dbms_lob.substr(t.sql_text,40,1) sql_text,
                                   m.percentage,
                                   m.wait_class,
                                   m.event,
                            --       m.top_obj,
                                   m.top_obj_pct,
                                   o.object_name top_object,
                                   o.object_type top_obj_type,
                                   m.total_samples
                            from master m, dba_hist_sqltext t, dba_objects o
                            where m.top_obj=o.object_id(+) and m.sql_id=t.sql_id
                            order by m.topn, m.percentage desc
                            /      
                              
                            
                            Other Query..
                            
                            +++++++++++++++
                            
                            
                            col type for a10
                            col "CPU" for 999999
                            col "IO" for 999999
                            select * from (
                            select
                            ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
                            sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
                            sum(decode(ash.session_state,'WAITING',1,0)) -
                            sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "WAIT" ,
                            sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO" ,
                            sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
                            from dba_hist_active_sess_history ash,
                            audit_actions aud
                            where SQL_ID is not NULL
                            -- and ash.dbid=&DBID
                            and ash.sql_opcode=aud.action
                            -- and ash.sample_time > sysdate - &minutes /( 60*24)
                            group by sql_id, SQL_PLAN_HASH_VALUE , aud.name
                            order by sum(decode(session_state,'ON CPU',1,1)) desc
                            ) where rownum < 10
                            /
                            Baskar.l
                            • 26. Re: V$sessions
                              sb92075
                              you need to learn how to use
                               tags & formatting code!
                              
                              [code]
                              SELECT s.sid, 
                                     s.serial#, 
                                     Substr(p.program, 1, 20)    program, 
                                     p.spid                      process, 
                                     s.username, 
                                     Substr(w.event, 1, 20)      event, 
                                     s.last_call_et, 
                                     w.seconds_in_wait, 
                                     Substr(q.sql_text, 1, 30)   sql_text, 
                                     l.sofar / l.totalwork * 100 PCT_COMPLETE 
                              FROM   v$sql q, 
                                     v$session s, 
                                     v$session_wait w, 
                                     v$process p, 
                                     (SELECT * 
                                      FROM   v$session_longops 
                                      WHERE  time_remaining > 0) l 
                              WHERE  q.sql_id = s.sql_id 
                                     AND q.address = s.sql_address 
                                     AND q.child_number = s.sql_child_number 
                                     AND s.sid = w.sid 
                                     AND s.paddr = p.addr 
                                     AND ( s.sid = l.sid(+) 
                                           AND s.serial# = l.serial#(+) ) 
                                     AND s.status = 'ACTIVE' 
                                     AND s.TYPE != 'BACKGROUND' 
                              ORDER  BY s.last_call_et DESC; 
                              [/code]                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                              • 27. Re: V$sessions
                                Catfive Lander
                                And you need to learn when to butt out.

                                There's nothing wrong with my code tags.
                                And there's nothing wrong with my SQL formatting. You choose to do it one way, I don't.

                                <li>I like all my "from" tables on one line. </li>
                                <li>I like my columns on one line. </li>
                                <li>I don't like upper case keywords. </li>

                                My code is functional and runs correctly. Click the beautifier if you insist. Using vi, I don't have one, but I have my long-established coding preferences.

                                Really, you spend an awful lot of time here, judging by the absurd number of posts attributed to you. Surely you can spend it more productively than re-formatting other people's code!
                                • 28. Re: V$sessions
                                  yxes2013
                                  I thank you all, I am still analyzing what you have been discussing above. :)
                                  I don't know which one to use or which is the best.
                                  • 29. Re: V$sessions
                                    yxes2013
                                    Hi Sb, there is no column as SQL_CHILD_NUMBER in v$session. Thanks
                                    SELECT s.sid, 
                                           s.serial#, 
                                           Substr(p.program, 1, 20)    program, 
                                           p.spid                      process, 
                                           s.username, 
                                           Substr(w.event, 1, 20)      event, 
                                           s.last_call_et, 
                                           w.seconds_in_wait, 
                                           Substr(q.sql_text, 1, 30)   sql_text, 
                                           l.sofar / l.totalwork * 100 PCT_COMPLETE 
                                    FROM   v$sql q, 
                                           v$session s, 
                                           v$session_wait w, 
                                           v$process p, 
                                           (SELECT * 
                                            FROM   v$session_longops 
                                            WHERE  time_remaining > 0) l 
                                    WHERE  q.sql_id = s.sql_id 
                                           AND q.address = s.sql_address 
                                           AND q.child_number = s.sql_child_number 
                                           AND s.sid = w.sid 
                                           AND s.paddr = p.addr 
                                           AND ( s.sid = l.sid(+) 
                                                 AND s.serial# = l.serial#(+) ) 
                                           AND s.status = 'ACTIVE' 
                                           AND s.TYPE != 'BACKGROUND' 
                                    ORDER  BY s.last_call_et DESC; 
                                    ORA-00904: "S"."SQL_CHILD_NUMBER": invalid identifier
                                    00904. 00000 - "%s: invalid identifier"
                                    *Cause:   
                                    *Action:
                                    Error at Line: 20 Column: 29