Add new comment

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, RAC Aware)

-- In Flight SQL (with SQL as CLOB, RAC Aware)
SELECT SES.INST_ID, 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 GV$SESSION SES,  
       GV$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.INST_ID = SQL.INST_ID
   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;

How much TEMP is currently used?

-- Temp Tablespace Usage Summary
SELECT TABLESPACE_NAME,
       ROUND(TABLESPACE_SIZE/1024/1024/1024,2) as MAX_SIZE_GB,
       ROUND(ALLOCATED_SPACE/1024/1024/1024,2) as ALLOCATED_GB,
       ROUND(FREE_SPACE/1024/1024/1024,2) as FREE_GB,
       ROUND((TABLESPACE_SIZE-FREE_SPACE)/1024/1024/1024,2) AS USED_GB
  FROM DBA_TEMP_FREE_SPACE;

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;

Temp Usage from ASH History
This gives the sql_id and maximum allocated temp space of any queries that ran in the past two days and exceeded 10 gigabytes of temp space. From this article.

select sql_id,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig 
from DBA_HIST_ACTIVE_SESS_HISTORY 
where 
sample_time > sysdate-2 and 
TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) 
group by sql_id order by 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
  AND totalwork > 0
 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
  AND totalwork > 0
 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;

Auditing Info
Recent Logon/Logoff Activity
Note: AUDIT_TRAIL must be set to 'DB' for auditing records to be gathered in SYS.AUD$.

SELECT USERID, NAME, USERHOST, NTIMESTAMP# 
FROM sys.aud$ A, AUDIT_ACTIONS AA
WHERE A.ACTION#=AA.ACTION
  AND AA.NAME IN ('LOGON','LOGOFF')
ORDER BY NTIMESTAMP# DESC;

Flashback Stuff

SHOW PARAMETER DB_RECOVERY_FILE_DEST
SHOW PARAMETER DB_FLASHBACK

select * from v$recovery_area_usage;

COLUMN NAME FORMAT A20
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)",
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2)
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
FROM v$RECOVERY_FILE_DEST;

select	name
,	floor(space_limit / 1024 / 1024) "Size MB"
,	ceil(space_used  / 1024 / 1024) "Used MB"
from	v$recovery_file_dest
order by name;