How to Monitor Oracle database performance and sessions monitor

 How to Monitor Oracle database performance and sessions monitor

1) First find the sessions holding locks, and objects that are currently being locked.

The following SQL prompts you for a schema/owner name and lists all currently lock objects and idle sessions locking them:

select s.username locker,s.status,s.sid,s.serial#,

to_char((s.last_call_et/60), ‘99999D99′) idle_mins,

object_name,object_type,s.PROCESS, DECODE (l.locked_mode

, 0, ‘None’

, 1, ‘Null’

, 2, ‘Row-S’

, 3, ‘Row-X’

, 4, ‘Share’

, 5, ‘S/Row-X’

, 6, ‘Exclusive’) lock_mode

from v$locked_object l,dba_objects o, v$session s

where l.object_id=o.object_id

and s.sid=l.session_id

and s.username=l.oracle_username

and s.status in (‘INACTIVE’,'KILLED’)

and OWNER like upper(‘&SCHEMA’)

order by 6;

2) Lookup detail about the sessions holding locks.

Now use the following SQL to see more detail about the locking session using session ID or SID you had listed from previous lock finding SQL:

select s.sid,p.spid os_pid,s.status, s.osuser,s.username,s.COMMAND,

s.MACHINE,s.MODULE,s.SERIAL#, s.SCHEMANAME, s.LOCKWAIT,s.action

from v$session s, v$process p

WHERE s.paddr = p.addr

and s.sid = &SESSION_ID;

3) Lookup the session’s current SQL.

Use the following SQL to the associated SQL using the SID you have found earilier:

select sa.sql_text txt

from v$session s,

v$sqlarea sa

where s.sql_address=sa.address(+)

and s.sql_hashvalue=sa.hash_value(+)

and s.sid = &SESSION_ID;