Queries to get the SESSION INFORMATION
Checking Timing details, Client PID of associated oracle SID set head off set verify off set echo off set pages 1500 set linesize 100 set lines 120 prompt prompt Details…
Read MoreSession info based on process id(pid)
set echo off set linesize 132 set verify off set feedback off set serveroutput on; declare SID number := 0 ; SERIAL number := 0 ; username …
Read MoreDetails of the Session information
select ‘ Sid, Serial#, Aud sid : ‘|| s.sid||’ , ‘||s.serial#||’ , ‘|| s.audsid||chr(10)|| ‘ DB User / OS User : ‘||s.username|| ‘ / ‘||s.osuser||chr(10)|| ‘ Machine – Terminal :…
Read MoreTo identify the Largest Archivelog generators
select s.sid, username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr order by 3;
Read MoreChecking rollback/Undo segment info used by SID (single – multiple)
column rr heading ‘RB Segment’ format a18 column us heading ‘Username’ format a15 column os heading ‘OS User’ format a10 column te heading ‘Terminal’ format a10 SELECT r.name rr, nvl(s.username,’no…
Read MoreTo find Sqltext based on the SID
select sql_text from v$sqltext a,v$session b where a.address=b.sql_address and b.sid=&sidnumber order by piece; select b.sid, b.serial#,b.username,a.sql_text, a.address, a.hash_value from v$sqltext a,v$session b where a.address=b.sql_address and b.sid=’&SID’;
Read MoreSQL Script to pull out complete Session details information on a single page
Procedure to use 1) Choose a handy location to save this file in Unix server 2) Open a new vi file vi sid.sql 3) Copy the whole content below and…
Read MoreTo check the sessions with most Active I/O
SELECT DISTINCT wait_class FROM gv$event_name ORDER BY 1; SELECT sql_id, COUNT(*) FROM gv$active_session_history ash, gv$event_name evt WHERE ash.sample_time > SYSDATE – 1/24 AND ash.session_state = ‘WAITING’ AND ash.event_id = evt.event_id…
Read MoreTo get the Most Active SQL in the previous hour
SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM gv$active_session_history WHERE sample_time > SYSDATE – 1/24 AND session_type = ‘BACKGROUND’ GROUP BY sql_id ORDER BY COUNT(*) DESC; SELECT sql_id,COUNT(*),ROUND(COUNT(*)/SUM(COUNT(*)) OVER(), 2) PCTLOAD FROM…
Read MoreTo check the Active Waiting sessions
Select sysdate sample_time, s.sid “SESSION_ID” , decode(w.WAIT_TIME, 0,’WAITING’,’ON CPU’) “SESSION_STATE”, s.serial# “SESSION_SERIAL#”, s.user# “USER_ID”, s.sql_address “SQL_ADDRESS”, s.sql_hash_value “SQL_HASH” , s.command “SQL_OPCODE” , s.type “SESSION_TYPE” , w.event “EVENT”, w.p1 “P1” ,…
Read More