The below SQL is very much helpful to find the current status of database.

prompt ---------- sessions with highest CPU consumption------------
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as"CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

prompt ---------- Wait events -----------------

select w.sid, w.event,s.module,s.username,w.p1,w.p2,w.p3
from v$session_wait w, v$session s
where w.sid=s.sid and w.state='WAITING' and
w.event not in ('pmon timer',
'smon timer',
'rdbms ipc message',
'pipe get',
'SQL*Net message from client',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'SQL*Net more data from client',
'wakeup time manager',
'slave wait',
'SQL*Net more data to client') and w.event not like '%slave wait'
group by w.sid,w.event,s.module,s.username,w.p1,w.p2,w.p3 order by 2 desc;

prompt ---------------- lock info -----------------------
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
order by s1.sid;

prompt ---------- Temp Usage -----------

column sid_serial format a20
SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",
a.sid||','||a.serial# SID_SERIAL,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;

rem----select b.address,b.hash_value,b.child_number,b.plan_hash_ value,b.sql_text from v$session a, v$sql b where a.SQL_ADDRESS=b.ADDRESS and a.sid=1087;

prompt ---- display undo stat---------
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
NVL(s.username, 'None') orauser,
s.program,
r.name undoseg,
t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
FROM sys.v_$rollname r,
sys.v_$session s,
sys.v_$transaction t,
sys.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size';

prompt ---------- Display hit ratio for each session ----------

COLUMN unix_id FORMAT a10 HEAD Username
COLUMN oracle_id FORMAT a15 HEAD OracleID
COLUMN os_user FORMAT a10 HEAD OS_User
COLUMN sid FORMAT 999 HEAD SID
COLUMN serial_id FORMAT 999999 HEAD Serial#
COLUMN unix_pid FORMAT a9 HEAD UNIX_Pid
COLUMN consistent_gets FORMAT 999,999,999,999,999 HEAD Cons_Gets
COLUMN block_gets FORMAT 999,999,999,999,999 HEAD Block_Gets
COLUMN physical_reads FORMAT 999,999,999,999,999 HEAD Phys_Reads
COLUMN hit_ratio FORMAT 999.00 HEAD Hit_Ratio
SELECT
p.username unix_id
, s.username oracle_id
, s.osuser os_user
, s.sid sid
, s.serial# serial_id
, LPAD(p.spid,7) unix_pid
, sio.consistent_gets consistent_gets
, sio.block_gets block_gets
, sio.physical_reads physical_reads
, ROUND((consistent_gets+Block_gets-Physical_reads) /
(Consistent_gets+Block_gets)*100,2) hit_ratio
FROM
v$process p
, v$session s
, v$sess_io sio
WHERE
p.addr=s.paddr
AND s.sid = sio.sid
AND (sio.consistent_gets + sio.block_gets) > 0
AND s.username is not null
ORDER BY hit_ratio;

prompt --------------- SGA stat ----------------

DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
dbcache number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
select value into dbcache from v$parameter where name = 'db_cache_size';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> db_cache_size = '||dbcache||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/