ba6.us - Dave's Database Related Stuff

My Session Info

-- My Session Info
select username, sid, serial#, terminal, program, machine from v$session where sid=userenv('sid');

List Blocking Sessions

By: Natalka Roshak

select s1.username || '@' || s1.machine
  || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  from v$lock l1, v$session s1, v$lock l2, v$session s2
  where s1.sid=l1.sid and s2.sid=l2.sid
  and l1.BLOCK=1 and l2.request > 0
  and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;

Quick Trace - Current Session

ALTER SESSION SET tracefile_identifier = 'MyTrace1';
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

SELECT /* MyTrace1 */ FROM DUAL;

ALTER SESSION SET EVENTS '10053 trace name context OFF';
ALTER SESSION SET EVENTS '10046 trace name context OFF';

Quick Trace - Another Session

Run these commands as SYS:

--Start Binds
exec dbms_system.set_ev([sid],[serial#],10046,4,'');
--Start Waits
exec dbms_system.set_ev([sid],[serial#],10046,8,'');
--Start Binds + Waits
exec dbms_system.set_ev([sid],[serial#],10046,12,'');

-- Run your Query

-- Stop
exec dbms_system.set_ev([sid],[serial#],10046,0,'');

In Flight SQL (with SQL as Text)

-- In Flight SQL
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' UNAM, 
       machine,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT,
       'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt
  FROM V$SESSION SES,  
       V$SQLtext_with_newlines SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and Ses.AUDSID <> userenv('SESSIONID')
 order by runt desc, 1,sql.piece;

In Flight SQL (with SQL as CLOB)

-- In Flight SQL (SQL as CLOB)
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' UNAM, 
       machine,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT,
       SQL_FULLTEXT STMT,
       'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt,
      ses.sql_id
  FROM V$SESSION SES,  
       V$SQL SQL
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and Ses.AUDSID <> userenv('SESSIONID')
 order by runt desc;

What SQL Statement is this Unix PID running?

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' UNAM, 
       machine,
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT,
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT,
       'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt
  FROM V$SESSION SES,  
       V$SQLtext_with_newlines SQL
 where SES.SQL_ADDRESS    = SQL.ADDRESS
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
   and SES.AUDSID <> userenv('SESSIONID')
   and SES.SID=(select substr(b.sid,1,5) sid 
from v$session b, v$process a 
 where b.paddr = a.addr
   and spid=&UnixPID
   and type='USER' )
 order by runt desc, 1,sql.piece;

Active Session / Operating System PID Detail

SELECT v$session.username, 
       v$session.status, 
       v$session.sid, 
       v$process.spid as ServerProcessID
FROM v$session, v$process 
WHERE v$session.username IS NOT NULL
  AND status='ACTIVE' 
  AND v$session.paddr = v$process.addr
ORDER BY 1,3;

SQL Statements With High Reads

What is normal: This might be able to point out some SQL statements that are getting greedy with I/O.

-- SQL Statements With High Reads
SELECT parsing_user_id,
       executions,
       sorts,
       command_type,
       disk_reads,
       sql_text
  FROM v$sqlarea
 WHERE disk_reads > 500000
 ORDER BY disk_reads;

Which SQL statements are currently using TEMP?

select s.sid || ',' || s.serial# sid, s.username, u.tablespace, a.sql_text,
       round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u, v$session s, v$sqlarea a, v$parameter p
where s.saddr = u.session_addr
      and a.address (+) = s.sql_address
      and a.hash_value (+) = s.sql_hash_value
      and p.name = 'db_block_size'
      and s.username != 'SYSTEM'
group by s.sid || ',' || s.serial#,
         s.username, a.sql_text,
         u.tablespace, round(((u.blocks*p.value)/1024/1024),2);

Try 2 now that v$sort_usage has been deprecated (post 9i):

select u.username, s.sql_fulltext, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;

Cursor Usage Summary

select user_name, count(*) cursors
  from V$OPEN_CURSOR
 group by user_name
 order by 2 desc;

Long Operations

Use this query to keep tabs on long operations (exports, queries, long updates, analyze etc).

SELECT ROUND(sofar/totalwork*100,2), v$session_longops.*
  FROM v$session_longops
 WHERE sofar <> totalwork
 ORDER BY target, sid;

SELECT ROUND(sofar/totalwork*100,2) as PctDone, 
       sid,
       username, 
        opname,
        target,
        sofar,
        totalwork,
        units,
        time_remaining, 
        elapsed_seconds, 
        message, 
        sql_hash_value
  FROM v$session_longops
 WHERE sofar <> totalwork
 ORDER BY target, sid;

Full Table Scans

SELECT sql_text,username,object_name,operation, options,b.cost,c.module,c.program
FROM v$sqltext a,
(SELECT operation, options, object_name, cost,ADDRESS
FROM v$sql_plan WHERE (operation='TABLE ACCESS' AND options='FULL') 
                      OR (operation='PARTITION RANGE' AND options='ALL')) b, v$session c
WHERE a.address = b.address
AND c.sql_address =a.address
ORDER BY a.address, piece;