Oracle Database - What all to test in a quick health check for Oracle DB

One of the repetitive things I have seen and been asked for as DBA is to do a quick health check for the database. 

Well, it has can comprise of a lot of things, but I have over time thought over few things which could help me to do a quick validation of the database. 

Here is my list (and followed by the SQL statements as well)

1. Invalid Objects Currently in database
2. Blocking Session in Database
3. Details of Blocking SIDs
4. Sessions > 20MB PGA
5. Current Wait Events in the Database
6. Detailed Sess Waits 
7. Wait Events in Last 7 Minutes - Database
8. Wait Events in Last 15 Minutes - Database
9. Wait Events in Last 60 Minutes - Databas
10. Current IO Functions Statistics
11. Load Average For Server 
12. CPU and Memory
13. Tablespace Utilizaiton
14. Temp Space Utilization Stats
15. Current Locked Objects in Database

## SQL - HealthCheck###
set echo off
set heading off
set lines 500 pages 500

prompt Health Check Report for Database  
select chr(9)||chr(9)||chr(9)|| name from V$database;
prompt Instance Name
select  INSTANCE_NAME, to_char(STARTUP_TIME,'dd-MON-yyyy hh24:mi') STARTUP_TIME from V$instance;

set heading on
col OWNER for a15
col OBJECT_NAME for  a30
break on owner on object_name
prompt ############################################################################
prompt Invalid Objects Currently in database
prompt ############################################################################

select owner, object_name, object_type from dba_objects where status <> 'VALID'
order by 1,2,3;

clear breaks

prompt ############################################################################
prompt Blocking Session in Database
prompt ############################################################################

select sid, serial#, username, status, event, BLOCKING_INSTANCE, BLOCKING_SESSION, BLOCKING_SESSION_STATUS, FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION_STATUS, FINAL_BLOCKING_SESSION
from v$session
where blocking_session is not null;

prompt ############################################################################
prompt Details of Blocking SIDs
prompt ############################################################################

select sid, serial#, username, status, event from V$session
where sid in (select distinct BLOCKING_SESSION from v$session
where blocking_session is not null);

prompt ############################################################################
prompt Sessions > 20MB PGA
prompt ############################################################################


column PGA_ALLOC_MEM format 99,990
column PGA_USED_MEM format 99,990
column inst_id format 99
column username format a15
column program format a40
column logon_time format a20

select s.inst_id, s.sid, s.username, s.logon_time, s.program, PGA_USED_MEM/1024/1024 PGA_USED_MEM, PGA_ALLOC_MEM/1024/1024 PGA_ALLOC_MEM
from gv$session s
, gv$process p
Where s.paddr = p.addr
and s.inst_id = p.inst_id
and PGA_USED_MEM/1024/1024 > 20  -- pga_used memory over 20mb
order by PGA_USED_MEM;

prompt ############################################################################
prompt Current Wait Events in the Database
prompt ############################################################################


col wait_class for a40
col event for a60
select wait_class, event, count(*)  from v$session
where username is not null
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Detailed Sess Waits - (sesswaits.sql)
prompt ############################################################################


col seconds_in_wait heading "Wait|(Sec.)" format 9,999,999
select event,
       sid,BLOCKING_SESSION,sql_id,
       p1,
--       p1text,
       p2,
--       p2text,
SECONDS_IN_WAIT
from v$session
where event not in ('SQL*Net message from client',
                'SQL*Net message to client',
                'pipe get',
                'pmon timer',
                'rdbms ipc message',
                'Streams AQ: waiting for messages in the queue',
                'Streams AQ: qmn coordinator idle wait',
                'Streams AQ: waiting for time management or cleanup tasks',
                'PL/SQL lock timer',
                'Streams AQ: qmn slave idle wait',
                'jobq slave wait',
                'queue messages',
                'io done',
                'i/o slave wait',
                'sbtwrite2',
                'async disk IO',
                'smon timer')
order by event, p1,p2 ;


prompt ############################################################################
prompt Wait Events in Last 7 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/192
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 15 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/96
group by wait_class,event
order by 3,1,2;


prompt ############################################################################
prompt Wait Events in Last 60 Minutes - Database
prompt ############################################################################

select wait_class, event, count(*)  from v$active_session_history
where sample_time > sysdate - 1/24
group by wait_class,event
order by 3,1,2;

prompt ############################################################################
prompt Current IO Functions Statistics
prompt ############################################################################


col function_name    format a25         heading "File Type"
col reads            format 99,999,999  heading "Reads"
col writes           format 99,999,999  heading "Writes"
col number_of_waits  format 99,999,999  heading "Waits"
col wait_time_sec    format 999,999,999 heading "Wait Time|Sec"
col avg_wait_ms      format 999.99      heading "Avg|Wait ms"

set lines 80
set pages 10000

select
   function_name,
   small_read_reqs + large_read_reqs reads,
   small_write_reqs + large_write_reqs writes,
   wait_time/1000 wait_time_sec,
   case when number_of_waits > 0 then
          round(wait_time / number_of_waits, 2)
       end avg_wait_ms
from
   v$iostat_function
order by
    wait_time desc;


set heading off
prompt ############################################################################
prompt Load Average For Server
prompt ############################################################################
 select 'Load Average - ' ||   value  || ' NUM_CPUS  - ' || (select   value   from v$osstat where stat_name = 'NUM_CPUS') || ' LA/pCPU - ' || value/(select   value   from v$osstat where stat_name = 'NUM_CPUS')
   from v$osstat
 where stat_name = 'LOAD';

set pagesize 60
column "Tablespace" heading "Tablespace Name" format a30
column "Size" heading "Tablespace|Size (mb)" format 9999999.9
column "Used" heading "Used|Space (mb)" format 9999999.9
column "Left" heading "Available|Space (mb)" format 9999999.9
column "PCTFree" heading "% Free" format 999.99

ttitle left "Tablespace Space Allocations"
break on report
-- compute sum of "Size", "Left", "Used" on report
select /*+ RULE */
t.tablespace_name,
NVL(round(((sum(u.blocks)*p.value)/1024/1024),2),0) Used_mb,
t.Tot_MB,
NVL(round(sum(u.blocks)*p.value/1024/1024/t.Tot_MB*100,2),0) "USED %"
from v$sort_usage u,
v$parameter p,
(select tablespace_name,sum(bytes)/1024/1024 Tot_MB
from dba_temp_files
group by tablespace_name
) t
where p.name = 'db_block_size'
and u.tablespace (+) = t.tablespace_name
group by
t.tablespace_name,p.value,t.Tot_MB
order by 1,2;

prompt ############################################################################
PROMPT ======================= Total TEMP_TS consuming =======================
prompt ############################################################################
select tablespace, sum(blocks)*8192/1024/1024 consuming_TEMP_MB from
v$session, v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by tablespace;

prompt ############################################################################
PROMPT ======================= Sessions consuming TEMP_TS more than 10 MB =======================
prompt ############################################################################
select sid, tablespace,
sum(blocks)*8192/1024/1024 consuming_TEMP_MB from v$session,
v$sort_usage where tablespace in (select tablespace_name from
dba_tablespaces where contents = 'TEMPORARY') and session_addr=saddr
group by sid, tablespace having sum(blocks)*8192/1024/1024 > 10
order by sum(blocks)*8192/1024/1024 desc ;

prompt ############################################################################
PROMPT ======================= Current Locked Objects =======================
prompt ############################################################################

 col owner for a25
 col object_name for a35
 col oracle_username for a25
col os_user_name for a25

 SELECT B.Owner, B.Object_Name,b.object_type, A.Oracle_Username, A.OS_User_Name, A.SESSION_ID, A.LOCKED_MODE
 FROM V$Locked_Object A, All_Objects B
 WHERE A.Object_ID = B.Object_ID