Subject:  bde_chk_cbo.sql - Current, required and recommended Apps 11i init.ora params 
Creation Date:  31-JAN-2002 

SET term off ver off trims on serveroutput on size 1000000 feed off;
/*=============================================================================

bde_chk_cbo.sql - Current, required and recommended Apps 11i init.ora params

    
 Overview
 --------

    bde_chk_cbo.sql generates a spool file with all Apps 11i relevant init.ora
    parameters according to RDBMS release installed (8.1.6, 8.1.7 or 9.0.1).
    For each relevant init.ora parameter, it displays the current value, the
    required value (if any), the recommended value (if any), and the default 
    value (if any).
    It also displays any other init.ora parameter with non-default value, in
    other words, those parameters set manually on the init.ora file.
    bde_chk_cbo.sql is an extension to the seeded script AFCHKCBO.sql.


 Instructions
 ------------

 1. Copy this whole Note into a text file.  Name it bde_chk_cbo.sql when
    saving your text file.  Be sure filename is bde_chk_cbo.sql.
 2. Execute bde_chk_cbo.sql from SQL*Plus connecting as APPS:
    # sqlplus apps/apps@vis11i
    SQL> START bde_chk_cbo.sql;
 3. Review output file (spool): BDE_CHK_CBO.TXT
    The spool file gets created on same directory from which bde_chk_cbo.sql 
    is executed.  On NT, files may get created under $ORACLE_HOME/bin.
 4. Provide to Oracle Support the generated spool file BDE_CHK_CBO.TXT.


 Program Notes
 -------------

 1. Always download latest version from Metalink.  Note:174605.1
 2. If you need to ftp spool files from UNIX to any other server, use ASCII.
 3. For other SQL Tuning scripts, search on Metalink using keyword coescripts.
 4. A practical guide in Troubleshooting Oracle ERP Applications Performance
    Issues can be found on Metalink under Note:169935.1


 Parameters
 ----------

    None


 Caution
 -------

    The sample program in this article is provided for educational purposes
    only and is NOT supported by Oracle Support Services.  It has been tested 
    internally, however, and works as documented.  We do not guarantee that it
    will work for you, so be sure to test it in your environment before 
    relying on it.


 Portal
 ------

    Abstract: bde_chk_cbo.sql - Current, required and recommended init.ora parm
    Author: Carlos Sierra
    Date: 31-JAN-02
    Description: Current, required and recommended Apps 11i init.ora parameters
    EMail: carlos.sierra@oracle.com
    Internal_Only: N
    Keywords: sqltuning coescripts appsperf appssqltuning cbo init.ora coe bde
    Metalink_Note: 174605.1
    New_Win: Y
    Product: SQL*Plus script
    Version: 8.1-9.0 2002/01/31
    Download: bde_chk_cbo.zip

 =========================================================================== */

VARIABLE dbname varchar2(9);
VARIABLE numcpus number;
VARIABLE iname varchar2(16);
VARIABLE hname varchar2(64);
VARIABLE dbversion varchar2(17);
VARIABLE istartup varchar2(20);

SET term on;
PROMPT Creating COE_PARAMETERS...
SET term off;

DROP   TABLE COE_PARAMETERS_XYZ;
CREATE TABLE COE_PARAMETERS_XYZ
    (NAME VARCHAR2(64),VALUE VARCHAR2(512),REQUIRED VARCHAR2(512), 
     RECOMMENDED VARCHAR2(512),DEFAULTED VARCHAR2(512)) NOLOGGING CACHE;

BEGIN
    select substr(name,1,9)
      into :dbname
      from V$DATABASE
     where rownum = 1;
    select to_number(value)
      into :numcpus
      from V$PARAMETER
     where name = 'cpu_count'
       and rownum = 1;
    select substr(instance_name,1,16), 
           substr(host_name,1,64),
           to_char(startup_time,'DD-MON-YYYY HH24:MI:SS'),
           substr(version,1,17)
      into :iname, :hname, :istartup, :dbversion
      from V$INSTANCE
     where rownum = 1;
    IF SUBSTR(:dbversion,1,5) IN ('8.1.6','8.1.7','9.0.1') THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_fast_full_scan_enabled',null,'FALSE',null,'TRUE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_like_with_bind_as_equality',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_shared_pool_reserved_min_alloc',null,null,'4100-5000','5000');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_table_scan_cost_plus_one',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_trace_files_public',null,null,'TRUE','FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('aq_tm_processes',null,'1',null,'0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('cursor_sharing',null,null,'EXACT','EXACT');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('db_block_buffers',null,'5000 or more',null,'48 MB');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('db_block_size',null,null,'8192','2048');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('db_file_multiblock_read_count',null,'8',null,'8');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('db_files',null,'500 or more',null,'200');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('dml_locks',null,'500 or more',null,'4 x transactions');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('enqueue_resources',null,'5000 or more',null,'derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('hash_area_size',null,null,'512000-4096000','2 x sort_area_size');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('java_pool_size',null,'50000000 or more',null,'20000');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('job_queue_processes',null,'5 or more',null,'0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('log_buffer',null,'1048576 or more',null,'524288');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('log_checkpoint_interval',null,null,'100000 or more','os dependent');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('log_checkpoint_timeout',null,null,'1200','900');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('max_enabled_roles',null,'40 or more',null,'20');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('nls_date_format',null,'DD-MON-RR',null,'derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('nls_language',null,null,'AMERICAN','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('nls_numeric_characters',null,null,'.,','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('nls_sort',null,null,'BINARY','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('nls_territory',null,null,'AMERICA','os dependent');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('open_cursors',null,'500 or more',null,'50');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_index_caching',null,'0',null,'0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_index_cost_adj',null,'100',null,'100');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_mode',null,'CHOOSE',null,'CHOOSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_percent_parallel',null,'0',null,'0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('parallel_max_servers',null,null,'2 x cpu_count','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('parallel_min_percent',null,null,'0','0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('parallel_min_servers',null,null,'0','0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('parallel_threads_per_cpu',null,null,'2','2');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('processes',null,'75 or more','max active users','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('query_rewrite_enabled',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('row_locking',null,'ALWAYS',null,'ALWAYS');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('sessions',null,'150 or more','2 x processes','derived');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('shared_pool_reserved_size',null,null,'10% shared_pool',
                                                '5% shared_pool');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('shared_pool_size',null,null,'300000000','16 or 64 MB');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('sort_area_size',null,null,'256000-2048000','65536');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('sql_trace',null,null,'FALSE','FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('timed_statistics',null,null,'TRUE','FALSE');
    END IF;
    IF SUBSTR(:dbversion,1,5) IN ('8.1.6','8.1.7') THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_complex_view_merging',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_optimizer_mode_force',null,'TRUE',null,'TRUE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_optimizer_undo_changes',null,'FALSE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_ordered_nested_loop',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_push_join_predicate',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_push_join_union_view',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_sort_elimination_cost_ratio',null,'5',null,'0');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('always_anti_join',null,'NESTED_LOOPS',null,'NESTED_LOOPS');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('always_semi_join',null,'NESTED_LOOPS',null,'NESTED_LOOPS');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('max_dump_file_size',null,null,'UNLIMITED','5 MB');
    END IF;
    IF SUBSTR(:dbversion,1,5) IN ('8.1.7','9.0.1') THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_sqlexec_progression_cost',null,'0',null,'1000');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_system_trig_enabled',null,'TRUE',null,'TRUE');
    END IF;
    IF SUBSTR(:dbversion,1,5) = '8.1.6' THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_or_expand_nvl_predicate',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_use_column_stats_for_function',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('compatible',null,'8.1.6',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_features_enable',null,'8.1.6',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_max_permutations',null,'79000',null,'80000');
    END IF;
    IF SUBSTR(:dbversion,1,5) = '8.1.7' THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_or_expand_nvl_predicate',null,'TRUE',null,'TRUE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_use_column_stats_for_function',null,'TRUE',null,'TRUE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('compatible',null,'8.1.7',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_features_enable',null,'8.1.7',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('_new_initial_join_orders',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_max_permutations',null,'2000',null,'80000');
    END IF;
    IF SUBSTR(:dbversion,1,5) = '9.0.1' THEN
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('compatible',null,'9.0.1',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_features_enable',null,'9.0.1',null,'none');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('o7_dictionary_accessibility',null,'TRUE',null,'FALSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('always_anti_join',null,'NESTED_LOOPS',null,'CHOOSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('always_semi_join',null,'NESTED_LOOPS',null,'CHOOSE');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('max_dump_file_size',null,null,'UNLIMITED','UNLIMITED');
       INSERT INTO COE_PARAMETERS_XYZ VALUES
        ('optimizer_max_permutations',null,'2000',null,'2000');
    END IF;
END;
/

UPDATE COE_PARAMETERS_XYZ CP
   SET CP.VALUE = (SELECT SUBSTR(VP.VALUE,1,512)
                     FROM V$PARAMETER VP
                    WHERE VP.NAME = CP.NAME);

INSERT INTO COE_PARAMETERS_XYZ 
SELECT SUBSTR(VP.NAME,1,64),SUBSTR(VP.VALUE,1,512),NULL,NULL,NULL
  FROM V$PARAMETER VP
 WHERE VP.ISDEFAULT = 'FALSE'
   AND NOT EXISTS (SELECT NULL FROM COE_PARAMETERS_XYZ CP
                    WHERE CP.NAME = VP.NAME);

COLUMN today_date FORMAT a20 HEADING 'Today';
COLUMN dbname FORMAT a9 HEADING 'db|name';
COLUMN numcpus FORMAT 9999 HEADING 'CPUs';
COLUMN hiname FORMAT a24 HEADING 'host_instance|name';
COLUMN dbversion FORMAT a17 HEADING 'db|version';
COLUMN istartup FORMAT a20 HEADING 'instance startup|date and time';
COLUMN pname FORMAT a33 HEADING 'Parameter Name';
COLUMN pvalue1 FORMAT a20 HEADING 'Current|Value';
COLUMN pvalue2 FORMAT a85 HEADING 'Parameter Value';
COLUMN prequired FORMAT a20 HEADING 'Required|Value';
COLUMN precommended FORMAT a20 HEADING 'Recommended|Value';
COLUMN pdefaulted FORMAT a20 HEADING 'Default|Value';
COLUMN DUMMY1 FORMAT A30 NOPRINT;

SPOOL bde_chk_cbo.txt;
SET lin 255 pages 255 term on;
PROMPT bde_chk_cbo.sql 8.1-9.0 2002/01/31
PROMPT
PROMPT Current, required and recommended Apps 11i init.ora parameters
PROMPT ==============================================================
PROMPT

SELECT 
    to_char(sysdate,'DD-MON-YYYY HH24:MI:SS')     TODAY_DATE,
    substr(upper(:hname||'_'||:iname),1,24)       HINAME,
    :numcpus                                      NUMCPUS,
    :dbname                                       DBNAME,
    :dbversion                                    DBVERSION,
    :istartup                                     ISTARTUP
FROM 
    SYS.DUAL;

PROMPT
PROMPT Apps 11i - Required and Recommended
PROMPT ===================================
BREAK ON DUMMY1 SKIP 1;
SELECT 
    DECODE(REQUIRED,NULL,'2','1')                DUMMY1,
    NAME                                         PNAME,
    DECODE(UPPER(VALUE),NULL,'...',UPPER(VALUE)) PVALUE1,
    DECODE(REQUIRED,NULL,'...',REQUIRED)         PREQUIRED,
    DECODE(RECOMMENDED,NULL,'...',RECOMMENDED)   PRECOMMENDED,
    DECODE(DEFAULTED,NULL,'none',DEFAULTED)      PDEFAULTED
FROM
    COE_PARAMETERS_XYZ
WHERE
    REQUIRED    IS NOT NULL
OR  RECOMMENDED IS NOT NULL
ORDER BY
    DECODE(REQUIRED,NULL,'2','1'),
    NAME;

PROMPT
PROMPT Other non-default INIT.ORA parameters
PROMPT =====================================
SELECT 
    NAME  PNAME,
    VALUE PVALUE2
FROM
    COE_PARAMETERS_XYZ
WHERE
    REQUIRED    IS NULL
AND RECOMMENDED IS NULL
ORDER BY
    NAME;

SPOOL OFF;
SET ver on trims off pages 24 lin 80 serveroutput off feed on;