Quickly Monitor a database -Whats going on!!

General Database Status

select sessions_current, sessions_highwater from v$license;
select count(*) as bussines_tsession from v$session where username='BUSINNES.TSESSION';
select count(*) as LOCK_DB from v$lock;

select machine,count(1) from v$session group by machine;
select status,count(1) from v$session group by status;

Sessions by Machine

set pages 1000 lines 300
SELECT count(1) AS con_count, machine, username
FROM v$session
WHERE type <> 'BACKGROUND'
GROUP BY username, machine
ORDER BY con_count DESC;

col MACHINE for a40;col username for a15
SELECT s.machine
     , s.username
     , count(decode(s.STATUS, 'ACTIVE', 1))   as active_con
     , count(decode(s.STATUS, 'INACTIVE', 1)) as inactive_con
     , count(*)                               as total_con
FROM v$session s
WHERE type <> 'BACKGROUND'
GROUP BY username, machine
ORDER BY total_con DESC;


select sum(case when status = 'ACTIVE' then 1 else 0 end) as ACTIVE_COUNT
      , sum(case when status = 'INACTIVE' then 1 else 0 end) as INACTIVE_COUNT
      , count(*) as TOTAL_COUNT
      , username, machine
 from   v$session
 where type <> 'BACKGROUND'
 group by username, machine
 order by username, machine;

Sessions waiting for enqueue resources.

SELECT sid, serial#, event, wait_class
FROM v$session WHERE event LIKE 'enq:%';


ps -e -o pcpu,pid,user,tty,args|grep -i oracle|sort -n -k 1 -r|head

Top 10 CPU-consuming resources on the box:

ps -e -o pcpu,pid,user,tty,args | sort -n -k 1 -r | head

Top 10 memory-consuming processes:

ps -e -o pmem,pid,user,tty,args | sort -n -k 1 -r | head

Which SQL statements are using excessive I/O

set pages 300 lines 300
col PARSING_SCHEMA_NAME for a15
SELECT *
FROM
(SELECT
  parsing_schema_name
 ,direct_writes
 ,SUBSTR(sql_text,1,75)
 ,disk_reads
FROM v$sql
ORDER BY disk_reads DESC)
WHERE rownum < 20;

Which sessions are currently waiting for I/O resources, query V$SESSION:

col program for a15
col machine for a15
SELECT sid,username,program,machine,sql_id
FROM v$session
WHERE event LIKE 'db file%read';

To view objects that are waiting for I/O resources:

SELECT object_name,object_type,owner
FROM v$session a,dba_objects b
WHERE a.event LIKE 'db file%read'
AND   b.data_object_id = a.row_wait_obj#;