queries to monitor oracle sessions

Ram_A

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.

BEDE

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

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

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

Jonathan Lewis

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

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.