Oracle Database healthcheck Script

rem
rem Oracle Database Healthcheck script
rem
rem Note: In order to analyze the alert.log the script is creating a directory for bdump
rem
rem The report should be run after the system has been up for at least 10 hours
rem and should be run on several occasions over a period of time to get a feel for
rem what the real condition of the database is.  A one-time sample run on an
rem intactive system will not give an accurate picture of what is really occuring
rem within the database.
rem
rem
undefine all
ttitle off
whenever sqlerror continue
set echo off
set feedback off
set term off
column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'Mondd_hh24mi') timecol,
'.out' spool_extension from sys.dual;
column outp new_value dbname
select value || '_' outp
from v$parameter where name = 'db_name';
spool healthcheck_&&dbname&timestamp&&suffix
SET LINESIZE 80
SET ECHO OFF
SET NEWPAGE 1
SET HEADING OFF
SET TERMOUT ON
SET NUMWIDTH 9
SET VERIFY OFF
SET FEEDBACK OFF
SET PAGESIZE 58
SET RECSEP OFF
COLUMN nl NEWLINE
prompt
prompt *** Database Health Check ver 2.0
prompt
BEGIN
   SYS.DBMS_APPLICATION_INFO.set_module ('HealthCheck', NULL);
END;
/
select 'Timestamp: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') from dual;
prompt
select
 ' RAC database? : '||DECODE(vp1.value,'TRUE','Yes ('||
 decode(vp1.value,'TRUE',' instances: '||vp2.value)||')','No')     ||chr(10)||
 ' Current instance: '||instance_name
from
v$instance,
(select value
 from v$parameter
 where name like 'cluster_database'
) vp1,
(select value
 from v$parameter
 where name like 'cluster_database_instances'
) vp2;

prompt
prompt *** INSTANCE DETAILS:
prompt
select
 ' instance id   : '||inst_id   ||' ( '||instance_name||
 ' on '             ||host_name ||' )'                             ||chr(10)||
 ' inst status   : '||status                                       ||chr(10)||
 ' db status     : '||database_status                              ||chr(10)||
 ' active state  : '||active_state                                 ||chr(10)||
 ' shutd pending?: '||shutdown_pending                             ||chr(10)||
 ' startup time  : '||to_char(startup_time,'dd/mm/yyyy hh24:mi:ss')||chr(10)||
 ' uptime (days) : '||trunc(sysdate-startup_time+1)                ||chr(10)||
 ' version       : '||version                                      ||chr(10)||
 ' logins        : '||logins                                       ||chr(10)||
 ' instance role : '||instance_role                                ||chr(10)||
 ' archiver?     : '||archiver
from gv$instance
order by inst_id;
prompt
SET PAGESIZE 0
col msg for a80
select
 (case when trunc(sysdate-startup_time+1) < 2
  then '+ Database was recently restarted.'||chr(10)||
       '+ The inormation about wait events and hit ratios might be not useful'
  end) msg
from v$instance;
prompt
prompt *** DATABASE DETAILS:
prompt
COLUMN sort1 NOPRINT
SELECT
    1 sort1,' Database name    : '||name                          ||chr(10)||
            ' Database id      : '||dbid                          ||chr(10)||
            ' Created          : '||to_char(created,
                                      'dd/mm/yyyy hh24:mi:ss')    ||chr(10)||
            ' Log mode         : '||log_mode                      ||
              decode (log_mode,'ARCHIVELOG','','   !!!')          ||chr(10)||
            ' Force logging    : '||force_logging                 ||chr(10)||
            ' Open mode        : '||open_mode                     ||chr(10)||
            ' Remote archiving : '||remote_archive                ||chr(10)||
            ' Database role    : '||database_role
FROM v$database
UNION
SELECT
    2 sort1,' Datafiles        : '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(10)||
            ' Data f. size (Gb): '||trim(TO_CHAR(SUM(bytes)/
                                               1073741824, '9,990'))
FROM v$datafile
UNION
SELECT
    3 sort1,' Tempfiles        : '||trim(TO_CHAR(COUNT(*),'9,990'))||chr(10)||
            ' Temp f. size (Gb): '||trim(TO_CHAR(SUM(bytes)/
                                               1073741824, '9,990'))
FROM v$tempfile
UNION
SELECT
    4 sort1,' Segm size  (Gb)  : '||trim(TO_CHAR(SUM(bytes)/
                                               1073741824, '9,990'))
FROM dba_segments
UNION
SELECT
    5 sort1,' Tables / Indexes : '||
        trim(TO_CHAR(SUM(DECODE(type#, 2, 1, 0)), '999,990'))||'/'||
        trim(TO_CHAR(SUM(DECODE(type#, 1, 1, 0)), '999,990'))
FROM sys.obj$
WHERE owner# <> 0
UNION
SELECT
    6 sort1,' Total DB Users   : ' ||trim( TO_CHAR(COUNT(*), '9,990'))
FROM  sys.user$
WHERE type# = 1
UNION
SELECT
    7 sort1,' Online Sessions  : ' ||trim( TO_CHAR(COUNT(*), '9,990'))
FROM  gv$session
WHERE type='USER'
UNION
SELECT
    8 sort1,' Active Sessions  : ' ||trim( TO_CHAR(COUNT(*), '9,990'))
FROM  gv$session
WHERE type='USER' and status = 'ACTIVE'
UNION
SELECT
    9 sort1,' Session highwater: ' ||
                             trim(TO_CHAR(sessions_highwater, '9,990'))
FROM
    v$license
UNION
SELECT
    10 sort1,' SGA (Mb)         : '||trim(TO_CHAR(SUM(value)/
                                                 1048576, '99,990.99'))
FROM
    v$sga;
SET PAGESIZE 50
set head on
prompt
prompt *** Database components
prompt
col comp_name for a40 heading "Component Name"
col version   for a12 heading "Version"
col status    for a15 heading "Status"
select
 COMP_NAME,
 VERSION,
 STATUS
from
 dba_registry;

prompt
prompt
prompt *** Current SESSION workload:
prompt
col inst_id             format 9999        heading "Inst|ID"
col status              format a20         heading "Session status"
col sno                 format 9999999999  heading "# sessions"
BREAK ON inst_id
select inst_id,status, count(*) sno
from gv$session
where type='USER'
group by inst_id,status
order by 1,2;
prompt
prompt *** DATABASE RESOURCE USAGE
prompt
col inst_id             format 9999        heading "Inst|ID"
col resource_name       format a22         heading "Resource|Name"
col current_utilization format 9999999999  heading "Curret|Utilization"
col max_utilization     format 9999999999  heading "Max|Utilization"
col initial_allocation  format a18         heading "Initial|Allocation"
col mpr                 format a4          heading "Max %|reached"
BREAK ON inst_id
select
 inst_id,
 resource_name,
 current_utilization,
 max_utilization,
 initial_allocation
from
 gv$resource_limit
where
 max_utilization > 0
order by
 inst_id,
 resource_name;

prompt
prompt *** CONTROLFILES location and mirroring
prompt
COLUMN num      FORMAT 99   HEADING '#'
COLUMN name     FORMAT a60  HEADING 'Location' WORD_WRAPPED
COLUMN status   FORMAT a7   HEADING 'Status'
SELECT
    rownum num,
    name,
    status
FROM
    v$controlfile;
prompt

set pagesize 0
select (case
         when count(1) <2 then '+ At least 2 controlfiles are recommended'
         when count(1) >=2 and count(1) <=3 then '+ '||count(1)||' mirrors for controlfile detected. - OK'
         else '+ More than 3 controlfiles might have additional overhead. Check the wait events.'
       end)
from v$controlfile;
col msg format a79
select
(case when value <45 then '+ ! "control_file_record_keep_time='||value||'" to low. Set to at least 45'
else '+ "control_file_record_keep_time='||value||'"  - OK.'
end) msg
from v$parameter where name = 'control_file_record_keep_time';
set pagesize 50
set heading on
prompt
prompt *** REDO LOGs location, size and mirroring
prompt
COLUMN thread#          FORMAT 9            HEADING "Th"
COLUMN group#           FORMAT 90           HEADING "Group"
COLUMN sequence#        FORMAT 999999999990 HEADING "Sequence|Number"
COLUMN mbytes           FORMAT 9999990      HEADING "Size|(Mb)"
COLUMN members          FORMAT 90           HEADING "Members"
COLUMN first_time       FORMAT A15          HEADING "First|Time"
COLUMN status           FORMAT A12          HEADING "Status"    TRUNC
COLUMN archived         FORMAT A3           HEADING "Arc"
SELECT
    thread#,
    group#,
    sequence#,
    bytes / 1024 / 1024 mbytes,
    TO_CHAR(first_time, 'DD-MON-YY HH24:MI') first_time,
    status,
    archived
FROM
    v$log
ORDER BY
    thread#,
    group#;
-- prompt
-- prompt +  STATUS     Description
-- prompt +  ********** ***********************************************************
-- prompt +  UNUSED     Indicates the online redo log has never been written to.
-- prompt +             This is the state of a redo log that was just added, or just
-- prompt +             after a RESET-LOGS, when it is not the current redo log.
-- prompt +  CURRENT    Indicates this is the current redo log. This implies that
-- prompt +             the redo log is active. The redo log could be open or closed.
-- prompt +  ACTIVE     Indicates the log is active but is not the current log. It is
-- prompt +             needed for instance recovery. It might or might not be
-- prompt +             archived.
-- prompt +  CLEARING   Indicates the log is being recreated as an empty log after
-- prompt +             an ALTER DATABASE CLEAR LOGFILE command. After the log is
-- prompt +             cleared, the status changes to UNUSED.
-- prompt +  CLEARING   Indicates that the current log is being cleared of a closed
-- prompt +  _CURRENT   Thread. The log can stay in this status if there is some
-- prompt +             failure in the switch such as an I/O error writing the new
-- prompt +             log header.
-- prompt +  INACTIVE   Indicates the log is no longer needed for instance recovery.
-- prompt +             It may be in use for media recovery. It might or might not be
-- prompt +             archived.
-- prompt +  INVALID    Thread. File is inaccessible.
-- prompt +  STALE      File contents are incomplete (such as when an instance is shut
-- prompt +             down with SHUTDOWN ABORT or due to a system crash).
-- prompt +  DELETED    File is no longer used.
prompt
set pagesize 0
select 'Group# '||group#||': '||
       (case
          when members<2 then '+ Redo log mirroring is recommended'
          else ' '||members||' members detected. - OK'
        end)
from v$log
where members < 2;
prompt
select (case
          when count(*) > 1 then '+ Groups with different size detected. - Not recommended.'
          else '+ All groups have the same size. - OK'
        end)
from (select distinct bytes from v$log);
select
       (case
          when count(*)>3 then '+ '||count(*)||' times detected when log switches occured more than 1 log per 5 minutes.'||chr(10)||
                                               '+ You may consider to increase the redo log size.'
          else '+ Redo log size: OK'
        end)
from (
select trunc(FIRST_TIME,'HH') Week, count(*) arch_no, trunc(10*count(*)/60) archpermin
from v$log_history
group by trunc(FIRST_TIME,'HH')
having trunc(5*count(*)/60)>1
);
prompt
prompt *** REDO CONTENTION STATISTICS: 
prompt 
prompt The following shows how often user processes had to wait for space in 
prompt the redo log buffer: 
set head off
 
select name||' = '||value 
from   v$sysstat 
where  name = 'redo log space requests';
set head on 

prompt 
prompt This value should be near 0.  If this value increments consistently, 
prompt processes have had to wait for space in the redo buffer.  If this 
prompt condition exists over time, increase the size of LOG_BUFFER in the 
prompt init.ora file in increments of 5% until the value nears 0. 
prompt ** NOTE: increasing the LOG_BUFFER value will increase total SGA size. 
prompt 
prompt
prompt Another way to check the contention on redo log buffer:
prompt
prompt The following query output shows the system waits on
prompt                           'log file sync' and 'db file parallel write'
prompt

select (case  when sync_waits = 0 then '+ Waits for log file sync not detected: log_buffer is OK'
        else  '+ Waits for log file sync detected ('||sync_waits||' times): Consider to increase the log_buffer'
        end)
from (
 select
  decode(
    sum(w.total_waits),
    0, 0,
    nvl(100 * sum(l.total_waits) / sum(w.total_waits), 0)
  )  sync_waits
from
  sys.v_$bgprocess  b,
  sys.v_$session  s,
  sys.v_$session_event  l,
  sys.v_$session_event  w
where
  b.name like 'DBW_' and
  s.paddr = b.paddr and
  l.sid = s.sid and
  l.event = 'log file sync' and
  w.sid = s.sid and
  w.event = 'db file parallel write'
);

prompt
prompt
prompt *** REDO LOG SWITCHING ACTIVITY (last 30 days):
prompt
set pagesize 0
select '+ Daily (max) : '||max(no)||' switches. Size: '||round((max(no) * max(logsize ))/1024,2)||'Gb'||chr(10)||
       '+ Daily (avg) : '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize ))/1024,2)||'Gb'||chr(10)||
       '+ Daily (min) : '||min(no)||' switches. Size: '||round((min(no) * max(logsize ))/1024,2)||'Gb'
from (
select trunc(FIRST_TIME,'DD'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'DD')),
(select max(bytes/1024/1024) logsize from v$log);
prompt
select '+ Weekly (max): '||max(no)||' switches. Size: '||round((max(no) * max(logsize ))/1024,2)||'Gb'||chr(10)||
       '+ Weekly (avg): '||trunc(avg(no))||' switches. Size: '||round((avg(no) * max(logsize ))/1024,2)||'Gb'||chr(10)||
       '+ Weekly (min): '||min(no)||' switches. Size: '||round((min(no) * max(logsize ))/1024,2)||'Gb'
from (
select trunc(FIRST_TIME,'WW'), count(*) no
from v$log_history
where FIRST_TIME > sysdate - 31
group by trunc(FIRST_TIME,'WW')),
(select max(bytes/1024/1024) logsize from v$log);
set pagesize 50
prompt
prompt List redo log switches for the last 24 hours per hour:
prompt
col Hour     format a20    heading "Hour"
col switches format 999999 heading "# of redo|switches"

select to_char(trunc(FIRST_TIME,'HH'),'dd-MON-yy hh24')||'h' Hour, count(*) switches
from v$log_history
where FIRST_TIME > (sysdate-1) - 1/24
group by to_char(trunc(FIRST_TIME,'HH'),'dd-MON-yy hh24')||'h'
order by 1;
prompt
prompt
prompt *** SGA DETAILS:
prompt
set pagesize 50
COLUMN inst_id  format 9999                HEADING "Inst|id"
COLUMN name     FORMAT A25                 HEADING "Part"
COLUMN value    FORMAT 99999999999999990   HEADING "Bytes"
COLUMN k        FORMAT 999999999990.0      HEADING "Kbytes"
COLUMN m        FORMAT 9999990.0           HEADING "Mbytes"
BREAK ON inst_id
COMPUTE SUM LABEL Total OF m ON inst_id
SET HEADING ON
SELECT
    inst_id,
    name,
    value,
    value / 1024 k,
    value / 1024 /1024 m
FROM
    GV$SGA
order by 1;

prompt
prompt + !!! BUG alert:
prompt + !!!  ORA-600 error might occurs with instance crash when the parameter
prompt + !!!  SGA_TARGET is set to an exact multiple of 4Gb due to Bug 4433838.
prompt
prompt
prompt *** SGA HIT RATIOS:
set pagesize 0
COLUMN val2 NEW_VALUE lib NOPRINT
SELECT (1 - (SUM(reloads) / SUM(pins)))*100 val2
FROM v$librarycache;
COLUMN val2 NEW_VALUE dict NOPRINT
SELECT (1 - (SUM(getmisses) / SUM(gets)))*100 val2
FROM v$rowcache;
COLUMN val2 NEW_VALUE phys_reads NOPRINT
SELECT value val2
FROM v$sysstat
WHERE name = 'physical reads';
COLUMN val2 NEW_VALUE log1_reads NOPRINT
SELECT value val2
FROM v$sysstat
WHERE name = 'db block gets';
COLUMN val2 NEW_VALUE log2_reads NOPRINT
SELECT value val2
FROM v$sysstat
WHERE name = 'consistent gets';
COLUMN val2 NEW_VALUE chr NOPRINT
SELECT (1 - (&phys_reads / (&log1_reads + &log2_reads)))*100 val2
FROM DUAL;
SET HEADING OFF
SELECT
    ' Data Block Buffer Hit Ratio (%):  ' || &chr           || chr(10) ||
    ' Shared SQL Pool'                                      || chr(10) ||
    '    Dictionary Hit Ratio (%):      ' || &dict          || chr(10) ||
    '    Shared SQL Buffers (Library Cache)'                || chr(10) ||
    '        Cache Hit Ratio (%):       ' || &lib
FROM DUAL;
set pagesize 45
SET HEADING OFF
prompt
PROMPT *** LIBRARY CACHE STATISTICS:
prompt

select 'Instance id: '||inst_id,
       'PINS    - # of times an item in the library cache was executed - '|| 
        sum(pins), 
       'RELOADS - # of library cache misses on execution steps - '||
        sum (reloads), 
       'RELOADS / PINS * 100 = '||round((sum(reloads) / sum(pins) * 100),2)||'%'
from    gv$librarycache
group by 'Instance id: '||inst_id
order by 1; 
prompt
prompt + Increase memory until RELOADS is near 0 but watch out for 
prompt + Paging/swapping
prompt + To increase library cache, increase SHARED_POOL_SIZE 
prompt + 
prompt + ** NOTE: Increasing SHARED_POOL_SIZE will increase the SGA size. 
prompt + 
prompt + Library Cache Misses indicate that the Shared Pool is not big 
prompt + enough to hold the shared SQL area for all concurrently open cursors. 
prompt + If you have no Library Cache misses (PINS = 0), you may get a small 
prompt + increase in performance by setting CURSOR_SPACE_FOR_TIME = TRUE which 
prompt + prevents ORACLE from deallocating a shared SQL area while an 
prompt + application cursor associated with it is open. 
prompt 

SET HEADING ON
prompt
prompt *** CACHE HIT RATIO per Buffer Pool:
prompt
set pagesize 0
COLUMN val1 NEW_VALUE defblsize NOPRINT
select
 to_number(value)/1024 val1
from
 v$parameter
where
 name like 'db_block_size';
set heading on
set pagesize 25
col pool        FORMAT A20                  HEADING "Buffer pool"
col ratio       FORMAT 999.90      HEADING "Hit Ratio (%)"
col comment     FORMAT A40                  HEADING "Comment"
select name||'.'||block_size/1024||'k' pool,
    100-round((physical_reads/(db_block_gets+consistent_gets+0.001))*100,2) as ratio,
    (case when (100-round((physical_reads/(db_block_gets+consistent_gets+0.001))*100,2)) >90
      then 'OK' else 'Consider to increase db_'||DECODE(block_size/1024,&defblsize,'',block_size/1024||'k_')||'cache_size' end) "comment"
from v$buffer_pool_statistics;

prompt
prompt *** FREE MEMORY AVAILABLE IN THE SGA:
col pool format A20
col name format A20
col sgas  heading "Allocated|SGA (Mb)"
col frees heading "Free|(Kb)"
col freep heading "%|Free"
select pool,
       name,
       round(sgasize/1024/1024,2) sgas,
       round(bytes/1024,2) frees,
       round(bytes/sgasize*100, 2) freep
from   (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where  f.name = 'free memory';
prompt
prompt *** MEMORY INIT PARAMETERS:
prompt
column inst_id     heading "Inst|id"             format 9999
column param       heading "Parameter"           format a50
BREAK ON inst_id
select
 inst_id,
 rpad(name,25,' ')||'= '||nvl(value ,0) param
from
 gv$parameter
where
    name like 'db%cache_size'
 or name like 'log_buffer'
 or name like '%pool_size'
 or name like 'db_block_buffers'
 or name like 'db_block_size'
 or name like 'sga_max_size'
 or name like 'sga_target'
 or name like 'pga_aggregate_target'
order by inst_id, param;
set heading off
prompt
select 'Default block size : db_block_size     = '||to_number(value)/1024||'k' from v$parameter where name like 'db_block_size'
union
select 'Default buffer pool: db_cache_size     = '||value from v$parameter where name like 'db_cache_size'
union
select 'Other buffer pools : '||name||' = '||value from v$parameter where name <> 'db_cache_size' and name like 'db%k_cache_size' and value <>'0';
prompt
select
 '+ Buffer Cache Advisory is: '||value||chr(10)||
 DECODE (value,'ON','+ Review the buff cache advisory below and adjust the memory settings if needed',
               'OFF','+ Enable the Buffer Cache Advisory by setting init parameter ''db_cache_advice=ON''',
               'READY','+ The Buffer Cache Advisor is READY. Please switch it ON using'||chr(10)||
                       '      ALTER SYSTEM SET DB_CACHE_ADVICE = ON')
from v$parameter where name like 'db_cache_advice';
prompt
prompt *** Buffer Pool Advisory:
prompt
select 'Instance ID: '||inst_id||' Buffer pool: '||pool||' status='||status from (
select inst_id,name||' '||block_size/1024||'k' pool, advice_status status, count(*)
from gV$DB_CACHE_ADVICE
group by inst_id,name||' '||block_size/1024||'k', advice_status)
order by 1;
column inst_id     heading "Inst|id"               format 9999
column block_size  heading "Block|Size"            format 999999
column size_factor heading 'Size Factor'    format 99.90
column c1          heading 'Cache Size (m)'        format 999,999,999,999
--column c2        heading 'Buffers'               format 999,999,999
column c3          heading 'Estd Phys|Read Factor' format 999.90
column c4          heading 'Estd Phys| Reads'      format 999999999999999
BREAK ON inst_id
set heading on
select name||'.'||block_size/1024||'k' pool,
    100-round((physical_reads/(db_block_gets+consistent_gets+0.001))*100,2) as ratio,
    (case when (100-round((physical_reads/(db_block_gets+consistent_gets+0.001))*100,2)) >90
      then 'OK' else 'Consider to increase db_'||DECODE(block_size/1024,&defblsize,'',block_size/1024||'k_')||'cache_size' end) "comment"
from v$buffer_pool_statistics;
select
   inst_id,
   block_size,
   size_factor,
   size_for_estimate          c1,
--   buffers_for_estimate       c2,
   estd_physical_read_factor  c3,
   estd_physical_reads        c4
from
   gv$db_cache_advice
where
   name = 'DEFAULT'
and
   advice_status = 'ON'
order by 1,2,3;
prompt
set pagesize 0
select
 '+ Instance# '||s1.inst_id||
 ' "db_'||DECODE(s1.block_size/1024,&defblsize,'',s1.block_size/1024||'k_')||
 'cache_size"='||s1.size_for_estimate||'MB'||chr(10)||
 '+   Increasing "db_'||DECODE(s1.block_size/1024,&defblsize,'',s1.block_size/1024||'k_')||
 'cache_size" to '||se.size_for_estimate||
 ' MB may reduce disk read factor to '||se.estd_physical_read_factor
from
(
select inst_id,block_size,size_factor,size_for_estimate,estd_physical_read_factor
from   gv$db_cache_advice
where  name = 'DEFAULT'
  and  advice_status = 'ON'
  and  size_factor = 1
) s1,
(
select sa.inst_id,sa.block_size,sa.size_factor,sa.size_for_estimate,sa.estd_physical_read_factor
from
(
select inst_id,block_size,size_factor,size_for_estimate,estd_physical_read_factor
from   gv$db_cache_advice
where  name = 'DEFAULT'
  and  advice_status = 'ON'
) sa
,
(select inst_id,block_size,max(size_factor) sfm
from    gv$db_cache_advice
where   name = 'DEFAULT' and advice_status = 'ON'
group by inst_id,block_size
) sm
where sa.inst_id = sm.inst_id
  and sa.block_size = sm.block_size
  and sa.size_factor = sm.sfm
) se
where se.inst_id = s1.inst_id
  and se.block_size = s1.block_size
  and se.estd_physical_read_factor < 0.90
order by s1.block_size, s1.inst_id;

set pagesize 50
prompt
prompt *** SORT Statistics:
prompt
COLUMN r    FORMAT A10      HEADING "Sorted|Rows"
COLUMN m    FORMAT A10      HEADING "Memory|Sorts"
COLUMN d    FORMAT A7       HEADING "Disk|Sorts"
COLUMN pct  FORMAT 990.00   HEADING "Disk|%"       
COLUMN comm FORMAT A26      HEADING "Comment"      
COLUMN p    FORMAT A14      HEADING "SORT_AREA_SIZE"
SELECT
    TO_CHAR(r.value) r,
    TO_CHAR(m.value) m,
    TO_CHAR(d.value) d,
    DECODE(m.value, 0, 1, d.value / m.value * 100) pct,
    DECODE(SIGN(DECODE(m.value, 0, 1, d.value / m.value) - 0.05),
        1, 'Increase SORT_AREA_SIZE / PGA',
        'OK') comm
--,   p.value p
FROM
    v$sysstat r,
    v$sysstat m,
    v$sysstat d,
    v$parameter p
WHERE
    r.name = 'sorts (rows)'
    AND m.name = 'sorts (memory)'
    AND d.name = 'sorts (disk)'
    AND p.name = 'sort_area_size';

prompt
prompt
prompt *** PGA statistics:
prompt
set pagesize 0
select '+ Automatic SQL Execution Memory Management is '||decode (UPPER(value),'AUTO','ENABLED','DISABLED')||chr(10)||
       '+   workarea_size_policy='||UPPER(value) from v$parameter where name = 'workarea_size_policy'
union all
select '+   pga_aggregate_target='||value from v$parameter where name = 'pga_aggregate_target';
select decode(UPPER(value),'MANUAL', '+ Enable Automatic SQL Execution Memory Management as described below:','')
from v$parameter where name = 'workarea_size_policy';
-- prompt
-- prompt + Optimization of the PGA memory structures could be very time consuming
-- prompt + depending on the type of operations the system was performing.
-- prompt + Oracle9i allows to leave configuration of the PGA up to Oracle by setting two
-- prompt + initialization parameters:
-- prompt +         WORKAREA_SIZE_POLICY = AUTO
-- prompt +         PGA_AGGREGATE_TARGET = ?????K
-- prompt
-- prompt + Actually the Automatic SQL Memory Management feature can be enabled by setting
-- prompt + only the parameter PGA_AGGREGATE_TARGET to a non-zero value.
-- prompt + The WORKAREA_SIZE_POLICY parameter tell the server that it should take over
-- prompt + PGA memory management. The PGA_AGGREGATE_TARGET parameter specifies the total
-- prompt + amount of memory the server can allocate to the PGA.
prompt + Oracle quote the following equations as a base for calculating the value
prompt + of this parameter:
prompt +         PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 20% for an OLTP system
prompt +         PGA_AGGREGATE_TARGET = (TOTAL_MEM * 80%) * 50% for a DSS system
prompt + Where TOTAL_MEMORY is the total available memory for the system.
prompt + When the Automatic SQL Execution Memory Management feature is enabled, any
prompt + *_AREA_SIZE settings are ignored.
prompt

prompt *** DISPLAY PGA TARGET ADVICE:
prompt

-- prompt + Besides SGA, the Oracle database also assigns each server process a private
-- prompt + memory region called the .Program Global Area. (PGA). A PGA is created for
-- prompt + each server process when it is started (i.e. when a new session is initiated
-- prompt + while using the dedicate server configuration or when a new shared server
-- prompt + process is created). It contains data and control information for a server
-- prompt + process and, unlike the SGA, each server process has exclusive access to its
-- prompt + PGA.
-- prompt + Although the actual content of the PGA varies depending on whether a process
-- prompt + is running in dedicated or shared mode, it can generally be categorized as
-- prompt + follows:
-- prompt +   o Session Memory: The memory allocated to hold session.s logon information
-- prompt +     and other such details. For shared server processes, this information is
-- prompt +     stored in the SGA.
-- prompt +   o SQL Execution Memory: The memory allocated on behalf of SQL statements
-- prompt +     being executed. The SQL Execution Memory has a persistent and a run time
-- prompt +     area
-- prompt +      o The persistent area is allocated when a cursor is opened for SQL
-- prompt +        execution. It contains the information that persists across multiple
-- prompt +        execution of the same statement (i.e. cursor) such as bind details,
-- prompt +        data type conversion, etc. The persistent area is de-allocated when the
-- prompt +        cursor is closed. For shared server processes, the persistent area is a
-- prompt +        part of the SGA.
-- prompt +      o The runtime area contains information used while a SQL statement is
-- prompt +        being executed. Its size depends on the type and complexity of SQL
-- prompt +        statement as well as the number and size of rows being processed. It is
-- prompt +        de-allocated once the execution completes. For shared sever processes,
-- prompt +        the run time area is resides in the PGA for DML/DDL operation and in
-- prompt +        the SGA for queries. For complex queries, such as those used for
-- prompt +        reporting purposes or as ad hoc queries in a data-warehousing
-- prompt +        environment, a large portion of the run time SQL execution memory is
-- prompt +        dedicated as the working area for operations such as sort, hash-join
-- prompt +        and bitmap-merge.
-- prompt + Larger working areas can significantly improve the performance of these
-- prompt + operations and thereby, reduce the response time of queries. Ideally, the size
-- prompt + of work area should be large enough such that it can accommodate all input
-- prompt + data and auxiliary structure needed by the operation.

column p1     heading 'Target(M)'
column p2     heading 'Estimated|Cache Hit %'
column p3     heading 'Estimated|Over-Alloc.'
set heading on
set pagesize 50
BREAK ON inst_id

SELECT
   inst_id,
   ROUND(pga_target_for_estimate /(1024*1024)) p1,
   PGA_TARGET_FACTOR          "Size Factor",
   estd_pga_cache_hit_percentage               p2,
   estd_overalloc_count                        p3
FROM
   gv$pga_target_advice
order by 1 asc, 2 asc;
prompt
prompt + The following output shows us how many operations, or executions, were
prompt + performed in the work areas. These executions fall into three categories.
prompt + The optimal executions are those operations that were performed entirely in
prompt + memory. As the name suggests, this is the most favorable type of execution.
prompt + If the operation was too big to be performed in memory, then part of the
prompt + operation spills onto disk. If only one pass was needed on disk, then this
prompt + execution is noted in the onepass statistic. If more than one pass was needed
prompt + on disk, then this execution is noted in the multipass statistic. Ideally,
prompt + all executions should be in the optimal statistic and the statistics for
prompt + onepass and multipass should be zero. If the # of multipass executions is
prompt + high consider increasing the value of pga_aggregate_target parameter.
prompt
col name for a35
select
 inst_id,
 name,
 value
from
 gv$sysstat
where name like 'workarea executions%'
order by 1 asc, 2 desc;
set heading off
col msg for a50
select (case when value>1000 then
                        '+ You may need to increase pga_aggregate_target for inst #'||inst_id||chr(10)||
                        '+ Check the PGA Advisor for suggested size'
        else '+ pga_agregate_target is OPTIMAL for inst #'||inst_id
  end) msg 
from gv$sysstat
where name like 'workarea executions - multipass';
prompt
prompt
prompt *** DISPLAY SHARED POOL ADVICE:
prompt
set pages  999
column  s1 heading 'Pool |Size(M)'
column  s2 heading 'Size|Factor'
column  s3 heading 'Est|LC(M)  '
column  s4 heading 'Est LC|Mem. Obj.'
column  s5 heading 'Est|Time|Saved|(sec)'
column  s6 heading 'Est|Parse|Saved|Factor'
column s7 heading 'Est|Object Hits'   format 999,999,999,999
prompt + This advisory gives advice on library cache memory and predicts the effect of
prompt + altering the shared pool size on the total amount parsing activities in the
prompt + system.
prompt + The following view displays information about estimated parse timesavings in
prompt + different sizes of shared pool. The sizes range from 50% to 200% of current
prompt + shared pool size, in equal intervals. The value of the interval depends on
prompt + current shared pool size. Parse Time Saved refers to the amount of time saved
prompt + by keeping library cache memory objects in the shared pool, as opposed to
prompt + having to reload these objects.
set heading on
BREAK ON inst_id
SELECT
   inst_id,
   shared_pool_size_for_estimate s1,
   shared_pool_size_factor  s2,
   estd_lc_size   s3,
   estd_lc_memory_objects  s4,
--   estd_lc_time_saved  s5,
   estd_lc_time_saved_factor s6,
   estd_lc_memory_object_hits s7
FROM
   gv$shared_pool_advice
order by 1,3;

PROMPT
PROMPT *** LIST ORACLE JOBS (Check for broken jobs here)
PROMPT
COLUMN jid  FORMAT 9999  HEADING 'Id'
COLUMN subu FORMAT A10  HEADING 'Submitter'     TRUNC
COLUMN secd FORMAT A10  HEADING 'Security'      TRUNC
COLUMN proc FORMAT A19  HEADING 'Job'           WORD_WRAPPED
COLUMN lsd  FORMAT A5   HEADING 'Last|Ok|Date'
COLUMN lst  FORMAT A5   HEADING 'Last|Ok|Time'
COLUMN nrd  FORMAT A5   HEADING 'Next|Run|Date'
COLUMN nrt  FORMAT A5   HEADING 'Next|Run|Time'
COLUMN fail FORMAT 999  HEADING 'Errs'
COLUMN ok   FORMAT A3   HEADING 'Ok?'
SELECT
  job jid,
  log_user subu,
  priv_user secd,
  substr(what,1,50) proc,
  TO_CHAR(last_date, 'MM/DD') lsd,
  SUBSTR(last_sec, 1, 5) lst,
  TO_CHAR(next_date, 'MM/DD') nrd,
  SUBSTR(next_sec, 1, 5) nrt,
  failures fail,
  DECODE(broken, 'Y', 'No', 'Yes') ok
FROM
  sys.dba_jobs;
PROMPT
PROMPT *** INVALID DATABASE OBJECTS:
PROMPT
COLUMN owner        FORMAT A16   HEADING "Object Owner"
COLUMN object_name  FORMAT A30   HEADING "Object Name"
COLUMN object_type  FORMAT A25   HEADING "Object Type"
COLUMN last_time    FORMAT A18   HEADING "Last Change Time"
COLUMN no           FORMAT 99999 HEADING "# of Invalid|Objects"
prompt
prompt SUMMARY:
SELECT
    owner,
    object_type,
    count(*) no
FROM
    dba_objects
WHERE
    status = 'INVALID'
GROUP BY
    owner,
    object_type
ORDER BY
    1,2;
PROMPT
PROMPT LISTING ONLY SYS INVALID OBJECTS:
PROMPT ...
PROMPT
col owner         for a10 heading "Owner"
col object_name   for a33 heading "Object Name"
col object_type   for a15 heading "Object Type"
col last_ddl_time for a18 heading "Last DDL Time"
SELECT
     owner,
     object_type,
     object_name,
     TO_CHAR(last_ddl_time, 'DD-MON-YY hh:mi:ss') last_ddl_time
FROM
     dba_objects
WHERE
     status = 'INVALID'
     and owner in ('SYS','SYSTEM')
ORDER BY
     owner,
     object_type,
     object_name;

PROMPT
PROMPT *** NON-SYSTEM objects in SYSTEM tablespace:
PROMPT
COLUMN owner         FORMAT A30   HEADING "Object Owner"
COLUMN segment_type  FORMAT A22   HEADING "Object Type"
COLUMN no            FORMAT 99999 HEADING "# of Objects"
set pagesize 0
SELECT
    '+ '||count(*)||' NON-SYSTEM objects detected in SYSTEM tablespace with '||
                    'total size:  '||NVL(round(sum(bytes)/1024/1024,2),0)||'Mb'
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM','CSMIG','OUTLN');
set pagesize 50
SELECT
    owner,
    segment_type,
    count(*) no
FROM
    dba_segments
WHERE
    tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM','CSMIG','OUTLN')
GROUP BY
    owner,
    segment_type
ORDER BY
    1,2;

prompt
prompt + Run $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid objects (if any)
prompt + Make sure that all SYS and SYSTEM objects are valid
prompt + Address the application invaild objects to the application support team
prompt

set heading off

-- PROMPT
-- PROMPT *** NON-INDEXED FOREIGN KEYS:
-- PROMPT
-- prompt + Lists foreign keys that are missing indexes on the foreign key
-- prompt + columns in the child table.
--
-- col msg for a70
--
-- SELECT (case when count(*)>0 then '+ '||count(*) ||' non-indexes foreign key detected.'
--         else '+ Non-indexes foreign key not detected. OK.'
--         end) msg
-- FROM 
--     all_cons_columns acc,
--     all_constraints ac
-- WHERE
--     acc.owner not in ('SYS','SYSTEM','PERFSTAT')
--     AND acc.table_name not like 'MVIEW$%'
--     AND ac.constraint_name = acc.constraint_name
--     AND ac.constraint_type = 'R'
--     AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
--     (
--         SELECT
--             acc.owner,
--             acc.table_name,
--             acc.column_name,
--             acc.position
--         FROM  
--             all_cons_columns acc,
--             all_constraints ac
--         WHERE 
--             ac.constraint_name = acc.constraint_name
--             AND ac.constraint_type = 'R'
--         MINUS
--         SELECT
--             table_owner,
--             table_name,
--             column_name,
--             column_position
--         FROM
--             all_ind_columns
--     );
--
-- set pagesize 25
-- set heading on
--
-- COLUMN owner    FORMAT A20    HEADING "Owner"
-- COLUMN no       FORMAT 999999 HEADING "# of non-indexed FK"
-- SELECT
--     acc.owner,
--     count(*) no
-- FROM 
--     all_cons_columns acc,
--     all_constraints ac
-- WHERE
--     acc.owner not in ('SYS','SYSTEM','PERFSTAT')
--     AND acc.table_name not like 'MVIEW$%'
--     AND ac.constraint_name = acc.constraint_name
--     AND ac.constraint_type = 'R'
--     AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
--     (
--         SELECT
--             acc.owner,
--             acc.table_name,
--             acc.column_name,
--             acc.position
--         FROM  
--             all_cons_columns acc,
--             all_constraints ac
--         WHERE 
--             ac.constraint_name = acc.constraint_name
--             AND ac.constraint_type = 'R'
--         MINUS
--         SELECT
--             table_owner,
--             table_name,
--             column_name,
--             column_position
--         FROM
--             all_ind_columns
--     )
-- GROUP BY
--     acc.owner
-- ORDER BY
--     acc.owner;
--
-- set pagesize 50
-- set heading on
--
-- prompt
-- prompt + Script for details report for non-indexed foregn keys:
-- prompt
-- prompt + set pagesize 50
-- prompt + set heading on
-- prompt
-- prompt + COLUMN owner    FORMAT A15 HEADING "Owner"
-- prompt + COLUMN cons     FORMAT A20 HEADING "Constraint"
-- prompt + COLUMN tab_name NOPRINT
-- prompt + COLUMN col_name NOPRINT
-- prompt + COLUMN pos      NOPRINT
-- prompt + COLUMN cols     FORMAT A43 HEADING "Table.Column(Pos)"
-- prompt +
-- prompt + BREAK ON owner SKIP 1
-- prompt
-- prompt + SELECT DISTINCT
-- prompt +     acc.owner,
-- prompt +     acc.constraint_name cons,
-- prompt +     acc.table_name tab_name,
-- prompt +     acc.column_name col_name,
-- prompt +     acc.position pos,
-- prompt +     acc.table_name || '.' ||
-- prompt +     acc.column_name || '(' ||
-- prompt +     acc.position || ')' cols
-- prompt + FROM 
-- prompt +     all_cons_columns acc,
-- prompt +     all_constraints ac
-- prompt + WHERE
-- prompt +     acc.owner not in ('SYS','SYSTEM','PERFSTAT')
-- prompt +     AND acc.table_name not like 'MVIEW$%'
-- prompt +     AND ac.constraint_name = acc.constraint_name
-- prompt +     AND ac.constraint_type = 'R'
-- prompt +     AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN
-- prompt +     (
-- prompt +         SELECT
-- prompt +             acc.owner,
-- prompt +             acc.table_name,
-- prompt +             acc.column_name,
-- prompt +             acc.position
-- prompt +         FROM  
-- prompt +             all_cons_columns acc,
-- prompt +             all_constraints ac
-- prompt +         WHERE 
-- prompt +             ac.constraint_name = acc.constraint_name
-- prompt +             AND ac.constraint_type = 'R'
-- prompt +         MINUS
-- prompt +         SELECT
-- prompt +             table_owner,
-- prompt +             table_name,
-- prompt +             column_name,
-- prompt +             column_position
-- prompt +         FROM
-- prompt +             all_ind_columns
-- prompt +     )
-- prompt + ORDER BY
-- prompt +     acc.owner,
-- prompt +     acc.table_name,
-- prompt +     acc.column_name,
-- prompt +     acc.position;
-- prompt
--
-- prompt + Having Unindexed foreign keys can be a performance issue. There are two issues
-- prompt + associated with unindexed foreign keys. The first is the fact that a table
-- prompt + lock will result if you update the parent records primary key (very very
-- prompt + unusual) or if you delete the parent record and the child's foreign key is not
-- prompt + indexed.
-- prompt
-- prompt + The second issue has to do with performance in general of a parent child
-- prompt + relationship. Consider that if you have an on delete cascade and have not
-- prompt + indexed the child table (eg: EMP is child of DEPT. Delete deptno = 10 should
-- prompt + cascade to EMP. If deptno in emp is not indexed -- full table scan). This full
-- prompt + scan is probably undesirable and if you delete many rows from the parent
-- prompt + table, the child table will be scanned once for each parent row deleted.
-- prompt
-- prompt + Also consider that for most (not all, most) parent child relationships, we
-- prompt + query the objects from the 'master' table to the 'detail' table. The glaring
-- prompt + exception to this is a code table (short code to long description). For
-- prompt + master/detail relationships, if you do not index the foreign key, a full scan
-- prompt + of the child table will result.
set pagesize 0
prompt *** BLOCK CONTENTION STATISTICS: 
prompt 
column inst_id heading 'Inst|ID'             format 9999
column class   heading 'Class Type'          format a20            TRUNC
column count   heading 'Times Waited'        format 99,999,999,999
column time    heading 'Total Waited Time'   format 99,999,999,999 
column wtp     heading 'Total Waited %'      format 99.99 
BREAK ON inst_id

select
 w.inst_id,
 w.class, 
 w.count, 
 w.time,
 round(w.time/t.time*100,2) wtp 
from  
 gv$waitstat w,
(select inst_id,sum(time) time
 from gv$waitstat
 where  count > 0
 group by inst_id
) t  
where
 t.inst_id = w.inst_id 
 and w.count > 0 
 order  by w.inst_id asc,w.time desc;

prompt
prompt
prompt *** TOP SYSTEM Timed Events (Waits):
prompt
COLUMN event       FORMAT A50           HEADING "Wait Event"  TRUNC
COLUMN time_waited FORMAT 9999999999999 HEADING "Time|Waited"
COLUMN wait_pct    FORMAT 99.90         HEADING "Wait|(%)"
BREAK ON inst_id
SELECT
 w.inst_id,
 w.event,
 w.time_waited,
 round(w.time_waited/tw.twt*100,2) wait_pct
FROM
 gv$system_event w,
 (select inst_id, sum(time_waited) twt
  from gv$system_event
  where time_waited>0
  and event NOT IN
 ('Null event',
 'client message',
 'rdbms ipc reply',
 'smon timer',
 'rdbms ipc message',
 'PX Idle Wait',
 'PL/SQL lock timer',
 'file open',
 'pmon timer',
 'WMON goes to sleep',
 'virtual circuit status',
 'dispatcher timer',
 'SQL*Net message from client',
 'parallel query dequeue wait',
 'pipe get') 
  group by inst_id
  ) tw
WHERE
 w.inst_id = tw.inst_id
 and w.time_waited>0
 and round(w.time_waited/tw.twt*100,2) > 1
 and w.event NOT IN
 ('Null event',
 'client message',
 'rdbms ipc reply',
 'smon timer',
 'rdbms ipc message',
 'PX Idle Wait',
 'PL/SQL lock timer',
 'file open',
 'pmon timer',
 'WMON goes to sleep',
 'virtual circuit status',
 'dispatcher timer',
 'SQL*Net message from client',
 'parallel query dequeue wait',
 'pipe get')
ORDER by 1 asc, 4 desc;

prompt
prompt
prompt *** LOCKS HELD IN THE DATABASE:
prompt
prompt
PROMPT  +------------------------------------------------------------+
PROMPT  |LOCKS DEFINED                                               |
PROMPT  |                                                            |
PROMPT  |RS  - Row Share           - no exclusive WRITE              |
PROMPT  |RX  - Row eXclusive       - no exclusive READ or WRITE      |
PROMPT  |S   - Share               - no modifications                |
PROMPT  |SRX - Share Row eXclusive - no modifications or RX          |
PROMPT  |X   - eXclusive           - no access period                |
PROMPT  |                                                            |
PROMPT  |TX  - Row Locks                                             |
PROMPT  |TM  - Table Locks                                           |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |SQL Statement                   |Mode   |RS |RX |S  |SRX|X  |
PROMPT  |                                |       |   |   |   |   |   |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |SELECT ... FROM table ...       |none   |Y  |Y  |Y  |Y  |Y  |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |INSERT INTO table ...           |none   |Y* |Y* |N  |N  |N  |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |UPDATE table ...                |none   |Y* |Y* |N  |N  |N  |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |DELETE FROM table ...           |none   |Y* |Y* |N  |N  |N  |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT  |SELECT ... FROM table ...       |none   |Y* |Y* |Y* |Y* |N  |
PROMPT  |  FOR UPDATE OF ...             |       |   |   |   |   |   |
PROMPT  +--------------------------------+-------+---+---+---+---+---+
PROMPT
PROMPT  * If no conflicting locks are held by another transaction.
PROMPT   Otherwise, waits occur.
PROMPT
PROMPT  ...
PROMPT
COLUMN username     FORMAT A10  HEADING "Username"  WORD_WRAPPED
COLUMN type                     HEADING "TY"
COLUMN opid         FORMAT A10  HEADING "SID/SER"
COLUMN lmode        FORMAT A5   HEADING "Lock"
COLUMN lrequest     FORMAT A4   HEADING "Req?"
COLUMN obj          FORMAT A20  HEADING "Object"    WORD_WRAPPED
COLUMN block        FORMAT A12  HEADING "Blocking?"

select  
         ORACLE_USERNAME            username,
--         OS_USER_NAME               osuser,
--         PROCESS as                 ospid,
         l.SID||'/'||s.serial#      opid,
         DECODE(l.TYPE
   -- Long locks
         , 'TM', 'dml/data enq (TM)'
         , 'TX', 'transac enq (TX) '
         , 'UL', 'pls usr lock (UL)'
   -- Short locks                  
         , 'BL', 'buf hash tbl (BL)'
         , 'CF', 'control file (CF)'
         , 'CI', 'cross inst f (CI)'
         , 'CU', 'cursor bind (CU) '
         , 'DF', 'data file (CF)   '
         , 'DL', 'direct load (DL) '
         , 'DM', 'mount/strtup (DM)'
         , 'DR', 'reco lock (DR)   '
         , 'DX', 'distrib tran (DX)'
         , 'FI', 'sga opn file (FI)'
         , 'FS', 'file set (FS)    '
         , 'IN', 'instance num (IN)'
         , 'IR', 'instce recvr (IR)'
         , 'IS', 'get state (IS)   '
         , 'IV', 'libcache inv (IV)'
         , 'JQ', 'job queue (JQ)   '
         , 'KK', 'log sw kick (KK) '
         , 'LS', 'log switch (LS)  '
         , 'MM', 'mount def (MM)   '
         , 'MR', 'media recvry (MR)'
         , 'PF', 'pwfile enq (PF)  '
         , 'PR', 'process strt (PR)'
         , 'RW', 'row wait (RW)    '
         , 'RT', 'redo thread (RT) '
         , 'SC', 'scn enq (SC)     '
         , 'SM', 'smon lock (SM)   '
         , 'SN', 'seqno instce (SN)'
         , 'SQ', 'seqno enq (SQ)   '
         , 'ST', 'space transc (ST)'
         , 'SV', 'seqno value (SV) '
         , 'TA', 'generic enq (TA) '
         , 'TD', 'dll enq (TD)     '
         , 'TE', 'extend seg (TE)  '
         , 'TS', 'temp segment (TS)'
         , 'TT', 'temp table (TT)  '
         , 'UN', 'user name (UN)   '
         , 'WL', 'write redo (WL)  '
         , 'TYPE = ' || l.TYPE) AS                 type ,
         DECODE(l.lmode, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6, 
                'EXCL', TO_CHAR(l.lmode))     lmode ,  
--         DECODE(l.request, 0, 'none', 1, 'null', 2, 'RS', 3, 'RX', 4, 'S', 5, 'SRX', 6,
--                  'X', TO_CHAR(l.request)) AS lrequest,
         decode(BLOCK,
            0, 'Not Blocking',
            1, 'Blocking',
            2, 'Global', block)                     block,
         OWNER||'.'||OBJECT_NAME                    obj
         from     sys.v_$locked_object lo, dba_objects do, sys.v_$lock l,         
         (SELECT a.sid, a.serial#
          FROM v$session a, v$bgprocess b
          WHERE a.paddr = b.paddr(+)) s
where    lo.OBJECT_ID = do.OBJECT_ID
AND      l.SID = lo.SESSION_ID
and s.sid = l.sid;
set pagesize 25
set heading on
prompt
prompt *** PL/SQL THAT MAY NEED TO BE CACHED
prompt
col owner  for  a19
col name   for  a30  heading "Name" WORD_WRAPPED
select owner, name, sharable_mem,kept
from v$db_object_cache
where sharable_mem >100000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
and kept ='NO';
prompt
prompt + Use the DBMS_SHARED_POOL package to pin objects
prompt +    EXEC SYS.DBMS_SHARED_POOL.KEEP('<owner>','<name>');
prompt
prompt
prompt *** SEGMENT USAGE STATISTICS
prompt
prompt *** Most i/o operation (TOP 5):
prompt
col object_type    format a15         heading "Object Type"
col object_name    format a27         heading "Object Name"
col statistic_name format a22         heading "Statistic Name"
col value     format 99999999999 heading "Value"
SELECT * from (
SELECT object_type, object_name, statistic_name, VALUE
  FROM v$segment_statistics
  WHERE statistic_name LIKE '%phys%' AND VALUE > 50
  ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most buffer gets (TOP 5):
prompt
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
  FROM v$segment_statistics
  WHERE statistic_name LIKE '%logi%' AND VALUE > 50
  ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt + Review the query causing high amount of buffer gets.
prompt + Use StatsPack to detect them.
prompt + Create additional indexes to avoid FULL table scans.
prompt
 
prompt
prompt *** Most Buffer Busy Waits (TOP 5):
prompt
SELECT * from (
SELECT object_type, object_name, statistic_name,VALUE
  FROM v$segment_statistics
  WHERE statistic_name LIKE '%buffer%' AND VALUE > 0
  ORDER BY 4 DESC
) where rownum < 6;
prompt
set pagesize 0
select
 '+ Buffer Busy Wait Ratio: '||round((bbc.total_waits*100/(cg.value+dbg.value)),4)
from
 v$system_event bbc, v$sysstat cg,  v$sysstat dbg
where
     bbc.event='buffer busy waits'
 and cg.name  ='consistent gets'
 and dbg.name ='db block gets';
set pagesize 100
-- prompt
-- prompt + As buffer busy waits are due to contention for particular blocks
-- prompt + then you cannot take any action until you know which blocks are
-- prompt + being competed for and why.
-- prompt + Eliminating the cause of the contention is the best option.
-- prompt + Note that "buffer busy waits" for data blocks are often due to
-- prompt + several processes repeatedly reading the same blocks
-- prompt + (eg: if lots of people scan the same index) -
-- prompt + the first session processes the blocks that are in the buffer cache
-- prompt + quickly but then a block has to be read from disk - the other sessions
-- prompt + (scanning the same index) quickly 'catch up' and want the block which
-- prompt + is currently being read from disk - they wait for the buffer as someone
-- prompt + is already reading the block in.
-- prompt
-- prompt + The following hints may be useful for particular types of contention -
-- prompt + these are things that MAY reduce contention for particular situations:
-- prompt
-- prompt + Block Type and Possible Actions
-- prompt 
-- prompt + data blocks
-- prompt +   Eliminate HOT blocks from the application. Check for repeatedly
-- prompt +   scanned / unselective indexes. Change PCTFREE and/or PCTUSED. Check for
-- prompt +   'right- hand-indexes' (indexes that get inserted into at the same point
-- prompt +   by many processes). Increase INITRANS. Reduce the number of rows per block
-- prompt + segment header
-- prompt +   Increase of number of FREELISTs. Use FREELIST GROUPs (even in single
-- prompt +   instance this can make a difference).
-- prompt + freelist blocks
-- prompt +   Add more FREELISTS. In case of Parallel Server make sure that each instance
-- prompt +   has its own FREELIST GROUP(s).
-- prompt + undo header
-- prompt +   Add more rollback segments.
-- prompt

prompt
prompt *** Most Row Level Locks (TOP 5):
prompt
SELECT * from (
SELECT object_type,object_name, statistic_name,VALUE
  FROM v$segment_statistics
  WHERE statistic_name LIKE '%row%' AND VALUE > 0
  ORDER BY 4 DESC
) where rownum < 6;
prompt
prompt *** Most ITL waits (TOP 5):
prompt
SELECT * from (
SELECT object_type,object_name, statistic_name,VALUE
  FROM v$segment_statistics
  WHERE statistic_name LIKE '%ITL%' AND VALUE > 0
  ORDER BY 4 DESC
) where rownum < 6;
-- prompt
-- prompt + What Is an ITL Wait?
-- prompt + This can occur because either (i) the block is so packed that the ITL
-- prompt + cannot grow to create a free slot, or (ii) the MAXTRANS has already
-- prompt + been reached. In this case, the transaction that needs to lock a row
-- prompt + has to wait until a slot becomes available. This wait is termed as ITL
-- prompt + waits and can be seen from the view v$session_wait, in which the session
-- prompt + is waiting on an event named "enqueue."
-- prompt
-- prompt + The INITTRANS setting controls Initial Transaction Slots (ITLs).
-- prompt + A transaction slot is required for any session that needs to modify a
-- prompt + block in an object. For tables INITRANS defaults to 1 for indexes, 2.
-- prompt
-- prompt + The MAXTRANS setting controls the maximum number of ITLs that a block
-- prompt + can allocate (usually defaults to 255). If a block is sparsely populated
-- prompt + then Oracle will dynamically increase the number of ITLs up to MAXTRANS.
-- prompt
-- prompt + ITL's, INITRANS and Block Waits
-- prompt + However, if the block has little or no free space then transactions will
-- prompt + serialize waiting on a free ITL. This is one cause for data base block waits.
-- prompt + By setting INITRANS to the number of expected simultaneous DML transaction
-- prompt + for a single block, you can avoid serialization for ITL slots.
-- prompt
-- prompt + The maximum value suggested for INITRANS is 100 and settings over this
-- prompt + size rarely improve performance. Therefore a setting of INITRANS to the
-- prompt + average number of simultaneous DML users and setting MAXTRANS to 100 will
-- prompt + most likely result in the best utilization of resources and performance.
-- prompt + Remember, each ITL requires approximately 23 bytes in the block header.
-- prompt


prompt
prompt *** CROSSTAB of object and statistic
prompt
col obj      format a20          heading "Object|Name"       WORD_WRAP
col itl      format 999999       heading "ITL|Waits"
col bbw      format 999999999    heading "Buffer|Busy|Waits"
col phr      format 999999999999 heading "Physical|Reads"
col lr       format 999999999999 heading "Logical|Reads"
col rlw      format 999999999    heading "Row|Lock|Waits"

set pages 100

select * from
(
   select
      DECODE
      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)
   AS obj,
sum(case when
   a.statistic_name = 'ITL waits'
then
   a.value else null end) "itl",
sum(case when
   a.statistic_name = 'buffer busy waits'
then
   a.value else null end) "bbw",
sum(case when
   a.statistic_name = 'row lock waits'
then
   a.value else null end) "rlw",
sum(case when
   a.statistic_name = 'physical reads'
then
   a.value else null end) phr,
sum(case when
   a.statistic_name = 'logical reads'
then
   a.value else null end) lr
from
   v$segment_statistics a
where
   owner not like 'SYS%'
group by
   rollup(a.object_name)) b
where (b."itl">0 or b."bbw">0)
order by 3 desc
;
prompt
prompt + The cross tab report generates a listing showing the statistics of concern
prompt + as headers across the page rather than listings going down the page and
prompt + summarizes them by object. This allows us to easily compare total buffer
prompt + busy waits to the number of ITL or row lock waits. This ability to compare
prompt + the ITL and row lock waits to buffer busy waits lets us see what objects may
prompt + be experiencing contention for ITL lists, which may be experiencing excessive
prompt + locking activity and through comparisons, which are highly contended for
prompt + without the row lock or ITL waits.
prompt
-- prompt + AN example of the output of the report, edited for length, is shown below.                                                              
-- prompt
-- prompt +     Object        ITL       Buffer      Row Lock   Physical     Logical 
-- prompt +                   Waits     Busy Waits     Waits      Reads       Reads
-- prompt +     ************* ********* ********** ********* *********** **********
-- prompt +     BILLING           0          63636     38267    1316055   410219712
-- prompt +     BILLING_INDX1     1          16510        55     151085    21776800
-- prompt +     ...
-- prompt +     DELIVER_INDX1   1963         36096     32962   1952600     60809744      
-- prompt +     DELIVER_INDX2     88         16250      9029   18839481   342857488 
-- prompt +     DELIVER_PK      2676         99748     29293   15256214   416206384
-- prompt + ...
-- prompt + All Objects    12613      20348859   1253057 1139977207 20947864752
-- prompt + 
-- prompt + In the above report the BILLING_INDX1 index has a large amount of  buffer
-- prompt + Reads busy waits but we can.t account for them from the ITL or Row lock
-- prompt + waits, this indicates that the index is being constantly read and the blocks
-- prompt + then aged out of memory forcing waits as they are re-read for the next
-- prompt + process. On the other hand, almost all of the buffer busy waits for the
-- prompt + DELIVER_INDX1 index can be attributed to ITL and Row Lock waits.
-- prompt + In situations where there are large numbers of ITL waits we need to consider
-- prompt + the increase of the INITRANS setting for the table to remove this source of
-- prompt + contention. If the predominant wait is row lock waits then we need to
-- prompt + determine if we are properly using locking and cursors in our application
-- prompt + (for example, we may be over using the SELECT.FOR UPDATE type code.)
-- prompt + If, on the other hand all the waits are un-accounted for buffer busy waits,
-- prompt + then we need to consider increasing the amount of database block buffers
-- prompt + we have in our SGA.
-- prompt
prompt
prompt *** SEGMENTS WITH MORE THAN 100 extents
prompt ...
col owner for a13
col segment_name for a22
col segment_type for a18
col segsize for 999999.90 heading "Segment|Size (Mb)"
col segcount for 999999   heading "# of extents"
select
 OWNER,
 SEGMENT_NAME,
 SEGMENT_TYPE,
 sum(bytes)/1024/1024 segsize,
 count(*) segcount
from
 dba_segments
where
 owner not in ('SYS','SYSTEM')
group by
 OWNER, SEGMENT_NAME, SEGMENT_TYPE
having
 count(*) > 100;
prompt
prompt + Segments with high # of extents in dictionary manged tablespace might cause
prompt + performance isssues.
prompt + Use Locally managed tablespaces.
prompt

prompt
prompt *** TABLES with many indexes (>6)
prompt
set pagesize 0
select count(*)||' tables detected with more than 4 indexes' 
from
(select owner, table_name, count(*)
from dba_indexes
where owner not in ('SYS','SYSTEM')
group by owner, table_name
having count(*) > 6
);
set pagesize 50
col owner      for a30 heading Owner
col table_name for a30 heading "Table Name"
col no         for 999 heading "# of indexes"
select
 owner,
 table_name,
 count(*) no
from
 dba_indexes
where
 owner not in ('SYS','SYSTEM')
group by
 owner, table_name
having
 count(*) > 6
order by 3 desc;

prompt
prompt + Consider to use index monitoring feature
prompt + Monitoring an Index determine weather the particular index is used or not
prompt + used, so that you can drop un-necessary indexes.
prompt
-- prompt + Enable Monitoring By :
-- prompt +    ALTER INDEX <INDEXNAME> MONITORING USAGE ;
-- prompt
--
--
-- prompt + use the following query to generate the script for INDEX MONITORING:
-- prompt
-- prompt + select
-- prompt +  '    ALTER INDEX '||owner||'.'||index_name||' MONITORING USAGE;' 
-- prompt + from dba_indexes i
-- prompt +  where table_name in (select table_name from ( 
-- prompt +                       select owner, table_name, count(*)
-- prompt +                       from dba_indexes
-- prompt +                       where owner not in ('SYS','SYSTEM')
-- prompt +                       group by owner, table_name
-- prompt +                       having count(*) > 6));
-- prompt
-- prompt + Check the monitoring status using "V$OBJECT_USAGE" view (column "USED"):
-- prompt
-- prompt + select * from V$OBJECT_USAGE;
-- prompt
-- prompt + INDEX_NAME       TABLE_NAME      MON USED START_MONITORING    END_MONITORING
-- prompt + *************** *************    *** **** *****************   **************
-- prompt + ....
-- prompt
-- prompt + Run the application for a sufficient (~1 week) time to use the table and
-- prompt + associated indexes. Work with application team to remove unused indexes.
-- prompt
-- prompt + Disable the monitoring By:
-- prompt +     ALTER INDEX <INDEXNAME> NOMINTORING USAGE;
-- prompt

prompt
prompt *** SEQUENCE CACHE STATS (listing only non-cached)
prompt
col sequence_owner format a15       heading "Sequence|Owner"
col sequence_name  format a30       heading "Sequence Name"
col cache_size     format 999999999 heading "Sequnce|cache|size"
col comments       format a21       heading "Comments"
select
 sequence_owner,
 sequence_name,
 cache_size,
 (case when cache_size < 20
       then '- Set cache to min 20'
       else 'Ok'
  end)                   comments
from
 dba_sequences
where
 sequence_owner not in ('SYS','SYSTEM','DBSNMP')
 and cache_size = 0
order by 1,2,3;

prompt
prompt On application with high amount of inserts set cache size to min. 40
prompt
prompt
prompt
prompt *** ROLLBACK / UNDO segments statistics
prompt
set pagesize 0
col msg format A50
prompt init parameters:
prompt
select
 name||'='||value
from v$parameter
where name like 'undo%'
   or name like 'rollback%';
prompt
select (case when value = 'MANUAL' then
                               '+ '||value ||' UNDO management is used.'||chr(10)||
                 '+ Recommendation: Enable Automatic UNDO management.'  ||chr(10)||
                 '+     set undo_management=AUTO'                       ||chr(10)||
                 '+     undo_tablespace=<tablespace_name>'              ||chr(10)||
                 '+     undo_retention=1800'                            ||chr(10)||
                 '+ Additionally the following parameter can be set to suppress '||
                 'errors while executing'||chr(10)||
                 '+ manual undo management mode operations such as '||
                 '.SET TRANSACTION USE ROLLBACK SEGMENT ...'
        else '+ Automatic UNDO management is used.'
  end) msg
from v$parameter
where name ='undo_management';
set pagesize 50
column "Rollback Segment"       format a16
column "Size (Kb)"              format 9,999,999
column "Gets"                   format 999,999,990
column "Waits"                  format 9,999,990
column "% Waits"                format 90.00
column "# Shrinks"              format 999,990
column "# Extends"              format 999,990

select rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)", rs.Gets "Gets",
       rs.waits "Waits", (rs.Waits/rs.Gets)*100 "% Waits",
       rs.Shrinks "# Shrinks", rs.Extends "# Extends"
from   sys.v_$RollName rn, sys.v_$RollStat rs
where  rn.usn = rs.usn;
prompt
prompt GETS - # of gets on the rollback segment header
prompt WAITS - # of waits for the rollback segment header 
ptompt
set pagesize 0
select 'The average of waits/gets is '|| 
   round((sum(waits) / sum(gets)) * 100,2)||'%' 
From    v$rollstat;
set pagesize 50

prompt
PROMPT
prompt *** ROLLBACK / UNDO SEGMENTS CONTENTION:
prompt
COLUMN class    FORMAT A30                 HEADING 'Header Type'
COLUMN count    FORMAT 999,999,999,999     HEADING 'Number|of Waits'
COLUMN con_get  FORMAT 999,999,999,999,999 HEADING 'Logical|Reads'
COLUMN pct      FORMAT 990.99              HEADING 'Percent of|Contention'
SELECT
    a.class,
    count,
    SUM(value) con_get,
    ((count / SUM(value)) * 100) pct
FROM
    v$waitstat a,
    v$sysstat b
WHERE
    name IN ('db block gets', 'consistent gets')
    AND a.class IN (
        'system undo header',
        'system undo block',
        'undo header',
        'undo block')
GROUP BY
    a.class,
    count;

prompt 
prompt Another way to gauge rollback contention is: 
prompt 
 
column xn1 format 9999999 
column xv1 new_value xxv1 noprint 

set head on 

select class, count 
from   v$waitstat 
where  class in ('system undo header', 'system undo block',
                 'undo header',        'undo block'          ); 

set head off 

select 'Total requests = '||sum(count) xn1, sum(count) xv1 
from    v$waitstat; 

select 'Contention for system undo header = '|| 
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' 
from  v$waitstat 
where   class = 'system undo header';
 

select 'Contention for system undo block = '|| 
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' 
from    v$waitstat 
where   class = 'system undo block';
 

select 'Contention for undo header = '|| 
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' 
from    v$waitstat 
where   class = 'undo header';
 

select 'Contention for undo block = '|| 
       (round(count/(&xxv1+0.00000000001),4)) * 100||'%' 
from    v$waitstat 
where   class = 'undo block';
prompt 
prompt + If the percentage for an area is more than 1% or 2%, consider 
prompt + creating more rollback/undo segments.   
prompt 
 
set head on

prompt
prompt
prompt *** UNDO USAGE STATISTICS:
prompt
col BeginT format a6            heading "Begin|Time"                 WORD_WRAPPED
col EndT   format a6            heading "End|Time"                   WORD_WRAPPED
col UDBPS  format 9999          heading "UNDO|Blk|Per|Sec"
col T1     format 9999999999    heading "Total|Undo|Blocks|Used"
col T2     format 9999999999999 heading "Total|Num|Trans|Executed"
col T3     format 999999999     heading "Longest|Query|(in secs)"
col T4     format 999999        heading "Highest|Conc TX|Count"
col T5     format 99999         heading "# of|ORA|01555"
col T6     format 99999         heading "# of|Space|Err"
select TO_CHAR(MIN(Begin_Time),'DDMMYYHH24MISS') BeginT,
    TO_CHAR(MAX(End_Time),'DDMMYYHH24MISS')      EndT,
    ceil(sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60)) UDBPS,
    SUM(Undoblks)        T1,
    SUM(Txncount)        T2,
    MAX(Maxquerylen)     T3,
    MAX(Maxconcurrency)  T4,
    SUM(Ssolderrcnt)     T5,
    SUM(Nospaceerrcnt)   T6
from V$UNDOSTAT;
prompt
prompt
prompt *** UNDO TS size estimation:
prompt
prompt + Undo Space in Bytes = (UR * UDBPS * DB_Block Size) + Overhead
prompt + where
prompt + UR = Undo Retention Time in Seconds
prompt + UDBPS = Undo Blocks used Per Second
prompt + Overhead = One DB block for metadata
prompt
prompt Current UNDO tablespace(s):
prompt
set pagesize 0
select  '+ UNDO tablespace: '||ts||'. Size: '||sz||'MB'
from
(
select
 t.tablespace_name ts, ROUND(sum(bytes)/1024/1024,2) sz
from
 dba_tablespaces t,
 dba_data_files f
where
 f.tablespace_name = t.tablespace_name
 and t.contents= 'UNDO'
group by
 t.tablespace_name
);
prompt
prompt The following size estimation is based on the current DB usage.
prompt

SELECT '+ Estimated UNDO TS size for  undo_retention='||rd||' (current)  is  '||trunc((rd * (udbps * overhead) + overhead)/1024/1024)||'MB'
FROM
(SELECT value AS RD
FROM v$parameter
WHERE name = 'undo_retention'),
(SELECT (sum (undoblks) /
sum ( ((end_time - begin_time) * 86400))) as UDBPS
FROM v$undostat),
(SELECT value AS OVERHEAD
FROM v$parameter
WHERE name = 'db_block_size');
prompt
prompt
prompt + On Oracle 10g use Undo Advisor PL/SQL package to estimate the UNDO size:
prompt +           set serveroutput on;
prompt +           declare utbsize_in_MB number;
prompt +           begin
prompt +             utbsize_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
prompt +             dbms_output.put_line(TO_CHAR(utbsize_in_MB));
prompt +           end;
prompt +           /
prompt
prompt
prompt ROLLBACK SEGMENTS USAGE BY ACTIVE TRANSACTIONS
prompt ...
set pagesize 50
COLUMN name     FORMAT A20 HEADING "Rollback Segment"
COLUMN sid      FORMAT 990 HEADING "Session|ID"
COLUMN username FORMAT A16 HEADING "Oracle|Username"
COLUMN osuser   FORMAT A16 HEADING "O/S|Username"
COLUMN program  FORMAT A16 HEADING "Program"
SELECT
    r.name,
    l.sid,
    s.username,
    s.osuser,
    s.program
FROM
    v$rollname r,
    v$session s,
    v$lock l
WHERE
    r.usn = TRUNC(l.id1 / 65536)
    AND l.sid = s.sid
    AND l.type = 'TX'
ORDER BY
    r.name;

prompt
prompt *** MISSING STATISTICS ?
prompt
set pagesize 0
select 'optimizer_mode='||value from v$parameter where name = 'optimizer_mode';
prompt
select (case when value = 'RULE' then '+ '||value ||
             ' based optimizer is used. Ignore the statistics,'||chr(10)||
             '+ ... unless you are not using SQL hints invoking COST based optimizer.'
        else '+ Cost based optimizer is used. Make sure to keep the statistics '||
             'uptodate.'
        end)
from v$parameter where name = 'optimizer_mode';
set pagesize 50
column schema_name format a19
column clusters_with format 99999999 heading "CLUSTERS|WITH|STATS"
column tables_with format 99999999 heading "TABLES|WITH|STATS"
column indexes_with format 99999999 heading "INDEXES|WITH|STATS"
column clusters_without format 99999999 heading "CLUSTERS|WITHOUT|STATS"
column tables_without format 99999999 heading "TABLES|WITHOUT|STATS"
column indexes_without format 99999999 heading "INDEXES|WITHOUT|STATS"
select
  u.name  schema_name,
  nvl(c.stats, 0)  clusters_with,
  t.stats  tables_with,
  nvl(i.stats, 0)  indexes_with,
  nvl(c.total - c.stats, 0)  clusters_without,
  t.total - t.stats  tables_without,
  nvl(i.total - i.stats, 0)  indexes_without
from
  ( select
      o1.owner#,
      count(c1.avgchn)  stats,
      count(*)  total
    from
      sys.clu$  c1,
      sys.obj$  o1
    where
      c1.obj# = o1.obj#
    group by
      o1.owner#
  )  c,
  ( select
      o2.owner#,
      count(t2.rowcnt)  stats,
      count(*)  total
    from
      sys.tab$  t2,
      sys.obj$  o2
    where
      t2.obj# = o2.obj# and
      t2.tab# is null
    group by
      o2.owner#
  )  t,
  ( select
      o3.owner#,
      count(i3.blevel)  stats,
      count(*)  total
    from
      sys.ind$  i3,
      sys.obj$  o3
    where
      i3.obj# = o3.obj# and
      i3.type# between 1 and 7
    group by
      o3.owner#
  )  i,
  sys.user$  u
where
  u.user# = t.owner# and
  u.user# = c.owner# (+) and
  u.user# = i.owner# (+)
order by
  t.total - t.stats
+ nvl(c.total - c.stats, 0)
+ nvl(i.total - i.stats, 0);
prompt
prompt Schemas detected with stats older than 30 days:
prompt
set pagesize 50
col owner for a20    heading "Schema"
col no    for 999999 heading "# of objects"
select owner, sum(no) no
from
(
 select owner, trunc(last_analyzed) dt, count(*) no
 from dba_tables
 where owner not in ('SYS','SYSTEM')
 group by owner, trunc(last_analyzed)
)
where
 dt < sysdate - 30
group by owner
order by 2 desc, 1 asc;
prompt
prompt + Use only DBMS_STATS package to gather statistics.
prompt
prompt + Ex.:  exec DBMS_STATS.GATHER_schema_stats (ownname => '<schemaname>',
prompt +                                            options => 'GATHER',
prompt +                                            cascade => TRUE,
prompt +                                            estimate_percent => 30,
prompt +                                            degree => 4);
prompt
prompt + ! Avoid using (estimate_percent => dbms_stats.auto_sample_size)
prompt
prompt + Recommendation: Save database statistics on regularly basis (once a month)
prompt + using:
prompt +      dbms_stats.create_stat_table('<owner>','<stats_tab_name>');
prompt +      dbms_stats.export_schema_stats('<owner>','<stats_tab_name>','<owner>');
prompt
prompt
prompt *** TABLESPACE FREE SPACE report:
prompt
COLUMN ts         HEADING "Tablespace Name"   FORMAT A25
COLUMN msg        HEADING "Tablespace Name"   FORMAT A27
COLUMN megs_alloc HEADING "Alloc|(Mbytes)"    FORMAT 999999
COLUMN megs_used  HEADING "Used |(Mbytes)"    FORMAT 999999
COLUMN Pct_Free   HEADING "% Free"            FORMAT 999.9

SELECT  a.tablespace_name ts,
       ROUND(a.bytes_alloc / 1024 / 1024, 2) megs_alloc,
--       ROUND(NVL(b.bytes_free, 0) / 1024 / 1024, 2) megs_free,
       ROUND((a.bytes_alloc - NVL(b.bytes_free, 0)) / 1024 / 1024, 2) megs_used,
       ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2) Pct_Free,
       (case when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<=0
                                                then 'Immediate action required!'
             when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<5 
                                                then 'Critical (<5% free)'
             when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<15
                                                then 'Warning (<15% free)'
             when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)<25
                                                then 'Warning (<25% free)'
             when ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100,2)>60
                                                then 'Waste of space? (>60% free)'
             else 'OK'
             end) msg
FROM  ( SELECT  f.tablespace_name,
               SUM(f.bytes) bytes_alloc,
               SUM(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        FROM DBA_DATA_FILES f
        GROUP BY tablespace_name) a,
      ( SELECT  f.tablespace_name,
               SUM(f.bytes)  bytes_free
        FROM DBA_FREE_SPACE f
        GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
UNION
SELECT h.tablespace_name,
       ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2),
--       ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2),
       ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2),
       ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2),
      (case when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<=0 then 'Immediate action required!'
            when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<5  then 'Critical (<5% free)'
            when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<15 then 'Warning (<15% free)'
            when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)<25 then 'Warning (<25% free)'
            when ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2)>60 then 'Waste of space? (>60% free)'
            else 'OK'
            end) msg
FROM   sys.V_$TEMP_SPACE_HEADER h, sys.V_$TEMP_EXTENT_POOL p
WHERE  p.file_id(+) = h.file_id
AND    p.tablespace_name(+) = h.tablespace_name
GROUP BY h.tablespace_name
ORDER BY 1;
PROMPT
PROMPT *** TABLESPACE FRAGMENTATION REPORT (ts with more than 100 free chunks) (TOP 5)
PROMPT
col tsname  format A31        heading "Tablespace Name"
col totsize format 999999.990 heading "Total|size (Mb)"
col smsize  format 999999.990 heading "Smalles|chunk (Kb)"
col bigsize format 999999.990 heading "Biggest|chunk (Mb)"
col nochunk format 999999     heading "# of free|space chunks"
select * from
(
select
 tablespace_name tsname,
 round(sum(bytes)/1024/1024,2) totsize,
 round(min(bytes)/1024,2) smsize,
 round(max(bytes)/1024/0124,2) bigsize,
 count(*) nochunk
from dba_free_space
where tablespace_name not in (select tablespace_name
                              from dba_tablespaces
                              where contents = 'UNDO')
group by tablespace_name
having count(*)>100
order by 3 desc
)
where rownum < 6;

prompt
prompt + Tablespace fragmentation is not a performance problem, but merely
prompt + a waste of space problem.
prompt
prompt
prompt *** TABLESPACE analysis
prompt
set pagesize 50
col tablespace_name   for a21    heading  "Tablespace Name"
col bs                for a4     heading  "Bl|Size"
col contents          for a4     heading  "Cont|ents"       TRUNC
col log               for a4     heading  "Logg|ing"
col extent_management for a6     heading  "Extent|Mgmt"     TRUNC
col allocation_type   for a7     heading  "Alloc"
col ssm               for A5     heading  "ASSM?"
col status            for A6     heading  "Status"          TRUNC
col fno               for 99999  heading  "Total|Files"
col fsize             for 999.99 heading  "Total|Size|(GB)"
select
 t.tablespace_name,
 block_size/1024||'k' bs,
 t.status,
 contents,
 decode(logging,'LOGGING','Yes','No') log,
 extent_management,
 allocation_type,
 decode(segment_space_management,'AUTO','Yes','No') ssm,
 count(*) fno,
 round(sum(bytes)/1024/1024/1024,2) fsize
from
 dba_tablespaces t,
 dba_data_files d
where t.tablespace_name = d.tablespace_name 
group by
 t.tablespace_name,
 block_size/1024||'k',
 t.status,
 contents,
 decode(logging,'LOGGING','Yes','No'),
 extent_management,
 allocation_type,
 decode(segment_space_management,'AUTO','Yes','No')
order by
 contents,
 tablespace_name;
prompt
prompt + Localy Manged (9i and 10g) tablespaces and ASSM (10g) are recommended
prompt
prompt
set pagesize 0
select (case when count(*)=0 then '+ Temporary Tablespace not defined!'
        else '+ Temporary tablespace: '||max(tablespace_name)
        end)
from dba_tablespaces
where contents = 'TEMPORARY'
group by tablespace_name;
select (case when count(*)=0 then '+ No temp files defined! TEMP files have to be'||
             ' used for TEMP tablespaces.'
        else '+ Tablespace '||max(tablespace_name)||' has '||count(*)||' temp files.'
  end)
from dba_temp_files
group by tablespace_name;
prompt
prompt *** Datafile status
prompt
set pagesize 50
col message    for a70
col status     for a15   heading "File status"
col enabled    for a15   heading "Enabled mode"
col bs         for a10   heading "Block size"
col dfno       for 99999 heading "# Files"
select status, enabled, block_size/1024||'k' bs, count(*) dfno
from v$datafile
group by  status, enabled, block_size/1024||'k'
order by block_size/1024||'k', status, enabled;
prompt
prompt *** Tempfile status
prompt
select status, enabled, block_size/1024||'k' bs, count(*) dfno
from v$tempfile
group by  status, enabled, block_size/1024||'k'
order by block_size/1024||'k', status, enabled;
set pagesize 0
PROMPT
PROMPT *** I/O DISTRIBUTION BY TABLESPACE (TOP 5)
PROMPT
VARIABLE n_rd NUMBER
VARIABLE n_wr NUMBER
SET TERMOUT OFF
BEGIN
    SELECT
        SUM(phyblkrd),
        SUM(phyblkwrt)
    INTO
        :n_rd,
        :n_wr
    FROM
        v$filestat
    ;
END;
/

DEFINE nameln = 30
DEFINE devsep = "GREATEST(0, -
    INSTR(df.name, '/', -1), -
    INSTR(df.name, ':', -1), -
    INSTR(df.name, '\', -1))"
DEFINE device = "SUBSTR(df.name, 1, &&devsep)"
DEFINE filenm = "SUBSTR(df.name, &&devsep + 1)"
SET TERMOUT ON
set echo off
set pagesize 100
COLUMN name     FORMAT A22         HEADING "Name"
COLUMN tsize FORMAT 9,990     HEADING "GBytes"
COLUMN reads    FORMAT 999999,990  HEADING "Reads|(1000s)"
COLUMN readp    FORMAT 999         HEADING "Read|   %" JUSTIFY RIGHT
COLUMN writs    FORMAT 999999,990  HEADING "Writes|(1000s)"
COLUMN writp    FORMAT 990         HEADING "Wri-|te %" JUSTIFY RIGHT
COLUMN total    FORMAT 999999,990  HEADING "Total|(1000s)"
COLUMN totlp    FORMAT 990         HEADING "Tot-|al %" JUSTIFY RIGHT
SELECT * FROM
(
SELECT
    MAX(ts.name) name,
    SUM(df.bytes / (1024 * 1024 * 1024)) tsize,
    SUM(st.phyblkrd) / 1000 reads,
    SUM(st.phyblkrd) / (:n_rd) * 100 readp,
    SUM(st.phyblkwrt) / 1000 writs,
    SUM(st.phyblkwrt) / (:n_wr) * 100 writp,
    SUM(st.phyblkrd + st.phyblkwrt) / 1000 total,
    SUM(st.phyblkrd + st.phyblkwrt) / (:n_rd + :n_wr) * 100 totlp
FROM
    v$datafile df,
    v$filestat st,
    sys.file$ sf,
    sys.ts$ ts
WHERE
    df.file# = st.file#
    AND df.file# = sf.file#
    AND sf.ts# = ts.ts#
GROUP BY
    ts.name
ORDER BY 8 DESC
)
WHERE ROWNUM < 6;

PROMPT
PROMPT *** I/O DISTRIBUTION BY DEVICE (TOP 5)
PROMPT
SELECT * FROM
(
SELECT
    MAX(&&device) name,
    SUM(df.bytes / (1024 * 1024 * 1024)) tsize,
    SUM(st.phyblkrd) / 1000 reads,
    SUM(st.phyblkrd) / (:n_rd) * 100 readp,
    SUM(st.phyblkwrt) / 1000 writs,
    SUM(st.phyblkwrt) / (:n_wr) * 100 writp,
    SUM(st.phyblkrd + st.phyblkwrt) / 1000 total,
    SUM(st.phyblkrd + st.phyblkwrt) / (:n_rd + :n_wr) * 100 totlp
FROM
    v$datafile df,
    v$filestat st,
    sys.file$ sf,
    sys.ts$ ts
WHERE
    df.file# = st.file#
    AND df.file# = sf.file#
    AND sf.ts# = ts.ts#
GROUP BY
    &&device
ORDER BY 8 DESC
)
where rownum <6;

PROMPT
PROMPT *** I/O DISTRIBUTION BY FILE (TOP 5)
PROMPT

SELECT * FROM
(
SELECT
    MAX(df.name) name,
    SUM(df.bytes / (1024 * 1024 * 1024)) tsize,
    SUM(st.phyblkrd) / 1000 reads,
    SUM(st.phyblkrd) / (:n_rd) * 100 readp,
    SUM(st.phyblkwrt) / 1000 writs,
    SUM(st.phyblkwrt) / (:n_wr) * 100 writp,
    SUM(st.phyblkrd + st.phyblkwrt) / 1000 total,
    SUM(st.phyblkrd + st.phyblkwrt) / (:n_rd + :n_wr) * 100 totlp
FROM
    v$datafile df,
    v$filestat st,
    sys.file$ sf,
    sys.ts$ ts
WHERE
    df.file# = st.file#
    AND df.file# = sf.file#
    AND sf.ts# = ts.ts#
GROUP BY
    df.name
ORDER BY 8 DESC
)
where rownum <6;

set pagesize 0
col namevalue           format a79  heading "Name" WORD_WRAPPED
set pagesize 0
col namevalue           format a79  heading "Name" WORD_WRAPPED
prompt
prompt *** RMAN
prompt

select
 'CONFIGURE '||name ||' '|| value namevalue
from
 v$rman_configuration
order by
 conf#;
set pagesize 50
prompt
prompt *** DATABASE ACCOUNTS report:
prompt
col USERNAME           format a15   heading "Username"           WORD_WRAPPED
col STATUS             format a10   heading "Account|Status"     WORD_WRAPPED
col PROFILE            format a27   heading "Profile"            WORD_WRAPPED
col CREATED            format a8    heading "Created|Time" 
col PWD_CHANGE_TIME    format a8    heading "Pwd|Last|Time|Chg" 
col DAYS_BEFORE_EXPIRE format 99999 heading "Days|Before|Expire"
select
  u.name                                          AS "USERNAME",
  decode(d.account_status,
          'EXPIRED(GRACE)','EXP(GRACE)',
          d.account_status)                       AS "STATUS",
  d.profile                                       AS "PROFILE",
  to_char(u.ctime,'DD/MM/YY')              AS "CREATED",
  to_char(u.ptime,'DD/MM/YY')            AS "PWD_CHANGE_TIME",
  (case when trunc(u.ptime + days - sysdate) < 0
        then 0
        else trunc(u.ptime + days - sysdate)
   end)                                           AS "DAYS_BEFORE_EXPIRE"
from
(select name,ptime,ctime from sys.user$) u,
(select username,profile,account_status from sys.dba_users) d,
(select profile, decode(limit,'UNLIMITED',99999,'DEFAULT',99999,limit) "DAYS"
from sys.dba_profiles where resource_name = 'PASSWORD_LIFE_TIME') p
where u.name=d.username
--  and u.name not in (select username from dba_users where profile like '%GDBA%')
  and d.profile = p.profile
order by 6 desc,2,3,1;
prompt
prompt *** SECURITY (roles and privileges) checking:
prompt
set pagesize 50
set recsep off
col grantee      for a20 heading "Grantee"
col granted_role for a20 heading "Granted Role"
col privilege    for a20 heading "Granted Privilege"
col admin_option for a10 heading "Admin|Option?"
col default_role for a10 heading "Default|Role?"
select
 grantee,
 granted_role,
 admin_option,
 default_role
from
 dba_role_privs
where
 granted_role in ('DBA','EXP_FULL_DATABASE','IMP_FULL_DATABASE','OEM_MONITOR')
and
 grantee not in ('SYS','SYSTEM','DBA')
order by 1;

select
  grantee,
  privilege,
  admin_option
from
  dba_sys_privs
where privilege in
  ('ALTER DATABASE',
  'ALTER PROFILE',
  'ALTER SYSTEM',
  'ALTER TABLESPACE',
  'BECOME USER',
  'BACKUP ANY TABLE',
  'ALTER USER',
  'CREATE ANY DIRECTORY',
  'CREATE DATABASE',
  'CREATE ROLLBACK SEGMENT',
  'CREATE TABLESPACE',
  'DROP ANY DIRECTORY',
  'DROP DATABASE',
  'DROP PROFILE',
  'DROP ROLLBACK SEGMENT',
  'DROP TABLESPACE',
  'DROP USER',
  'EXECUTE ANY PROCEDURE',
  'GRANT ANY OBJECT PRIVILEGE',
  'GRANT ANY PRIVILEGE',
  'GRANT ANY ROLE',
  'INSERT ANY TABLE',
  'MANAGE TABLESPACE',
  'UNDER ANY TABLE',
  'UNDER ANY TYPE',
  'UNDER ANY VIEW',
  'UPDATE ANY TABLE',
  'ALTER ANY PROCEDURE',
  'ALTER ANY ROLE',
  'ALTER ANY TABLE',
  'ALTER ANY TRIGGER',
  'ALTER ANY TYPE',
  'DELETE ANY TABLE',
  'DROP ANY PROCEDURE',
  'DROP ANY ROLE',
  'DROP ANY TABLE',
  'FORCE ANY TRANSACTION',
  'FORCE TRANSACTION',
  'ALTER ROLLBACK SEGMENT',
  'CREATE USER',
  'CREATE PUBLIC DATABASE LINK',
  'RESTRICTED SESSION',
  'SELECT ANY TABLE')
and grantee not in
 ('SYS',
  'SYSTEM',
  'DBA',
  'IMP_FULL_DATABASE',
  'EXP_FULL_DATABASE')
order by 1;
prompt
prompt
prompt *** PROFILES PASSWORD AGING SETTINGS:
prompt
set pagesize 100
col profile       format a24 heading "Profile Name"
col resource_name format a24 heading "Resource Name"
col limit         format a18 heading "Limit"
col com           format a10 heading "Comment" 
BREAK ON profile SKIP 1
SELECT
    profile,
    resource_name,
 limit,
    (case when limit = 'UNLIMITED' or limit = 'DEFAULT'
       then 'Unsecured?'
  end) com  
FROM
    sys.dba_profiles
WHERE
    resource_name In ('FAILED_LOGIN_ATTEMPTS',
               'PASSWORD_LIFE_TIME',
        'PASSWORD_REUSE_TIME',
        'PASSWORD_REUSE_MAX',
        'PASSWORD_VERIFY_FUNCTION',
        'PASSWORD_LOCK_TIME',
        'PASSWORD_GRACE_TIME')
ORDER BY
    profile,
    resource_name;
-- prompt
-- prompt + Standard GDBA password aging profiles:
-- prompt +++++++++++++++++++++++++++++++++++++++++++++
-- prompt + create PROFILE GDBA_USER LIMIT
-- prompt + PASSWORD_LIFE_TIME 90
-- prompt + PASSWORD_GRACE_TIME 10
-- prompt + PASSWORD_REUSE_TIME 1800
-- prompt + PASSWORD_REUSE_MAX UNLIMITED
-- prompt + FAILED_LOGIN_ATTEMPTS 5
-- prompt + PASSWORD_LOCK_TIME 1/24
-- prompt + PASSWORD_VERIFY_FUNCTION verify_function;
-- prompt +++++++++++++++++++++++++++++++++++++++++++++
-- prompt + create PROFILE HP_GDBA_ADMIN LIMIT
-- prompt + PASSWORD_LIFE_TIME 45
-- prompt + PASSWORD_GRACE_TIME 10
-- prompt + PASSWORD_REUSE_TIME 1800
-- prompt + PASSWORD_REUSE_MAX UNLIMITED
-- prompt + FAILED_LOGIN_ATTEMPTS 5
-- prompt + PASSWORD_LOCK_TIME 1/24
-- prompt + PASSWORD_VERIFY_FUNCTION verify_function12;
-- prompt +++++++++++++++++++++++++++++++++++++++++++++
-- prompt + Create  PROFILE HP_APP_SERVICE LIMIT
-- prompt + PASSWORD_LIFE_TIME 365
-- prompt + PASSWORD_GRACE_TIME 10
-- prompt + PASSWORD_REUSE_TIME 1800
-- prompt + PASSWORD_REUSE_MAX UNLIMITED
-- prompt + FAILED_LOGIN_ATTEMPTS 5
-- prompt + PASSWORD_LOCK_TIME 1/24
-- prompt + PASSWORD_VERIFY_FUNCTION verify_function12;
-- prompt +++++++++++++++++++++++++++++++++++++++++++++
-- prompt
-- prompt + More info in "Database Security - User Account Setup.doc" on GDBA share
-- prompt

prompt
prompt *** NLS settings
prompt
COLUMN "Parameter"        FORMAT A25
COLUMN "Database Value"   FORMAT A20
COLUMN "Instance Value"   FORMAT A15
COLUMN "Session Value"    FORMAT A15
SELECT
    d.parameter "Parameter",
    d.value "Database Value",
    i.value "Instance Value",
    s.value "Session Value"
FROM
    nls_database_parameters d,
    nls_instance_parameters i,
    nls_session_parameters  s
WHERE
    d.parameter = i.parameter(+)
    AND d.parameter = s.parameter(+);
prompt
prompt + Make sure you are using the correct NLS_LANG when using RMAN, exp/imp
prompt
set pagesize 0
prompt
prompt *** CURRENT SESSION DETAILS:
prompt
SELECT
    'SID:         ' || s.sid      || CHR(10) ||
    'Serial#:     ' || s.serial#  || CHR(10) ||
    'Process:     ' || s.process  || CHR(10) ||
    'SPID:        ' || p.spid     || CHR(10) ||
    'OS User:     ' || s.osuser   || CHR(10) ||
    'Oracle User: ' || s.username || CHR(10) ||
    'Machine:     ' || s.machine  || CHR(10) ||
    'Terminal:    ' || s.terminal || CHR(10) ||
    'Logon Time:  ' || TO_CHAR(s.logon_time, 'DD-MON-RR HH24:MI')
FROM
    v$session s,
    v$process p
WHERE
    audsid = USERENV('SESSIONID')
    AND s.paddr = p.addr
    and lower(substr(s.program,1,7))  = 'sqlplus';
prompt
select 'Timestamp: '||to_char(sysdate,'Mon dd yyyy hh24:mi:ss') from dual;
prompt
prompt
prompt *** END of healthcheck report
spool off
select
 '+ Script Outp saved to: healthcheck_&&dbname&timestamp&&suffix ' "Output script"
from dual;
prompt
prompt + If you run this script on your Windows PC the output will be saved into
prompt + %ORACLE_HOME%\bin
prompt