This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Feb 2, 2013 8:06 AM by sb92075 Go to original post RSS
  • 15. Re: V$sessions
    yxes2013 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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