APPLIES TO:

PURPOSE

Refer to:
- Multi-Lingual Support (MLS) in the current MOS Environment (Document 1480131.1)

Lists EBS initialization parameters side by side to those from official notes 216205.1 and 396009.1

REQUIREMENTS

• Use only on EBS applications (a.k.a. Oracle Applications or APPS)
• Connect as APPS

CONFIGURING

• Download from this note and execute connected as APPS
• This bde_chk_cbo.sql script is also included within SQLT (SQLTXPLAIN) when SQLT is used on an APPS instance.

INSTRUCTIONS

• Connect into sqlplus as APPS
• START bde_chk_cbo.sql

CAUTION

SCRIPT

See attachments.

SAMPLE OUTPUT

See attachments.

REFERENCES

NOTE:396009.1 - Database Initialization Parameters for Oracle E-Business Suite Release 12

附：

SPOOL bde_chk_cbo.log;
SET ECHO ON TERM OFF;
REM
REM $Header: 174605.1 bde_chk_cbo.sql 12.1.04 2013/11/11 csierra $
REM
REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM NAME
REM   bde_chk_cbo.sql
REM
REM DESCRIPTION
REM   Lists EBS initialization parameters side by side to
REM   those from official notes 216205.1 and 396009.1
REM
REM PRE-REQUISITES
REM   1. Only used by EBS (Oracle Applications - APPS).
REM   2. Execute connecting as APPS.
REM
REM PARAMETERS
REM   None.
REM
REM EXAMPLE
REM   #sqlplus apps
REM   SQL> START bde_chk_cbo.sql;
REM
REM UPDATES
REM   dchbane  2013/11/11  Added parameters for 12cR1
REM

SET FEED OFF VER OFF HEA OFF LIN 2000 PAGES 0 TRIMS ON;

VAR v_cpu_count     VARCHAR2(10);
VAR v_database      VARCHAR2(32);
VAR v_host          VARCHAR2(64);
VAR v_instance      VARCHAR2(32);
VAR v_platform      VARCHAR2(80);
VAR v_rdbms_release VARCHAR2(17);
VAR v_rdbms_version VARCHAR2(17);
VAR v_apps_release  VARCHAR2(50);
VAR v_sysdate       VARCHAR2(15);
VAR v_user          VARCHAR2(30);

COL p_cpu_count     NEW_V p_cpu_count     FOR A10;
COL p_database      NEW_V p_database      FOR A32;
COL p_host          NEW_V p_host          FOR A64;
COL p_instance      NEW_V p_instance      FOR A32;
COL p_platform      NEW_V p_platform      FOR A80;
COL p_rdbms_release NEW_V p_rdbms_release FOR A17;
COL p_rdbms_version NEW_V p_rdbms_version FOR A10;
COL p_apps_release  NEW_V p_apps_release  FOR A50;
COL p_sysdate       NEW_V p_sysdate       FOR A15;
COL p_user          NEW_V p_user          FOR A30;

/******************************************************************************/

EXEC :v_cpu_count     := 'Unknown';
EXEC :v_database      := 'Unknown';
EXEC :v_host          := 'Unknown';
EXEC :v_instance      := 'Unknown';
EXEC :v_platform      := 'Unknown';
EXEC :v_rdbms_release := 'Unknown';
EXEC :v_rdbms_version := 'Unknown';
EXEC :v_apps_release  := 'Unknown';
EXEC :v_sysdate       := TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI');
EXEC :v_user          := USER;

BEGIN
    SELECT i.host_name,
           i.version,
           SUBSTR(UPPER(i.instance_name)||'('||TO_CHAR(i.instance_number)||')', 1, 40)
      INTO :v_host, :v_rdbms_release, :v_instance
      FROM v$instance i;
END;
/

-- If you need to execute in preparation for an upgrade, just uncomment one of the 5 commands below:
--EXEC :v_rdbms_release := '8.1.7.X';
--EXEC :v_rdbms_release := '9.2.0.X';
--EXEC :v_rdbms_release := '10.1.X';
--EXEC :v_rdbms_release := '10.2.X';
--EXEC :v_rdbms_release := '11.1.X';
--EXEC :v_rdbms_release := '11.2.X';
--EXEC :v_rdbms_release := '12.1.X';

BEGIN
    :v_rdbms_version := :v_rdbms_release;
    IF :v_rdbms_release LIKE '12.1.%' THEN :v_rdbms_version := '12.1.X'; END IF;
    IF :v_rdbms_release LIKE '11.2.%' THEN :v_rdbms_version := '11.2.X'; END IF;
    IF :v_rdbms_release LIKE '11.1.%' THEN :v_rdbms_version := '11.1.X'; END IF;
    IF :v_rdbms_release LIKE '10.2.%' THEN :v_rdbms_version := '10.2.X'; END IF;
    IF :v_rdbms_release LIKE '10.1.%' THEN :v_rdbms_version := '10.1.X'; END IF;
    IF :v_rdbms_release LIKE '9.2.0.%' THEN :v_rdbms_version := '9.2.0.X'; END IF;
    IF :v_rdbms_release LIKE '8.1.7.%' THEN :v_rdbms_version := '8.1.7.X'; END IF;
END;
/

BEGIN
    SELECT db.name||'('||TO_CHAR(db.dbid)||')'
      INTO :v_database
      FROM v$database db;
END;
/

BEGIN
    SELECT SUBSTR(value, 1, 10)
      INTO :v_cpu_count
      FROM v$parameter
     WHERE name = 'cpu_count';
END;
/

BEGIN
    SELECT SUBSTR(REPLACE(REPLACE(pcv1.product, 'TNS for '), ':' )||pcv2.status, 1, 80)
      INTO :v_platform
      FROM product_component_version pcv1,
           product_component_version pcv2
     WHERE UPPER(pcv1.product) LIKE '%TNS%'
       AND UPPER(pcv2.product) LIKE '%ORACLE%'
       AND ROWNUM = 1;
END;
/

BEGIN
    SELECT release_name
      INTO :v_apps_release
      FROM applsys.fnd_product_groups;
END;
/

SELECT :v_cpu_count     p_cpu_count,
       :v_database      p_database,
       :v_host          p_host,
       :v_instance      p_instance,
       :v_platform      p_platform,
       :v_rdbms_release p_rdbms_release,
       :v_rdbms_version p_rdbms_version,
       :v_sysdate       p_sysdate,
       :v_apps_release  p_apps_release,
       :v_user          p_user
  FROM DUAL;

DROP TABLE chk$cbo$parameter_apps;
CREATE TABLE chk$cbo$parameter_apps (
  release                    VARCHAR2(64) NOT NULL,
  version                    VARCHAR2(32) NOT NULL,
  id                         INTEGER NOT NULL,
  name                       VARCHAR2(128) NOT NULL,
  set_flag                   CHAR(1) NOT NULL,
  mp_flag                    CHAR(1) NOT NULL,
  sz_flag                    CHAR(1) NOT NULL,
  cbo_flag                   CHAR(1) NOT NULL,
  value                      VARCHAR2(512)
);

CREATE OR REPLACE PROCEDURE chk$ebs$parameters (
  p_rdbms_version IN VARCHAR2,
  p_apps_release  IN VARCHAR2 )
IS
  my_sequence INTEGER := 0;

  PROCEDURE ins (
    p_version  IN VARCHAR2,
    p_name     IN VARCHAR2,
    p_set_flag IN VARCHAR2,
    p_mp_flag  IN VARCHAR2,
    p_sz_flag  IN VARCHAR2,
    p_cbo_flag IN VARCHAR2,
    p_value    IN VARCHAR2 )
  IS
    my_count INTEGER;
  BEGIN
    IF p_version <> 'COMMON' AND p_version <> p_rdbms_version THEN
      RETURN;
    END IF;

    SELECT COUNT(*)
      INTO my_count
      FROM chk$cbo$parameter_apps
     WHERE name = p_name;

    my_sequence := my_sequence + 1;

    IF my_count = 0 THEN
      INSERT INTO chk$cbo$parameter_apps VALUES (
        p_apps_release,
        p_version,
        my_sequence,
        p_name,
        p_set_flag,
        p_mp_flag,
        p_sz_flag,
        p_cbo_flag,
        p_value );
    ELSE
      UPDATE chk$cbo$parameter_apps SET
        release  = p_apps_release,
        version  = p_version,
        id       = my_sequence,
        set_flag = p_set_flag,
        mp_flag  = p_mp_flag,
        sz_flag  = p_sz_flag,
        cbo_flag = p_cbo_flag,
        value    = http://www.mamicode.com/p_value>
报表
174605.1 bde_chk_cbo 12.1.04
Identification
Date:
10-DEC-14 16:19
Host:
erp
Platform:
Linux 64bit Production
Database:
PROD(272800234)
Instance:
PROD(1)
RDBMS Release:
11.2.0.3.0(11.2.X)
User:
APPS
APPS Release:
12.1.3
CPU Count:
16
Common database initialization parameters
Parameter
Current Value
Required Value
CBO
MP
SZ
db_name
PROD
prodr12
 
 
 
control_files
/u01/prod/db/apps_st/data/cntrl01.dbf
three copies of control file
 
 
 
control_files
/u01/prod/db/apps_st/data/cntrl02.dbf
three copies of control file
 
 
 
control_files
/u01/prod/db/apps_st/data/cntrl03.dbf
three copies of control file
 
 
 
db_block_size
8192
8192
 
Y
 
_system_trig_enabled
TRUE
TRUE
 
Y
 
o7_dictionary_accessibility
FALSE
FALSE
 
Y
 
nls_territory
AMERICA
AMERICA
 
 
 
nls_date_format
DD-MON-RR
DD-MON-RR
 
Y
 
nls_numeric_characters
.,
".,"
 
 
 
nls_sort
BINARY
BINARY
 
Y
 
nls_comp
BINARY
BINARY
 
Y
 
nls_length_semantics
BYTE
BYTE
 
Y
 
audit_trail
NONE (NOT SET)
TRUE (optional)
 
 
 
max_dump_file_size
20480
20480
 
 
 
_trace_files_public
FALSE
TRUE
 
 
 
processes
1000
200-2500
 
 
Y
sessions
1536
400-5000
 
 
Y
db_files
512
512
 
 
 
dml_locks
10000
10000
 
 
 
cursor_sharing
EXACT
EXACT
Y
Y
 
open_cursors
600
600
 
 
 
session_cached_cursors
500
500
 
 
 
sga_target
1073741824
2-14G
 
 
Y
db_block_checking
FALSE
FALSE
 
 
 
db_block_checksum
TRUE
TRUE
 
 
 
log_checkpoint_timeout
1200
1200
 
 
 
log_checkpoint_interval
100000
100000
 
 
 
log_buffer
10485760
10485760
 
 
 
log_checkpoints_to_alert
TRUE
TRUE
 
 
 
shared_pool_size
419430400
600-3000M
 
 
Y
shared_pool_reserved_size
41943040
60-300M
 
 
Y
cursor_space_for_time
FALSE (NOT SET)
FALSE (default)
 
 
 
utl_file_dir
/u01/prod/db/tech_st/11.2.0/appsutil/outbound/PROD_erp
/ebiz/prodr12/utl_file_dir
 
 
 
utl_file_dir
/u01/prod/temp
/ebiz/prodr12/utl_file_dir
 
 
 
utl_file_dir
/usr/tmp
/ebiz/prodr12/utl_file_dir
 
 
 
utl_file_dir
/usr/tmp
/ebiz/prodr12/utl_file_dir
 
 
 
aq_tm_processes
1
1
 
 
 
job_queue_processes
2
2
 
 
 
log_archive_start
FALSE (NOT SET)
TRUE (optional)
 
 
 
parallel_max_servers
8
8 (up to 2*CPUs)
 
 
 
parallel_min_servers
0
0
 
 
 
_sort_elimination_cost_ratio
5
5
Y
Y
 
_like_with_bind_as_equality
TRUE
TRUE
Y
Y
 
_fast_full_scan_enabled
FALSE
FALSE
Y
Y
 
_b_tree_bitmap_plans
FALSE
FALSE
Y
Y
 
optimizer_secure_view_merging
FALSE
FALSE
Y
Y
 
cluster_database
FALSE (NOT SET)
TRUE (if using RAC)
 
Y
 
instance_groups
(NOT SET)
appsN (N is inst_id if using RAC)
 
 
 
parallel_instance_group
(NOT SET)
appsN (N is inst_id if using RAC)
 
 
 
pga_aggregate_target
1073741824
1-20G
Y
 
Y
workarea_size_policy
AUTO
AUTO
Y
Y
 
olap_page_pool_size
4194304
4194304
 
 
 
CBO: Cost-based Optimizer Parameter.
MP: Mandatory Parameter and Value
SZ: For recommended values according to particular environment size, refer to Notes 216205.1 and 396009.1
Release-specific database initialization parameters for 11.2.X
Parameter
Current Value
Required Value
CBO
MP
SZ
compatible
11.2.0
11.2.0
 
Y
 
diagnostic_dest
/u01/prod/db/tech_st/11.2.0/admin/PROD_erp
?/prod12
 
 
 
undo_management
AUTO
AUTO
 
Y
 
undo_tablespace
APPS_UNDOTS1
APPS_UNDOTS1
 
Y
 
plsql_code_type
INTERPRETED
NATIVE
 
 
 
_optimizer_autostats_job
FALSE
FALSE
 
Y
 
sec_case_sensitive_logon
FALSE
FALSE
 
Y
 
parallel_force_local
FALSE (NOT SET)
TRUE (if using RAC)
Y
Y
 
CBO: Cost-based Optimizer Parameter.
MP: Mandatory Parameter and Value
SZ: For recommended values according to particular environment size, refer to Notes 216205.1 and 396009.1
Removal list for 11.2.X
Parameter
Current Value
CBO
_always_anti_join
(NOT SET)
Y
_always_semi_join
(NOT SET)
Y
_complex_view_merging
(NOT SET)
Y
_index_join_enabled
(NOT SET)
Y
_kks_use_mutex_pin
(NOT SET)
 
_new_initial_join_orders
(NOT SET)
Y
_optimizer_cost_based_transformation
(NOT SET)
Y
_optimizer_cost_model
(NOT SET)
Y
_optimizer_mode_force
(NOT SET)
Y
_optimizer_undo_changes
(NOT SET)
Y
_or_expand_nvl_predicate
(NOT SET)
Y
_ordered_nested_loop
(NOT SET)
Y
_push_join_predicate
(NOT SET)
Y
_push_join_union_view
(NOT SET)
Y
_shared_pool_reserved_min_alloc
(NOT SET)
 
_sortmerge_inequality_join_off
(NOT SET)
Y
_sqlexec_progression_cost
(NOT SET)
Y
_table_scan_cost_plus_one
(NOT SET)
Y
_unnest_subquery
(NOT SET)
Y
_use_column_stats_for_function
(NOT SET)
Y
always_anti_join
(NOT SET)
Y
always_semi_join
(NOT SET)
Y
background_dump_dest
(NOT SET)
 
core_dump_dest
(NOT SET)
 
db_block_buffers
(NOT SET)
 
db_cache_size
(NOT SET)
 
db_file_multiblock_read_count
(NOT SET)
Y
drs_start
(NOT SET)
 
enqueue_resources
(NOT SET)
 
event="10932 trace name context level 32768"
(NOT SET)
 
event="10933 trace name context level 512"
(NOT SET)
 
event="10943 trace name context forever, level 2"
(NOT SET)
 
event="10943 trace name context level 16384"
(NOT SET)
 
event="38004 trace name context forever, level 1"
(NOT SET)
 
hash_area_size
(NOT SET)
Y
java_pool_size
(NOT SET)
 
job_queue_interval
(NOT SET)
 
large_pool_size
(NOT SET)
 
max_enabled_roles
(NOT SET)
 
nls_language
(NOT SET)
 
optimizer_dynamic_sampling
(NOT SET)
Y
optimizer_features_enable
(NOT SET)
Y
optimizer_index_caching
(NOT SET)
Y
optimizer_index_cost_adj
(NOT SET)
Y
optimizer_max_permutations
(NOT SET)
Y
optimizer_mode
(NOT SET)
Y
optimizer_percent_parallel
(NOT SET)
Y
plsql_compiler_flags
(NOT SET)
 
plsql_native_library_dir
(NOT SET)
 
plsql_native_library_subdir_count
(NOT SET)
 
plsql_optimize_level
2
 
query_rewrite_enabled
(NOT SET)
Y
rollback_segments
(NOT SET)
 
row_locking
(NOT SET)
 
sort_area_size
(NOT SET)
Y
sql_trace
(NOT SET)
 
sql_version
(NOT SET)
 
timed_statistics
(NOT SET)
 
undo_retention
(NOT SET)
 
undo_suppress_errors
(NOT SET)
 
user_dump_dest
(NOT SET)
 
CBO: Cost-based Optimizer Parameter.
Additional initialization parameters with non-default values
Parameter
Current Value
audit_sys_operations
TRUE
ifile
/u01/prod/db/tech_st/11.2.0/dbs/PROD_erp_ifile.ora
local_listener
PROD_LOCAL
log_archive_dest_1
LOCATION=/u01/prod/db/apps_st/data/archive
log_archive_format
%t_%s_%r.dbf
os_authent_prefix
 
sql92_security
TRUE

EBS initialization parameters - Healthcheck