SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINES 132

REM
REM || Title : show_init_anal.sql
REM ||
REM || Purpose : This scripts Check Instances initialisation parameters and suggest
REM || modification if needed
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

spool show_init_anal.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

SET PAGES 80
SET FEEDBACK ON