Forum Stats

  • 3,750,250 Users
  • 2,250,140 Discussions
  • 7,866,859 Comments

Discussions

queries to monitor oracle sessions

Ram_A
Ram_A Member Posts: 111 Red Ribbon

Hi Experts,

Could you please provide queries to monitor oracle sessions in terms of performance monitoring.

Basically I want to know what are all sessions running, which sessions are taking time , any locked sessions, any waiting sessions and which SQLs are running for long time.

Thanks in advance.

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,274 Gold Trophy

    so, you have:

    select *

    from sys.gv_$session

    ;

    To see what sessions are actually executing:

    select t.state,t.blocking_session,t.blocking_instance,

    sq.sql_id,t.inst_id, t.sid,t.status,sq.executions, sq.fetches, sq.rows_processed, sq.sql_text, sq.sql_fulltext, t.*

    from sys.gv_$session t

    join sys.gv_$open_cursor oc on oc.inst_id=t.inst_id and oc.sid=t.sid

    join sys.gv_$sql sq on sq.sql_id=oc.sql_id and sq.inst_id=oc.inst_id

     and sq.users_executing>0

    ;

    and if you wish to see sessions which execute a given piece of code containing my_procedure:

    select t.state,t.blocking_session,t.blocking_instance,

    sq.sql_id,t.inst_id, t.sid,t.status,sq.executions, sq.fetches, sq.rows_processed, sq.sql_text, sq.sql_fulltext, t.*

    from sys.gv_$session t

    join sys.gv_$open_cursor oc on oc.inst_id=t.inst_id and oc.sid=t.sid

    join sys.gv_$sql sq on sq.sql_id=oc.sql_id and sq.inst_id=oc.inst_id

    where (t.inst_id,t.sid) in (

    select c.inst_id, c.sid

    from sys.gv_$open_cursor c

    where lower(c.sql_text) like lower('%my_procedure%')

    )

     and sq.users_executing>0

    order by sq.sql_text, t.inst_id, t.sid 

    ;

  • BEDE
    BEDE Oracle Developer Member Posts: 2,274 Gold Trophy

    To see objects locked in the database by sessions that are blocking, I use:

    select s1.osuser,s1.username,s1.program,s1.machine, 

    s2.osuser blocker_osuser,s2.username blocker_username,s2.program blocker_program,s2.machine blocker_machine, 

    o.owner, o.object_name,

    t.*

    from sys.gv_$session_blockers t

    left join sys.gv_$session s1 on t.sid=s1.sid and t.inst_id=s1.inst_id and t.sess_serial#=s1.serial#

    left join sys.gv_$session s2 on t.blocker_sid=s2.sid and t.blocker_instance_id=s2.inst_id and t.blocker_sess_serial#=s2.serial#

    left join sys.gv_$locked_object lk on t.blocker_sid=lk.session_id and t.blocker_instance_id=lk.inst_id

    left join sys.dba_objects o on lk.object_id=o.object_id

    ;

  • EdStevens
    EdStevens Member Posts: 28,381 Gold Crown

    With a question like that, are you sure you are not nursing a case of Compulsive Tuning Disorder?

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,729 Gold Crown

    I think the basic query you need is:

    "How much does it cost to licence OEM / Solar Winds / other?"

    With a view to having a graphic display of very recent workload plus a history that you can drill into to find causes of contention or excess workload.#

    Regards

    Jonathan Lewis

  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown

    Are you licensed for the Diagnostic and Tuning packs? If so there are creative ways to use v$active_session_history and the dba_hist_ views.