Doc

      Name:   initparm.sql

      Author: Mark Gurry

      This script checks various key INIT.ORA parameters and advises on their appropriateness.


#


set linesize 100
set heading off
set feedback off
set verify off
set echo off

spool initparm.lis

select 'Archiving is not turned on for the '||
                 ' database! This means that recovery is only ' nl,
       'possible up to the last cold backup or export. This is not good practice for a ' nl,
       'production database. Check if this is acceptable.' nl
  from v$parameter
 where name = 'log_archive_start'
   and value = 'FALSE'
/

select 'TIMES_STATISTICS has been set to TR'||
                 'UE. This is useful for performance monitoring but will slow ' nl,
       'performance by between 5 and 10%. If there are serious performance problems, ' nl,
       'consider turning it off.                           ' nl
  from v$parameter
 where name = 'timed_statistics'
   and value = 'TRUE' 
/

select 'The Buffer cache (DB_BLOCK_BUFFERS * DB_BLOCK_SIZE ) is set too low for a Production ' nl,
       'database. It is set to '||to_char(bytes) ||'. It should be at least 16 Megabytes for a serious' nl,
       'production system. If you have sufficient free memory, consider increasing it '
  from v$sgastat  
 where name = 'db_block_buffers' 
   and bytes < 16000000
/

select 'Your DB_BLOCK_SIZE is below the minimum recommended for an Oracle Database. The minimum ' nl,
       'recommended is 4k. Unfortunately to increase the parameter, you need to re-build the database.' nl,
       'If a database re-organisation or re-build is planned, create the dataase with DB_BLOCK_SIZE' nl,
       'set to 4 K of 8K.  '                               nl
  from v$parameter
 where name = 'db_block_size'           
   and value < '4096'     
/

select 'Your SORT_AREA_RETAINED_SIZE and SORT_AREA_SIZE are set to the same value('||a.value||'). ' nl,
       'Unless you are running a database which is totally dedicated to large batch jobs, it is best ' nl,
       'to allocate the extra memory only to the people that need it. Typical settings are 64K for' nl,
       'SORT_AREA_RETAINED_SIZE and 2Meg for SORT_AREA_SIZE '
  from v$parameter a , v$parameter b
 where a.name  = 'sort_area_size'
   and b.name  = 'sort_area_retained_size' 
   and b.value = a.value
/

select 'The SEQUENCE_CACHE_ENTRIES is undersized. It should ideally be sized to fit all of the  ' nl,
       'cached entries required for all sequences. The parameter is set to '||b.value||'.' nl,
       'It should be set to '||sum (a.cache_size) ||'.'
  from sys.dba_sequences a, v$parameter b
 where b.name='sequence_cache_entries'
 group by b.value
having sum (a.cache_size) < b.value
/


select 'Your LOG_BUFFER may be able to be enlarged to improve performance. It is currently set ' nl,
       'to '||b.value||'. There have been a number of redo log space request ('||a.value||') waits.' nl,
       'Consider enlarging the LOG_BUFFER to a value such as '||b.value * 1.5||'.'
from   v$parameter b, v$sysstat a
where  b.name = 'log_buffer'
  and  a.name = 'redo log space requests'
  and  a.value > 50 
  and  b.value < (select 1000000 from dual)
/

select 'Warning: Enqueue Timeouts are '||value||'. They should be zero if the INIT.ora parameter is ' line1,
       'high enough. Try increasing INIT.ora parameter ENQUEUE_RESOURCES and see if the Timeouts reduces.'
  from v$sysstat
 where name  = 'enqueue timeouts'
   and value > 0
/

spool off