我這裡小機是IBM P55A，4顆雙核CPU，12G內存，外存是IBM的陣列，做RAID5，本機系統盤AIX 5.3L 用2顆硬盤做了軟鏡像，page 8G。
此小機原來8G內存，跑起來CPU 50％，comp mem 70％以上，paging 30％
半個月前加了4G內存，現在跑起來CPU佔用90％以上，comp mem 60％以上，pageing 1％。
下面是數據庫的參數文件，附件是statpack的log。
未加內存前在用戶多的高峰期，因為讀寫paging space，系統盤I／O 100％，進而造成用戶的wating，現在paging是小了，但是在高峰期還是會造成陣列磁盤I／O 100％，引起wating。
請大家幫忙看看數據庫的設置有什麼問題嗎？


###########################################################################
# $Header: afinit_db920.ora 115.17 2005/03/31 07:57:21 adan ship $
#
#       Oracle Applications 11i - init.ora
#
#       This file contains a listing of init.ora parameters for 9.2.0.
#
#       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                         = PROD
control_files                   = /u2/PROD/prodora/proddata/cntrl01.dbf,/u2/PROD/prodora/proddata/cntrl02.dbf,/u2/PROD/prodora/proddata/cntrl03.dbf

#########
#
#       Database block size
#
#       The required block size for Oracle Applications is 8K.
#      
#########

db_block_size                   = 8192

#########
#
#       Compatible
#      
#       Compatibility should be set to the current release.
#
#########

compatible                      = 9.2.0

#########
#
#       _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


#########
#
#       O7_DICTIONARY_ACCESSIBILITY
#
#       O7_DICTIONARY_ACCESSIBILITY should be left to its defaule value
#       of FALSE.
#
#########

O7_DICTIONARY_ACCESSIBILITY     = FALSE


#########
#
#       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
nls_length_semantics            = BYTE    # 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                  = /u2/PROD/prodora/proddb/9.2.0/admin/PROD_erpdb/udump
background_dump_dest            = /u2/PROD/prodora/proddb/9.2.0/admin/PROD_erpdb/bdump
core_dump_dest                  = /u2/PROD/prodora/proddb/9.2.0/admin/PROD_erpdb/cdump

max_dump_file_size              = 20480   # 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                       = 800    # Max. no. of users x 2
sessions                        = 1600                 # 2 X processes  
db_files                        = 512         # Max. no. of database files
dml_locks                       = 10000           
cursor_sharing                  = EXACT # Required 11i settting
open_cursors                    = 600   # Consumes process memory, unless using MTS.
session_cached_cursors                = 200
enqueue_resources               = 32000  # Max. no of concurrent database locks.

########
#
#       Buffer Cache
#
#       For 9i releases, the parameter db_cache_size should be used to
#       size the buffer cache in place of db_block_buffers.
#       The minimum size for the buffer cache (for Apps) is 156MB.
#       The buffer cache should be tuned as per the recommendations
#       in the sizing table (in the section Database Initialization
#       parameter sizing).
#
#       The use of multiple buffer pools for Oracle Applications is not
#       supported.  Only a single buffer pool should be used (i.e. default).
#
########

db_cache_size                   = 629145600
db_block_checking               = FALSE
db_block_checksum               = TRUE

########
#
#       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          = 1200 # Checkpoint at least every 20 mins.
log_checkpoint_interval         = 100000
log_buffer                      = 10485760
log_checkpoints_to_alert        = TRUE

########
#
#       Rollback segments
#
#       As of 9iR2, Oracle Applications requires the use of System Managed Undo.
#       System Managed Undo is much more efficient, and reduces the chances
#       of snapshot too old errors.  In addition, it is much easier to
#       manage and administer system managed undo than manually managing
#       rollback segments.
#
#        Note:
#
#         Oracle Applications will function correctly using rollback segments
#         but Oracle recommends that customers move to System Managed Undo.
#
#       To use System Managed Undo, you must create an UNDO tablespace.
#
########

#rollback_segments               = NOROLLBACK

#        undo_management
#
#        Set to AUTO to enable System Managed Undo

undo_management=AUTO

#
#        undo_retention
#
#        An estimate of the time (in seconds) to keep committed data in
#        the undo tablespace. Actual time depends on the space available for
#        active transactions.

undo_retention=3600

#
#        undo_suppress_errors
#
#        When set to TRUE, will suppress errors caused by executing old-style
#       "ALTER ROLLBACK" commands. This is useful where there is custom code
#       or DBA scripts that use a specific rollback segment - the error will
#       be ignored and the transaction will use System Managed Undo.
#

undo_suppress_errors=FALSE

#
#        undo_tablespace
#
#        The name of the System Managed Undo tablespace.
#

undo_tablespace=APPS_UNDOTS1

########
#
#       Sort Area / Hash Area Size
#
#       As of 9iR2, the auto. memory manager is being used.  This avoids the
#       need to manually tune sort_area_size and hash_area_size.
#       Auto. Memory Manager also improves performance and scalability
#       as the memory is released to the OS after the call.
#
#        Although manual tuning via sort_area_size/hash_area_size is still
#        supported, it is strongly recommended that customers convert to
#        automatic workarea management.
#       
########

#
#        workarea_size_policy
#
#        Set to AUTO to enable automatic workarea management.
#

workarea_size_policy=AUTO

#
#        pga_aggregate_target
#
#        pga_aggregate_target is the total memory available for work area
#        operations ( predominantly sorts ). The available memory will grow
#        and shrink as the workload changes.
#
#        Set to low, it will have a dramatic effect on performance, as
#        sort operations will be forced to disk.  The recommended minimum
#        is 1G, although for small test or demo systems, 500M is acceptable.
#
#        The upper bound is determined by the available memory allocated to
#        the instance. Once you've subtracted O/S overhead, and memory used
#        by other systems, 95% of instance memory is accounted for by:
#
#              SGA Pools - shared_pool,java_pool and large_pool
#               db_block_buffers
#              pga_aggregate_target
#
#        Never set pga_aggregate_target such that total allocated memory will be
#        greater than physical memory, or the server will swap and cause
#        severe performance problems.
#
#        Use statspack to determine the optimum target. However, a simple rule
#        of thumb would be 2M per database session.

pga_aggregate_target    =          4G

olap_page_pool_size     =          4194304


########
#
#       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                = 600000000
shared_pool_reserved_size       = 60000000
_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                 = 52428800

########

########
#
#       PL/SQL parameters
#
#       The utl_file_dir must be set as per the installation manuals.
#       utl_file_dir = , ...
#
########


utl_file_dir = /usr/tmp,/usr/tmp,/u2/PROD/prodora/proddb/9.2.0/appsutil/outbound/PROD_erpdb,/usr/tmp,/u2/PROD/prodora/proddb/9.2.0/admin/PROD_erpdb/bdump

########
#
#       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.
#
#       job_queue_interval is obsolete and should no longer be required.
#       It is now an undocumented parameter ( _job_queue_interval ).
########

aq_tm_processes                 = 1
#Job_queue_processes             = 2

Job_queue_processes             = 10

########
#
#       Archiving
#
#       Archiving parameters, including destination ( optionally multiple
#       destinations in 9i ) need to be specified.
#
########

log_archive_start             = true      # if you want automatic archiving
log_archive_dest_1='LOCATION=/u2/PROD/prodora/arclog'
log_archive_format='arch_%s.arc'
log_archive_max_processes=2

########
#
#       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.
#
#       The following events are required for backward compatibility.
#
#       They must be set for Oracle Applications release 11.5.7.
#       For all other certified releases ( 11.5.8 and above ), they
#        are not required and should be either commented out or removed
#        from the init.ora file.
#
########

#event="10932 trace name context level 32768"
#event="10943 trace name context level 16384"
#event="10933 trace name context level 512"

########
#
#       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
###########################################################################

#########
#
#       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       = 9.2.0

#########
#       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   # Required for 11i setting

#########
#       _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

#########
#       _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

#########
#       _fast_full_scan_enabled
#
#       This parameter is used to disable fast full scans.
#########

_fast_full_scan_enabled         = FALSE

#########
#       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

#########
#      _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=2147483647

#########
#     _index_join_enabled
#     
#     This parameter enables index joins
#     It should be set to FALSE for Oracle Applications
#
#
#########

_index_join_enabled = FALSE

#########
#      _b_tree_bitmap_plans
#
#      This parameter enables bitmap plans for b-tree indexes
#      It should be set to FALSE for Oracle Applications
#
#
#########

_b_tree_bitmap_plans = FALSE

#############################################################################
#
#  END OF CBO PARAMETERS SECTION
#
#############################################################################


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

#
#        Customer Settings.
#

IFILE=/u2/PROD/prodora/proddb/9.2.0/dbs/PROD_erpdb_ifile.ora

sp1556_1702.txt
(2007-04-09 14:10:11, Size: 128 KB, Downloads: 31)