RSS

Tag Archives: dba monitor

Query: SESSION ADMINISTRATION

---SESSION ADMINISTRATION
select sesion.sid,
 sesion.username,
 optimizer_mode,
 hash_value,
 address,
 cpu_time,
 elapsed_time,
 sql_text
 from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
 and sesion.sql_address = sqlarea.address
 and sesion.username is not null 
and lower(SQL_TEXT) like lower('%'|| :part_of_code_or_object ||'%')
order by 1 desc
 ;
 
----------------
 
select sid,
 to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
 username,
 type,
 status,
 process,
 sql_address,
 sql_hash_value
 from v$session
where username is not null 
-- AND SID=:sid_number
;

--shows SID and its correspondent sql statment
 select sesion.sid,
 sql_text
 from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
 and sesion.sql_address = sqltext.address
 and sesion.username is not null
 order by 1 desc , sqltext.piece
 ;
 
 
 -- shows SID and correspondent physical, logical reads
 select sess_io.sid,
 sess_io.block_gets,
 sess_io.consistent_gets,
 sess_io.physical_reads,
 sess_io.block_changes,
 sess_io.consistent_changes
 from v$sess_io sess_io, v$session sesion
 where sesion.sid = sess_io.sid
 and sesion.username is not null
 order by 1 desc 
 ;
------


Cheers

Muhammad Rashed
 
Leave a comment

Posted by on December 27, 2012 in Queries

 

Tags: , , , ,