Oracle 配置管理服务

概要参数：

*************************************************************************************************************************************************************************

Site Level 配置设置：

SQL>Select * From FND_PROFILE_OPTION_VALUES ----ALL the setting of parameters
SQL>Select * From FND_PROFILE_OPTIONS --------------- The descriptions of the parameters

*************************************************************************************************************************************************************************

InitMISSID.ora参数调整

###########################################################################

# $Header: afinit_db817.ora 115.6 2003/03/13 16:42:35 rsanders noship $

#

# Oracle Applications 11i - init.ora

#

# This file contains a listing of init.ora parameters for 8.1.7

#

# This is a reference file and contains the major initialisation

# parameters used in Oracle Applications.

#

# It is divided into two main sections:

#

# General Parameters

# Optimizer Parameters

#

# General Parameters

#

# Some of the general parameters are mandatory and are noted

# as such. All others provide good initial values but may need

# to be increased depending on the number of users.

#

# Optimizer Parameters

#

# All optimizer parameters are mandatory. Parameters starting

# with a leading underscore are defined as undocumented. No

# undocumented optimizer parameter must be set other than those

# listed in this section.

#

###########################################################################

#########

#

# Database parameters

#

# The database parameters define the name of the database and the names

# of the control files.

#

# The database name is established when the database is built, and for

# most customers matches the instance name. It should not normally be

# necessary to change the database name, except for the purposes of

# database cloning.

#

# There should be at least two control files, preferably three,

# located on different disks. The control files can dynamically grow,

# so allow at least 20M per file for growth.

#

#########

db_name = MISSID

control_files = /data/MISSID/MISSIDdata/cntrl01.dbf,/data/MISSID/MISSIDdata/cntrl02.dbf,/data/MISSID/MISSIDdata/cntrl03.dbf

#########

#

# Database block size

#

# The standard block size for Oracle Applications is 8K. However,

# this can vary, depending on the O/S block and stripe size. For

# Oracle Parallel Server, a 4K block size is recommended.

#

#########

db_block_size = 8192

#########

#

# Compatible

#

# Compatibility should be set to the current release.

#

#########

compatible = 8.1.7

#########

#

# _system_trig_enabled

#

# The _system_trig_enabled should normally be set to TRUE.

# If the _system_trig_enabled parameter is set to FALSE it will

# disable system triggers from being executed.

#

#########

_system_trig_enabled = TRUE

#########

#

# Required parameters.

#

# These parameters are required for Oracle Applications and MUST NOT

# be changed.

#

#########

row_locking = always

#########

# NLS parameters

#

# Some NLS parameter values are marked as being required 11i settings.

# These are the only supported settings for these parameters for

# Applications 11i and must not be modified to other values.

# Other NLS parameters have been given default values.

#########

nls_language = american

nls_territory = america

nls_date_format = DD-MON-RR

nls_numeric_characters = ".,"

nls_sort = binary # Required 11i setting

nls_comp = binary # Required 11i setting

#

#########

#

# Multi-threaded Server (MTS)

#

# Most Oracle Applications customers DO NOT need to use MTS,and the

# default is to leave it disabled.

#

# If MTS is used, it can have a dramatic effect on the SGA, as session

# memory, including sort and cursor areas, is taken from the SGA.

#

# Configuring MTS requires the large pool to be allocated. The

# minimum size for the large pool is 50M.

#

#########

#########

#

# Auditing and Security

#

# Logon auditing is very useful in determining the I/O profile of

# batch ( concurrent manager ) processes. This information will be

# available in FND_CONCURRENT_REQUESTS in a later release of Oracle

# Applications.

#

# The cost of log on auditing is minimal, and the only additional

# requirement is for a housekeeping procedure to periodically

# purge the SYS.AUD$ table. Statement level auditing is not recommended.

#

# Some products require max_enabled_roles to be set. This should be set

# to a minimum of 40, although higher values are quite acceptable.

#

#########

#audit_trail = true # if you want auditing

max_enabled_roles = 100 # Some modules depend on

# this feature.

########

#

# Dump parameters

#

# These specify the destination of the trace and core files, and would

# normally point into the appropriate OFA trace directories.

# The maximum size of a dump file can be changed at the session level,

# and prevents a trace file using an excessive amount of disk space.

#

########

user_dump_dest = /ora/MISSID/MISSIDdb/8.1.7/admin/MISSID_hostname/udump

background_dump_dest = /ora/MISSID/MISSIDdb/8.1.7/admin/MISSID_hostname/bdump

core_dump_dest = /ora/MISSID/MISSIDdb/8.1.7/admin/MISSID_hostname/cdump

max_dump_file_size = 10240 # trace file size

########

#

# Timed statistics

#

# On most platforms, enabling timed statistics has minimal effect on

# performance. There are a handful of exceptions. It can be

# enabled/disabled dynamically at both the system and session level.

#

# This information is used by many options, including SQL_TRACE,

# Oracle Trace,statspack and Oracle Enterprise Manager.

#

########

timed_statistics = true

########

#

# Trace parameters

#

########

# _trace_files_public

#

# As the data server machine should be in a secure environment,

# setting to true enables trace file analysis.

_trace_files_public = TRUE

# Oracle Trace

#

# SQL trace should be disabled at the instance level and enabled

# for specific sessions as needed via the Application or profiles.

#

sql_trace=FALSE

#

########

#

# Fixed SGA

#

# The fixed SGA parameters represent resources that have their size

# fixed on startup. If the maximum size is reached ( e.g. no of

# sessions ), then the resource is unavailable until freed by the

# instance.

#

########

# Processes/sessions

#

# A database process can be associated with one or more database

# sessions. For all technologies other than FORMS, you can assume

# a 1-to-1 mapping between sessions and procesess.

#

# For FORMS processes, there will be one database session per open form,

# with a minimum of two open forms(sessions).

#

# Either explicity set sessions accordingly or just double the

# number of processes.

#

# The other parameters will depend on the specific installation, but the

# values given are not untypical of many Oracle Applications customers.

processes = 600 # Max. no. of users x 2

db_files = 500 # Max. no. of database files

dml_locks = 1000

open_cursors = 1000 # Consumes process memory, unless using MTS.

session_cached_cursors = 200

enqueue_resources = 10000 # Max. no of concurrent database locks.

########

#

# Buffer Cache

#

# The buffer cache requires ( db_block_size x db_block_buffers ) bytes

# within the SGA. Its sizing can have a significant effect on

# performance. Values less than 5000 are unrealistic for most customers,

# and can be increased as memory permits.

#

# It is also possible to divide the buffer cache into pools,

# for objects (data blocks ) that should be kept in the cache and those

# that should be recycled. If the keep buffer pool is configured , it

# will only cache those objects ( tables ) marked for the keep buffer

# pool.

#

########

db_block_buffers = 30000

########

#

# Log Writer

#

# The log writer parameters control the size of the log buffer within

# the SGA and how frequently the redo logs are checkpointed ( all dirty

# buffers written to disk to create a new recovery point ).

#

# The log buffer can be a megabyte or more but given the commit frequency

# in an OLTP environment, little benefit is achieved above 5M. It must be

# a multiple of redo block size, normally 512 bytes.

#

# The checkpoint interval and timeout control the frequency of

# checkpoints.

#

########

log_checkpoint_timeout = 72000 # Checkpoint at least every 20 mins.

log_checkpoint_interval = 100000

log_buffer = 1048576

#log_checkpoints_to_alert = TRUE

########

#

# Rollback segments

#

# Rollback segments can be brought online / taken offline on demand.

# However, the normal rollback configuration , typically OLTP, should be

# included here. The number of rollback segments is dependent on the

# number of concurrent OLTP transactions, but 6 to 12 is a typical

# profile.

#

# If rollback contention is seen in the bstat report, then just

# add more rollback segments. It is generally recommended to create

# private rather than public rollback segments.

#

########

rollback_segments = (rbs01, rbs02, rbs03, rbs04, rbs05, rbs06,largerbs)

########

#

# Sort Area

#

# The sort area is allocated from process memory, unless using MTS.

# This parameter can have a dramatic effect on performance.

# Set too low, users will suffer with excessive disk sorts; set

# too high, there is a real chance of significant paging.

#

# The recommended values for OLTP range from 256K to 2M , depending on

# available memory. The parameter can be changed dynamically for

# specific batch jobs, such as index rebuild and analyze.

#

########

sort_area_size = 256000 #According to the sort info 256k- à 512k

########

#

# Hash Area Size

#

# The hash area is allocated as per sort_area_size, and the default is

# 2 x sort_area_size. This should be fine for most customers, but can be

# changed dynamically for specific jobs. It can potentially have the

# same dramatic effect on memory as sort_area_size.

#

########

########

#

# Shared Pool

#

# The shared pool size is key to controlling contention between SQL and

# PL/SQL objects. 300M is a reasonable estimate for 11i, with 30M for

# the reserved area ( 10% ).

#

########

shared_pool_size = 400000000

shared_pool_reserved_size = 30000000

_shared_pool_reserved_min_alloc = 4100

# cursor_space_for_time

#

# Reduces contention within the shared pool but requires at least a 50%

# increase in the shared pool. Only set on the advice of Oracle Support

# or Development.

########

#

# Java Pool

#

# In 11i, certain products use Java Stored Procedures. If your

# installation uses these products, an initial setting for the Java Pool

# is 50M, but this may need to be increased as required.

#

########

java_pool_size = 50000000

########

########

#

# PL/SQL parameters

#

# The utl_file_dir must be set as per the installation manuals.

# utl_file_dir = <dir1>,<dir2> ...

#

########

utl_file_dir = /app/MISSID/MISSIDcomn/temp, /usr/tmp,/ora/MISSID/MISSIDdb/8.1.7/appsutil/outbound/MISSID_hostname

########

#

# Advanced Queuing (AQ) and Job Queues

#

# AQ requires the TM process to handle delayed messages. A number

# of Application modules use AQ, including workflow. Job Queues

# enable advanced queue to submit background jobs.

#

########

aq_tm_processes = 1

job_queue_processes = 2

job_queue_interval = 90

########

#

# Archiving

#

# Archiving parameters, including destination ( optionally multiple

# destinations in 8i ) need to be specified.

#

########

# log_archive_start = true # if you want automatic archiving

########

#

# Parallel Query

#

# Not normally required for OLTP systems. If enabled, tables/indexes

# MUST NOT have degree set. Use hints to enable parallel query.

#

# To use the parallel option with DBMS_STATS ( FND_STATS ) in 11i,

# parallel_max_servers must be set - it is not a dynamic parameter. It

# should be set based on the number of available CPUs. Examples are

# given below but may need to be altered. These are reasonable values

# for DBMS_STATS.

#

# Parallel Query uses the large_pool for message buffers and the

# large_pool_size may therefore need to be specified

# - see above for guidelines.

#

########

parallel_max_servers = 8

parallel_min_servers = 0

########

#

# Events

#

# Events are used by Oracle Support and Development. They should only be

# set as requested. Many exist for historical reasons. All those listed

# below ARE NOT be needed by 11i customers.

#

########

########

#

# Platform specific parameters

#

########

#

# spin_count

#

# This parameter is used on SMP platforms. It determines how long to

# spin trying to acquire exclusive access to low-level SGA data

# structures.

#

# With 8i, this parameter is undocumented and SHOULD NEVER be set except

# at the request of Oracle Support.

# ------------------- MANDATORY OPTIMIZER PARAMETERS ----------------------

###########################################################################

# CBO parameters

# Apps MANDATORY CBO init.ora parameters for 8.1.7

#

###########################################################################

#########

#

# Optimizer

#

# Release 11i uses the Cost Based Optimizer (CBO). The

# following optimizer parameters MUST be set as below, and should not

# be changed.

#

#########

#########

# optimizer_features_enable

#

# This is required to be set to the current release,

# as Oracle Applications relies on optimizer fixes and new features.

#########

optimizer_features_enable = 8.1.7

#########

# optimizer_mode

#

# Prior to 11i, optimizer_mode was always set to rule. For 11i,

# choose is mandatory.

#

# IMPORTANT : The CBO requires accurate table and index statistics,

# and FND_STATS should be run regularly. See the FND

# documentation for further details.

#########

optimizer_mode = choose

#########

# optimizer_undo_changes

#

# This parameter is NOT dynamic and for RBO compatibility must remain

# as TRUE. For 11i, it is recommended that it is set to FALSE although

# if custom code continues to use the RBO, and experiences performance

# problems, setting to true should not affect CBO queries.

#

# This will be removed in a future release.

#########

_optimizer_undo_changes = false

#########

# _optimizer_mode_force

#

# Must be set to true. Forces recursive SQL ( packaged SQL ) to use the

# optimizer_mode from the current environment.

#########

_optimizer_mode_force = true

#########

# db_file_multiblock_read_count

#

# Many APPS customers have multiblock read count set at 16 or 32,

# depending on block size. For 11i, the required value is now 8,

# as this provides the best value for the CBO.

#

# This parameter can be set at the session level, so specific batch jobs,

# index rebuilds, and analyze can take full advantage of the maximum

# available multiblock I/O.

#########

db_file_multiblock_read_count = 8

#########

# optimizer_max_permutations

#

# This should be set to 2000, to allow extra CPU time to process

# multiple initial join orders.

#########

optimizer_max_permutations = 2000

#########

# _complex_view_merging

#

# This parameter enables the complex view merging feature which allows

# certain types of complex views to be merged such as the Apps KFV views.

# This parameter is disabled by default, so it must be explicitly set

#########

_complex_view_merging = TRUE

#########

# _push_join_predicate

#

# This parameter enables the push join predicate feature which allows

# the optimizer to push join predicates inside non-mergable views.

# This helps eliminate full table scans against the adjoining table of a

# non-mergable view. Pushing the join predicate allows the optimizer to

# promote an index on the table inside the view and utilize a nested loop

# join to the outer referencing table.

#

# Push join predicate is disabled by default, so it must be explicitly

# enabled.

#########

_push_join_predicate = TRUE

#########

# _sort_elimination_cost_ratio

#

# Setting it to five forces the optimizer to only eliminate the

# sort when it is 1/5th the cost of the index probe (or conversely the

# index probe is 5 times as costly as the sort).

#########

_sort_elimination_cost_ratio = 5

#########

# _use_column_stats_for_function

#

# This parameter allows the optimizer to utilize dictionary statistics

# for columns that are involved in no-op expressions such as [col + 0]

# and [col || '']. If this parameter is disabled (FALSE), the optimizer

# will employ internal default statistics for such complex expressions

# which can result in higher parse times and more expensive execution

# plans.

#########

_use_column_stats_for_function = TRUE

#########

# _like_with_bind_as_equality

#

# This parameter forces the optimizer to treat expressions of the form

# [indexed-column like :b1] similar to [index-column = :b1].

# Oracle Apps has many queries which use the LIKE operator on indexed

# columns with binds. Since binds are involved, the CBO assigns

# internal default selectivity estimates for the LIKE operator (5%),

# and hence does not consider the index selective.

#########

_like_with_bind_as_equality = TRUE

#########

# _or_expand_nvl_predicate

#

# This parameter allows the optimizer to probe on an index for a column

# involved in an nvl() function as an r-value.

#

# If enabled, the optimizer transforms expressions of the form

# [p.project_id = nvl(:b1,p.project_id)]

# into an OR expanded UNION where one-side of the UNION contains the

# predicate

# ((:b1 is not null) and (p.project_id =:b1))

# and the second branch of the UNION contains the predicate

# (:b1 is null).

# Therefore, if a value for the project_id bind is supplied, the

# optimizer executes the first branch of the UNION and probes on the

# project_id index.

#

# This improves performance for the Apps legacy code which employs the

# nvl() construct on indexed columns. This parameter was introduced as

# the fix for bug 958846.

#########

_or_expand_nvl_predicate = TRUE

#########

# _push_join_union_view

#

# This new feature allows the optimizer to push join predicates inside

# non-mergable views which contain UNION ALL set operators.

# This improves query execution performance for queries joining to views

# which contain UNION ALL operators.

#########

_push_join_union_view = TRUE

#########

# _table_scan_cost_plus_one

#

# This parameter increases the cost of a full table scan by one in order

# to eliminate ties between a full table scan on a small lookup table

# and the cost of a unique or range index scan on the lookup table.

#########

_table_scan_cost_plus_one = TRUE

#########

# _fast_full_scan_enabled

#

# This parameter is used to disable fast full scans.

#########

_fast_full_scan_enabled = FALSE

#########

# _ordered_nested_loop

#

# Reduce the cost of a nested loop join/index probe when the left side

# of the join input is being satisfied via an index or sort row source.

#########

_ordered_nested_loop = TRUE

#########

# optimizer_percent_parallel

#

# The amount of parallelism to include in the CBO cost function.

# The default is zero, and should not normally be changed. It is

# necessary to ensure that parallel query is not included in costing.

#########

optimizer_percent_parallel=0

#########

# query_rewrite_enabled

#

# Required for materialised views and function based indexes, which are

# used in some 11iproducts. The recommended value is true.

#########

query_rewrite_enabled=true

#########

# always_anti_join

#

# This parameter specifies the join method to be used for

# anti-joins. Anti-joins can be used for queries which require

# anti-join semantics such as queries which contain a NOT IN

# sub-query. This parameter should not be altered from the

# default setting of NESTED_LOOPS.

#########

always_anti_join=NESTED_LOOPS

#########

# always_semi_join

#

# This parameter specifies the join method to be used for

# semi-joins. The optimizer can transform a query which

# consists of an explicit join into a semi-join provided that

# the semantics of the query permit such a transformation.

# The transformation of a regular equi-join into a semi-join

# is equivalent to replacing the join with an EXISTS sub-query.

# This parameter should not be altered from the default

# setting of NESTED_LOOPS.

#########

always_semi_join=NESTED_LOOPS

#########

# _sqlexec_progression_cost

#

# This parameter specifies the cost threshold for the

# progress meter. Non-zero values can prevent cursors

# from being shared when timed_statistics=TRUE.

#

#########

_sqlexec_progression_cost=0

#########

# _new_initial_join_orders

#

# This parameter enables join permutation optimization.

#

#########

_new_initial_join_orders=TRUE

##########

#

# Explicitly set the optimizer index caching values to their defaults.

#

##########

optimizer_index_caching=0

optimizer_index_cost_adj=100

#############################################################################

#

# END OF CBO PARAMETERS SECTION

#

#############################################################################

# ----------------- END OF MANDATORY OPTIMIZER PARAMETERS -------------------

#

# Customer Settings.

#

IFILE=/ora/MISSID/MISSIDdb/8.1.7/dbs/MISSID_hostname_ifile.ora

*************************************************************************************************************************************************************************

Internal管理器环境

*************************************************************************************************************************************************************************

无效对象编译

@invalid_function_compile.sql

set pagesize 0
set linesize 200
set trimspool on
spool invalid_procedure_compile.sql
SELECT 'alter procedure ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status='INVALID'
and object_type='PROCEDURE' ;
spool off

@invalid_procedure_compile.sql

set pagesize 0
set linesize 200
set trimspool on
spool invalid_view_compile.sql
SELECT 'alter view ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status='INVALID'
and object_type='VIEW' ;
spool off

@invalid_view_compile.sql

set pagesize 0
set linesize 200
set trimspool on
spool invalid_trigger_compile.sql
SELECT 'alter trigger ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status='INVALID'
and object_type='TRIGGER' ;
spool off

@invalid_trigger_compile.sql

set pagesize 0
set linesize 200
set trimspool on
spool invalid_package_compile.sql
SELECT 'alter package ' || owner || '.' || object_name || ' compile;'
from dba_objects
where status='INVALID'
and object_type='PACKAGE' ;
spool off

@invalid_package_compile.sql

set pagesize 0
set linesize 200
set trimspool on
spool invalid_package_body_compile.sql
SELECT 'alter package ' || owner || '.' || object_name || ' compile body;'
from dba_objects
where status='INVALID'
and object_type='PACKAGE BODY' ;
spool off

@invalid_package_body_compile.Sql

*************************************************************************************************************************************************************************

其它配置调整

如：磁盘I/O，操作系统，RAC，硬件平台级别参数的调整。

*************************************************************************************************************************************************************************

For a detailed quote, contact Oracle Support Experts via e-mail, or Call 0086-755-81078317