Real Time Monitoring of Oracle

This is a collection of real time monitoring queries.

These are helpful to get an idea of what is going on for an instance.

Explain Plan

1) Run $ORACLE_HOME/rdbms/admin/utlxplan.sql to create a plan_table in your schema.

2) Explain the statement...

TRUNCATE TABLE plan_table;
EXPLAIN PLAN SET STATEMENT_ID='&name' FOR [ SELECT STATEMENT ];

3) View the output:

@$ORACLE_HOME/rdbms/admin/utlxplps

- or -

select plan_table_output
from table(dbms_xplan.display('plan_table',null,'basic'));

- or -

SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' ' ||
object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query Plan",other
FROM plan_table
START WITH id = 0 AND statement_id='&name'
CONNECT BY PRIOR ID = PARENT_ID AND statement_id = '&name';

References:
http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci976623,00.html
http://www.dbspecialists.com/presentations/use_explain.html

Import/Export

Import/Export Feedback Monitoring

If you set FEEDBACK=10000 while doing an import or export you can use the query below to estimate the number of periods you should see to indicate progress. I also included number of lob columns because this can affect the import/export speed.

select table_name,
round(num_rows/10000,2) as dots,
round(num_rows/10000/75,2) as lines,
(select count(*) from user_lobs where user_tables.table_name=user_lobs.table_name having count(*) > 0) as num_lob_cols
from user_tables
--where table_name like 'REVIEW%'
order by table_name

Instance

General Status

select name, log_mode, open_mode
from v$database;

Archive Log Usage
This query summarizes Archive Log Switches for today. Change the where clause in this statement to change the time period analyzed.

select trunc(completion_time,'HH'), count(*)
from v$archived_log
where trunc(completion_time) = trunc(sysdate)
group by trunc(completion_time,'HH');

Transactions Per Second
Defined as User Commits + User Rollbacks.

-- Calculate Transactions Per Second
SET SERVEROUTPUT ON
DECLARE
begindate date;
enddate date;
beginval number;
endval number;
BEGIN

select sysdate, sum(value)
into begindate, beginval
from v$sysstat
where name in ('user commits','user_rollbacks');

dbms_lock.sleep(5);

select sysdate, sum(value)
into enddate, endval
from v$sysstat
where name in ('user commits','user_rollbacks');

dbms_output.put_line( (endval-beginval) ||
' transactions');

dbms_output.put_line( ((enddate-begindate) * 86400) ||
' seconds');

dbms_output.put_line( (endval-beginval) / ((enddate-begindate) * 86400) ||
' transactions per second');

END;
/

Undo Status

alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select usn,
state,
undoblockstotal "Total",
undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,
0,
'unknown',
sysdate+(((undoblockstotal-undoblocksdone) /
(undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;

Locks

Processes Waiting on Locks

-- Processes Waiting on Locks
SELECT
holding_session bsession_id,
b.username busername,
b.machine bmachine,
waiting_session wsession_id,
a.username wusername,
a.machine wmachine,
c.lock_type type,
mode_held,
mode_requested,
lock_id1,
lock_id2,
b.machine
FROM
sys.v_$session b,
sys.dba_waiters c,
sys.v_$session a
WHERE
c.holding_session=b.sid
and
c.waiting_session=a.sid;

Blockers

-- Blockers
SELECT
a.session_id,
b.username,
b.machine,
type,
mode_held,
mode_requested,
lock_id1,
lock_id2
FROM
sys.v_$session b,
sys.dba_blockers c,
sys.dba_lock a
WHERE
c.holding_session=a.session_id
AND
c.holding_session=b.sid;

Transaction Enqueues
What is normal? Note the CTIME for this query. It is time in seconds since the last mode change for the transaction. The number is updated by PMON so it may lag a little, but if there is a transaction that has been waiting for more than a few seconds (10-15) it may need a closer look. Transactions waiting more than 60 seconds definitely need a closer look!

If you query this multiple times, you should see the transactions progress and then disappear once they are done.

-- Transaction Enqueues
select V$TRANSACTION_ENQUEUE.*, v$session.username, v$session.machine
From V$TRANSACTION_ENQUEUE, v$session
where V$TRANSACTION_ENQUEUE.SID = v$session.SID
order by ctime desc;

Report on All DML Locks Held

-- Report on All DML Locks Held
SELECT
NVL(owner,'SYS') owner,
session_id,
name,
mode_held,
mode_requested
FROM
sys.dba_dml_locks
ORDER BY 2;

Report on all DDL Locks Held

SELECT
NVL(owner,'SYS') owner,
session_id,

Jared Still's showlock.sql script
Original post here.

-- showlock.sql - show all user locks
--
-- see ML Note 1020008.6 for fully decoded locking script
-- parts of the that script to not work correctly, but the
-- lock types are current
-- (script doesn't find object that is locked )
--
-- speeded up greatly by changing order of where clause,
-- jks 04/09/1997 - show lock addresses and lockwait

-- jks 04/09/1997 - outer join on all_objects
-- encountered situation on 7.2
-- where there was a lock with no
-- matching object_id
-- jks 02/24/1999 - join to dba_waiters to show waiters and blockers
-- jkstill 05/22/2006 - revert back to previous version without tmp tables
-- update lock info
-- add lock_description and rearrange output
-- jkstill 04/28/2008 - added command column
-- updated lock types
-- removed one outer join by using inline view on sys.user$
-- jkstill 04/28/2008 - added subquery factoring
-- converted to ANSI joins
-- changed alias for v$lock to l and v$session to s

set trimspool on
ttitle off
set linesize 150
set pagesize 60
column command format a15
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a18 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column lock_description heading 'Lock Description'format a16 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WATR' format 9999
col holding_session head 'BLKR' format 9999

with dblocks as (
select /*+ ordered */
l.kaddr,
s.sid,
s.username,
lock_waiter.waiting_session,
lock_blocker.holding_session,
(
select name
from sys.user$
where user# = o.owner#
) ||'.'||o.name
object,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||'-UNKNOWN'
) COMMAND,
l.type lock_type,
decode
(
l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PF','Password file lock',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PI','Parallel operation lock',
'PJ','Library cache pin instance lock (J=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PR','Process startup lock',
'PS','Library cache pin instance lock (S=namespace)',
'PS','Parallel operation lock',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QK','Row cache instance lock (L=cache)',
'QL','Row cache instance lock (K=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TS',decode(l.id2,
0,'Temporary segment enqueue lock (ID2=0)',
1,'New block allocation enqueue lock (ID2=1)',
'UNKNOWN!'
),
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'UNKOWN'
) lock_description,
decode
(
l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'No Lock', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SRX)', /* C */
6, 'Exclusive', /* X */
to_char(l.lmode)
) mode_held,
decode
(
l.request,
0, 'None', /* Mon Lock equivalent */
1, 'No Lock', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(l.request)
) mode_requested,
s.osuser,
s.machine,
s.program,
s.process
from
v$lock l
join v$session s on s.sid = l.sid
left outer join sys.dba_waiters lock_blocker on lock_blocker.waiting_session = s.sid
left outer join sys.dba_waiters lock_waiter on lock_waiter.holding_session = s.sid
left outer join sys.obj$ o on o.obj# = l.id1
where s.type != 'BACKGROUND'
)
select
--kaddr,
sid,
username,
waiting_session,
holding_session,
object,
command,
lock_type,
lock_description,
mode_held,
mode_requested,
--osuser,
--machine,
program,
process
from dblocks
order by sid, object
/

Memory

PGA Memory Used per Session

-- PGA Memory Used per Session
col name for a20
col machine for a15
break on report
compute sum label "total pga mem" of value on report
SELECT s.sid,
       username,
       machine,
       ss.status,
       name,
       value
FROM   v$statname n,
       v$sesstat s,
       v$session ss
WHERE  n.STATISTIC# = s.STATISTIC#
       AND s.sid = ss.sid
       AND n.name ='session pga memory'
 ORDER BY value DESC;

PGA Memory Usage Summary by Username

-- PGA Memory Usage Summary by Username
SELECT username,
       RTRIM(TO_CHAR(SUM(value), '999,999,999,999')) as TotalMemUsed,
       RTRIM(TO_CHAR(AVG(value), '999,999,999,999')) as AvgMemUsed,
       COUNT(*) as NumSessions
FROM   v$statname n,
       v$sesstat s,
       v$session ss
WHERE  n.STATISTIC# = s.STATISTIC#
       AND s.sid = ss.sid
       AND n.name like 'session pga memory'
 GROUP BY USERNAME
 ORDER BY AVG(VALUE) DESC;

AWE Memory Map Activity Percent (8i, 9i)
If you are using AWE Memory Windowing on Windows operating system, this can tell you how much action the Memory Window is seeing. 

-- AWE Memory Map Activity Percent 
select (select value from v$sysstat where statistic# = 155) / 
       (select value from v$sysstat where statistic# = 154) * 100  as percent 
from dual;

Monitoring Jobs

DBMS_JOBs (8i,9i)

Creating

Modifying

Deleting

Status

Job Definitions

SELECT * FROM DBA_JOBS;

Currently Running Jobs

select v$session.sid, serial#, JOB
from v$session, dba_jobs_running
where v$session.sid = dba_jobs_running.sid;

SCHEDULED_JOBs (10g+)

Creating

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'QIPMU.DO_PURGE',
job_type=>'STORED_PROCEDURE',
job_action=>'QIPMU.SMART_VIEWS_PKG.SP_PURGETABLES',
start_date=>trunc(sysdate+7,'D')+18/24,
enabled=>true,
repeat_interval => 'trunc(sysdate+7,''D'')+18/24',
comments => 'Weekly log purge'
);
END;
/

Modifying

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'QIPMU.DO_PURGE',
attribute => 'MAX_FAILURES',
value => 3);
END;
/

Deleting

BEGIN
DBMS_SCHEDULER.DROP_JOB('QIPMU.DO_PURGE');
END;
/

Status

select * from dba_scheduler_jobs;

select * from dba_scheduler_running_jobs;

SELECT max_failures, MAX_FAIL,
job_priority,
schedule_limit SCHED_LIMIT,
logging_level
FROM user_scheduler_jobs;

select * from dba_scheduler_job_log
order by log_date desc;

select job_name, status, error#, run_duration
FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

SQL to help diagnose issues in a pinch...

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;

Sessions

All Session Summary
Query

-- All Session Summary
select status,
count(*)
from v$session
group by rollup(status);

Example output:

STATUS COUNT(*)

ACTIVE 23
INACTIVE 59
82

Session Summary with Active Count
Query

-- Session Summary with Active Count
select username,
SUM(CASE status WHEN 'ACTIVE' THEN 1 ELSE 0 END) ||
' active / ' || count(*) || ' total' as Active_Total_CONN
from v$session
where username IS NOT NULL
and username not in ('DBSNMP','SYS','SYSTEM')
group by username order by username

Example output:

USERNAME ACTIVE_TOTAL_CONN

DBUSER1 0 active / 1 total
DBUSER2 3 active / 56 total

Sessions with Unix Process Detail

-- Sessions with Unix Process Detail
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

Session Summary with Machine Info

select username, machine, count(*)
From v$session
WHERE LENGTH(USERNAME) > 0
group by username, machine
order by count(*) desc;

Session Detail with Kill Statement

select sid,
serial#,
username,
status,
machine,
terminal,
program,
module,
logon_time,
last_call_et,
'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt
from v$session;

Sniped Sessions Waiting to be Killed
What is normal: Normally there are a few sessions hanging out waiting to be cleaned up by PMON. If there are more than 10 in this list, we are probably having a lot more session churn than usual. Killing sniped sessions may help release resources faster but it looks like PMON still has the last word on releasing resources.

SELECT 'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as sqltext
FROM v$session
WHERE status='SNIPED';

Session Events By User

-- Session Events By User
SELECT
username,
machine,
event,
total_waits,total_timeouts,
time_waited,average_wait
FROM
sys.v_$session_event a,
sys.v_$session b
WHERE
a.sid= b.sid
ORDER BY 1,2;

Redo Blocks Generated per Session
What is normal: Because of lifetimes of sessions this is not always the most useful stat but it might give you an idea of sessions that are doing more work.

-- Redo Blocks Generated per Session
SELECT s.sid, s.serial#, s.username, s.program,

Session Wait Event Summary

-- Session Wait Event Summary
SELECT ses.username,
swc.SID,
ses.status,
swc.wait_class,
swc.total_waits,
ROUND ((swc.time_waited / 100), 2) time_waited_secs
FROM SYS.v_$session_wait_class swc,
SYS.v_$session ses
WHERE ses.SID = swc.SID
AND ses.username IS NOT NULL
AND swc.wait_class != 'Idle'
ORDER BY username, SID, wait_class;

Tools
Tanel Poder's Oracle Session Snapper
http://tech.e2sn.com/oracle-scripts-and-tools/session-snapper
A low impact way to observe performance counters for a session.

Storage and Segments

Read Speed by Datafile
These numbers are derived from v$filestat which is reset upon instance restart. 

select FILE_NAME as "Name",
       READTIM as "Read time",
       PHYRDS as "Phys Rds Done",
       round(READTIM*10/(PHYRDS+1),2) "Phys Read AVG (ms)",
       PHYBLKRD as "Phys Blocks Read",
       round(READTIM*10/(PHYBLKRD+1),2) "Block Read AVG (ms)",
       WRITETIM "Write time",
       PHYWRTS "Phys Wrts Done",
       round(WRITETIM*10/(PHYWRTS+1),2) "Phys Write AVG (ms)",
       PHYBLKWRT "Phys Blocks Wrt",
       round(WRITETIM*10/(PHYBLKWRT+1),2) "Block Write AVG (ms)",
       PHYRDS+PHYWRTS "TOTAL IO"
from V$FILESTAT s,
     DBA_DATA_FILES f
where s.FILE# = f.FILE_ID
order by 4 desc, 8 desc;

WITH MainQuery AS (
   SELECT f.file_name, 
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,1,SINGLEBLKRDS,0)) MILLI1,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,2,SINGLEBLKRDS,0)) MILLI2,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,4,SINGLEBLKRDS,0)) MILLI4,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,8,SINGLEBLKRDS,0)) MILLI8,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,16,SINGLEBLKRDS,0)) MILLI16,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,32,SINGLEBLKRDS,0)) MILLI32,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,64,SINGLEBLKRDS,0)) MILLI64,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,128,SINGLEBLKRDS,0)) MILLI128,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,256,SINGLEBLKRDS,0)) MILLI256,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,512,SINGLEBLKRDS,0)) MILLI512,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,1024,SINGLEBLKRDS,0)) MILLI1024,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,2048,SINGLEBLKRDS,0)) MILLI2048,
       MAX(DECODE(SINGLEBLKRDTIM_MILLI,4096,SINGLEBLKRDS,0)) MILLI4096
  FROM V$FILE_HISTOGRAM h,
       DBA_DATA_FILES f
 WHERE h.FILE# = f.FILE_ID
 GROUP BY FILE_NAME
 ORDER BY FILE_NAME),
Pass2 AS (SELECT MainQuery.*,
       (MILLI1+MILLI2+MILLI4+MILLI8+MILLI16) AS Fast,
       (MILLI32+MILLI64+MILLI128+MILLI256+MILLI512+MILLI1024+MILLI2048+MILLI4096)AS Slow
  FROM MainQuery ORDER BY FILE_NAME)
SELECT Pass2.*, ROUND(FAST/(FAST+SLOW) * 100,2)as FASTRatio FROM Pass2 ;

Totals Megs by Tablespace

-- Totals Megs by Tablespace
SELECT TABLESPACE_NAME, 
       SUM(dba_data_files.bytes/1024/1024) as total_megs
FROM dba_data_files 
  group by TABLESPACE_NAME;

Megs Used by Tablespace

-- Megs Used by Tablespace
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 as MEGS_USED 
FROM DBA_SEGMENTS 
GROUP BY TABLESPACE_NAME;